Excel Help

INDEX and MATCH: The Lookup Combo That Works in Every Excel

Two small functions that team up to find any value in a list. Learn how they work, and when to choose them over XLOOKUP.

About 9 minutes to read

Two Functions, One Job

Imagine you ask a librarian for a book. The librarian does two things. First they check the catalogue and say, "It is on shelf number 3." Then they walk to the shelf and pick up the book.

INDEX and MATCH work the same way, and they are always used as a pair:

MATCH is the catalogue. It tells you where something is in a list ("it is the 3rd item").
INDEX is the librarian walking to the shelf. It gives you the item at a position you choose ("give me the 3rd item").

In one sentence: MATCH finds the position of what you are looking for, and INDEX uses that position to fetch the answer from another column.

Together they do the same job as XLOOKUP: find something in one column and bring back the matching value from another. INDEX and MATCH have simply been around much longer, so you will see them in countless spreadsheets, and they work in every version of Excel.

Is INDEX and MATCH Useful for Me?

Probably, if you ever need to:

Look up a price, name or phone number from a list
Do this in an older version of Excel that does not have XLOOKUP
Share a workbook with colleagues who might have an older Excel
Understand or fix a spreadsheet someone else made

The last point matters more than you might think. Many existing spreadsheets use INDEX and MATCH, so it is a very good formula to be able to read.

See It in Action

We will use the same small price list as in our XLOOKUP article. Column A has the products and column B the prices.

	A	B	D	E
1	Product	Price	Search for	Price
2	Notebook	4.50	Desk lamp	24.90
3	Pen set	7.20
4	Desk lamp	24.90
5	Stapler	9.00

In cell E2 we type one formula. Excel looks for "Desk lamp" (orange) in column A, finds it (blue), and brings back the price from column B (green). It gets there in two small steps.

What you search for Where it was found What comes back

Step 1: MATCH Finds the Position

MATCH looks for your word in a column and tells you which position it is in: 1st, 2nd, 3rd, and so on.

=MATCH( D2, A2:A5, 0 )

This means: "Find the word in D2 inside A2:A5, and tell me its position." In our list, "Desk lamp" is the 3rd item, so the answer is 3.

What is the 0 for? It means "find an exact match". Always include it. If you leave it out, Excel may quietly guess and give you the wrong answer.

Step 2: INDEX Fetches the Value

INDEX takes a column and a position, and gives you what is at that position.

=INDEX( B2:B5, 3 )

This means: "In the price column B2:B5, give me the 3rd item." The 3rd price is 24.90.

Notice that INDEX does not search for anything. It only counts down the column. That is why it needs MATCH to tell it the position.

Putting Them Together

Instead of typing the number 3 ourselves, we let MATCH provide it. We place the MATCH formula where the 3 was:

=INDEX( B2:B5, MATCH( D2, A2:A5, 0 ) )

What do I bring back?

B2:B5 is the column with the prices. This is the INDEX part.

What am I looking for?

D2 is the cell where you typed "Desk lamp".

Where should Excel look?

A2:A5 is the column with the product names. The 0 at the end means an exact match.

Excel works from the inside out: MATCH finds 3, then INDEX picks the 3rd price. Change D2 to "Stapler" and the price changes to 9.00 on its own.

Good to know: the "bring back" column goes first, and the "look in" column goes inside the MATCH. It feels backwards at first, but you will get used to it.

Comma or Semicolon? It Depends on Where You Live

If a formula from the internet gives an error even though it looks perfect, the separator is often the reason.

Style	Separator	Typical regions	Example
American	Comma ,	USA, UK, Canada, Australia	`=INDEX(B2:B5, MATCH(D2, A2:A5, 0))`
European	Semicolon ;	Finland, Sweden, Germany, France, Spain	`=INDEX(B2:B5; MATCH(D2; A2:A5; 0))`

The formula does the same thing in both styles. In many European countries the comma is the decimal mark (2,5 instead of 2.5), so Excel uses the semicolon between the pieces. If you get an error, swap every comma for a semicolon, or the other way around. The examples in this article use commas.

How to Use It Yourself

Click the empty cell where you want the answer.
Type =INDEX( and select the column with the answers you want back. Type a comma.
Type MATCH(, click the cell with what you are searching for, and type a comma.
Select the column to search in, type a comma and then 0.
Close both brackets )) and press Enter.

Tip: If it feels like too much at once, build it in two steps. First type the MATCH formula in a spare cell and check that it gives a sensible number. Then wrap INDEX around it.

What If the Item Is Not in the List?

If you search for "Calculator" and it is not in the list, Excel shows #N/A. To show a friendly message instead, wrap the whole formula in IFERROR:

=IFERROR(INDEX(B2:B5, MATCH(D2, A2:A5, 0)), "Not found")

IFERROR means "try this, and if it gives an error, show this instead". Now you will see Not found instead of an error.

INDEX and MATCH vs. XLOOKUP

Both do the same job, so which should you use? Here is how they compare:

	INDEX and MATCH	XLOOKUP
How it is written	Two functions, one inside the other	One function with three pieces
Ease for beginners	Harder, you need to understand two steps	Easier to read and write
Exact match	You must add 0 yourself	Exact match is the default
Item not found	Needs IFERROR around it	Has a built-in "not found" message
Looking left	Works	Works
Adding a column later	Keeps working correctly	Keeps working correctly
Excel versions	Every version	Microsoft 365 and Excel 2021 or newer
Older spreadsheets	Very common, good to know	Newer, found in more recent files

So which one should I use?

Use XLOOKUP if you have it and you are the only one using the file. It is simpler and harder to get wrong. Read our XLOOKUP article to learn it.
Use INDEX and MATCH if you share the file with people who might have an older Excel, or if you need to understand a spreadsheet that already uses it.

If you are unsure, check whether your Excel has XLOOKUP by typing =XLOOKUP( in a cell. If Excel suggests it, you have it.

Advanced

Looking Up Across Rows and Columns

So far we looked in one direction: down a column. But sometimes the answer sits where a row and a column cross, like a price table with products down the side and months along the top. INDEX and MATCH is excellent at this, because INDEX can take both a row and a column.

	A	B	C	D
1	Product	Jan	Feb	Mar
2	Notebook	120	135	150
3	Pen set	80	95	90
4	Desk lamp	30	25	40
5	Stapler	60	70	55

How many Pen sets did we sell in February? We need the crossing of the "Pen set" row (blue) and the "Feb" column (blue). The answer is 95 (green).

=INDEX( B2:D5, MATCH("Pen set", A2:A5, 0), MATCH("Feb", B1:D1, 0) )

Read it in three parts:

B2:D5 is the whole block of numbers.
The first MATCH finds which row "Pen set" is in: the 2nd.
The second MATCH finds which column "Feb" is in: the 2nd.

INDEX then gives you the number in the 2nd row and 2nd column of the block: 95. In practice you would replace the words "Pen set" and "Feb" with cells, so you can change the question without touching the formula.

Common Problems and How to Fix Them

You see #N/A

MATCH could not find what you searched for. Check the spelling, and look for extra spaces at the start or end of the text. "Desk lamp " (with a space) is not the same as "Desk lamp". Or use IFERROR, as shown above.

You get the wrong answer, with no error

You probably left out the 0 at the end of MATCH. Without it, Excel assumes your list is sorted and guesses, which gives wrong answers on a list that is not sorted. Always finish MATCH with , 0).

You get the answer from the wrong row

The "bring back" range and the "look in" range must start on the same row. If INDEX uses B2:B5 but MATCH uses A3:A6, every answer is shifted by one row.

Excel says there is a problem with your formula

Check the separators. If your Excel uses European settings, use semicolons (;) instead of commas (,), as explained above. Also count the brackets: this formula needs two closing brackets at the end.

You see #REF!

INDEX was asked for a position outside the range, for example the 6th item in a list of 4. Check that the ranges are the right size.

The numbers look right but nothing matches

Sometimes a number is stored as text, so "1001" and 1001 look the same but Excel treats them as different. If the cell has a small green triangle or the number is left-aligned, convert it to a real number.

The Short Version

MATCH finds the position of something in a list. INDEX fetches the item at a position. Together they look things up.
The formula is =INDEX(bring back this, MATCH(find this, in here, 0)).
Always end MATCH with 0 for an exact match.
XLOOKUP is simpler. INDEX and MATCH works in every version of Excel, and is everywhere in older files.
Wrap it in IFERROR for a friendly "Not found" message.
Use commas in American-style Excel and semicolons in European-style Excel.