Stop 02 · Excel & SpreadsheetsTopics · 2/10

Formulas & Functions (VLOOKUP, INDEX-MATCH, XLOOKUP)

In short

You have orders in one sheet and customer cities in another. Lookup functions join them in seconds. It's the formula interviewers ask about most.

Lookup functions let you pull matching data from another table — VLOOKUP is the classic, INDEX-MATCH is more flexible, and XLOOKUP (in modern Excel) is the simpler, more powerful replacement for both.

The jaadu at an enquiry counter pulls one card from a long drawer of index cards.

Think of it like this

The PNR enquiry counter

You give the railway clerk your PNR number. He flips to the matching card and reads out your coach and seat. A lookup does exactly that: take a key, find its row in another table, and bring back the value you asked for.

Watch it happen

XLOOKUP finding C-103's city

C-101 Delhi

C-102 Pune

C-103 Kochi

C-104 Jaipur

1/5The orders sheet has customer ID C-103 but no city. The customers table has both.

The big ideas

What you need to know

VLOOKUP: the classic

Searches the first column of a range and returns a column by number. Breaks if someone inserts a column.

INDEX-MATCH: flexible

MATCH finds the row number, INDEX fetches the value. Works left or right and survives column inserts.

XLOOKUP: the modern default

Simpler syntax, exact match by default and a built-in 'not found' value. Available in Excel 365 and Google Sheets.

Always exact match

VLOOKUP's last argument should be FALSE. Leaving it out can quietly return the wrong row.

See it in code

Walk through the code

excel

1=VLOOKUP(A2, Customers!A:C, 3, FALSE)2 3=INDEX(Customers!C:C, MATCH(A2, Customers!A:A, 0))4 5=XLOOKUP(A2, Customers!A:A, Customers!C:C, "Not found")

1/3VLOOKUP: look for A2 in column A, return the 3rd column, exact match only.

Three ways to fetch the city for the customer ID in A2. All three give Kochi for C-103.

Save yourself the pain

Common mistakes

Oops

Leaving out FALSE in VLOOKUP and trusting the result.

Do this instead

Always use FALSE (or 0) for exact match, or switch to XLOOKUP, which is exact by default.

Oops

Ignoring extra spaces, so 'C-103 ' doesn't match 'C-103'.

Do this instead

Clean keys with TRIM first. Most 'missing' lookups are invisible spaces or text-vs-number mismatches.

Your mission

Join two tables by hand

~25 min

Quick check

Why do many analysts prefer INDEX-MATCH or XLOOKUP over VLOOKUP?

Go deeper

1Practice the same lookup with all 3 functions on the same dataset.
2Learn why VLOOKUP breaks when columns are inserted, and why INDEX-MATCH/XLOOKUP don't.
3Memorize XLOOKUP's argument order — it'll be your default once you know it.