Excel INDEX MATCH Not Working? Fix the Formula

If Excel INDEX MATCH is not working, first check that MATCH ends with 0 for an exact match, and that the lookup and return ranges start and end on the same rows. Then check for missing values, numbers stored as text, extra spaces, and duplicate keys; these are frequent causes of #N/A or an unexpected result.

How INDEX MATCH is supposed to work

INDEX returns a value from a range at a specified position, while MATCH finds the position of a lookup value in a range. Together, they let you search one column and return a related value from another. Unlike a VLOOKUP formula, the lookup column does not have to be to the left of the return column.

Assume product IDs are in A2:A6, their prices are in B2:B6, and the ID to find is in E2. For the sample rows P-101 / 12, P-102 / 15, P-103 / 18, P-104 / 21, and P-105 / 24, use:

=INDEX(B2:B6,MATCH(E2,A2:A6,0))

If E2 contains P-103, MATCH returns 3 because P-103 is the third item in A2:A6. INDEX then returns the third item in B2:B6: 18. The final zero tells MATCH to find an exact match. It is important: when the match type is omitted, MATCH uses approximate matching, which may return a result that looks plausible but is not the requested item.

Check the match type and lookup value

For most lists of names, IDs, invoice numbers, and product codes, use exact matching. In the formula above, the final argument of MATCH is 0. Do not confuse that argument with the row number returned by MATCH: it controls how the search is performed.

MATCH also supports approximate matching. A match type of 1 finds the largest value less than or equal to the lookup value and expects the lookup range to be sorted in ascending order. A match type of -1 finds the smallest value greater than or equal to the lookup value and expects descending order. If the data is unsorted, or you need a precise ID, approximate matching can give a misleading result. Use 0 unless you deliberately need an approximate lookup and have arranged the data appropriately.

Also verify the value being searched. A trailing space in E2, a mistyped character, or a different dash character can make two entries that look alike on screen fail to match. Compare the cell contents directly, and check whether the source value has been changed by a formula or imported with hidden characters.

Diagnose #N/A without guessing

#N/A usually means MATCH did not find an eligible match. It does not necessarily mean the lookup value is visibly absent: a value may look identical while being stored differently, or a space may be hidden at the beginning or end. Start by checking the lookup range and the value, then work through data types and cleanup.

Confirm that the lookup cell contains the value you intend to search for, not a nearby cell or a value with an unnoticed typo.
Check that the lookup range contains the relevant records and does not exclude the last row.
Inspect whether the lookup item is text or a number. The text value 123 and numeric value 123 are not always interchangeable in a lookup.
Look for leading, trailing, or repeated spaces. Imported data can also contain nonbreaking spaces, which ordinary trimming may not remove.
For a text lookup, consider whether characters such as an asterisk or question mark are being treated as wildcards in an exact MATCH search.

A quick way to isolate the issue is to put the lookup value beside the source column and compare the underlying values, rather than relying on formatting. Number formatting can make different values display the same way. For example, a numeric 001 and text 001 may appear alike but represent different contents.

Fix numbers stored as text and unwanted spaces

Imported data often mixes numeric values with numbers stored as text. This happens with CSV exports, copied data, and codes that contain leading zeroes. If the values are quantities or numeric IDs without meaningful leading zeroes, convert both sides consistently. If leading zeroes matter, treat the identifiers as text instead; converting them to numbers would discard that distinction.

For example, assume A2:A100 contains numeric IDs that arrived as text, and you want a cleaned numeric helper column beginning in C2. Enter the following in C2 and fill it down:

=VALUE(A2)

If A2 contains text 123, C2 returns the number 123. Use the helper column as the lookup range, and make sure the lookup value is numeric too. VALUE is not appropriate for arbitrary text labels or codes with meaningful leading zeroes; those should remain text and be normalized as text.

To remove ordinary leading and trailing spaces from product codes, assume the source codes are in A2:A100 and use a helper column starting at C2:

=TRIM(A2)

Fill the formula down, then match against the cleaned helper range. TRIM removes ordinary extra spaces but does not remove every kind of invisible character. If the source came from a web page or external system, a nonbreaking space may need to be replaced separately. Do not clean only the lookup column if the lookup value still contains the unwanted character: normalize both sides in a consistent way.

Make sure the ranges line up

The array supplied to INDEX should correspond position by position with the range supplied to MATCH. If the lookup range is A2:A100, the return range should normally cover the same records, such as B2:B100. A shifted range can return a value from the wrong row even when MATCH correctly finds the item.

For instance, using A2:A6 as the lookup range and B3:B7 as the return range offsets the result by one row. A match at position 3 in A2:A6 makes INDEX return the third item of B3:B7, which is not the record on the matched row. Check both the first and last row references, especially after inserting rows, copying a formula, or changing a table’s size.

When copying a formula down or across, relative references may also move. If every row should search the same fixed dataset, lock those range references with dollar signs. With lookup values in E2:E20, product IDs in A2:A100, and prices in B2:B100, use this in F2 and copy down:

=INDEX($B$2:$B$100,MATCH(E2,$A$2:$A$100,0))

For E2 containing a matching ID, the formula returns that ID’s price; as you copy down, E2 changes to E3 and later rows, while the source ranges remain fixed. If your data is in an Excel Table, structured references can expand with the table, but verify that the column names and table data are correct.

Understand errors and unexpected results

Different errors point to different problems. #N/A generally indicates that the lookup was not found. #REF! means a reference is invalid, often because a referenced column or row was deleted. #VALUE! can indicate an incompatible argument or an issue in a referenced calculation. #NAME? can appear when Excel does not recognize a function name, often due to a misspelling or a function that is unavailable in that Excel version.

If the formula returns a value but it is the wrong one, check for duplicate lookup values. MATCH with exact matching returns the position of the first matching item it encounters. It does not automatically choose the newest, largest, or most relevant duplicate. Sort order does not change that first-match behavior for exact matching. If you need a specific record among duplicates, add another condition or use a method designed for multiple criteria.

You can use IFERROR to display a friendlier message, but it does not repair the underlying lookup. With IDs in A2:A100, prices in B2:B100, and the requested ID in E2, this formula displays Not found for any error:

=IFERROR(INDEX(B2:B100,MATCH(E2,A2:A100,0)),"Not found")

For a matching ID, it returns the corresponding price; when the lookup produces an error, it returns Not found. While troubleshooting, it is often better to leave the original error visible so you can distinguish a missing ID from a broken reference or another problem.

Use wildcards and understand their limits

Exact text matching in MATCH is not case-sensitive, so ABC and abc are treated as a match. If you need case-sensitive matching, ordinary INDEX MATCH with MATCH set to 0 is not enough; use a case-sensitive approach rather than assuming capitalization will distinguish records.

In an exact text search, MATCH can interpret the asterisk (*) as a sequence of characters and the question mark (?) as one character. A tilde (~) is used to escape a wildcard when you need to search for that character literally. This can matter when product names or filenames actually include wildcard characters. If a lookup unexpectedly matches a partial pattern, inspect the lookup text for * or ?, and use the appropriate escape where needed.

Compare INDEX MATCH with newer lookup options

For a single exact lookup, INDEX MATCH remains useful because it works in older Excel versions and can return values from columns on either side of the lookup column. It can also be easier to adapt when the lookup and return ranges are in different places. Its main drawback for beginners is that the position-based construction makes range alignment and match type easy to get wrong.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It can search one range and return from another directly, and it has an optional value to show when nothing is found. For the same product example, with IDs in A2:A6, prices in B2:B6, and the requested ID in E2, the equivalent exact lookup is:

=XLOOKUP(E2,A2:A6,B2:B6,"Not found")

If E2 contains P-103, it returns 18; if there is no match, it returns Not found. Older Excel versions that do not support XLOOKUP may display #NAME?, so INDEX MATCH is a practical compatibility choice when a workbook will be opened in older releases.

Frequently Asked Questions

Why does INDEX MATCH return #N/A when the value is there?
Check for a text-versus-number mismatch, hidden spaces or characters, a misspelled lookup value, and a range that does not include the record. A value that looks identical in the worksheet may not be identical internally.

Should the last MATCH argument be 0 or 1?
Use 0 for an exact match, which is the normal choice for IDs, names, and codes. Use 1 or -1 only when you need approximate matching and have sorted the lookup range in the required order.

Does INDEX MATCH distinguish uppercase and lowercase?
No. MATCH with exact match type 0 is not case-sensitive. If capitalization separates otherwise identical entries, use a case-sensitive formula or create a separate key that makes the records distinct.

Why does my formula return the wrong row?
Check that the INDEX return range and MATCH lookup range begin and end on corresponding rows. Also check for duplicate lookup values: exact MATCH returns the first occurrence, not necessarily the record you intended.

Can I use INDEX MATCH in older Excel versions?
Yes. INDEX and MATCH are established Excel functions and work in many older versions. XLOOKUP, by contrast, requires Microsoft 365 or Excel 2021 or later, so compatibility can affect which formula you choose.

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.