Excel INDEX MATCH Returning Wrong Value? Fixes

Formulas & functions · Updated November 22, 2026

If your Excel INDEX MATCH formula is returning the wrong value, the most common causes are mismatched row/column references, missing exact match (0) in MATCH, or using whole-column references that shift when data is sorted. The fix is usually to ensure MATCH uses 0 for exact match, lock the lookup array with absolute references, and verify the lookup value exists in the lookup column.

How INDEX MATCH Actually Works

INDEX MATCH is a two-function combo. INDEX(array, row_num, [column_num]) returns the value at a given row and column position. MATCH(lookup_value, lookup_array, [match_type]) returns the position of a value within a range. When combined, MATCH feeds the row number to INDEX. For example, with names in A2:A20 and sales in B2:B20, to find sales for “John” in A5, the formula =INDEX(B2:B20, MATCH("John", A2:A20, 0)) returns the value in B5. The third argument in MATCH—0 for exact match, 1 for approximate, -1 for reverse approximate—is the most common source of errors.

Why Your Formula Returns a Wrong Value Instead of #N/A

If your formula gives a value that is not the one you expected, but not an error, the problem is usually one of these:

  • Match type 1 (approximate): Without 0, MATCH finds the largest value less than or equal to the lookup value, assuming the lookup array is sorted. If your data isn’t sorted, it returns an arbitrary position.
  • Wrong column reference: If you point INDEX at the wrong array (e.g., B2:B20 instead of C2:C20), you get a valid value from the wrong column.
  • Text vs. number mismatch: If the lookup value is text “123” but the cell contains number 123, MATCH won’t match. Use TEXT or VALUE to convert, or ensure consistent data types.
  • Leading/trailing spaces: Extra spaces in the lookup value or in the data column break exact matches. Use TRIM on both sides.
  • Hidden characters or non-printing characters: Imported data may contain CHAR(160) (non-breaking space) or line breaks. Use CLEAN and TRIM.

Step-by-Step: Correcting a Typical Wrong-Value Formula

Assume you have a table with product codes in A2:A100 and prices in B2:B100. You want the price for code “X-100” in cell D1. The wrong formula might be =INDEX(A2:B100, MATCH(D1, A2:A100))—missing the 0 and using the whole table in INDEX. The correct version:

  1. Change MATCH to include 0: MATCH(D1, A2:A100, 0).
  2. Change INDEX to reference only the return column: INDEX(B2:B100, ...).
  3. Use absolute references if you plan to copy the formula: =INDEX($B$2:$B$100, MATCH($D$1, $A$2:$A$100, 0)).
  4. Wrap with IFERROR to handle missing codes: =IFERROR(INDEX($B$2:$B$100, MATCH($D$1, $A$2:$A$100, 0)), "Not found").

This returns the price for X-100 if it exists, otherwise “Not found”.

Common Mistakes with Whole-Column References

Many users write =INDEX(B:B, MATCH(D1, A:A, 0)). This works, but it can cause two issues. First, if you insert a row above the data, the formula references shift, but because it’s a whole column, it still works—however, if you have other data below your table, MATCH might find a value in a cell you didn’t intend. Second, whole-column references can slow down calculation in large workbooks. More importantly, if your data has headers and you use MATCH(D1, A:A, 0), the header text might match if the lookup value equals the header. To avoid that, use a defined range like A2:A100 or a table reference like Table1[Code].

INDEX MATCH vs. VLOOKUP vs. XLOOKUP

VLOOKUP is simpler but limited: it can only look to the right, requires the lookup column to be the first column, and breaks if you insert or delete columns. INDEX MATCH is more flexible—it can return values from any column, left or right, and is not affected by column insertions. However, XLOOKUP (Excel 2021 and Microsoft 365) replaces both with a simpler syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]). For example, =XLOOKUP(D1, A2:A100, B2:B100, "Not found"). XLOOKUP defaults to exact match, so it avoids the match_type pitfall. If you have Microsoft 365, use XLOOKUP; if you need compatibility with older Excel, use INDEX MATCH.

How to Diagnose with Evaluate Formula and F9

To see exactly what your formula is doing, go to Formulas > Evaluate Formula (Windows) or use the F9 key in the formula bar. Select the MATCH part of the formula and press F9 to see what it returns. For example, if MATCH(D1, A2:A100, 0) returns #N/A, the lookup value isn’t in the range. If it returns a number, that’s the row position. Then check if the INDEX array is correct. On Mac, the Evaluate Formula button is under Formulas > Evaluate Formula as well. For Excel for the web, you can’t use F9, but you can copy the formula to a cell and use the Formula bar to see the result of a selected sub-expression by pressing F9 in the desktop version only.

Preventing Wrong Values in Dynamic Arrays

If you’re using dynamic array functions like FILTER or SORT, INDEX MATCH can still be used, but be aware that MATCH expects a single value, not an array. For example, if you want to match multiple criteria, you might use MATCH(1, (A2:A100="X")*(B2:B100="Y"), 0) as an array formula. In Excel 365, this works without Ctrl+Shift+Enter, but in older versions you must press Ctrl+Shift+Enter. This returns the row where both conditions are true. If no row matches, it returns #N/A. Always ensure the arrays are the same size. Alternatively, use XLOOKUP with a concatenated lookup: =XLOOKUP("X"&"Y", A2:A100&B2:B100, C2:C100).

Frequently Asked Questions

Why does INDEX MATCH return a value from the wrong row when I sort my data?

If you don’t use absolute references (like $A$2:$A$100), sorting can change the range references if the formula is relative. For example, if your formula is =INDEX(B2:B100, MATCH(D1, A2:A100, 0)) and you sort by column A, the formula references might shift because the cells move. Use absolute references or convert your data to an Excel Table (Ctrl+T) so references adjust automatically.

Why does my formula work for some values but not others?

This often points to data type inconsistency. For example, if some product codes are stored as text (green triangle) and others as numbers, MATCH with 0 will only match the same type. Use the TYPE function to check, or convert all to text with TEXT(A2,"0") or to numbers with VALUE.

Can I use INDEX MATCH to return multiple columns?

Yes. Use MATCH to find the row, then INDEX with a column number. For example, to return both price and stock for a product, you can use =INDEX(B2:D100, MATCH(D1, A2:A100, 0), 2) for the second column. For multiple columns at once, use dynamic arrays: =INDEX(B2:D100, MATCH(D1, A2:A100, 0), {1,2,3}) in Excel 365, which spills three values.

What does #N/A mean in INDEX MATCH?

#N/A means MATCH could not find the lookup value in the lookup array. This is not a wrong value—it’s an error. Check for typos, spaces, or data type mismatches. Use IFERROR to display a friendly message.

Is INDEX MATCH faster than VLOOKUP?

In most cases, INDEX MATCH is faster than VLOOKUP because VLOOKUP scans the entire table, while MATCH only scans the lookup column. However, with small datasets the difference is negligible. XLOOKUP is also efficient. For very large datasets, consider using binary search with match type 1, but that requires sorted data.

How do I fix a formula that returns a value from the header row?

If your lookup array includes the header row, MATCH might find the header text if it matches. For example, if you search for “Product” and the header is “Product”, it returns row 1. Solution: start your lookup array at row 2, or use MATCH(D1, A2:A100, 0) instead of A1:A100.

Final Checks and Best Practices

Before you assume your formula is broken, run these checks:

  • Confirm the lookup value exists in the lookup column (use COUNTIF).
  • Check for hidden spaces with =LEN(A2) compared to the visible text length.
  • Ensure the match type is 0 for exact matches.
  • Verify the INDEX array is the column you want to return.
  • Use absolute references if copying the formula.
  • Test with a known value to see if the formula returns the correct result.

If you’re still stuck, rebuild the formula step by step in a blank cell, testing MATCH first, then adding INDEX. This isolates the problem.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.