How to Use INDEX and MATCH in Excel (Explained Simply)

INDEX and MATCH are two separate functions that, put together, make a lookup more flexible than VLOOKUP. It can look left, it doesn't break when you insert columns, and it works in every version of Excel. The trick is to understand each half on its own first.

MATCH tells you where something is in a list. It returns a number, not the value itself.

=MATCH("Smith", A2:A100, 0)

If "Smith" is the 5th name in A2:A100, this returns 5.

The 0 at the end means exact match. Always include it.

INDEX does the opposite. You give it a list and a position, and it returns what's there.

=INDEX(C2:C100, 5)

This returns the 5th value in C2:C100.

Now replace the 5 in INDEX with the MATCH formula:

=INDEX(C2:C100, MATCH(E2, A2:A100, 0))

Read it from the inside out:

1. MATCH finds the value from E2 in column A and works out which row it's in.
2. INDEX goes to that same row in column C and returns the value.

That's it. The only rule is that both ranges must be the same size and start on the same row (here, both start at row 2 and end at row 100).

Staff IDs are in column B, names in column A, and departments in column D. You want the name for the ID in G2.

=INDEX(A2:A200, MATCH(G2, B2:B200, 0))

Notice the name column (A) is to the left of the ID column (B). VLOOKUP can't do this. INDEX and MATCH doesn't care.

• It looks in any direction. The return column can be left or right of the lookup column.
• It doesn't break. You point at the actual return column instead of counting columns, so inserting or deleting columns doesn't affect it.
• It works everywhere. Every version of Excel, plus Google Sheets and Excel for Mac.

If you have Microsoft 365 or Excel 2021, XLOOKUP does all this in one function and is simpler to write. INDEX and MATCH is still the one to use if you share files with people on older versions.

INDEX can take a row number and a column number, so you can use MATCH twice to look up a value in a grid.

Say you have months across row 1 (B1:M1), salespeople down column A (A2:A20) and sales figures in between. To find the figure for the person in P1 and the month in P2:

=INDEX(B2:M20, MATCH(P1, A2:A20, 0), MATCH(P2, B1:M1, 0))

The first MATCH finds the row, the second finds the column.

Lock the ranges with $ signs so they don't move as you copy. Click each range in the formula bar and press F4:

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

#N/A. MATCH couldn't find the value. Check for extra spaces, or numbers stored as text. Wrap the formula in IFERROR(…, "Not found") to show a message instead.

Wrong result. Check both ranges start on the same row, and that MATCH ends with , 0.

#REF! The row number from MATCH is bigger than the INDEX range. Make sure both ranges are the same size.

MATCH finds the row, INDEX returns the value. Put MATCH inside INDEX and you have a lookup that goes in any direction and survives whatever changes you make to the sheet.