Ravindra BagaleCourses & study guides Track your progress

Guides

How to Use INDEX and MATCH Together in Excel

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Read this guide in: English · मराठी · हिंदी

MATCH finds the position of a lookup value. INDEX returns the value at a position. Together they build a flexible exact lookup — including left lookup.

Chala mitrano! VLOOKUP left side la return nahi karu shakat. INDEX + MATCH can. Why learn both? Interviews + old Excel files. How? MATCH finds row number, INDEX picks the cell. Stay with me.

Quick answer

Vertical steps:

  1. MATCH the Product ID in the ID column with 0 (exact).
  2. Note the position number (for example 2).
  3. INDEX the Price column with that position.
  4. Nest them: =INDEX(PriceCol, MATCH(E2, IDCol, 0)).
  5. Press Enter — price appears.
  6. Try when Price sits left of ID — still works.
=INDEX(B2:B6, MATCH(E2, A2:A6, 0))

Language: English · मराठी · हिंदी

Before and after (look at the tables first)

Product ID Price (₹)
P101 40
P102 25
P103 60

Lookup P102 → MATCH returns 2 → INDEX returns 25.

Before INDEX MATCH Product table before INDEX MATCH.

Before

After INDEX MATCH Combined formula returns 25.

After

Why INDEX + MATCH?

  1. Return column can be left of the key (VLOOKUP cannot).
  2. Inserting columns is less scary than a hard-coded VLOOKUP column index.
  3. Still widely asked in Excel interviews.

What each piece does

MATCH

=MATCH(E2, A2:A4, 0)
  1. Finds P102.
  2. Returns position 2 inside A2:A4.
  3. 0 = exact match (like VLOOKUP FALSE).

INDEX

=INDEX(B2:B4, 2)
  1. Goes to position 2 inside B2:B4.
  2. Returns 25.

Combined

=INDEX(B2:B4, MATCH(E2, A2:A4, 0))

What happens step by step

  1. E2 = P102.
  2. MATCH → 2.
  3. INDEX(…, 2) → 25.
  4. Missing ID → #N/A from MATCH.

Vertical practice steps

  1. Build ID + Price table.
  2. In a spare cell show MATCH alone — see the number.
  3. Wrap with INDEX.
  4. Swap columns so Price is left of ID; point INDEX at Price, MATCH at ID.
  5. Compare the same answer with XLOOKUP if you have Microsoft 365.
  6. Write both formulas in your notes.

Ravindra Bagale's Tip

Debug in two cells first: MATCH alone, then INDEX alone with a typed number. Nest only after both pieces behave. Ghan formula ekdum lihila ki confusion vadhate.

Practice task

  1. City in column A, fee in column B — INDEX+MATCH fee for Pune.
  2. Move fee to column left of city — repeat.
  3. Explain in one sentence why VLOOKUP failed on the left layout.

Learn it properly

Course lessons:

Related guides: VLOOKUP exact match · XLOOKUP for beginners · IF function

Samajla ka? MATCH = position, INDEX = value at position. Together = flexible exact lookup. Aata pudhe Remove Duplicates.

Frequently asked questions

Why INDEX+MATCH instead of VLOOKUP?

It can return a column to the left of the key and stays stable if you insert columns carefully.

What does MATCH 0 mean?

Exact match — same idea as VLOOKUP FALSE.

#N/A from MATCH?

Key not found or wrong range. Check the lookup value and the ID list.

Two-way lookup?

MATCH for row and MATCH for column inside INDEX — covered in the course chapter.

XLOOKUP available?

Use XLOOKUP for new work on Microsoft 365; INDEX+MATCH remains widely taught.

Course lesson?

Chapter 4 — INDEX and MATCH.