Home / Software / Microsoft / Microsoft Excel / How to Use INDEX MATCH in Excel

How to Use INDEX MATCH in Excel

VLOOKUP is the function most people learn first for searching through a table of data — but it has real limitations that trip people up in practice. INDEX MATCH is a more flexible alternative that solves those limitations, and once you understand how the two functions work together, it is not much harder to write than VLOOKUP. This guide explains both functions individually and then shows how to combine them.

What INDEX Does

The INDEX function returns the value at a specific position within a range. Think of it like reading coordinates on a map — you give it a table, a row number, and a column number, and it gives you back whatever is in that cell.

The syntax is:

=INDEX(array, row_num, [col_num])

For example, if A1:C10 contains a table of data:

=INDEX(A1:C10, 3, 2)

This returns the value in the third row, second column of that range — which would be cell B3. If you only have one column, you can omit the col_num argument.

On its own, INDEX is not especially useful — you rarely know the row number off the top of your head. That is where MATCH comes in.

What MATCH Does

The MATCH function finds the position of a value within a range and returns its row (or column) number. It is the lookup half of the pair.

The syntax is:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value — the value you are searching for
  • lookup_array — the range to search in (one row or one column)
  • match_type — use 0 for an exact match, which is what you almost always want

For example:

=MATCH("Smith", A1:A10, 0)

This searches column A for “Smith” and returns its position — say 4 if Smith is in the fourth row of that range. If the value is not found, MATCH returns an #N/A error.

Combining INDEX and MATCH

The magic happens when you use MATCH as the row_num argument inside INDEX. Instead of typing a fixed row number, you let MATCH figure it out dynamically:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Here is a practical example. You have a table where column A contains customer names and column B contains their account balances. You want to find the balance for a specific customer whose name is in cell E1:

=INDEX(B1:B10, MATCH(E1, A1:A10, 0))

This works in two steps:

  1. MATCH looks through A1:A10 for the value in E1 and returns the row number (e.g., 6).
  2. INDEX uses that row number to return the value from B1:B10 in the same row — the balance for that customer.

The result is identical to what VLOOKUP would give you here — but read on to see where INDEX MATCH pulls ahead.

The Main Advantage: Left-Side Lookup

VLOOKUP can only return a value to the right of the lookup column. If your table has customer names in column C and account numbers in column A, VLOOKUP cannot look up the name and return the account number — because account numbers are to the left.

INDEX MATCH has no such restriction. The return range and the lookup range are completely independent. You can look up in column C and return a value from column A with no problem:

=INDEX(A1:A10, MATCH(E1, C1:C10, 0))

This is the most common reason people switch from VLOOKUP to INDEX MATCH. If someone reorganises your spreadsheet and moves columns around, VLOOKUP breaks because it relies on column number position. INDEX MATCH uses range references, so as long as the ranges are updated it still works.

Two-Way Lookup

You can extend INDEX MATCH to look up both a row and a column simultaneously — useful for tables where you need to find a value at the intersection of a row and a column.

=INDEX(B2:D10, MATCH(F1, A2:A10, 0), MATCH(F2, B1:D1, 0))

In this formula:

  • The first MATCH finds the row position by looking up F1 in column A
  • The second MATCH finds the column position by looking up F2 in the header row
  • INDEX returns the value at the intersection

This is something VLOOKUP simply cannot do. A two-way lookup like this is handy for pricing tables, rate cards, or any matrix where you are looking up two variables at once.

Performance on Large Datasets

On large spreadsheets — tens of thousands of rows — INDEX MATCH is typically faster than VLOOKUP. VLOOKUP searches through the entire lookup range for every calculation. INDEX MATCH with an exact match (match_type 0) also searches the full range, but in practice the combination tends to recalculate more efficiently, particularly in complex workbooks with many lookups. The difference is negligible on small data; it can matter on large datasets.

When to Use XLOOKUP Instead

If you are using Microsoft 365 or Excel 2021 or later, you also have access to XLOOKUP, which is simpler to write than INDEX MATCH and handles most of the same use cases:

=XLOOKUP(lookup_value, lookup_array, return_array)

XLOOKUP can look left, look right, look up or down, and handles missing values cleanly. For straightforward lookups, XLOOKUP is the easiest option. Use INDEX MATCH when you need a two-way lookup (XLOOKUP cannot do this natively), or when your workbook needs to work in older versions of Excel that do not have XLOOKUP.

Common Errors and How to Fix Them

#N/A Error

The most common error — it means MATCH could not find the lookup value. Check for:

  • Typos in the lookup value
  • Leading or trailing spaces (use TRIM to clean the data)
  • Number stored as text — a cell that looks like 123 may actually contain the text “123”, which will not match the number 123
  • The lookup value genuinely not existing in the range

To return a friendly message instead of an error, wrap the formula in IFERROR:

=IFERROR(INDEX(B1:B10, MATCH(E1, A1:A10, 0)), "Not found")

#REF! Error

Usually means a range reference is broken — often caused by deleting rows or columns that were included in the formula range. Check each range argument.

#VALUE! Error

Check that your lookup_array in MATCH is a single row or column, not a multi-column range. MATCH only works with one-dimensional ranges.

Sign Up For Daily Newsletter

Stay updated with our weekly newsletter. Subscribe now to never miss an update!

[mc4wp_form]

Leave a Reply

Your email address will not be published. Required fields are marked *