INDEX and MATCH

INDEX and MATCH are two simple functions that, combined, make a lookup that works in every version of Excel, and in Google Sheets and LibreOffice too.

The same employee list as the XLOOKUP lesson:

INDEX: the value at a position

=INDEX(D2:D6, 3)            → 150000     the 3rd value in the range
=INDEX(A2:D6, 4, 2)         → Omar       row 4, column 2 of the table

MATCH: the position of a value

=MATCH("Maya", B2:B6, 0)    → 3          Maya is the 3rd name
=MATCH("E105", A2:A6, 0)    → 5

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

Together

MATCH finds the row; INDEX fetches the value in that row:

=INDEX(D2:D6, MATCH("Maya", B2:B6, 0))       → 150000     Maya's salary
=INDEX(A2:A6, MATCH("Omar", B2:B6, 0))       → E104       looks left, which VLOOKUP can't

Read it from the inside out: "find Maya's position in the names, then take the salary at that position".

Why people like it

• It can look left or right: the two ranges are independent.
• Inserting or deleting columns doesn't break it, because there's no column number to go stale.
• It only reads the two columns involved, which keeps very large workbooks fast.

Two-way lookups

Monthly sales by region, in A1:D4:

=INDEX(B2:D4, MATCH("South", A2:A4, 0), MATCH("Feb", B1:D1, 0))    → 110

One MATCH finds the row and another finds the column, and INDEX returns the value where they cross. Put the region and month in cells, say G1 and G2, refer to those instead of typing them, and you have a little query tool:

=INDEX(B2:D4, MATCH(G1, A2:A4, 0), MATCH(G2, B1:D1, 0))

Approximate matching

With a sorted list of lower limits, MATCH with 1 finds the band a value falls into:

=INDEX(G2:G4, MATCH(2500, F2:F4, 1))     → 8%     2500 falls in the band starting at 1000

INDEX/MATCH or XLOOKUP?

XLOOKUP is simpler to write and read, so use it where everyone has Microsoft 365 or Excel 2021. Use INDEX/MATCH when a workbook must work in older versions, and recognise it, because you'll find it in countless existing spreadsheets.