# How to use INDEX and MATCH together in Excel

MATCH finds the row and INDEX returns the value from that row: `=INDEX(D2:D9,MATCH(G1,B2:B9,0))`.

**Excel:** `=INDEX(D2:D9,MATCH(G1,B2:B9,0))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Employee ID | Name | Department | Salary |  | Name | Rosa Diaz |
| E-1031 | Marcus Lee | Finance | 68500 |  | Salary |  |
| E-1032 | Priya Nair | Marketing | 72400 |  |  |  |
| E-1033 | Tom Becker | Sales | 59800 |  |  |  |
| E-1034 | Rosa Diaz | IT | 81250 |  |  |  |
| E-1035 | Ken Watanabe | Sales | 63900 |  |  |  |
| E-1036 | Aisha Khan | Finance | 77100 |  |  |  |
| E-1037 | Leo Novak | IT | 69950 |  |  |  |
| E-1038 | Grace Kim | Marketing | 74300 |  |  |  |

Formula in G2: `=INDEX(D2:D9,MATCH(G1,B2:B9,0))`

Result: $81,250

## 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.

## Other ways

- `=XLOOKUP(G1,B2:B9,D2:D9)`: The same lookup in one function, for Excel 2021, Microsoft 365 and Google Sheets.
- `=VLOOKUP(G1,B2:D9,3,FALSE)`: 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 `0` makes 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,…)` with `MATCH(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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/index-and-match-together