INDEX MATCH Exact Match: How to Use It

INDEX MATCH exact match is a formula combination in Excel that looks up a value in a table and returns a corresponding value from another column, but only when the lookup value matches exactly. You enter the lookup value, the column to search, and the column to return from, and the formula gives you the matching result. This guide explains how to build it, why it is often preferred over VLOOKUP, and how to avoid common errors.

Formula

The INDEX MATCH exact match formula combines two functions:

• MATCH finds the position of the lookup value in a column. Its syntax is MATCH(lookup_value, lookup_array, [match_type]). When the third argument is 0, it requires an exact match.
• INDEX returns the value at a given position in a range. Its syntax is INDEX(array, row_num, [column_num]).

Combined: INDEX(return_range, MATCH(lookup_value, search_range, 0))

Where:

• lookup_value is the value you want to find (e.g., a product ID).
• search_range is the column (or row) that contains the lookup value.
• return_range is the column (or row) from which you want the result.
• The 0 in MATCH forces an exact match.

Worked Example

Using the defaults from the calculator block above:

• Lookup value: 102
• Search column: A2:A10
• Return column: B2:B10

Assume column A contains: 101, 102, 103, 104, 105, 106, 107, 108, 109
Column B contains: Red, Orange, Yellow, Green, Blue, Indigo, Violet, Pink, Brown

Step 1: MATCH(102, A2:A10, 0) finds the position of 102 in the range. Since 102 is the second cell, MATCH returns 2.

Step 2: INDEX(B2:B10, 2) returns the second value in column B, which is “Orange”.

So the formula returns “Orange”.

Why Use INDEX MATCH Instead of VLOOKUP?

INDEX MATCH has several advantages over VLOOKUP:

• Column order doesn’t matter: VLOOKUP requires the lookup column to be the leftmost column in the table. INDEX MATCH can look up from any column and return from any other column, regardless of order.
• Inserting columns doesn’t break it: If you add a column to the table, VLOOKUP’s column index may shift and return wrong data. INDEX MATCH references ranges directly, so it stays correct.
• Faster on large data: INDEX MATCH only looks at the two columns you specify, not the entire table, so it can be more efficient.
• Can return values to the left: With VLOOKUP, you can’t return a value from a column to the left of the lookup column. INDEX MATCH can.

How to Use INDEX MATCH Exact Match in Excel

Here are the steps to create the formula in a worksheet:

1. Click the cell where you want the result.
2. Type =INDEX( and then select the column that contains the value you want to return.
3. Type a comma, then MATCH( and select the cell containing the lookup value.
4. Type a comma, select the column that contains the lookup value, and type a comma and 0.
5. Close the parentheses: =INDEX(B:B, MATCH(D2, A:A, 0)).
6. Press Enter. The result appears.

Note: When using entire columns (like A:A), Excel handles it fine, but it may be slower on very large sheets. Using a defined range (like A2:A10000) is a good compromise.

Common Mistakes and How to Fix Them

• #N/A error: This means no exact match was found. Check for extra spaces, non-printing characters, or numbers stored as text. Use TRIM() and CLEAN() to clean data, or convert text to numbers.
• Wrong match type: If you use 1 or omit the third argument, MATCH may return the nearest smaller value, which is not an exact match. Always use 0 for exact match.
• Mismatched range sizes: The search range and return range must be the same size. If they aren’t, INDEX will return an error or a wrong value.
• Lookup value not found: If the value doesn’t exist in the search column, you get #N/A. You can wrap the formula in IFERROR() to display a custom message like “Not found”.

Advanced Variations

INDEX MATCH can be extended for more complex lookups:

• Two-way lookup: Combine INDEX with two MATCH functions to find a value at the intersection of a specific row and column (like a table of prices).
• Case-sensitive lookup: Use EXACT() inside MATCH to differentiate between uppercase and lowercase. For example, MATCH(TRUE, EXACT(lookup, search_range), 0) entered as an array formula (Ctrl+Shift+Enter in older Excel).
• Lookup with multiple criteria: Use concatenation: MATCH(lookup1&lookup2, range1&range2, 0) as an array formula.

Frequently Asked Questions

Why does INDEX MATCH return #N/A even though the value exists?

The most common cause is a mismatch in data types. The lookup value might be a number, but the search column contains text (or vice versa). Check the cell formatting and use the VALUE() or TEXT() function to convert. Also, check for leading or trailing spaces using TRIM().

Can I use INDEX MATCH to look up from right to left?

Yes. Unlike VLOOKUP, INDEX MATCH doesn’t care about column order. You can search in column B and return from column A, as long as the ranges are the same size.

How do I use INDEX MATCH with multiple criteria?

You can concatenate the criteria and the lookup columns. For example, =INDEX(C:C, MATCH(E2&F2, A:A&B:B, 0)). In Excel 365, you can also use the XLOOKUP function with multiple criteria directly, but INDEX MATCH still works.

What is the difference between INDEX MATCH and XLOOKUP?

XLOOKUP is a newer function that does everything INDEX MATCH does, but with a simpler syntax. It also has built-in error handling and can return multiple values. However, XLOOKUP is only available in Excel 2021 and Microsoft 365, so INDEX MATCH remains useful for compatibility with older versions.

Can I use INDEX MATCH with wildcards?

Yes, but only when using the match type 0 and the lookup value contains a wildcard character (* or ?). For example, MATCH("A*", A:A, 0) will find the first cell starting with “A”. This is useful for partial matches.

Why is my formula returning a wrong value instead of an error?

This usually happens when the match type is not 0. With match type 1 or omitted, MATCH finds the largest value less than or equal to the lookup value, which can be a wrong match. Always use 0 for exact match.

Related

• Rbash Restricted Bash Shell: What It Does and Limits
• Old Hotmail Account: Already Hacked or Deleted?
• Kirin 990 vs A13 Bionic: Real-World Differences

in lookup | Lookup value | - | 102 | 0
in search | Search column range | - | A2:A10 | 0
in return | Return column range | - | B2:B10 | 0
out result | Matched value | - | INDEX(return, MATCH(lookup, search, 0)) | 2