How do I use INDEX and MATCH together?

MATCH finds the position of a value in a range, and INDEX returns the value at a given position in another range. Combined as =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), they look up a value in any direction, including to the left, which VLOOKUP cannot do.

Formula (Excel and Google Sheets)

=INDEX(Sheet2!C:C, MATCH(A2, Sheet2!A:A, 0))

MATCH(A2, Sheet2!A:A, 0) answers "what row number is A2 found on, within column A of Sheet2?" The 0 means an exact match. INDEX(Sheet2!C:C, ...) then answers "what value is in column C at that row number?" Put together, you get the value from column C that corresponds to the row where A2 was found in column A, regardless of whether column C is to the left or right of column A.

This is more flexible than VLOOKUP because the lookup column and the return column do not need to be next to each other or in any particular order.

Step by step

1
Write the MATCH first
MATCH(lookup_value, lookup_range, 0) finds the row position of your value in the lookup column.
2
Wrap it in INDEX
INDEX(return_range, [the MATCH formula]) returns the value from the return range at that same row position.
3
Keep both ranges the same length
The lookup range and the return range should cover the same rows, or the position INDEX uses will point to the wrong row.

Common questions

Is INDEX/MATCH better than VLOOKUP?

It is more flexible (it can look left, and does not break if you insert a column), but VLOOKUP is simpler to read for a straightforward left-to-right lookup.

What does the 0 in MATCH mean?

It requires an exact match. Leaving it out can return the wrong row on data that is not sorted.

Related formulas

How to Use VLOOKUP in Excel and Google Sheets

How to Use XLOOKUP in Excel and Google Sheets

How to Write a Nested IF Formula (and When to Avoid It)

How to Use IFERROR to Replace a Formula Error with a Clean Result

From the blog

How to Make Sense of Your Spreadsheet Data Without Being an Analyst

Want a formula written for your exact spreadsheet, not a generic example?

Try the free formula generator