INDEX MATCH in Excel: The Beginner's Guide

INDEX MATCH is a lookup made of two functions: MATCH finds where a value is, and INDEX returns what is at that place in another column. The INDEX MATCH Excel formula can look up in any direction, including to the left, and it doesn't break when columns move. This guide is for beginners who know VLOOKUP and want the next step, often for a job test.

What are INDEX and MATCH?#

Each function does half of a lookup. Learn them one at a time first, and the combined formula makes sense.

INDEX returns the item at a position#

=INDEX(range, row_num)

=INDEX(B2:B9,3) returns the 3rd item in B2:B9. Positions count from the first cell of the range, so B2 is position 1. INDEX can also take a column number for a table: =INDEX(A2:E9,7,3) returns row 7, column 3 of A2:E9. The INDEX function guide has more examples.

MATCH returns the position of a value#

=MATCH(lookup_value, lookup_array, 0)

=MATCH("Omar Haddad",B2:B9,0) returns 6, because Omar is the 6th name in the list. The 0 means exact match. MATCH doesn't return Omar's details, only his position. See the MATCH function guide for the other match types.

How to write an INDEX MATCH Excel formula step by step#

Put MATCH inside INDEX, where the position goes. Here is an employee directory where the ID is to the left of the name:

You have a name in G2 and need the ID. VLOOKUP can't do this, because it only returns columns to the right of the one it searches. INDEX MATCH can:

1. Write the MATCH. MATCH(G2,B2:B9,0) finds the row of the name in the Name column.
2. Wrap it in INDEX. INDEX takes the column you want back, the IDs, and uses MATCH as the position.

=INDEX(A2:A9,MATCH(G2,B2:B9,0))

If G2 is Grace Liu, MATCH returns 5 and INDEX returns the 5th ID: E-1081.

3. Lock the ranges before you fill down. Add $ signs to both ranges, but not to G2, so each row looks up its own name: =INDEX($A$2:$A$9,MATCH(G2,$B$2:$B$9,0)). Then fill down with Ctrl+D (Mac: ⌘D).

A good way to read the formula aloud: "Give me the ID from the row where the name matches G2."

Why use INDEX MATCH instead of VLOOKUP?#

INDEX MATCH points at two ranges instead of counting columns, and that brings three benefits:

• It looks in any direction. The return column can be to the left or right of the search column.
• It survives inserted columns. VLOOKUP's column number is a typed number, like 3. If someone inserts a column, the 3 still points at the old place and returns the wrong data. INDEX MATCH uses ranges, and Excel updates ranges for you.
• It combines with other functions. You can put any calculation in MATCH. In a stock report with product names in A2:A9 and stock counts in E2:E9, this finds the product with the most stock:

=INDEX(A2:A9,MATCH(MAX(E2:E9),E2:E9,0))

MAX finds the biggest stock number, MATCH finds its row, and INDEX returns the product name from that row. The formula stays right when the stock numbers change.

MATCH match types: 0, 1 and -1#

MATCH's last argument sets how it matches, and for lookups you almost always want 0.

If you leave the argument out, MATCH uses 1. On unsorted names or codes, that can return a wrong position with no error. Type the 0 every time unless you are placing numbers into bands.

Two-way lookups with INDEX and two MATCHes#

INDEX can take a row number and a column number, so you can use two MATCHes: one finds the row, the other finds the column.

=INDEX(A2:E9,MATCH(H11,B2:B9,0),MATCH(H12,A1:E1,0))

If H11 is "Julia Santos" and H12 is "Department", this returns Julia's department. Our two-way lookup guide explains how to line up the ranges and build a small report from one formula.

Common INDEX MATCH mistakes#

Most problems come from ranges that don't line up:

• Ranges that start on different rows. =INDEX(A2:A9,MATCH(G2,B1:B9,0)) includes the header in MATCH, so every position is 1 too high and you get the next person's ID. There is no error, just a wrong answer.
• Missing the 0. Without it, MATCH does an approximate match.
• Forgetting the $ signs. The ranges slide down as you fill, and the last rows return #N/A.
• The value really isn't there. Extra spaces or numbers stored as text also cause #N/A. Our guide on how to fix #N/A errors walks through each cause.

How to check an INDEX MATCH formula#

When a result looks wrong, check the MATCH on its own first. Two quick ways:

• Test it in a spare cell. Copy just the MATCH part, like =MATCH(G2,B2:B9,0), into an empty cell. If it shows the position you expect, the problem is in the INDEX range. If it shows #N/A, the value isn't being found.
• Use Evaluate Formula. Select the cell and choose Formulas > Evaluate Formula. Click Evaluate to watch Excel work out the MATCH first, then the INDEX, one step at a time.

You can also select the MATCH part inside the formula bar and press F9 to see its result. Press Esc afterwards, or the number replaces the formula.

INDEX MATCH or XLOOKUP?#

If everyone who opens your file has Excel 2021 or Microsoft 365, XLOOKUP does the same job in one function: =XLOOKUP(G2,B2:B9,A2:A9). See XLOOKUP in Excel: everything a beginner needs. INDEX MATCH is still worth knowing: it works in every version of Excel, you'll find it in many existing workbooks, and it is a classic topic in Excel interview questions.

Practise it#

INDEX MATCH clicks once you've built it a few times with your own hands. ExcelReady's INDEX and MATCH lesson takes you from each function alone to the combined lookup and a two-way lookup, in a live spreadsheet. Then test yourself with the INDEX/MATCH skill check: a stock report where you look up to the left, find the best-stocked item and build a small report. For more test-style tasks, see our list of practice tasks to do before an Excel test.