INDEX + MATCH Function in Excel, Google Sheets, Airtable & Notion
INDEX + MATCH combines two functions into a lookup: MATCH finds the row where the key is, and INDEX returns the value in that row of another column. Unlike VLOOKUP it can return columns to the left and does not break when columns are inserted.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) |
| Google Sheets | ✔ Built in | INDEX(return_range, MATCH(search_key, lookup_range, 0)) |
| Airtable | ✘ Not available | — Airtable has no lookup functions. Link the two tables with a linked record field, then add a lookup field to pull the matching value (for example the Price of the linked Product). Formulas can only see the current record, so the link does the matching. |
| Notion | ≈ Workaround | prop("Products").filter(current.prop("Code") == "D-400").first().prop("Price")Notion looks values up through a relation. Filter on any property and return any other, so “left” and “right” do not matter. Matching is case-sensitive. |
| Excel | =INDEX(B2:B6, MATCH("D-400", A2:A6, 0)) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =INDEX(B2:B6, MATCH("D-400", A2:A6, 0)) | open sheets.new ↗ |
| Airtable | ✘ No equivalent. Airtable has no lookup functions. Link the two tables with a linked record field, then add a lookup field to pull the matching value (for example the Price of the linked Product). Formulas can only see the current record, so the link does the matching. | |
| Notion workaround | prop("Products").filter(current.prop("Code") == "D-400").first().prop("Price") | |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Code Price A-100 4.5 B-200 12 C-300 7.25 D-400 30 E-500 18.9
The Notion formula assumes a relation property Products to a database with Code and Price properties.
Arguments
| Argument | What it means |
|---|---|
return_range | The column holding the answer. It can be anywhere, including left of the lookup column. |
lookup_value | The value to find. |
lookup_range | The column to search, with the same number of rows as return_range. |
0 | MATCH's match_type. Use 0 for an exact match. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=INDEX(B2:B6, MATCH("D-400", A2:A6, 0)) | 30 | Tester data: MATCH finds row 4, INDEX returns row 4 of the price column. | |
=INDEX(A2:A6, MATCH(18.9, B2:B6, 0)) | E-500 | Looks left: search prices, return the code. | |
=INDEX(A2:A6, MATCH(MAX(B2:B6), B2:B6, 0)) | D-400 | Which product has the highest price? | |
=INDEX(B2:E6, MATCH("Ben", A2:A6, 0), MATCH("Q3", B1:E1, 0)) | Ben’s Q3 value | Two-way lookup: one MATCH for the row, one for the column. |
Examples use Excel / Google Sheets syntax unless noted.
How INDEX + MATCH works
Before XLOOKUP, INDEX + MATCH was the standard answer to VLOOKUP's limits, and it is still the best choice when a workbook must open in Excel 2019 or older. Read it inside out: MATCH(lookup_value, lookup_range, 0) returns a row number, and INDEX(return_range, that_row) returns what is in that row of the answer column.
Because the lookup column and the return column are separate references, the return column can be on either side, and inserting or deleting columns between them does not shift a hard-coded column number. Both ranges must start on the same row and have the same height, or the result comes from the wrong row. Always pass 0 to MATCH: without it MATCH does an approximate match on what it assumes is sorted data.
When the key is missing MATCH returns #N/A and INDEX passes it on; wrap the whole thing in IFERROR or IFNA. Adding a second MATCH for the column turns it into a two-way (row and column) lookup. Airtable has no formula lookups (use linked records with lookup fields), and in Notion you filter a relation on one property and read another.
Frequently Asked Questions
- Is INDEX + MATCH faster than VLOOKUP?
- On large sheets it can be, because it only reads two columns instead of a whole table, but the main reasons to use it are looking left and robustness to inserted columns.
- Should I use INDEX + MATCH or XLOOKUP?
- If everyone has Excel 2021 / 365 or Google Sheets, XLOOKUP is shorter. INDEX + MATCH works in every version.