XLOOKUP vs VLOOKUP vs INDEX MATCH: Which Should You Use?

If you work in Excel, you look things up all day: a price from a product code, a manager from an employee ID, a region from a pin code. There are three main ways to do it, and the XLOOKUP vs VLOOKUP question comes up in almost every office. INDEX MATCH sits in between, older than XLOOKUP but much more flexible than VLOOKUP.

This guide shows how each function works on the same small table, where each one breaks and a simple rule for choosing. It is the kind of thing we drill early in the Advanced Excel course, because lookups sit underneath almost every report.

Quick answer: Use XLOOKUP whenever everyone who opens the file has Microsoft 365, Excel 2021 or later: it is exact by default, looks in any direction and handles missing values cleanly. Use INDEX MATCH when the workbook must work in Excel 2019 or older, or when you inherit files built on it. Keep VLOOKUP for quick, simple lookups in older files, and always end it with FALSE.

The example table

All the formulas below use this product list in cells A1:D6, with the item you are searching for typed in G2.

	A: Code	B: Product	C: Price (₹)	D: Stock
2	P101	Steel bottle	450	120
3	P102	Lunch box	699	45
4	P103	Desk lamp	1,299	18
5	P104	Notebook set	249	300
6	P105	Backpack	1,850	0

The task: type a code into G2 and return the price.

How VLOOKUP works

=VLOOKUP(G2, A2:D6, 3, FALSE)

VLOOKUP searches the first column of the range for G2, then moves across to column 3 of that range and returns the value. The last argument, FALSE, asks for an exact match.

Where VLOOKUP breaks

• The default is approximate match. Leave out FALSE and Excel assumes TRUE. On unsorted data that can quietly return a wrong row instead of an error. This is the most expensive Excel mistake we see in real reports.
• It can’t look left. The lookup column must be the first column of the range. If you have the product name and want the code, VLOOKUP can’t do it without rearranging the sheet.
• The column number is hard-coded. Insert a new column between A and C and “3” now points at the wrong data.
• One value at a time. You need a separate formula for price and for stock.

VLOOKUP is still fine for a quick check in a small, stable sheet. It is risky in reports other people will edit.

How INDEX MATCH works

=INDEX(C2:C6, MATCH(G2, A2:A6, 0))

Read it from the inside out. MATCH finds the position of G2 in A2:A6 (for P103, that is 3). INDEX then returns the third value from C2:C6. The 0 in MATCH means exact match.

Why people switched to INDEX MATCH

• It looks in any direction. The lookup column and the return column are separate ranges, so the return column can be to the left.
• Inserting columns doesn’t break it, because it points at ranges, not a column number.
• It works in every Excel version, which still matters in many offices.

The two-way lookup

INDEX MATCH really shines when you need a row and a column at the same time, for example the value in a sales grid for a given product and month:

=INDEX(B2:M20, MATCH(G2, A2:A20, 0), MATCH(H1, B1:M1, 0))

The first MATCH finds the row and the second finds the column. Once you understand this pattern, a lot of “impossible” Excel problems become simple.

The downside is readability. A long INDEX MATCH with two MATCHes is hard for a colleague to audit, and it is easy to misalign the ranges.

How XLOOKUP works

=XLOOKUP(G2, A2:A6, C2:C6, "Not found")

You give XLOOKUP the value, the column to search and the column to return. The fourth argument is what to show if nothing matches. According to Microsoft’s XLOOKUP reference, the match mode defaults to exact and the search runs from the first item to the last.

What XLOOKUP adds

• Exact match by default, so the classic VLOOKUP trap is gone.
• Built-in “not found” handling, with no need to wrap the formula in IFERROR (which can also hide real errors).
• Return several columns at once. =XLOOKUP(G2, A2:A6, B2:D6) spills the product, price and stock across three cells.
• Search from the bottom. Setting search_mode to -1 returns the last match, ideal for “latest price” or “most recent order”.
• Approximate matches without sorting. match_mode -1 returns the next smaller item and 1 the next larger, useful for commission bands or discount slabs.
• Wildcards with match_mode 2, for partial text such as "*lamp*".

Two-way lookup with XLOOKUP

You can nest one XLOOKUP inside another:

=XLOOKUP(G2, A2:A20, XLOOKUP(H1, B1:M1, B2:M20))

The inner XLOOKUP returns the whole month column, and the outer one picks the product’s row from it. Many people find this easier to read than INDEX MATCH MATCH.

XLOOKUP vs VLOOKUP vs INDEX MATCH, side by side

Feature	VLOOKUP	INDEX MATCH	XLOOKUP
Default match	Approximate	Set by you (use 0)	Exact
Look left	No	Yes	Yes
Safe when columns are inserted	No	Yes	Yes
Built-in “not found” value	No	No	Yes
Return several columns at once	No	Not directly	Yes (spills)
Find the last match	No	Awkward	Yes (search_mode -1)
Two-way lookup	Clumsy	Yes	Yes (nested)
Works in Excel 2016/2019	Yes	Yes	No
Easy for others to read	Fairly	Harder	Yes

Version problems you need to know about

The one real argument against XLOOKUP is compatibility. Microsoft says XLOOKUP is not available in Excel 2016 or Excel 2019. Someone using those versions can open your file, but the XLOOKUP formulas won’t recalculate properly and may show #NAME? errors when they refresh.

Before you choose, ask two questions:

1. Who opens this file? If it goes to a client, a bank or a government portal running old Excel, use INDEX MATCH.
2. Is it a living report or a one-off? Living reports that others edit benefit most from XLOOKUP’s safety.

If your company is fully on Microsoft 365, there is very little reason to write new VLOOKUPs.

Common lookup errors (and fixes)

• #N/A when the value is clearly there. Usually extra spaces or a number stored as text. Clean with TRIM, or convert text numbers with VALUE, ideally in Power Query before the data reaches your formulas.
• The wrong value, with no error. Almost always a VLOOKUP without FALSE, or an approximate XLOOKUP on the wrong match mode.
• Duplicates in the lookup column. All three functions return the first match only (XLOOKUP can return the last). If codes repeat, fix the data or use FILTER to return every match.
• Slow workbooks. Whole-column references such as A:A across thousands of formulas add up. Convert data to an Excel Table (Ctrl+T) and use structured references like Products[Code].

Which should you use? A simple rule

• Building something new on Microsoft 365, Excel 2021 or later? XLOOKUP.
• Sharing with people on Excel 2019 or older? INDEX MATCH.
• Editing an old workbook full of VLOOKUPs? Leave working formulas alone unless they are causing errors; fix the risky ones (missing FALSE, hard-coded column numbers) first.
• Need many columns back for one key? XLOOKUP returning a range.
• Need every match, not just one? FILTER.

Lookups are also where many people realise they should be joining tables properly. In Power Pivot and Power BI, you link tables with relationships instead of writing thousands of lookups. Our Power BI roadmap explains that next step.

Practise it in 15 minutes

Rebuild the product table above and try these:

1. Return the price for P103 with all three functions.
2. Return the code for “Desk lamp” (a left lookup). Notice VLOOKUP can’t.
3. Return product, price and stock in one XLOOKUP.
4. Add a second “P102” row with a new price and return the latest one with search_mode -1.
5. Insert a column between A and B and see which formulas still work.

Ten minutes of breaking formulas on purpose teaches more than an hour of watching videos. For the bigger picture of what to learn after lookups, see our guide to advanced Excel skills employers want.

Learn it with Omebly

Omebly Academy’s Advanced Excel course covers formula mastery, XLOOKUP and dynamic arrays in month one, then pivot tables, Power Pivot and Power Query, and finally dashboards, financial models and VBA macros. Classes are live and mentor-led, with recordings for revision and weekly one-to-one reviews of your own workbooks.

• Core (3 months, $699): formulas and lookups, pivots, Power Query, dashboards and VBA.
• Professional (6 months, $1,199): adds advanced modelling, scenario analysis, Office Scripts and Power Automate.
• Career (12 months, $1,999): adds SQL and Power BI for Excel users and a live client-style reporting project.

You get an Omebly certificate of completion with your projects listed. Placement assistance means CV and LinkedIn help, mock interviews, portfolio review and referrals where possible; it is not a job guarantee. Browse every programme on the Omebly Academy page.

Tired of fixing broken VLOOKUPs? Contact us or message us on WhatsApp to book a free call.