What it does
On its own INDEX does something simple: =INDEX(B2:B6,3) returns the third cell of the range. If the range has several columns, the second number picks the column: =INDEX(B2:E5,2,4) is row 2, column 4. The numbers are positions inside the range, not sheet rows.
Its real strength shows with MATCH. MATCH finds the position of the key, and INDEX returns the value at that position from another column. Unlike VLOOKUP, the pair can return a column to the left of the lookup column and does not break when columns are inserted. With one MATCH for the row and one for the column, it reads two-way tables (region × band, product × month).
In Microsoft 365 and Excel 2021, XLOOKUP writes single-column lookups more briefly; INDEX + MATCH works in every version, so it is still the safest choice for shared files.
Syntax
=INDEX(array, row_num, [column_num])=İNDİS(dizi; satır_no; [sütun_no])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
array | Required | The range to return a value from, e.g. B2:E5. Lock it, like $B$2:$E$5, if you will copy the formula. |
row_num | Required | The row position inside the range; the first row is 1. Use 0 to return the whole column. In a single-row range this number picks the column. |
column_num | Optional | The column position inside the range. Leave it out for a single-column range. |
Examples
1Return a value from a list by its position
An on-call rota lists the weeks in order. The person on call for the week number typed in D2 should appear in E2. INDEX returns the cell at that position in B2:B6.
=INDEX(B2:B6,D2)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Week | On Call | Week No | On Call | |
| 2 | 1 | Emma | 3 | Olivia | |
| 3 | 2 | Liam | |||
| 4 | 3 | Olivia | |||
| 5 | 4 | Noah | |||
| 6 | 5 | Ava |
D2 says 3, so the third cell of the range comes back: Olivia. The position counts inside B2:B6, not on the sheet, so the result comes from sheet row 4.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Return a product name to the left of its code with MATCH
The product name (A) is to the left of the code (B). MATCH finds the position of the code in E2 within column B; INDEX returns the name from A and the stock from C at that position.
=INDEX(A2:A6,MATCH(E2,B2:B6,0))| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Code | Stock | Code | Product | Stock | |
| 2 | A4 Squared Notebook | KR-110 | 140 | KR-140 | Sticky Notes | 210 | |
| 3 | Ballpoint Pen | KR-120 | 620 | ||||
| 4 | Ring Binder | KR-130 | 85 | ||||
| 5 | Sticky Notes | KR-140 | 210 | ||||
| 6 | Staples | KR-150 | 95 |
- F2
=INDEX(A2:A6,MATCH(E2,B2:B6,0)) - G2
=INDEX(C2:C6,MATCH(E2,B2:B6,0))
For KR-140 the name is Sticky Notes and the stock is 210. Both formulas use the same MATCH; only the column INDEX returns from changes.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Read a fee by region and weight band
In the rate table, regions run down the rows and weight bands across the columns. The first MATCH finds the region's row, the second finds the band's column in the header row, and INDEX returns the fee where they cross.
=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Region | 0-5 kg | 5-10 kg | 10-20 kg | Region | Band | Fee | |
| 2 | London | 85.00 | 120.00 | 165.00 | Midlands | 10-20 kg | 190.00 | |
| 3 | South East | 95.00 | 135.00 | 180.00 | ||||
| 4 | Midlands | 100.00 | 140.00 | 190.00 | ||||
| 5 | Scotland | 120.00 | 165.00 | 225.00 |
The fee where the Midlands row meets the 10-20 kg column comes back: 190.00. Change F2 or G2 and the fee updates by itself.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #REF!
Cause: row_num or column_num points outside the range, e.g. row 6 of a five-row range.
Fix: Widen the range or correct the position; if the position comes from MATCH, check that both ranges are the same size.
- #N/A
Cause: The inner MATCH did not find the value, and the error passes through INDEX.
Fix: Check the lookup value and its spelling; wrap the formula in IFERROR to show a message.
- #VALUE!
Cause: row_num or column_num is not a number, for example a reference to a text cell.
Fix: Make sure the position is a number.
- Wrong result
Cause: The INDEX range and the MATCH range start on different rows (
A2:A6againstB1:B5), so the result is off by one row.Fix: Start and end both ranges on the same rows.
Good to know
- The INDEX + MATCH pattern:
=INDEX(return_column,MATCH(value,lookup_column,0)). The 0 at the end means exact match; leave it out and the result can come from the wrong row. - Use 0 as row_num to get the whole column:
=SUM(INDEX(B2:E5,0,3))adds up the third column of the range.
In Nexell
Get Nexell to do it from a sentence
Add the shipping fee to each parcel from the rate table by region and weight band
Convert to tableBring the parcel list and the rate table into the tool and type this. Nexell builds the two-way lookup with real Excel formulas and shows each fee in the preview; when you change a rate in the downloaded file, the fees follow.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
What is the difference between INDEX MATCH and VLOOKUP?
VLOOKUP only searches the first column and returns a column by its number, which shifts when a column is inserted. INDEX + MATCH takes the lookup and return columns separately: the result can come from the left, and inserting columns does not break the formula.
Can INDEX look up with two conditions?
Yes. Multiply the conditions to build one lookup array: =INDEX(C2:C9,MATCH(1,(A2:A9=F2)*(B2:B9=G2),0)). It works directly in Microsoft 365 and Excel 2021; in older versions, enter it with Ctrl+Shift+Enter.
Related functions
- MATCHKAÇINCI · Position of a value in a list (0 = exact match).
- XLOOKUPÇAPRAZARA · Searches an array and returns the matching item; the fourth argument if not found.
- VLOOKUPDÜŞEYARA · Looks up a value in the first column and returns a value from the same row. Use 0 as the last argument for exact match.
- XMATCHÇAPRAZEŞLEŞTİR · Position of a value; exact match by default.
- OFFSETKAYDIR · A reference offset from a starting reference.
- CHOOSEELEMAN · Chooses a value from a list by position.