Skip to content

Excel function · Lookup & reference

Excel INDEX function

INDEX returns the cell at the row and column number you give inside a range. Paired with MATCH, it is the most flexible way to pull a value from another column by a key, and it works in every version.

In Turkish Excel: İNDİS

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

English Excel=INDEX(array, row_num, [column_num])
Turkish Excel=İ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

ArgumentRequiredDescription
arrayRequiredThe range to return a value from, e.g. B2:E5. Lock it, like $B$2:$E$5, if you will copy the formula.
row_numRequiredThe 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_numOptionalThe 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.

E2fx=INDEX(B2:B6,D2)
Return a value from a list by its position: example table
ABCDE
1WeekOn CallWeek NoOn Call
21Emma3Olivia
32Liam
43Olivia
54Noah
65Ava

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.

F2fx=INDEX(A2:A6,MATCH(E2,B2:B6,0))
Return a product name to the left of its code with MATCH: example table
ABCDEFG
1ProductCodeStockCodeProductStock
2A4 Squared NotebookKR-110140KR-140Sticky Notes210
3Ballpoint PenKR-120620
4Ring BinderKR-13085
5Sticky NotesKR-140210
6StaplesKR-15095
  • 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.

H2fx=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))
Read a fee by region and weight band: example table
ABCDEFGH
1Region0-5 kg5-10 kg10-20 kgRegionBandFee
2London85.00120.00165.00Midlands10-20 kg190.00
3South East95.00135.00180.00
4Midlands100.00140.00190.00
5Scotland120.00165.00225.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:A6 against B1: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

Type this in Nexell

Add the shipping fee to each parcel from the rate table by region and weight band

Convert to table

Bring 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

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free