Excel FormulasVLOOKUP 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.
| 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.
| A | B | C | 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.
| 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.