INDEX MATCH in Excel: Syntax, Examples and Errors
Updated 2026-10-12 · Free · Works in Excel & Google Sheets
Syntax
=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))Quick answer
INDEX and MATCH together find a value in one column and return items from any other column — including columns to the left: =INDEX($E$2:$E$500, MATCH(A2, $D$2:$D$500, 0)). The pair works in every Excel version and in Google Sheets. #N/A means the value is missing, stored as text instead of a number, or carrying invisible spaces.
Syntax
=INDEX(return_array, MATCH(lookup_value, lookup_array, 0))| Argument | Belongs to | What it means |
|---|---|---|
| return_array | INDEX | The column to return from. It can sit to the LEFT of the lookup column — the main advantage over VLOOKUP. |
| lookup_value | MATCH | The value to search for: a number, text, date or a cell reference like A2. |
| lookup_array | MATCH | The column to search in. One column only — MATCH returns a position, not a value. |
| 0 | MATCH | 0 = exact match. Write it every time: omitting it switches MATCH to approximate mode, which silently returns wrong results on unsorted data. |
Depending on your regional settings, arguments are separated by semicolons instead of commas: =INDEX($E$2:$E$500; MATCH(A2; $D$2:$D$500; 0)). This is standard in countries where the comma is the decimal separator.
Basic examples
=INDEX($E$2:$E$500, MATCH(A2, $D$2:$D$500, 0))The everyday pattern: MATCH finds the position of A2 inside column D, and INDEX returns the item at that same position in column E. Read it as "position of A2 in D, then row of E".
=INDEX($D$2:$D$500, MATCH(A2, $E$2:$E$500, 0))A left-lookup: the return column sits to the left of the lookup column. This is the case VLOOKUP cannot do at all, and the reason INDEX/MATCH became the power-user standard.
=INDEX($B$2:$E$50, MATCH(A2, $A$2:$A$50, 0), MATCH(C1, $B$1:$E$1, 0))A two-way lookup: the first MATCH finds the row, the second MATCH finds the column, and INDEX returns the cell at their intersection — a dynamic cross-reference table without any helper cells.
The INDEX range and the MATCH range must span exactly the same number of rows. If MATCH scans D2:D500 but INDEX only covers E2:E300, a match in rows 301-500 produces #REF!.
Common errors and how to fix them
| Error | Most common cause | Fix |
|---|---|---|
| #N/A | The value is not in the lookup column — or it only looks identical (text vs. number, trailing or non-breaking spaces). | Run =MATCH(A2, D2:D500, 0) alone to see which half fails; convert text-numbers with VALUE() and clean invisible spaces, or wrap the whole formula in IFERROR. |
| #REF! | The INDEX range is shorter than the MATCH range, so the found position points outside it. | Make both ranges exactly the same height, e.g. D2:D500 with E2:E500. |
| #NAME? | INDEX or MATCH is misspelled — a missing N or a stray space is enough. | Type =INDEX( and =MATCH( and let autocomplete complete the names. |
| #VALUE! | The MATCH arguments are swapped: range first, value second. | Order is MATCH(value, range, 0) — the value to find comes first, the column to search second. |
Trailing spaces and non-breaking spaces copied from the web are the sneakiest #N/A cause here too: to MATCH, "NYC " and "NYC" are different strings. Clean the whole column in one click before searching.
INDEX MATCH vs. VLOOKUP
| INDEX MATCH | VLOOKUP | |
|---|---|---|
| Available in | Every Excel version (and Google Sheets) | Every Excel version (and Google Sheets) |
| Looks left of the key column | Yes | No |
| Survives inserted columns | Yes — ranges point at separate columns | No — the fixed column index can shift |
| Exact match | You write 0 in MATCH | You must add FALSE (default is approximate!) |
| Multiple-column return | No — one value per formula | No — one value per formula |
| Two-way lookup | Yes — INDEX + MATCH + MATCH | No |
Prefer INDEX MATCH over VLOOKUP in shared workbooks: it survives column insertions and looks left. On Microsoft 365, XLOOKUP packages the same advantages into a single function — same behavior, one name to remember.
INDEX MATCH in Google Sheets
Google Sheets supports INDEX and MATCH with the same arguments as Excel, so the formulas above can be pasted over unchanged. It remains the most portable lookup pattern across spreadsheets apps.
- The trailing 0 for exact match is just as important in Sheets — omitting it silently switches to approximate mode.
- In regional settings that use a comma as the decimal separator (much of Europe and Latin America), type semicolons between arguments: =INDEX($E$2:$E$500; MATCH(A2; $D$2:$D$500; 0)). Excel behaves the same way.
- Google Sheets also offers XLOOKUP with the same rules as Excel 2021+; INDEX/MATCH is the version that opens correctly in every legacy file.
- A two-way INDEX/MATCH/MATCH cross-reference works identically in both apps — useful for matrix-style price and rate tables.
Limitations and pro tips
- Debug in halves: run the MATCH alone first. If it returns a number, INDEX is the problem; if it returns #N/A, the lookup column is.
- Never skip the 0 in MATCH — approximate match on unsorted data returns a plausible but wrong result, the worst kind of error.
- Double-click the fill handle (the small square at the corner of the selection) to copy the formula down to the last row in one go.
- Lock ranges with $ signs ($D$2:$D$500) so the lookup column does not slide sideways when you copy the formula.
- For a two-way lookup (row and column), nest MATCH inside MATCH as the column argument of INDEX — it replaces matrix-style cross tables.
Need to pull many columns from another file rather than a single value? That is a table join, not a lookup: the free tools below do it without formulas.
Related Tools
Related Functions
Frequently Asked Questions
What does INDEX MATCH do in Excel?
MATCH finds the position of a value inside one column, and INDEX returns the item at that position in another column — in either direction, with exact match when you add the 0. It is the classic combination for lookups that VLOOKUP cannot do, like looking left.
Why does INDEX MATCH return #N/A?
The value is not in the lookup column — or it only looks identical: numbers stored as text, or invisible trailing and non-breaking spaces. Run the MATCH part alone to see which half fails, then convert text-numbers with VALUE() or clean the spaces.
How do I look left with INDEX MATCH?
Put the return column first in INDEX and search anywhere with MATCH: =INDEX($D$2:$D$500, MATCH(A2, $E$2:$E$500, 0)) returns values from column D by searching column E. VLOOKUP cannot do this at all.
Why do I need the 0 in MATCH?
The third argument sets match type. 0 means exact match. If you omit it, MATCH uses approximate mode, which assumes sorted data and silently returns wrong rows — the most dangerous INDEX MATCH mistake because nothing looks broken.
Is INDEX MATCH better than VLOOKUP?
For shared files, yes: it looks left, survives inserted columns, and can build two-way lookups. VLOOKUP is simpler to read for beginners. On Microsoft 365, XLOOKUP gives you the same INDEX MATCH advantages in a single function.
Can INDEX MATCH return multiple columns at once?
No — one INDEX MATCH formula returns one value, so you write one formula per column (or drag it sideways). XLOOKUP with a multi-column return range, or a table-join tool, is the better fit for pulling several columns at once.
Does INDEX MATCH work in Google Sheets?
Yes — Sheets supports both functions with the same arguments as Excel, including the trailing 0 for exact match. Only the argument separator changes in comma-decimal regional settings, where you use semicolons instead.
Ready to try it on your own file?
Open Join Two Excel Tables by a Key Column — It's Free