INDEX MATCH Not Working? Fix Common Excel Lookup Errors

If INDEX MATCH is not working in Excel, start by using an exact-match formula and checking that the lookup and return ranges cover the same rows. With IDs in A2:A5, matching descriptions in B2:B5, and the ID to find in E2, use =INDEX(B2:B5,MATCH(E2,A2:A5,0)); if E2 contains 106 and the third description is Cedar, the result is Cedar.

Check the formula and match mode first

The most frequent fix is adding 0 as the third argument of MATCH. The zero requests an exact match. If you leave that argument out, MATCH uses approximate matching by default, which can return an unexpected row when the lookup values are unsorted or when you expect an exact text or number match.

For the example above, A2:A5 contains 104, 105, 106, and 107; B2:B5 contains Ash, Birch, Cedar, and Elm; and E2 contains 106. The formula returns Cedar. Check that the commas, parentheses, and range references match this pattern:

INDEX receives the range containing the answer.
MATCH receives the value to find, the range to search, and 0 for exact matching.
The lookup range and return range start and end on corresponding rows.

If your Excel installation uses semicolons as formula separators because of regional settings, replace the commas in the formula with semicolons. The examples here use standard comma separators.

How INDEX and MATCH work together

MATCH searches one row or one column and returns the position of a value within that range. INDEX uses a position number to return a value from another range. In this example, MATCH finds 106 in the third position of A2:A5, and INDEX returns the value in the third position of B2:B5. The position is relative to the selected range, not necessarily the worksheet row number.

You can isolate the search to see whether the problem is in MATCH or INDEX. With IDs 104, 105, 106, and 107 in A2:A5 and 106 in E2, the formula =MATCH(E2,A2:A5,0) returns 3. If it returns #N/A, Excel did not find an exact match in A2:A5. If it returns 3 but the combined formula gives the wrong description, inspect the INDEX range and the alignment of the two ranges.

INDEX and MATCH are separate functions, so the lookup column does not have to be to the left of the return column. This is one reason people use the combination instead of older VLOOKUP formulas. However, flexibility does not remove the need for corresponding ranges: the position MATCH finds must point to the intended item in the range supplied to INDEX.

Fix #N/A when the value seems to be present

#N/A means MATCH did not find a suitable value. Often the values only look identical on the worksheet. Check the underlying data rather than relying on what is displayed. Common causes include an extra space, a nonprinting character, different punctuation, a date stored as text in one place and as a date value in another, or a number stored as text.

Use these checks before changing the formula:

Click each value and inspect the formula bar for leading or trailing spaces.
Compare the source and lookup cells’ data types. A numeric 106 and text value 106 can appear alike but may not match.
Check that a date has the same underlying value, not merely the same display format.
Confirm that the value is actually inside the range in MATCH, rather than just beside it or on another worksheet.
Look for nonbreaking spaces copied from websites or reports. TRIM removes ordinary extra spaces but does not remove every special whitespace character.

To remove ordinary excess spaces from text, put the original value in A2 and enter =TRIM(A2) in a helper cell, such as C2. If A2 contains the text with spaces around 106, C2 returns 106 without those outside spaces. Fill the helper formula down, then use the cleaned values as the lookup range if appropriate. Do not overwrite source data until you have checked that cleaning it will not alter meaningful spacing.

Check range alignment and INDEX dimensions

A formula can find the right position but return the wrong result if the ranges are offset. For example, if MATCH searches A2:A5 but INDEX returns B3:B6, a position of 3 points to B5, not B4. Select the two ranges directly in the formula bar and verify their first and last rows. Sorting only one range separately can also break the relationship between IDs and descriptions; sort the complete table together.

For a two-way lookup, MATCH finds a row position and another MATCH finds a column position. Suppose product names are in A2:A4, quarter names are in B1:D1, and the sales figures are in B2:D4. The rows contain Cedar, Elm, and Pine; the column headings are Q1, Q2, and Q3. With Cedar in G2 and Q2 in G3, enter =INDEX(B2:D4,MATCH(G2,A2:A4,0),MATCH(G3,B1:D1,0)). If Cedar’s Q2 value in C2 is 240, the formula returns 240. If either MATCH cannot find its heading, the combined formula returns #N/A.

#REF! usually points to an invalid reference or an index position outside the supplied INDEX range. Check that the row and column numbers correspond to the dimensions of the INDEX array. For example, a one-column return range should not be given a column position of 2. If you deleted cells or sheets used by a formula, repair the broken reference rather than changing the match mode.

Understand approximate matches, duplicates, and wildcards

For ordinary exact lookups, use 0 in MATCH. Match type 1 looks for the largest value less than or equal to the lookup value and expects the lookup range to be sorted in ascending order. Match type -1 looks for the smallest value greater than or equal to the lookup value and expects descending order. Using either approximate mode on unsorted data can produce misleading results without the clear failure you might expect.

Exact matching is not case-sensitive: Cedar and CEDAR are treated as the same text by MATCH. If the lookup value appears more than once, exact MATCH returns the position of the first match it encounters. If the first duplicate is not the record you intend, make the lookup key unique, for example by combining a customer ID with a date or another distinguishing field.

With match type 0, MATCH can also use wildcards for text: * represents any number of characters, and ? represents one character. If product names are in A2:A5, corresponding prices are in B2:B5, and E2 contains ced, =INDEX(B2:B5,MATCH("*"&E2&"*",A2:A5,0)) returns the price on the first row whose name contains ced, assuming Cedar is the first such match. This is a partial-text search, not an exact lookup. Use a tilde to search for a literal wildcard character rather than treating it as a pattern.

Recognize other formula errors

#VALUE! can result from an invalid argument or a formula structure that does not fit the supplied ranges. Check that MATCH is searching a single row or a single column, and that INDEX has the row and, when needed, column positions in the correct order. Also check for a missing parenthesis or a range reference that was accidentally changed while copying the formula.

#NAME? commonly indicates a misspelled function name or an unrecognized name in the formula. Confirm that INDEX and MATCH are spelled correctly and that any named ranges really exist. If the formula appears as text in the cell instead of calculating, check whether the cell is formatted as Text; change it to General, then re-enter the formula. Also check that Show Formulas has not been turned on, since that setting displays formulas across the worksheet instead of their results.

When a formula returns a plausible but wrong answer, do not assume Excel has made a calculation error. Recheck whether approximate matching was used, whether duplicate keys exist, and whether a relative reference shifted after copying the formula. Add dollar signs to references that must stay fixed when copied, such as $A$2:$A$5, while leaving the lookup cell relative if it should change by row.

When to use XLOOKUP instead

In Microsoft 365, XLOOKUP can make a basic lookup easier to read because it takes the lookup range and return range directly and uses exact matching by default. With IDs in A2:A5, descriptions in B2:B5, and the lookup ID in E2, =XLOOKUP(E2,A2:A5,B2:B5) returns Cedar when E2 is 106. XLOOKUP is available in Microsoft 365 and Excel 2021; it is not available in some older Excel versions.

INDEX MATCH remains useful in workbooks that must support older versions, for two-way lookups, and where the existing formula already fits the task. Switching functions will not fix dirty data, misaligned ranges, or duplicate keys. First identify the actual cause, then decide whether keeping INDEX MATCH or using a newer lookup function makes the workbook clearer for its users.

Frequently Asked Questions

Why does INDEX MATCH return #N/A even though the value is visible?
Excel may be comparing different underlying values: for example, text digits against a number, a date against text, or a string with an extra space. Check the data types and hidden characters, and make sure the lookup range includes the visible value. Use exact match mode 0 for a normal exact lookup.

Does MATCH need the lookup column to be sorted?
No, not when you use match type 0 for exact matching. Approximate match types have sorting requirements: 1 expects ascending order and -1 expects descending order. If you omit match type, MATCH defaults to 1, so specify 0 when you want an exact result.

Why is INDEX MATCH returning the wrong row?
Check for approximate matching, duplicate lookup values, and ranges that start on different rows. MATCH returns a position within its own lookup range; INDEX uses that position within its return range. If the ranges are offset, the position can point to a different record.

Is INDEX MATCH case-sensitive?
No. MATCH with exact match type 0 does not distinguish uppercase from lowercase. If case must matter, a different approach using case-sensitive functions is needed; changing the capitalization of the INDEX MATCH formula alone will not make it case-sensitive.

Can INDEX MATCH look to the left?
Yes. MATCH can search a column and INDEX can return a value from a separate column on either side, provided both ranges line up by row. Unlike VLOOKUP, INDEX MATCH does not require the return column to be to the right of the lookup column.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.