How to use INDEX and MATCH together in Excel
Formula (Excel and Google Sheets)
=INDEX(D2:D9,MATCH(G1,B2:B9,0))MATCH finds the row and INDEX returns the value from that row: =INDEX(D2:D9,MATCH(G1,B2:B9,0)).
- Result on the example
- $81,250 in
G2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
MATCH(G1,B2:B9,0) returns the position of the name in the list: Rosa Diaz is 4th. INDEX(D2:D9,4) then returns the 4th value of the salary column. The 0 in MATCH asks for an exact match.
Because the search column and the result column are separate ranges, the pair works in any direction and keeps working when you insert columns.
=INDEX(D2:D9,MATCH(G1,B2:B9,0))Result81250
D2:D98 rows × 1 col: $68,500, $72,400, $59,800, $81,250, …A range: 8 cells in one column.
G1"Rosa Diaz"The value in G1.
B2:B98 rows × 1 col: "Marcus Lee", "Priya Nair", "Tom Becker", "Rosa Diaz", …A range: 8 cells in one column.
MATCH(G1,B2:B9,0)4MATCH: Position of a value in a list. 0 = exact match.- lookup_value
G1"Rosa Diaz" - lookup_array
B2:B98 rows × 1 col: "Marcus Lee", "Priya Nair", "Tom Becker", "Rosa Diaz", … - match_type
0
- lookup_value
INDEX(D2:D9,MATCH(G1,B2:B9,0))81250INDEX: Value at a given row and column of a range.- array
D2:D98 rows × 1 col: $68,500, $72,400, $59,800, $81,250, … - row_num
MATCH(G1,B2:B9,0)4
- array
In Google Sheets
The same formula works in Google Sheets: =INDEX(D2:D9,MATCH(G1,B2:B9,0)). We ran it in Google Sheets mode on the same data and it returns $81,250.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(G1,B2:B9,D2:D9) | $81,250 | The same lookup in one function, for Excel 2021, Microsoft 365 and Google Sheets. |
| =VLOOKUP(G1,B2:D9,3,FALSE) | $81,250 | Works here because the salary is to the right of the name. The table has to start at the search column. |
Common mistakes
- Leaving out the
0makes MATCH do an approximate match on a list that isn't sorted, which returns a wrong row without any error. - The INDEX range and the MATCH range must start on the same row.
INDEX(D2:D9,…)withMATCH(G1,B1:B9,0)returns the salary one row too high. - MATCH returns #N/A when the name isn't in the list. Check for extra spaces, or wrap the formula in IFNA.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Use INDEX and MATCH together
G2, use INDEX and MATCH to return the salary of the employee named in G1.Questions
Why use INDEX MATCH instead of VLOOKUP?
It can return a column to the left of the search column, it doesn't break when columns are inserted, and it works in every Excel version.
How do I match a row and a column at the same time?
Give INDEX a 2D range and two MATCHes: =INDEX(B2:E9,MATCH(G1,A2:A9,0),MATCH(H1,B1:E1,0)).
Is INDEX MATCH case-sensitive?
No. For a case-sensitive match use =INDEX(D2:D9,MATCH(TRUE,EXACT(B2:B9,G1),0)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.