INDEX & MATCH in Excel: How to Use It

INDEX and MATCH is a powerful Excel formula combination that looks up a value in a table and returns a corresponding value from another column or row. You enter the lookup value, the range to search, and the range to return from, and you get back the matching value. It is often used as a more flexible alternative to VLOOKUP, especially when your data is not arranged in a simple left-to-right table.

What Is INDEX and MATCH?

INDEX and MATCH are two separate Excel functions that work together to perform a two-way lookup. The MATCH function finds the position of a value in a row or column, returning a number (like 3 for the third cell). The INDEX function then uses that number to return the value at that position in another range. Combined, they let you look up a value in any column and return a result from any other column, regardless of order.

For example, if you have a table of employee IDs in column A and names in column B, you can use MATCH to find the row where the ID equals 102, then INDEX to return the name from that row. The formula looks like: =INDEX(B2:B10, MATCH(102, A2:A10, 0)).

Why Use INDEX and MATCH Instead of VLOOKUP?

VLOOKUP is simpler but has limitations that INDEX and MATCH overcome. VLOOKUP can only look from left to right, meaning the lookup column must be the leftmost column of the table. It also breaks if you insert or delete columns, because it uses a column number that may shift. INDEX and MATCH handles both situations gracefully.

• Left-to-right flexibility: You can look up values in any column and return from any column, even if the return column is to the left of the lookup column.
• Column insertion safety: Because you reference ranges directly, inserting a column does not change the formula’s logic.
• Faster on large datasets: INDEX and MATCH only evaluates the two ranges you specify, not the entire table, which can improve performance on big spreadsheets.

Step-by-Step Example

Suppose you have a product list: A2:A10 contains product codes, B2:B10 contains prices. You want to find the price for product code “P-345”.

1. Enter the product code in a cell, say D1: P-345.
2. In another cell, type: =INDEX(B2:B10, MATCH(D1, A2:A10, 0)).
3. Press Enter. The formula returns the price from column B that corresponds to the row where A contains “P-345”.

If the product code is not found, the formula returns #N/A. You can wrap it with IFERROR to show a custom message: =IFERROR(INDEX(...), "Not found").

Common Mistakes and How to Avoid Them

One frequent error is using the wrong match type. The third argument in MATCH should be 0 for an exact match, which is what you usually want. Using 1 or -1 assumes sorted data and can return incorrect results. Another mistake is mismatched range sizes: the lookup range and return range must be the same size, otherwise INDEX returns a #REF! error.

Also, be careful with absolute vs relative references. If you copy the formula down, you want the ranges to stay fixed, so use dollar signs like $A$2:$A$10.

Advanced Uses: Two-Way Lookups

INDEX and MATCH can also perform a two-way lookup, finding a value at the intersection of a specific row and column. For example, to find the sales figure for a specific product (row) in a specific month (column), you use two MATCH functions: one for the row and one for the column.

The formula is: =INDEX(data_range, MATCH(row_lookup, row_range, 0), MATCH(col_lookup, col_range, 0)). This is especially useful for financial models and data tables.

Frequently Asked Questions

Can INDEX and MATCH return multiple matches?

No, the standard formula returns only the first match. To return all matches, you need an array formula or a dynamic array function like FILTER (available in newer Excel versions).

Does INDEX and MATCH work with wildcards?

Yes, MATCH supports wildcards (* and ?) when match_type is 0. For example, =MATCH("A*", A2:A10, 0) finds the first cell starting with “A”.

Is INDEX and MATCH faster than VLOOKUP?

Generally yes, especially on large datasets, because it only looks at the specified ranges rather than the entire table. The difference is negligible on small sheets.

Can I use INDEX and MATCH with multiple criteria?

Yes, by using array formulas or by concatenating criteria. For example, =INDEX(C2:C10, MATCH(1, (A2:A10="X")*(B2:B10="Y"), 0)) entered with Ctrl+Shift+Enter in older Excel.

What if my lookup value is not found?

The formula returns #N/A. Use IFERROR to display a friendly message like “Not found” or 0.

Does this work in Google Sheets?

Yes, INDEX and MATCH work exactly the same in Google Sheets, making it a portable skill.

Related

• Printer Not Responding? Fixes That Work
• InDesign Plugins & Scripts That Improve Workflow
• Technology Excellence Awards: What They Are