INDEX and MATCH together look up a value in one column and return the matching value from any other column. They do what VLOOKUP does, plus left lookups, two-way lookups and lookups on several criteria. This page covers the syntax, each use with a worked example, and the one trap that catches most people.
Row 1 holds headers.
Master list, D2:E5 (name, ID):
Initech, 101
Umbrella Corp, 102
Acme Pty Ltd, 103
Globex Corporation, 104
Sales table, G1:J4 (rows are products, columns are quarters):
G1:J1 headers: Product, Q1, Q2, Q3
Widget, 10, 20, 30
Gadget, 40, 50, 60
Gizmo, 70, 80, 90
Orders, A2:C6 (customer, region, amount):
Acme, East, 100
Globex, West, 50
Acme, West, 25
Acme, East, 10
Globex, West, 70
MATCH finds a position. INDEX returns the value at a position.
=MATCH(103,$E$2:$E$5,0)
=INDEX($D$2:$D$5,3)
=INDEX($D$2:$D$5,MATCH(103,$E$2:$E$5,0))
The first returns 3 (103 is third in the list). The second returns Acme Pty Ltd (third item of the names). The third combines them and returns Acme Pty Ltd. The 0 in MATCH means exact match.
VLOOKUP can only return from columns to the right of the one it searches. INDEX MATCH has no such limit, because you give it the search column and the return column separately. To find a name from an ID, as above, or an ID from a name:
=INDEX($E$2:$E$5,MATCH("Initech",$D$2:$D$5,0))
This returns 101. Because it refers to the columns directly rather than counting a column number, inserting a column in between does not break it.
Use one MATCH for the row and another for the column. With the product in L1 and the quarter in L2:
=INDEX($H$2:$J$4,MATCH(L1,$G$2:$G$4,0),MATCH(L2,$H$1:$J$1,0))
With L1 = Gadget and L2 = Q2, this returns 50.
To match on customer and region together, multiply two tests so the result is 1 only where both are true, then find the first 1:
=INDEX($C$2:$C$6,MATCH(1,ARRAYFORMULA(($A$2:$A$6="Acme")*($B$2:$B$6="West")),0))
This returns 25. The ARRAYFORMULA makes the comparison run over the whole range. Add a third test by multiplying a third bracket. It returns the first row that satisfies every test, which here is the only one.
If you prefer something easier to read, build a helper key. In F2, filled down:
=A2&"|"&B2
Then look up the joined text:
=INDEX($C$2:$C$6,MATCH("Acme|West",$F$2:$F$6,0))
This also returns 25. The separator stops "AB"+"C" from matching "A"+"BC".
When there is no match, MATCH returns #N/A, and so does INDEX. Catch only that error with IFNA:
=IFNA(INDEX($E$2:$E$5,MATCH("Wayne",$D$2:$D$5,0)),"Not found")
This shows Not found. IFNA is safer than IFERROR here, because IFERROR also hides genuine mistakes such as a wrong range.
MATCH takes a third argument, and if you leave it out it is 1, not 0. A match_type of 1 does an approximate search that expects the list sorted ascending, and on an unsorted list it can return a wrong position without any error. On the unsorted name list D2:D5 above, =MATCH("Initech",$D$2:$D$5) returned 4 in our test, although Initech is in position 1. Always write 0:
=MATCH("Acme Pty Ltd",$D$2:$D$5,0)
This returns 3. The same trap catches VLOOKUP when the last argument is left out. Use 1 deliberately only for sorted lookups such as tax bands or grade boundaries.
If INDEX MATCH gives #N/A for a value you can see, the cause is almost always hidden spaces, text versus number, or a typo. Our #N/A guide walks through each: https://www.snakbacks.com/vlookup-na-error-google-sheets
XLOOKUP does the same left and right lookups in a single function, has a built-in not-found argument, and defaults to exact match, so it avoids the match_type trap. If you are starting fresh, it is simpler. See https://www.snakbacks.com/xlookup-google-sheets INDEX MATCH is still worth knowing because it works everywhere, and the multiple-criteria pattern above is the usual way to do that job.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
Exact lookups need only the formulas above. If your keys are spelled differently in the two lists, such as "Acme Pty Ltd" and "ACME Pty. Ltd", the Fuzzy Match Template for Google Sheets (US$9, no add-on install): https://snakback.gumroad.com/l/fuzzy-formulas adds fuzzy VLOOKUP-style formulas that match by similarity.
Our two free add-ons, Fuzzy Match and Duplicate Remover, are coming soon to the Google Workspace Marketplace.
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.