> ## Content Index
> Fetch the complete content index at: https://aabie.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# How to use Excel INDEX MATCH to look up the right value
- URL: https://aabie.com/excel-index-match-lookup-value/
- Published: 2026-11-10T02:04:58.000Z
- Updated: 2026-11-10T02:04:58.000Z
- Author: Aabie Editorial Team
- Tags: Education

Excel INDEX MATCH looks up a value in one column and returns the matching value from another column. Use it when you want exact-match lookups and when the return column is not to the right of the lookup column, which is where VLOOKUP gets awkward. In practice, I use it when I need a lookup that keeps working even if the table layout is less convenient than VLOOKUP expects.

## Before you start

- You need a worksheet with a lookup value, a lookup column, and a return column.
- The lookup range and return range should cover the same number of rows.
- If you plan to fill the formula down, decide which ranges need to stay fixed before you start.

## What INDEX MATCH does in one sentence

MATCH finds the position of your lookup value inside a range, and INDEX returns the value from another range at that same position.

Put more plainly: MATCH finds the row number within the lookup list, and INDEX uses that row number to pull the result from the return list.

Use INDEX MATCH instead of VLOOKUP when you need an exact match and the column you want to return is not to the right of the lookup column. That layout comes up often in real workbooks, so this is the formula I reach for when VLOOKUP would force me to rearrange data or rethink the table structure.

## The formula pattern you’ll use

The exact pattern is:

`=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))`

Here’s what each part does:

- `lookup_value`: the cell with the value you want Excel to find
- `lookup_range`: the column or row Excel searches in
- `0`: exact match
- `return_range`: the column or row that contains the value you want back
- `MATCH(...)`: returns the position of the lookup value inside the lookup range
- `INDEX(...)`: returns the value from the return range at that position

The lookup range and return range do not have to be next to each other. They only need to line up row for row.

## What INDEX and MATCH do separately

`MATCH` does one job: it tells you where a value appears in a range. If `Pen` is the second item in `A2:A4`, `MATCH` returns `2`.

`INDEX` does the other job: it returns the value at a specific position in a range. If you point `INDEX` at `B2:B4` and ask for position `2`, it returns the second value in that range.

INDEX MATCH combines those two jobs. MATCH finds the position, and INDEX uses that position to return the result.

## A simple worksheet example

Say your sheet looks like this:

| A        | B     |
| -------- | ----- |
| Product  | Price |
| Notebook | 4.99  |
| Pen      | 1.49  |
| Folder   | 2.25  |

If you type a product name in `D2`, you want the price to appear in `E2`.

In this setup:

- lookup value: `D2`
- lookup range: `A2:A4`
- return range: `B2:B4`

That is the standard pattern: one cell holds what you’re searching for, one column holds the key you search in, and another column holds the value you want back.

## Build the exact-match formula step by step

1. Click the cell where you want the answer to appear, such as `E2`.
2. Start with `INDEX` and select the return range.

```text
=INDEX(B2:B4,
```

Formula fragment

1. Add `MATCH` as the position argument.

```text
=INDEX(B2:B4, MATCH(
```

Formula fragment

1. Select the lookup value cell, then the lookup range.

```text
=INDEX(B2:B4, MATCH(D2, A2:A4,
```

Formula fragment

1. Type `0` to require an exact match, then close the parentheses.

```text
=INDEX(B2:B4, MATCH(D2, A2:A4, 0))
```

Finished formula

1. Press Enter. If `D2` contains `Pen`, the cell should return `1.49`.

If the formula works, Excel will return the price that matches the product in `D2`. If it does not, the problem is usually the ranges, the match type, or the data itself, not the basic idea of INDEX MATCH.

## How to use it when the lookup column is to the left of the return column

This is one of the main reasons to use INDEX MATCH instead of VLOOKUP.

With VLOOKUP, the lookup column usually has to be the leftmost column in the table array. With INDEX MATCH, the lookup column and return column can be in any order, because you point each function at the range it needs.

For example, if your table is arranged like this:

| A     | B        |
| ----- | -------- |
| Price | Product  |
| 4.99  | Notebook |
| 1.49  | Pen      |
| 2.25  | Folder   |

and you want to look up the product name and return the price, you can set `lookup_range` to `B2:B4` and `return_range` to `A2:A4`.

That flexibility is why INDEX MATCH is still useful in messy or older workbooks where the data layout is not built for VLOOKUP.

## Lock the ranges so you can fill the formula down

If you want to copy the formula down a column, lock the lookup range and return range with absolute references so they do not shift.

Use `$` signs like this:

```text
=INDEX($B$2:$B$4, MATCH(D2, $A$2:$A$4, 0))
```

Filled-down-safe formula

Notice what changes and what stays fixed:

- `D2` stays relative, so it becomes `D3`, `D4`, and so on when you fill down
- `$B$2:$B$4` stays fixed
- `$A$2:$A$4` stays fixed

That matters as soon as you copy the formula. Without absolute references, the source ranges drift and the lookup starts pointing at the wrong rows. If each row has its own lookup value, the lookup cell can stay relative while the source table ranges stay locked.

![A close-up shows a spreadsheet formula bar and repeated formulas on a laptop screen.](https://tse1.mm.bing.net/th?q=close%20up%20of%20spreadsheet%20formula%20bar%20on%20laptop%20photo&w=624&h=352&c=7)

## How to check that the result is correct

Do not trust the formula just because Excel returned something. I usually check it the same way I’d check a manual lookup.

- Pick a lookup value you can find easily in the source table.
- Compare the returned value against the row you expect.
- Test one or two known inputs, not just a blank cell or a random value.
- Make sure the result matches the exact row you meant, not a nearby row.

A quick sanity check is to find the matched row in the source table and compare that row’s return value with the formula result. If the formula looks plausible but is still wrong, the lookup range or match type is usually the first place to inspect.

## Common mistakes and what they usually mean

Here are the problems I see most often with INDEX MATCH:

- Wrong range sizes: the lookup range and return range do not cover the same number of rows, so the position MATCH returns does not line up with the return range.
- Missing `0`: without exact match, Excel uses different matching behavior that can return the wrong row.
- Reversed ranges: MATCH is pointed at the return column instead of the lookup column, or INDEX is pointed at the lookup column instead of the return column.
- `#N/A`: Excel could not find the lookup value in the lookup range. The value may be misspelled, have extra spaces, use a different number format, or simply not exist in the table.
- `#REF!`: the formula points to an invalid cell or range, often because rows or columns were deleted after the formula was built.

If you get `#N/A`, check the exact text or number first. If the formula returns the wrong but believable answer, check whether `0` is in the MATCH part and whether the lookup range is the column you actually meant to search.

## When a newer Excel lookup like XLOOKUP is the easier choice

If you’re using a newer Excel version that supports XLOOKUP, it may be the easier choice because it combines the lookup and return behavior into one function. That usually makes the formula shorter and easier to read.

INDEX MATCH is still common in older files and mixed environments where not everyone has the same Excel version. If compatibility matters, INDEX MATCH is still a safe choice.

Because function availability can vary by Excel version and update channel, check Microsoft’s current official documentation for your version before you rely on XLOOKUP or assume it is available.

## Conclusion

If your formula returns the expected value from the correct row, you’ve built INDEX MATCH correctly. If it doesn’t, recheck the lookup value, the lookup range, the return range, and whether you used \`0\` for an exact match.

## Frequently Asked Questions

### What does MATCH return in INDEX MATCH?

MATCH returns the position of the lookup value within the lookup range. It does not return the final answer by itself; INDEX uses that position to pull the value from the return range.

### Why does my INDEX MATCH formula return #N/A?

Usually it means Excel could not find the lookup value in the lookup range. Check spelling, extra spaces, number-versus-text formatting, and whether you used exact match with \`0\`.

### Can INDEX MATCH look left?

Yes. That is one of its main advantages over VLOOKUP. You can put the lookup column and the return column in any order as long as you point each argument at the right range.