XLOOKUP vs VLOOKUP
XLOOKUP vs VLOOKUP in Excel: compare direction, default match, not-found handling, column inserts, and version support with checked examples.
XLOOKUP vs VLOOKUP at a glance
| Feature | XLOOKUP | VLOOKUP |
|---|---|---|
| Lookup direction | Searches one column or row and returns from any other, left or right. | Searches the first column of the table only and returns from that column or one to its right. |
| Default match | Exact match. | Approximate match unless the fourth argument is FALSE. |
| Not-found handling | An optional fourth argument supplies your own result; otherwise #N/A. | Returns #N/A; wrap it in IFNA to replace it. |
| Column insert safety | The return range is a normal reference and moves with the data. | The column number is typed, so inserting a column inside the table can shift what it returns. |
| Search order | First to last by default; it can also search last to first. | First match from the top only. |
| Availability | Microsoft 365, Excel 2021 and later, and Excel for the web. Not in Excel 2019 or earlier. | All desktop versions of Excel and Excel for the web. |
What each function asks for
Both functions find a key in one column and return a related value from the same row. VLOOKUP takes a whole table and a column number: =VLOOKUP(103,A2:D5,2,FALSE) finds 103 in the first column of A2:D5 and returns the second column, "Cy". XLOOKUP takes the lookup column and the return column as separate arguments: =XLOOKUP(103,A2:A5,B2:B5) returns the same name.
On a small table the two formulas look alike, but their arguments mean different things, and that is where the practical differences come from. The sample table in the worked example has employee IDs in A, names in B, departments in C, and salaries in D. A second copy in F:J has a Grade column inserted, to show what happens when the layout changes.
Lookup direction
VLOOKUP can only search the first column of the table you give it, and it can only return from columns to the right. =VLOOKUP("Di",A2:D5,1,FALSE) returns #N/A because "Di" is a name, not an ID in column A. To find an ID from a name you would have to move the name column to the left of the ID column.
XLOOKUP has no such restriction: =XLOOKUP("Di",B2:B5,A2:A5) searches the names and returns the ID 104 from the column on the left. If you need a leftward lookup but must stay compatible with older versions, combine INDEX and MATCH, which work everywhere.
Default match and not-found handling
Exact matching is the default for XLOOKUP. VLOOKUP falls back to approximate matching when the fourth argument is left out. Approximate matching assumes the first column is sorted ascending and returns the closest smaller value, so a wrong key can still produce a believable row: =VLOOKUP(102.5,A2:D5,2) returns "Bo", the name for 102, although 102.5 is not an ID. The same key in =XLOOKUP(102.5,A2:A5,B2:B5,"Not found") returns "Not found". With VLOOKUP, type FALSE as the fourth argument for IDs, names, and codes, and keep approximate lookups for sorted bands such as tax rates or grades.
For a missing key, XLOOKUP accepts a fourth argument that is returned when nothing matches; =XLOOKUP(999,A2:A5,B2:B5,"Not found") returns "Not found". VLOOKUP has no such argument, so wrap it: =IFNA(VLOOKUP(999,A2:D5,2,FALSE),"Not found"). Prefer IFNA to IFERROR here, because IFERROR would also hide a mistake such as a wrong column number.
What happens when columns are inserted
In VLOOKUP the column number is a typed constant. If someone inserts a column inside the table, the range stretches to include it but the number stays the same, so the formula quietly returns a different column. In the copy in F:J, a Grade column was inserted, and =VLOOKUP(103,F2:J5,4,FALSE) now returns "Sales", the department, instead of the salary it used to return.
In XLOOKUP the return range is an ordinary reference, so Excel moves it with the data: =XLOOKUP(103,F2:F5,J2:J5) still returns 61000. This is the most common reason people move a shared workbook from VLOOKUP to XLOOKUP.
Which Excel versions have XLOOKUP
XLOOKUP is available in Microsoft 365, Excel 2021 and later, and Excel for the web. Excel 2019 and earlier do not include it, while VLOOKUP works in all of them. A workbook that contains XLOOKUP and is opened in a version without it can show #NAME? once it recalculates, so check what your colleagues, clients, or school computers run before relying on the newer function.
When VLOOKUP still makes sense
Keep VLOOKUP when the file must work in Excel 2019 or earlier, when you maintain a tested workbook whose layout is not changing, or when a course or interview expects it. It remains perfectly adequate for a key in the first column of a stable table, as long as the fourth argument is FALSE. Do not rewrite working formulas for style; rewrite them when a change request runs into one of the weaknesses above.
For everything new that stays in Microsoft 365 or Excel 2021 and later, start with XLOOKUP. Practice both in the live spreadsheet below, then continue with the lookup lessons.
Your turn
In F2, return the code for the product named in E2. The code is left of the product name, so use XLOOKUP and return "None" if the product is missing.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Find product | Code | |
| 2 | K-1 | Kettle | 30 | Blender | ||
| 3 | K-2 | Toaster | 25 | |||
| 4 | K-3 | Blender | 45 |
Answer: =XLOOKUP(E2,B2:B4,A2:A4,"None")
Worked example
Employees in A1:D5, and a copy in F1:J5 with a Grade column inserted. Each formula is evaluated on this data.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | ID | Name | Dept | Salary | ID | Name | Grade | Dept | Salary | |
| 2 | 101 | Ana | Sales | 52000 | 101 | Ana | A | Sales | 52000 | |
| 3 | 102 | Bo | Ops | 48000 | 102 | Bo | B | Ops | 48000 | |
| 4 | 103 | Cy | Sales | 61000 | 103 | Cy | A | Sales | 61000 | |
| 5 | 104 | Di | HR | 45000 | 104 | Di | B | HR | 45000 |
| Formula | Result |
|---|---|
=VLOOKUP(103,A2:D5,2,FALSE) | "Cy" |
=XLOOKUP(103,A2:A5,B2:B5) | "Cy" |
=VLOOKUP("Di",A2:D5,1,FALSE) | #N/A |
=XLOOKUP("Di",B2:B5,A2:A5) | 104 |
=VLOOKUP(102.5,A2:D5,2) | "Bo" |
=XLOOKUP(102.5,A2:A5,B2:B5,"Not found") | "Not found" |
=XLOOKUP(999,A2:A5,B2:B5,"Not found") | "Not found" |
=IFNA(VLOOKUP(999,A2:D5,2,FALSE),"Not found") | "Not found" |
=VLOOKUP(103,F2:J5,4,FALSE) | "Sales" |
=XLOOKUP(103,F2:F5,J2:J5) | 61000 |
Quick mistake checklist
- Leaving out the fourth
VLOOKUPargument and trusting an approximate match on unsorted IDs. - Typing a column number in
VLOOKUPand then inserting columns into the table. - Sending a workbook that uses
XLOOKUPto someone on Excel 2019 or earlier. - Hiding every error with
IFERRORinstead of handling the expected missing key withIFNAor theXLOOKUPfourth argument.
Frequently asked questions
Is XLOOKUP better than VLOOKUP?
XLOOKUP is better for most new work: it searches in any direction, matches exactly by default, and has a built-in not-found value. VLOOKUP is still the safer choice when the file must open in Excel 2019 or earlier.
Does XLOOKUP work in Excel 2019?
No. XLOOKUP is available in Microsoft 365, Excel 2021 and later, and Excel for the web. In Excel 2019 or earlier use VLOOKUP, or INDEX with MATCH.
Can I replace VLOOKUP with XLOOKUP?
Yes, if everyone who opens the file has a version with XLOOKUP. Turn =VLOOKUP(103,A2:D5,2,FALSE) into =XLOOKUP(103,A2:A5,B2:B5): the lookup column and the return column become separate arguments.
Why did VLOOKUP return the wrong column after I inserted a column?
The column number in VLOOKUP does not move when a column is inserted inside the table. Recount it, or switch to XLOOKUP, whose return range adjusts with the data.
Practice the skill
Continue with a checked FormulaCamp lesson: XLOOKUP Free
Open the free lesson