Featured image
INDEX in Excel
INDEX returns the value at a position inside a range: =INDEX(array, row_num, [column_num]). On its own that is rarely useful, because you have to know the position already. Its value comes from pairing it with MATCH, which works the position out for you, and the pair together does everything VLOOKUP does without VLOOKUP's restrictions.
=INDEX(array, row_num, [column_num])
INDEX is the simplest function on this site to describe and one of the hardest to see the point of. Position in, value out. Row 3 of this range is Chen. Nobody arrives at a spreadsheet knowing they want row 3.
The reason to learn it is what happens when the row number is itself a formula. MATCH turns a value you know into the position it sits at, INDEX turns that position back into a value from any column you like, and the combination has none of VLOOKUP's limitations: it looks in either direction, it survives inserted columns, and it works in every version of Excel ever released.
The arguments
Square brackets in the line above mark an optional argument. Optional in the syntax and optional in practice are not always the same thing, and where they differ this page says so.
- The range to read from. A single column, a single row, or a rectangle.
- Which row of the array, counted from 1 at the top of the range rather than from row 1 of the sheet. A range of A2:A7 numbers its rows 1 to 6, so A4 is row 3.
- optional
- Which column of the array, counted from 1 at its left edge. Needed only when the array is more than one column wide; on a single-column range it is left off.
Worked examples
Every example below is written against the same six-row table, and every result was produced by running the formula in this site’s spreadsheet engine rather than typed in by hand. The grid underneath holds that table, so you can try each one.
Read one cell out of a block by its position
=INDEX(A2:D7, 3, 1)Returns Chen
Row 3, column 1 of A2:D7. Both numbers count from the top-left corner of the range, not of the sheet, which is why the answer is the third name rather than whatever sits in row 3 of column A.
On a single column, leave the column number off
=INDEX(D2:D7, 5)Returns 5250
A one-column range needs one number. This is the form INDEX almost always takes in practice, because the version that earns its keep is the one with MATCH supplying that number.
INDEX and MATCH together, looking leftwards
=INDEX(A2:A7, MATCH(7600, D2:D7, 0))Returns Chen
MATCH finds 7600 in the revenue column and reports that it is the third entry. INDEX reads the third entry of the name column. VLOOKUP cannot express this at all, because the thing being searched sits to the right of the thing being returned.
Try it here
Try it here
A real spreadsheet, not a picture of one. Press an example to write it into A9, then edit it and watch the answer change.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Units | Revenue | Joined | Grade |
| 2 | Ada | North | 12 | 4800 | 15 Jan 2024 | A |
| 3 | Blake | South | 7 | 2100 | 2 Mar 2024 | C |
| 4 | Chen | North | 19 | 7600 | 20 Nov 2023 | A |
| 5 | Dara | East | 4 | 1200 | 8 Jun 2024 | B |
| 6 | Eli | South | 15 | 5250 | 30 Sep 2023 | B |
| 7 | Fay | East | 900 | 11 Feb 2024 | C | |
| 8 | ||||||
| 9 | ||||||
| 10 |
Type straight into a cell, press F2 to edit one, and drag the fill handle to copy a formula down. Nothing here is saved.
Why INDEX and MATCH rather than VLOOKUP
Read the pair from the inside out and it stops looking cryptic. MATCH answers where is it, INDEX answers what is at that position.
- 1MATCH(7600, D2:D7, 0) scans the revenue column for 7600 and returns 3, because it is the third cell in that range.
- 2INDEX(A2:A7, 3) reads the third cell of the name column.
- 3Put one inside the other and the 3 never has to be written down, so the formula keeps working when the data changes.
- It looks in both directions, because the searched range and the returned range are unrelated to each other.
- It survives an inserted column. VLOOKUP's column number is a count that knows nothing about the columns it is counting; INDEX points at a range, and a range moves when the sheet does.
- It works in every version of Excel, which XLOOKUP does not.
Errors and what causes them
Each formula below really returns the error beside it in the grid on this page. Paste one into the sandbox above if you want to see it happen, which is a faster way to learn an error value than reading about it.
=INDEX(A2:D7, 9, 1)Whyrow_num is larger than the array has rows. A2:D7 is six rows tall and the formula asked for the ninth.
FixCount rows within the range, not on the sheet. The same error appears when column_num exceeds the array's width, and when a range was shortened after the formula was written.
=INDEX(A2:A7, 0)Whyrow_num is zero. Counting from zero is a programming habit that Excel does not share.
FixThe first row of the array is 1. If the number is arriving from another formula, check that formula is not returning 0 for not found.
=INDEX(A2:A7, MATCH("Gus", A2:A7, 0))WhyThe error is not INDEX's. MATCH found nothing, returned #N/A, and INDEX passed it along.
FixDebug the MATCH on its own in a spare cell before blaming the INDEX around it. An #N/A from this shape of formula is nearly always a lookup value that does not exist or differs by a space.
What goes wrong
These are the mistakes that come up again and again with INDEX. Each one is reasonable. That is why people make it.
Counting row_num from row 1 of the sheet.
WhyThe row numbers are printed down the left-hand edge, and the range's own numbering is not printed anywhere.
InsteadCount from the top of the array. In INDEX(A2:A7, 3) the 3 means A4, because A2 is 1.
Giving a column number to a single-column range.
WhyThe syntax shows three arguments, so three get typed.
InsteadINDEX(D2:D7, 5) is the whole formula. The third argument only exists for ranges more than one column wide.
Leaving MATCH's third argument off.
WhyIt is optional, and on sorted test data it appears to work.
InsteadMATCH without a third argument assumes the list is sorted ascending and will return a wrong position on anything else. Write the 0.
Common questions
What is the difference between INDEX and MATCH?
MATCH takes a value and returns its position in a range. INDEX takes a position and returns the value at it. They are inverses, which is why nesting one inside the other turns a value you know into a value you want.
Is INDEX MATCH better than VLOOKUP?
It is more robust. It can look leftwards, it does not break when a column is inserted into the table, and it works in every version of Excel. It is also longer to write and harder to read at a glance, which is why XLOOKUP exists.
Why does my INDEX MATCH return #N/A?
The MATCH inside it found nothing. Test the MATCH alone in a spare cell: if that is #N/A, the problem is the lookup value, usually a trailing space or a number stored as text, and not the INDEX wrapped around it.
Can INDEX return a whole row or column?
In Excel, passing 0 as the row number returns the entire column of the array as an array. The grid here does not implement spill ranges, so the examples return single values.