VLOOKUP vs XLOOKUP: which should you use?
What each one does, where VLOOKUP trips people up, and when the old one is still the right call.
The short answer
Use XLOOKUP if everyone who opens the file has Excel 365 or Excel 2021 or later. It is easier to read, safer by default and does everything VLOOKUP does. Stick with VLOOKUP when the file goes to people on older versions of Excel, because XLOOKUP shows up there as #NAME?.
How VLOOKUP works
VLOOKUP looks for a value in the first column of a table and returns something from the same row, a set number of columns to the right.
=VLOOKUP(E2, A2:C50, 3, FALSE)
This reads: find the code in E2 in the first column of A2:C50, then return the value from the third column of that table. The final FALSE asks for an exact match.
That last argument is where most VLOOKUP mistakes start. Leave it out and Excel assumes TRUE, an approximate match. On a list that is not sorted, an approximate match can quietly return the wrong row with no error at all. Always type FALSE unless you really want the nearest value below.
Two more catches:
- It only looks right. The value you search for must be in the first column. If the code sits to the right of the name you want back, VLOOKUP cannot do it.
- The column number is typed in. Insert a column inside the table and
3now points at the wrong one. The formula does not update itself.
What XLOOKUP fixes
XLOOKUP takes the column to search and the column to return as two separate ranges.
=XLOOKUP(E2, A2:A50, C2:C50)
Find E2 in A2:A50, return the matching entry from C2:C50. Because the two ranges are separate:
- Exact match is the default. No trailing
FALSEto forget. - It can look left. The return column can be anywhere, before or after the search column.
- Inserting columns is safe. The ranges move with the sheet, so there is no column number to go stale.
- You choose what "not found" shows. A fourth argument replaces
#N/A:=XLOOKUP(E2, A2:A50, C2:C50, "Not found"). - It can search from the bottom. Set the sixth argument to
-1to get the last match instead of the first, handy for the latest price or most recent order.
When to stick with VLOOKUP
- Older Excel. Anyone on Excel 2019 or earlier will see
#NAME?instead of your answer. - A workbook full of them. If a model already uses VLOOKUP everywhere and works, rewriting it buys little. Use XLOOKUP for new formulas.
- Interviews and tests. Many Excel tests still ask for VLOOKUP by name, so it is worth knowing both.
If you need to look left on an older version, INDEX with MATCH does the job and works in every version of Excel.
Common questions
Does Google Sheets have XLOOKUP?
Yes. Google Sheets has had XLOOKUP since 2022, with the same arguments as Excel, so the formulas in this article work there too.
Can XLOOKUP return more than one column?
Yes. Give it a return range several columns wide, such as C2:E50, and the results spill across the cells to the right. VLOOKUP needs one formula per column.
How do I do an approximate match with XLOOKUP?
Set the fifth argument, the match mode, to -1 for an exact match or the next smaller value. That is what VLOOKUP's TRUE does for things like tax bands, except XLOOKUP does not need the list sorted.