SheetsdrillINDEX and MATCH
MATCH(search_key, range, 0) returns the position of a value in a column or row, and INDEX(range, row, [column]) returns the cell at that position, so INDEX(C:C, MATCH(A2, A:A, 0)) looks up A2 in column A and returns column C. Unlike VLOOKUP it works in any direction and does not break when columns are inserted.
Syntax
=INDEX(return_range, MATCH(search_key, lookup_range, 0))| Part | What it is |
|---|---|
return_range | The column (or table) to return a value from. |
search_key | The value to find. |
lookup_range | The single column or row to search; must be the same height as return_range. |
search_type | The third MATCH argument: 0 exact match, 1 largest value not above the key (sorted ascending, default), -1 smallest value not below (sorted descending). |
Examples
=INDEX(Products!A:A, MATCH(A2, Products!B:B, 0))Result: The SKU for the product name in A2, looking left
=INDEX(B2:F13, MATCH("Mar", A2:A13, 0), MATCH("West", B1:F1, 0))Result: The value where the Mar row meets the West column (two-way lookup)
=MATCH("Ben", A2:A10, 0)Result: 3 when Ben is the third name in the range
The position, not the row number of the sheet.
Common mistakes
- Wrong results because the 0 was left off MATCH; the default search type assumes sorted data and does approximate matching.
- #N/A even though the value exists: check for trailing spaces and number-versus-text mismatches with TRIM and VALUE.
- Using a lookup column that starts on a different row than the return column shifts every result by that offset; start both ranges on the same row.
Practice INDEX and MATCH
These drills check your answer on live data, free, no account needed.
- INDEX and MATCHReturn the salary of the employee ID in F1 using INDEX and MATCH. The IDs are in column C and the salaries in column A. Answer in F2.
- Row and column lookupReturn the units for the product in H1 and the quarter in H2 from the table. Use INDEX with two MATCH functions. Answer in H3.
- Latest status in a logThe log adds a new line every time an order changes. Return the most recent status for the order in F1, which is its last line in the log. Use XLOOKUP. Answer in F2.
Questions
INDEX MATCH or VLOOKUP, which is better?
INDEX/MATCH looks in any direction and survives inserted columns; VLOOKUP is shorter to type. XLOOKUP does both and is the simplest in current Google Sheets.
How do I INDEX MATCH with multiple criteria?
Multiply the conditions: =INDEX(C:C, MATCH(1, (A:A=E1)*(B:B=F1), 0)) inside ARRAYFORMULA, or use FILTER.
Related
- VLOOKUPHow VLOOKUP works in Google Sheets: the four arguments, exact and approximate match, lookups from another sheet, and fixes for #N/A and wrong results.
- XLOOKUPHow XLOOKUP works in Google Sheets: look up in any direction, set a not-found value, do approximate matches and search from the bottom, with common errors.
- FILTERHow the FILTER function in Google Sheets returns only rows that match one or more conditions, with examples for AND, OR, dates and text, plus common errors.
- HLOOKUPHow HLOOKUP works in Google Sheets to search the first row of a range and return a value from a row below, with examples for month tables and common errors.