VLOOKUP or XLOOKUP: which should you use?

Excel now has two main lookup functions. VLOOKUP has been in every version for decades. XLOOKUP arrived with Excel 2021 and Microsoft 365 and was designed to fix VLOOKUP's weak points. This page puts them side by side on the same data, so that you can choose with your eyes open.

Sample sheet: A small price list with item codes. The table is sorted by code.

	A	B	C	D	E
1	Code	Item	Category	Price	Stock
2	A101	Basmati Rice 5 kg	Grocery	650	40
3	A102	Toor Dal 1 kg	Grocery	160	75
4	B201	Notebook	Stationery	60	300
5	B202	Ball Pen	Stationery	10	0
6	C301	LED Bulb	Electronics	110	200
7	C302	Power Bank	Electronics	1299	18
8	D401	Steel Bottle	Home	450	55

The same lookup, both ways

Find the price of the item with code C302.

=VLOOKUP("C302",A2:E8,4,FALSE)
Result: 1299
=XLOOKUP("C302",A2:A8,D2:D8)
Result: 1299

VLOOKUP is given the whole table and a column number. XLOOKUP is given the column to search and the column to answer from.

Differences at a glance

	VLOOKUP	XLOOKUP
Default match	approximate, unless you type FALSE	exact
Direction	answer must be to the right	any direction
What you give it	whole table and a column number	search range and answer range
Inserting a column	can break the formula	no effect
Value not found	`#N/A`, needs IFERROR or IFNA	built-in "not found" argument
Last match	not possible	search from the end
Several columns back	one at a time	several at once
Wildcards	always on with exact match	on request, match mode 2
Versions	all	Excel 2021, Microsoft 365

The default match

Leave off VLOOKUP's last argument and it does an approximate match. On an unsorted table that returns wrong answers without any warning. It is the best-known trap in Excel.

=VLOOKUP("B202",A2:E8,2)
Result: Ball Pen
=VLOOKUP("B205",A2:E8,2)
Result: Ball Pen
=XLOOKUP("B205",A2:A8,B2:B8)
Result: #N/A

This table happens to be sorted by code, so the first formula is right. The second asks for B205, a code that does not exist, and VLOOKUP quietly returns the item for the nearest code below it. XLOOKUP, asked the same, reports that the code is missing.

Looking to the left

Find the code of the Notebook. The answer is to the left of the column being searched.

=XLOOKUP("Notebook",B2:B8,A2:A8)
Result: B201
=INDEX(A2:A8,MATCH("Notebook",B2:B8,0))
Result: B201

VLOOKUP cannot do this at all. Before XLOOKUP, the answer was INDEX and MATCH, shown in the second formula, which still works everywhere.

A value that is not found

=IFNA(VLOOKUP("Z999",A2:E8,2,FALSE),"Not found")
Result: Not found
=XLOOKUP("Z999",A2:A8,B2:B8,"Not found")
Result: Not found

Both give a message. XLOOKUP needs no wrapper.

Several answers at once

To bring back the item, category and price for a code, VLOOKUP needs three formulas with three column numbers. XLOOKUP takes a wider answer range and spills the results across.

=XLOOKUP("D401",A2:A8,B2:D8)
Result:
Steel Bottle Home 450

Bands and nearest matches

VLOOKUP's approximate mode needs the table sorted in rising order. XLOOKUP's match mode -1 (next smaller) and 1 (next larger) work on a table in any order.

Sample sheet: A marks table and a grade table. The grade table lists the lowest mark of each grade, in rising order.

	A	B	D	E
1	Name	Marks	From	Grade
2	Asha	91	0	E
3	Ravi	45	33	D
4	Meena	79	50	C
5	Kabir	28	65	B
6	Tara	65	80	A

=VLOOKUP(B4,$D$2:$E$6,2,TRUE)
Result: B
=XLOOKUP(B4,$D$2:$D$6,$E$2:$E$6,,-1)
Result: B

The last match

In a log kept in date order, the latest entry for a person is the last one. XLOOKUP can search from the bottom.

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=VLOOKUP("Asha",B2:F13,5,FALSE)
Result: 1200
=XLOOKUP("Asha",B2:B13,F2:F13,,0,-1)
Result: 3750

VLOOKUP finds Asha's first sale. XLOOKUP, with its last argument set to -1, finds her most recent one.

So which should you use?

Use XLOOKUP when everyone who will open the workbook has Excel 2021 or Microsoft 365. It is safer by default, shorter and more capable.

Use VLOOKUP or INDEX and MATCH when the file may be opened in Excel 2019, 2016 or earlier. There, an XLOOKUP formula shows #NAME?, and the person receiving the file cannot fix it without rewriting it. In offices with mixed versions this is the deciding point. Check which versions your colleagues, clients and accountant use before changing over.

Keep reading VLOOKUP either way. Millions of existing workbooks use it, and you will meet it for years.

Turning a VLOOKUP into an XLOOKUP

Take =VLOOKUP(G2, $A$2:$E$8, 4, FALSE).

The lookup value stays: G2.
The search range is the first column of the old table: $A$2:$A$8.
The answer range is the column that the number pointed to. Column 4 of a table starting at A is column D: $D$2:$D$8.
Drop the FALSE. Exact match is the default.

Result: =XLOOKUP(G2, $A$2:$A$8, $D$2:$D$8).

If the old formula ended in TRUE or had no fourth argument, it was an approximate match. Add match mode -1: =XLOOKUP(G2, $A$2:$A$8, $D$2:$D$8, , -1).

Habits that help with both

Lock the ranges with dollar signs before copying the formula down.
Make sure both sides are the same type: numbers with numbers, text with text.
Clean stray spaces with TRIM.
Remember that both return the first match. For every match, use FILTER.

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.