INDEX and MATCH together, step by step

INDEX and MATCH, used together, do everything VLOOKUP does and several things it cannot. The pair works in every version of Excel, which makes it the dependable choice for workbooks that many people open. It looks harder than it is. This page builds one slowly.

The sample

Sample sheet: A small price list with item codes. The table is sorted by code.

	A	B	C	D	E
1	Code	Item	Category	Price	Stock
2	A101	Basmati Rice 5 kg	Grocery	650	40
3	A102	Toor Dal 1 kg	Grocery	160	75
4	B201	Notebook	Stationery	60	300
5	B202	Ball Pen	Stationery	10	0
6	C301	LED Bulb	Electronics	110	200
7	C302	Power Bank	Electronics	1299	18
8	D401	Steel Bottle	Home	450	55

The task: given a code, find details of the item.

Step 1: MATCH finds the position

MATCH looks for a value in one row or column and says how far down it is.

=MATCH("C301",A2:A8,0)
Result: 5

The code C301 is the fifth entry in the list of codes. The final 0 asks for an exact match. Never leave it out.

Step 2: INDEX returns what is at a position

INDEX is handed a range and a position, and returns the cell there.

=INDEX(B2:B8,5)
Result: LED Bulb
=INDEX(D2:D8,5)
Result: 110

The fifth item name, and the fifth price.

Step 3: put MATCH inside INDEX

Replace the typed 5 with the MATCH formula that produced it.

=INDEX(B2:B8,MATCH("C301",A2:A8,0))
Result: LED Bulb
=INDEX(D2:D8,MATCH("C301",A2:A8,0))
Result: 110

That is the whole technique. In words: "from the column I want an answer from, give me the item at the position where the code is found".

The pattern to remember:

=INDEX(answer_column, MATCH(what_to_find, search_column, 0))

In real use, the code would be in a cell, not typed into the formula: =INDEX($D$2:$D$8,MATCH(G2,$A$2:$A$8,0)).

What VLOOKUP cannot do

Look to the left. VLOOKUP's answer must be to the right of the column it searches. With INDEX and MATCH the two columns are independent.

=INDEX(A2:A8,MATCH("Steel Bottle",B2:B8,0))
Result: D401

The search is in column B and the answer comes from column A.

Survive a new column. VLOOKUP holds a column number such as 4. Insert a column inside the table and it returns the wrong thing. INDEX holds a reference to the answer column itself, which moves along when columns are inserted.

Stay fast on big tables. VLOOKUP is given the whole table. INDEX and MATCH are given only the two columns they need.

A two-way lookup

Add a second MATCH to find the column as well as the row. This reads a value from the meeting point of a row label and a column heading.

=MATCH("D401",A2:A8,0)
Result: 7
=MATCH("Stock",A1:E1,0)
Result: 5
=INDEX(A2:E8,MATCH("D401",A2:A8,0),MATCH("Stock",A1:E1,0))
Result: 55

The first MATCH says row 7, the second says column 5, and INDEX returns the cell where they cross. Change "Stock" to "Price" and the same formula returns the price. Put the heading in a cell and the user can choose which detail to see.

A lookup on two conditions

Sometimes one column does not identify a row. In the sales sample, to find the amount of the sale made by Meena in the East region, both must match.

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=INDEX(F2:F13,MATCH(1,(B2:B13="Meena")*(C2:C13="East"),0))
Result: 2400

Each test gives TRUE or FALSE for every row. Multiplied together they give 1 only where both are true. MATCH then looks for the first 1. In Excel 2021 and Microsoft 365 this works as typed. In older versions, confirm it with Ctrl, Shift and Enter together.

The closest match in bands

MATCH with a last argument of 1 finds the largest value that does not exceed the lookup value, in a list sorted upwards. That is how marks become grades and amounts become slabs.

Sample sheet: A marks table and a grade table. The grade table lists the lowest mark of each grade, in rising order.

	A	B	D	E
1	Name	Marks	From	Grade
2	Asha	91	0	E
3	Ravi	45	33	D
4	Meena	79	50	C
5	Kabir	28	65	B
6	Tara	65	80	A

=INDEX($E$2:$E$6,MATCH(B2,$D$2:$D$6,1))
Result: A
=INDEX($E$2:$E$6,MATCH(B5,$D$2:$D$6,1))
Result: E

Handling a value that is not found

MATCH returns #N/A when the value is missing, and INDEX passes the error on.

=INDEX(B2:B8,MATCH("Z999",A2:A8,0))
Result: #N/A
=IFNA(INDEX(B2:B8,MATCH("Z999",A2:A8,0)),"Not found")
Result: Not found

IFNA replaces only the "not found" error and lets other mistakes show. See IFERROR.

The mistakes people make

Ranges that do not line up. If the answer column is B2:B8 and the search column is A3:A9, every answer is one row out, with no error. Start and end both on the same rows.
Forgetting the 0 in MATCH. The default is an approximate match.
No dollar signs. When the formula is copied down, both ranges must be locked: $B$2:$B$8.
Including the heading in one range and not the other.
Text and numbers that look alike. A code stored as the number 101 does not match the text "101".

INDEX MATCH or XLOOKUP?

In Excel 2021 and Microsoft 365, XLOOKUP does a simple lookup in one function and is easier to read. Use it when everyone who opens the file has a recent version. Use INDEX and MATCH when the workbook must work in Excel 2019 or earlier, and for two-way lookups, where it remains the clearest method. The comparison page VLOOKUP or XLOOKUP has more.

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.