Skip to content

All-Access: every lesson, no ads. $8.25€8.25US$8.25$4.08US$4.08$2.41US$2.41₹224.91MX$74.91 a month, billed yearly. Free to start. Cancel any time

See plans
All-Access, no ads: every lesson, course and template. $8.25€8.25US$8.25$4.08US$4.08$2.41US$2.41₹224.91MX$74.91 a month. + GST at checkoutSee the plans →

Functions

XLOOKUP vs VLOOKUP: Which to Use, with Examples

By the LogicExcel Editorial Team5 min read968 wordsPractice this

Verdict: use XLOOKUP when everyone who opens the file has Excel 2021 or later, Microsoft 365 or Excel for the web. Use VLOOKUP when the workbook must open in Excel 2019 or older, or when you are keeping a sheet that already works. XLOOKUP is the easier one to get right, because it is exact by default and does not count columns.

Ready to practice?

Apply what you just learned with interactive exercises.

Start Lesson 63

XLOOKUP and VLOOKUP both find a value in one column and return a value from the same row. The difference is in how much they ask of you. This page puts them side by side on one small price list, then shows when each is the better choice.

The short answer

  • XLOOKUP takes the column to search and the column to return as two separate ranges, so it can return a value from the left or the right of the lookup column.
  • VLOOKUP searches the first column of a table and returns a value a counted number of columns to the right. It cannot look left.
  • XLOOKUP is exact by default and has a built-in message for a missing value. VLOOKUP is approximate unless you type FALSE.

Side by side

VLOOKUPXLOOKUP
Syntax=VLOOKUP(lookup, table, col_number, [range_lookup])=XLOOKUP(lookup, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Looks left of the lookup columnNoYes
Match type if you leave the last argument outApproximateExact
Return columnA number you count, which does not follow if you insert a columnA range you select
When nothing matches#N/A, unless you wrap it in IFERRORYour own text in if_not_found
Search from the bottom upNoYes, with search_mode set to -1
Excel versionsAllExcel 2021 or later, Microsoft 365, Excel for the web

The same lookup, both ways

Here is a small price list. The SKU to find, A103, is typed in E2.

ABC
1SKUProductPrice
2A101Notebook4.50
3A102Pen1.20
4A103Stapler8.90
5A104Folder2.10
6A105Scissors6.40
To return the price for the SKU in E2:
=VLOOKUP(E2,A2:C6,3,FALSE)
=XLOOKUP(E2,A2:A6,C2:C6)

Both return 8.90. In the VLOOKUP formula the 3 means the third column of A2:C6 and FALSE asks for an exact match. In the XLOOKUP formula you point at the column to search and the column to return, and exact match is already on.

Now look left: find the SKU for a product name typed in F2.

=XLOOKUP(F2,B2:B6,A2:A6)

VLOOKUP cannot do this one, because SKU is to the left of Product. With VLOOKUP you would have to move the columns or switch to INDEX and MATCH.

To show your own message when the SKU is not on the list:

=XLOOKUP(E2,A2:A6,C2:C6,"Not found")
=IFERROR(VLOOKUP(E2,A2:C6,3,FALSE),"Not found")

Which one should you use?

Choose XLOOKUP for new work, for lookups to the left, for a clear message when a value is missing, and when you want the last match instead of the first (set search_mode to -1).

Choose VLOOKUP when the file has to open in Excel 2019 or older, when you are editing a sheet that already uses it, or when the key is the first column and you want the shorter, familiar formula.

Choose neither when you need a lookup that works in every version and also looks left. That is the job of INDEX and MATCH, compared with XLOOKUP in XLOOKUP vs INDEX MATCH.

Practice the difference

Reading about lookups does not make them stick. In the interactive XLOOKUP lesson you type the formula into a real sheet and it is marked as you go, and the VLOOKUP lesson does the same for the older function. If you want a path rather than one function, the Excel courses start free, and each one ends in a marked exam. More on each function: XLOOKUP and VLOOKUP.

FAQ

Is XLOOKUP better than VLOOKUP?

For most new work, yes. XLOOKUP looks in either direction, is exact by default and has a built-in message for a missing value. VLOOKUP is still the right choice when the workbook has to open in Excel 2019 or older, because XLOOKUP is not available there.

Does XLOOKUP work in Excel 2019?

No. XLOOKUP is in Excel 2021 and later, Microsoft 365 and Excel for the web. In Excel 2019 or older the formula shows a name error, so use VLOOKUP, or INDEX and MATCH, in files that older versions must open.

Can VLOOKUP look to the left?

No. VLOOKUP searches the first column of the table you give it and can only return a value from that column or one to its right. To look left, use XLOOKUP, or combine INDEX and MATCH.

Why does VLOOKUP return the wrong value when I leave out the last argument?

The last argument, range_lookup, is approximate by default. Approximate match expects the first column to be sorted and can return a nearby value instead of an error. Type FALSE (or 0) as the fourth argument to ask for an exact match.

Keep going

Guides people read next.