INDEX MATCH looks up a value in one column and returns the matching value from another. When it fails, it fails in two ways: with an error such as #N/A or #NUM!, or, worse, with a wrong value and no error at all. This checklist covers both. The examples use this small table in A1:C6:
| A: Product | B: Code | C: Price | |
|---|---|---|---|
| 2 | Apple | 101 | 1.2 |
| 3 | Banana (with a space at the end) | 102 (stored as text) | 0.5 |
| 4 | Cherry | 103 | 3 |
| 5 | apple pie | 104 | 4.5 |
| 6 | Date | 105 | 2 |
The correct formula to get the price of Cherry is =INDEX(C2:C6, MATCH("Cherry", A2:A6, 0)), which returns 3.
Tested in Google Sheets. We ran all 25 formulas from this guide on November 11, 2026, including the ones that return a wrong value silently, and copied each error message word for word. How we test
Quick diagnosis
| What you see | Most likely cause |
|---|---|
| A value, but the wrong one | 1. MATCH has no 0 at the end or 2. the two ranges don’t start on the same row |
| #N/A, Did not find value ‘…’ in MATCH evaluation. | 3. extra spaces, 4. numbers stored as text, 5. an empty lookup cell or the value really isn’t there |
| #N/A, MATCH range must be a single row or a single column. | 6. MATCH looks in more than one column |
| #NUM!, Function INDEX parameter 3 value 2 is out of range. | 7. a column number INDEX doesn’t have |
| A match you didn’t expect | 8. MATCH ignores case and understands wildcards |
1. MATCH has no 0 at the end (wrong value, no error)
MATCH has a third argument, the match type. If you leave it out, Google Sheets uses 1, which assumes the column is sorted in ascending order and returns the closest value that is not greater than the one you look for. On an unsorted list the result can be anything.
In our table, =INDEX(C2:C6, MATCH("Cherry", A2:A6)) returned 4.5, the price of “apple pie”, instead of 3. There was no warning. Another lookup without the 0, for “Date”, happened to return the right price, which is exactly why this mistake survives in so many sheets.
Fix: always end MATCH with 0 (or FALSE, which we found gives the same result) for an exact match: MATCH("Cherry", A2:A6, 0).
Found in testing: of the nine causes on this page, the two that did the most damage gave no error at all. A missing 0 and a misaligned range both returned a plausible price from the wrong row. If you only check for error values, you won’t find them: test a lookup with a value whose answer you already know.
2. The INDEX and MATCH ranges don’t start on the same row
MATCH returns a position, not a row number. Position 3 means “the third cell of the range you gave me”. If the MATCH range includes the header and the INDEX range doesn’t, every result is shifted by one row.
=INDEX(C2:C6, MATCH("Cherry", A1:A6, 0)) returned 4.5 instead of 3, again without an error. A related slip is a stray +1 after MATCH, which also returned 4.5.
Fix: make both ranges start and end on the same rows, such as C2:C6 and A2:A6. Whole columns (C:C and A:A) also line up: =INDEX(C:C, MATCH("Cherry", A:A, 0)) returned 3.
3. Extra spaces in the data
“Banana ” with a trailing space is not the same text as “Banana”. =INDEX(C2:C6, MATCH("Banana", A2:A6, 0)) returned #N/A with Did not find value ‘Banana’ in MATCH evaluation.
Fix: clean the column with Data > Data cleanup > Trim whitespace, or trim inside the formula:
=INDEX(C2:C6, MATCH("Banana", ARRAYFORMULA(TRIM(A2:A6)), 0))
That returned 0.5. If TRIM doesn’t help, the space may be a non-breaking space copied from a website; see how to remove extra spaces when TRIM doesn’t work.
4. Numbers stored as text (or text that looks like a number)
The code 102 in B3 was stored as text. Looking it up as a number failed: =INDEX(C2:C6, MATCH(102, B2:B6, 0)) returned Did not find value ‘102’ in MATCH evaluation. The opposite also fails: =MATCH("103", B2:B6, 0) did not find the real number 103.
Fixes that worked in our test:
- Look up the same type as the data:
MATCH("102", B2:B6, 0)returned the right row. - Convert the column inside the formula:
=INDEX(C2:C6, MATCH(102, ARRAYFORMULA(VALUE(B2:B6)), 0))returned 0.5. - Fix the data for good: select the column and choose Format > Number, then re-enter the values. You can check a cell with
=ISTEXT(B3), which returned TRUE here.
More conversion formulas, including currency and European formats, are in how to convert text to numbers.
5. The lookup cell is empty
When the value comes from a cell such as H2 and that cell is empty, MATCH looks for an empty string: Did not find value ” in MATCH evaluation. The message’s empty quotes are the clue. Check that the formula points to the right cell, or hide the result until something is typed: =IF(H2="", "", INDEX(C2:C6, MATCH(H2, A2:A6, 0))).
6. MATCH looks in more than one column
MATCH needs a single row or a single column. =INDEX(C2:C6, MATCH("Cherry", A2:B6, 0)) returned #N/A with MATCH range must be a single row or a single column. Point MATCH at the one column that holds the value you look for.
7. INDEX asks for a column it doesn’t have
If the INDEX range is a single column, it has no column 2. =INDEX(C2:C6, MATCH("Cherry", A2:A6, 0), 2) returned #NUM! (not #REF!) with Function INDEX parameter 3 value 2 is out of range. Either drop the third argument, or give INDEX the whole table and count from its first column: =INDEX(A2:C6, MATCH("Cherry", A2:A6, 0), 3) returned 3.
8. MATCH ignores case and understands wildcards
An exact match in Google Sheets is not case sensitive: MATCH("APPLE", A2:A6, 0) found “Apple”. It also treats * and ? as wildcards: MATCH("apple*", A2:A6, 0) returned the first row that starts with “apple”, which was “Apple”, not “apple pie”. If the first matching row isn’t the one you want, make the lookup value more specific. If your data contains a real asterisk or question mark, put a tilde before it, for example "5~*".
9. The value really isn’t there
Sometimes #N/A is the correct answer. MATCH("Fig", A2:A6, 0) returned Did not find value ‘Fig’ in MATCH evaluation. because there is no Fig. To show a message instead of the error, wrap the formula in IFNA: =IFNA(INDEX(C2:C6, MATCH("Fig", A2:A6, 0)), "Not found"). Avoid IFERROR here: it would also hide the #NUM! and #REF! errors that point to a broken formula. We compare the two in how to replace #N/A with a blank.
Looking up with two conditions
To match on two columns at once, multiply the conditions and look for 1:
=INDEX(C2:C6, MATCH(1, (A2:A6="Cherry")*(B2:B6=103), 0))
This returned 3. In our test it worked both on its own and wrapped in ARRAYFORMULA. If it doesn’t work in your sheet, wrapping it in ARRAYFORMULA( … ) is the first thing to try. XLOOKUP with the same multiplied conditions is different: it needs ARRAYFORMULA in Google Sheets, as we show in XLOOKUP with multiple criteria.