FormulaCamp
Completed
All Excel how-to articles
Practical Excel how-to

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

XLOOKUP and VLOOKUP compared
FeatureXLOOKUPVLOOKUP
Lookup directionSearches 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 matchExact match.Approximate match unless the fourth argument is FALSE.
Not-found handlingAn optional fourth argument supplies your own result; otherwise #N/A.Returns #N/A; wrap it in IFNA to replace it.
Column insert safetyThe 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 orderFirst to last by default; it can also search last to first.First match from the top only.
AvailabilityMicrosoft 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.

Example spreadsheet for product codes
ABCDEF
1CodeProductPriceFind productCode
2K-1Kettle30Blender
3K-2Toaster25
4K-3Blender45
Not yet solved.

Worked example

Employees in A1:D5, and a copy in F1:J5 with a Grade column inserted. Each formula is evaluated on this data.

ABCDEFGHIJ
1IDNameDeptSalaryIDNameGradeDeptSalary
2101AnaSales52000101AnaASales52000
3102BoOps48000102BoBOps48000
4103CySales61000103CyASales61000
5104DiHR45000104DiBHR45000
FormulaResult
=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 VLOOKUP argument and trusting an approximate match on unsorted IDs.
  • Typing a column number in VLOOKUP and then inserting columns into the table.
  • Sending a workbook that uses XLOOKUP to someone on Excel 2019 or earlier.
  • Hiding every error with IFERROR instead of handling the expected missing key with IFNA or the XLOOKUP fourth 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