VLOOKUP vs XLOOKUP: Which Should You Use?

Short answer

Use XLOOKUP if your Excel has it (Microsoft 365, Excel 2021 or later, and Excel for the web, per Microsoft). It looks left or right, defaults to exact match, and handles "not found" by itself. Learn VLOOKUP too, because many Indian offices still run Excel 2016 or 2019, and colleagues' old files are full of it. Interviewers commonly ask both.

I get this question a lot from learners on YUNOVA, so here's the plain version with one example you can try in five minutes.

The example we will use

Sheet Products:

A: Code	B: Product	C: Price
P101	Notebook	45
P102	Pen pack	60
P103	Stapler	120

On your Bill sheet, cell A2 has a product code. You want the price.

VLOOKUP version

=VLOOKUP(A2,Products!A:C,3,FALSE)

• A2 – what to find
• Products!A:C – the table
• 3 – return the 3rd column of that table
• FALSE – exact match

XLOOKUP version

=XLOOKUP(A2,Products!A:A,Products!C:C,"Not found")

• A2 – what to find
• Products!A:A – where to look
• Products!C:C – what to return
• "Not found" – message if no match (optional)

Side-by-side differences

Point	VLOOKUP	XLOOKUP
Default match	Approximate (if you skip the 4th argument)	Exact
Look to the left	No	Yes
Column number	You type it (3), breaks if a column is inserted	You select the return range, so inserting columns is safe
Not-found handling	Needs IFERROR / IFNA	Built-in argument
Search from bottom (latest entry)	No	Yes, search_mode -1
Return several columns at once	No	Yes, select a multi-column return range
Available in	Practically all versions	Microsoft 365, Excel 2021+, web

Mistakes people make with VLOOKUP

1. Forgetting FALSE. Without it, VLOOKUP does an approximate match and can quietly return a wrong price. This is the most dangerous error because nothing looks broken.
2. Lookup column not first. VLOOKUP only searches the left-most column of the table. If code is in column C and name in A, it cannot return the name.
3. Hard-coded column number. Someone inserts a "GST %" column and every 3 now points to the wrong data.
4. Extra spaces and text-numbers. "P101 " with a trailing space will not match "P101". Clean with TRIM. Codes saved as text vs number also fail to match.

Mistakes with XLOOKUP

1. Ranges of different sizes. A2:A100 with C2:C90 gives an error. Keep them equal or use full columns.
2. Sharing files with old-Excel users. The formula shows #NAME? on versions without XLOOKUP. Ask what version your client or manager uses.

What about INDEX + MATCH?

Before XLOOKUP, power users used:

=INDEX(Products!C:C,MATCH(A2,Products!A:A,0))

It looks left, survives inserted columns and works on every version. If you are stuck on Excel 2016, this is the best upgrade over VLOOKUP.

Which should you learn first?

• Students and freshers: learn VLOOKUP first (two days), then XLOOKUP (one day). You will understand both and can handle any office file.
• Working on Microsoft 365 already: write new sheets with XLOOKUP, but be able to read VLOOKUP.
• Interviews: be ready to explain the FALSE argument and the left-lookup limit. That single answer shows you have actually used it.

Quick practice task

1. Make the Products table above with 10 items.
2. On a Bill sheet, type 5 codes, one of them wrong.
3. Get prices with VLOOKUP + IFERROR.
4. Redo with XLOOKUP and its "Not found" argument.
5. Insert a new column between Product and Price. Watch which formula breaks.

That last step teaches the difference better than any explanation.

For the rest of the formulas you will need alongside lookups, see Excel formulas in Hindi.

FAQ

Is XLOOKUP available in Excel 2019?

Microsoft lists XLOOKUP for Microsoft 365, Excel 2021 and later, and Excel for the web. Excel 2019 and 2016 do not have it.

Can XLOOKUP replace HLOOKUP too?

Yes. Give it a row as the lookup and return ranges and it searches horizontally.

Why does my VLOOKUP return the wrong value without any error?

You probably left out the fourth argument, so it did an approximate match. Add FALSE.

Is VLOOKUP still asked in interviews?

Yes, very often for MIS, accounts and back-office roles. See our list of Excel interview questions.

Learn it free on YUNOVA

YUNOVA Excel skill में lookups के अलग lessons हैं: VLOOKUP, XLOOKUP, INDEX-MATCH, with quizzes. Final test pass करने पर आपको verifiable certificate मिलता है।

Learn Excel free on YUNOVA