INDEX MATCH Explained: Two-Way Lookups in Excel and Sheets

INDEX alone: row 2, column 3 of a range

Excel INDEX has 2 forms. The array form is INDEX(array, row_num, [column_num]) and the reference form is INDEX(reference, row_num, [column_num], [area_num]). Microsoft lists both for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016.

Numbers count from the top-left cell of the range you give, not from the sheet. With data in A2:B3 (Apples and Lemons in row 2, Bananas and Pears in row 3), =INDEX(A2:B3,2,2) returns Pears and =INDEX(A2:B3,2,1) returns Bananas. Ask for a row or column outside the range and INDEX returns #REF!.

• Row or column set to 0 returns the whole column or row as an array.
• In Microsoft 365 an array result spills after you press Enter in the top-left cell. Older Excel needs Ctrl+Shift+Enter over a selected output range.
• Google Sheets uses INDEX(reference, [row], [column]), and row and column default to 0.

MATCH turns a name into a position

MATCH returns where an item sits in a list, not the item. Google's page gives the syntax MATCH(search_key, range, [search_type]). The range must be one row or one column: a block taller and wider than 1 cell returns #N/A.

The third argument has 3 values. 1 is the default and assumes ascending order, returning the largest value less than or equal to the key. 0 is an exact match and is required when the list is unsorted. -1 assumes descending order and returns the smallest value greater than or equal to the key. Most people want 0.

The rules above come from Google Sheets, because Microsoft's MATCH page could not be opened on 2026-11-01. Check Excel's formula tooltip for MATCH details specific to Excel.

A worked example: the price of Lemons in a 5-row table

Microsoft's fruit table has Fruit in column A, Price in column B and Count in column C, for 9 products from row 2 to row 10. Apples cost 0.69 with a count of 40, Bananas 0.34 with 38, Lemons 0.55 with 15, Oranges 0.25 with 25, Pears 0.59 with 40.

Step 1: =MATCH("Lemons", A2:A6, 0) returns 3, because Lemons is the third item in A2:A6. Step 2: =INDEX(B2:B6, 3) returns 0.55. Step 3: join them: =INDEX(B2:B6, MATCH("Lemons", A2:A6, 0)) returns 0.55 in one cell.

To read the count instead, change only the first range: =INDEX(C2:C6, MATCH("Lemons", A2:A6, 0)) returns 15. The MATCH part stays the same, so one MATCH can feed several INDEX formulas.

Why people use it instead of VLOOKUP

VLOOKUP needs the lookup value in the first column of table_array and counts the result column from 1 at the left edge. Its range_lookup argument defaults to TRUE, which means an approximate match. If col_index_num is larger than the number of columns, you get #REF!.

With INDEX and MATCH you name the result column directly, so inserting a column does not shift a number you typed by hand. The lookup column can also be to the right of the result column. Google's INDEX page says this in plain words: unlike VLOOKUP, the lookup value can sit anywhere relative to the value you want back.

Microsoft's VLOOKUP page now recommends XLOOKUP, which works in any direction and defaults to exact match. XLOOKUP is not in Excel 2016 or 2019, so INDEX and MATCH remain the choice when a file must open in those versions.

A two-way lookup: row and column both found by MATCH

Use MATCH twice. Google's example is INDEX(A1:D6, MATCH(2, D1:D6, 0), MATCH("Guest Name", A1:D1, 0)), which returns Mary, the guest at table 2. The first MATCH searches down column D for the row, the second searches along row 1 for the column.

Put the row number first and the column number second. Swapping them is a common reason for a wrong answer that is not an error.

The 4 errors and what each one means

Microsoft's #N/A page lists the usual causes: the value is missing from the source, a number is compared with text, a cell has a leading or trailing space, or an approximate match was used. For MATCH, its fix is to set match_type to 0.

• #N/A from MATCH: the value is not in the list, or the list has extra spaces. Try TRIM on the data.
• #N/A from a two-dimensional range: MATCH needs a single row or column.
• #REF! from INDEX: the row or column number is larger than the range, for example 7 on a 5-row range.
• #VALUE! from INDEX: in the reference form, the areas are on different sheets.

Hiding the error with IFERROR

Wrap the whole formula to show your own text: =IFERROR(INDEX(B2:B6, MATCH("Kiwi", A2:A6, 0)), "Not in list"). Kiwi is not in the table, so the cell shows Not in list instead of #N/A. IFERROR catches 7 error types, so a typo in a range name would also show Not in list. Use IFNA when only #N/A should be replaced.

Who this suits, and who can skip it

It suits files shared with people on Excel 2016 or 2019, lookups where the key sits to the right of the answer, and tables where columns get inserted. It is also the portable choice: the same INDEX and MATCH pair works in Google Sheets with no change.

Skip it if everyone has Microsoft 365, Excel 2021 or Excel 2024 and the lookup is simple. XLOOKUP is shorter there. Excel 365, 2021 and 2024 also offer XMATCH, whose default is an exact match.

If a formula is already showing an error on your screen and you cannot tell which of the 4 causes it is, Crade can read the screen and point to the cell.