How to Use INDEX and MATCH Together in Excel (2026 Guide)

INDEX and MATCH are two small functions that, combined, do everything VLOOKUP does — plus the two things VLOOKUP cannot: look to the left, and survive a column being inserted. It works in every Excel version and in Google Sheets. Once you see how the two halves fit, it stops looking clever and starts looking obvious.

What each one does alone

MATCH finds a position. =MATCH("ABC123",A2:A100,0) asks: where in A2:A100 does ABC123 appear? It answers with a number — say, 7 — meaning the 7th row of that range. The 0 at the end means an exact match.

INDEX returns a value at a position. =INDEX(C2:C100,7) asks: what is the 7th value in C2:C100? It answers with the value itself — say, the price.

Put them together and you get a lookup: MATCH finds the row, INDEX fetches the answer from that row.

The basic lookup

Column A is the product code, column C is the price. Find the price of ABC123:

=INDEX(C2:C100,MATCH("ABC123",A2:A100,0))

Read it inside out: MATCH finds where ABC123 sits in A2:A100, then INDEX returns the value at that same position in C2:C100. Unlike VLOOKUP, the lookup column and the answer column are independent — the answer can sit to the left of the lookup column, which VLOOKUP cannot do at all.

Why it beats VLOOKUP

• Looking left works. Code in column C, price in column A? No problem: =INDEX(A2:A100,MATCH("ABC123",C2:C100,0)).
• Inserting a column does not break it. VLOOKUP counts columns (3 means "third column"), so a new column shifts the answer. INDEX and MATCH use ranges, which move with the sheet.
• It works everywhere. Every Excel version, Google Sheets, LibreOffice — no Microsoft 365 required, unlike XLOOKUP.

Two-way lookup: match a row AND a column

Your table has products down column A and months across row 1 (B1:D1 are Jan, Feb, Mar). Find the February value for product ABC123:

=INDEX(B2:D100,MATCH("ABC123",A2:A100,0),MATCH("Feb",B1:D1,0))

INDEX takes a row number and a column number here. The first MATCH finds the row, the second MATCH finds the column. VLOOKUP cannot do this at all — and it is the formula that makes summary tables and dashboards work.

The 3 mistakes that break it

• Forgetting the 0 in MATCH. =MATCH("ABC123",A2:A100) without a match type defaults to an approximate match, which needs sorted data and returns wrong answers on unsorted data. Always use 0 for lookups.
• Ranges of different sizes. MATCH searches A2:A100 but INDEX reads C2:C50 — the position numbers no longer line up. Keep the ranges the same height.
• Dirty lookup values. A trailing space or a number stored as text makes MATCH return #N/A for a value you can see in the table. Check with =A2=B2 in a spare cell, trim spaces with =TRIM(A2), and convert text-numbers with Data → Text to Columns → Finish.

FAQ

Is INDEX and MATCH better than VLOOKUP?

It is more flexible: it can look to the left, and inserting a column does not break it, because it uses ranges instead of a counted column number. It also works in Google Sheets and every Excel version, which XLOOKUP does not.

What does the 0 mean in MATCH?

The 0 is the match type: exact match. Use 0 for lookups unless you specifically want an approximate match in sorted data, which is rare.

Why does INDEX MATCH return #N/A?

MATCH could not find the lookup value — usually a hidden difference such as a trailing space or a number stored as text. Check with =A2=B2 in a spare cell: FALSE means the values only look the same.