Docs · guide

XLOOKUP: the six arguments, and when VLOOKUP is still right

By Alberto Gulotta · Updated · 13 min read

XLOOKUP answers the same question as VLOOKUP — find this, bring back that — and removes the four things that make the older function go wrong quietly. The shortest useful version: three required arguments, an exact match by default, and it can look to the left. The one thing to check before you switch a shared file over is which version of Excel will open it.

XLOOKUP and VLOOKUP on the four things that go wrong, from Microsoft’s own function pages
The situationVLOOKUPXLOOKUPSource
The answer is in a column to the left of what you search Cannot: your lookup value “must be in the first column of the range” Works — “regardless of which side the return column is on” Microsoft
Somebody inserts a column in the middle of the table Silently wrong: col_index_num is “the column number (starting with 1…)”, and the number does not move Unaffected: you hand it a return range, which moves with the column Microsoft
You forget the last argument Approximate match — “this is the default method if you don’t specify one” Exact match — “0 — Exact match… This is the default” Microsoft
The value is not there #N/A, and you wrap the whole formula in IFERROR [if_not_found] is the fourth argument: put the message in the formula Microsoft
The three required arguments of XLOOKUP in Excel: the value to find, the column to search and the column to return
Three arguments do the work; the other three remove the workarounds. Figure drawn by AI Tools Primer.
  1. Click the cell where the answer should appear and type =XLOOKUP(. Three arguments are required; the other three are optional and each one replaces a workaround.
  2. Give it the value to find. Microsoft calls this lookup_value: “the value to search for”. A cell reference is better than a typed string, because then the formula answers a different question when you change the cell.
  3. Give it the column to search in — lookup_array, “the array or range to search”. One column, not the whole table. This is the first real difference from VLOOKUP, which wants a block and insists the search column is its leftmost one.
  4. Give it the column to bring back — return_array. Another single column, anywhere in the sheet, left or right of the first. It has to be the same height.
  5. Add the message for when there is no match. The fourth argument: “where a valid match is not found, return the [if_not_found] text you supply”. Leave it out and you get #N/A.
  6. Press Return, then test it on a value you know is missing. A lookup that has never been shown a missing value has not been tested — that is the case it will meet on somebody else’s data.
Table comparing XLOOKUP and VLOOKUP in Excel on looking left, inserting a column, the default match and a missing value
Four situations, and where the two functions part company. Figure drawn by AI Tools Primer.

The six arguments, and what each one is for

Microsoft’s syntax line is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The first three are required. The three in brackets are the ones that make the function worth changing over to.

lookup_valueWhat you are looking for. “The value to search for”. If you omit it, XLOOKUP “returns blank cells it finds”.
lookup_arrayWhere to look. “The array or range to search” — one column or one row.
return_arrayWhat to bring back. “The array or range to return”. Same shape as the one you searched.
[if_not_found]What to say when there is nothing. Without it, “#N/A is returned”. With it, a sentence a colleague can act on.
[match_mode]How close counts. 0 exact, the default; -1 exact or the next smaller item; 1 exact or the next larger; 2 a wildcard match “where *, ? and ~ have special meaning”.
[search_mode]Which end to start from. 1 from the first item, the default; -1 “a reverse search starting at the last item”, which is how you get the most recent match instead of the oldest; 2 and -2 are binary searches that require a sorted list, and Microsoft is blunt about the cost: “if not sorted, invalid results will be returned”.

When XLOOKUP, and when the old one is still the right answer

This is not a case of one function being better. It is a case of two functions with different failure modes, and of one of them not existing on some of the computers your file will be opened on.

Use XLOOKUP when the table is going to be edited by other people

The column-number argument is the part of VLOOKUP that breaks without saying anything. Microsoft calls it “the column number”, counted “starting with 1 for the left-most column” of the table, so it describes a position rather than a column. Insert a column, and every VLOOKUP pointing past it now returns the wrong field — a real value, in the right shape, from the wrong place. XLOOKUP takes a range instead, and a range follows its column when the sheet is rearranged.

Use XLOOKUP when the identifier is not in the first column

VLOOKUP’s rule is stated as a requirement: the lookup value “must be in the first column of the range of cells you specify”. Real tables are not built that way — the name is often left of the ID, the ID left of the code. XLOOKUP works “regardless of which side the return column is on”, and that removes the whole habit of duplicating a column just to make a lookup possible.

Keep VLOOKUP when the file has to open on Excel 2019 or 2016

Microsoft is explicit: “XLOOKUP is not available in Excel 2016 and Excel 2019.” And it names the situation people actually meet: “you may come across a situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it, if it was created by someone else using a newer version of Excel.” If the workbook is going to a client, a finance department or a school, the older function is the compatible one.

Either, when the lookup is a throwaway

For a one-off answer in a sheet nobody else will open, the differences above cost nothing either way. The argument for changing habit is about files that outlive the afternoon.

Flow of when to use XLOOKUP and when to keep VLOOKUP in Excel, ending with the Excel 2019 compatibility check
The last box is the compatibility check: a file that must open in Excel 2019 keeps VLOOKUP. Figure drawn by AI Tools Primer.

The error that means “this file has been on a newer Excel”

If you open a workbook and a cell shows _xlfn.XLOOKUP with a #NAME? error beside it, nothing is corrupted and nothing needs repairing. That prefix is how Excel writes down a function it does not have: the file was built somewhere with XLOOKUP and opened somewhere without it. Microsoft describes exactly this situation on the function’s own page.

What to do depends on who owns the file. If it is yours and it has to keep working on the old version, rewrite the lookup with VLOOKUP or with INDEX and MATCH. If it came from somebody else, the useful message back is not that it is broken but that your Excel is 2019 and does not have XLOOKUP, which tells them what to change in one line.

And do not save over it first. Saving in the old version can turn a formula it cannot evaluate into something it cannot recover, so the copy you are looking at is worth keeping until the question is settled.

The failure that has no error message. Of the four differences above, only one produces a visible mistake. A missing value gives #N/A and you see it; an approximate match on an unsorted column gives a plausible wrong answer and you do not. That is the case for the exact match being the default rather than something you remember to ask for, and it is the difference that matters most on a sheet that somebody else will read as a number rather than as a formula.

Two columns of the same height. XLOOKUP works by lining up two ranges, so if the column you search is 500 rows and the column you return is 499, the result is wrong or an error depending on how the ranges were written. Selecting whole columns avoids the arithmetic, at the cost of asking Excel to consider a million rows; on a table inside a defined Excel Table, using the table’s own column names solves both problems at once, because the names grow with the data.

It is in Google Sheets too, with the same six arguments. Google added XLOOKUP to Sheets and documents it with the same argument order, so a formula written in one usually survives the trip to the other. What does not survive is the surrounding sheet: named ranges, table references and anything volatile behave differently, so the safe check is to open the converted file and look at the cells that should say “not found”.

When neither function is the right tool. If you are matching on two things at once — a part number and a warehouse — a lookup on a single column cannot express the question. One way out is a helper column that joins the two values; another is a pivot table, which answers “what is the number for this combination” without a formula at all. Both are better than a lookup that is right most of the time.

The syntax, the argument descriptions, the default behaviours and the sentence about Excel 2016 and 2019 are quoted from Microsoft’s own function pages for XLOOKUP and VLOOKUP, read on 4 September 2026 and listed below. The four-case comparison and the advice on testing a missing value are ours.

Where to start

Four ways in.

“I just want the formula.”
Six steps, above — then the six arguments
“Should I stop using VLOOKUP?”
Four cases, in when to switch
“It says #NAME? on my colleague’s PC.”
That is the version — see the version problem
“It returns the wrong row.”
Almost always the match mode — the six arguments

The six arguments

Three do the work and three remove a workaround each. The last two are where a lookup stops being approximately right.

VLOOKUP in ExcelThe older function, its column-number argument, and the default that catches people out.Open this guide → Pivot tables in Google SheetsWhen the question has two conditions, a lookup is the wrong shape and this is the right one.Open this guide → Percentages in ExcelThe four formulas, and why the same 10 can mean 10% or 1000%.Open this guide →

When to switch

The argument for changing habit is about files that outlive the afternoon: shared sheets, sheets other people edit, sheets read as numbers rather than formulas.

Removing duplicates in ExcelThree tools and only one deletes, and what Excel actually counts as a duplicate.Open this guide → Comparing two Excel filesWhat changed between two versions of the same workbook, without reading both.Open this guide → Lock cells in ExcelEvery cell is already locked, so the real job is unlocking the ones people should edit.Open this guide →

The version problem

A formula that exists on your computer and not on theirs is a failure that only appears after the file has been sent.

Merging cells in ExcelMicrosoft says merging deletes the other cells, and merged blocks break everything downstream.Open this guide → Recovering an unsaved Excel fileTwo lists, one path, and the setting that decides whether there is anything to recover.Open this guide → Freezing rows in ExcelOne command, and why it looks like it did nothing the first time.Open this guide →

Where the answer goes next

A lookup usually feeds something else. Each step out of the cell is a chance for a right number to be read wrongly.

Bar graphs in ExcelClustered, stacked and 100% stacked answer three different questions, and only one is comparable.Open this guide → Line graphs in ExcelMicrosoft’s one-line rule for when it should be a scatter chart instead.Open this guide → Mail merge from ExcelThe spreadsheet becomes letters, and the columns decide what can be said.Open this guide →

Questions people also ask

What does XLOOKUP do in Excel?

It finds a value in one column and brings back the matching item from another. Microsoft: it lets you “look in one column for a search term and return a result from the same row in another column, regardless of which side the return column is on”.

Is XLOOKUP better than VLOOKUP?

On four specific things, yes: it can look leftwards, it survives an inserted column, it defaults to an exact match, and it has a built-in message for a missing value. On compatibility, no — VLOOKUP runs everywhere and XLOOKUP does not.

Why does my XLOOKUP show #NAME? or _xlfn.XLOOKUP?

Because that copy of Excel does not have the function. Microsoft states that “XLOOKUP is not available in Excel 2016 and Excel 2019”, and the _xlfn. prefix is how a file records a function the program cannot find.

How do I stop XLOOKUP returning #N/A?

Use the fourth argument rather than wrapping the formula. “Where a valid match is not found, return the [if_not_found] text you supply” — so the sheet can say “not in the price list” instead of an error code.

Can XLOOKUP find the last match instead of the first?

Yes, with the sixth argument. search_mode set to -1 performs “a reverse search starting at the last item”, which is how you get the most recent entry when a value appears more than once.

Not covered here. It will not tell you to replace every VLOOKUP in every file. On a workbook that has to open in Excel 2019, that advice produces a broken sheet.

It will not cover INDEX and MATCH, which solves the same two problems in an older way and deserves its own page.

And it will not go near LOOKUP, the original, which Microsoft itself steers people away from. What holds instead is simple: the syntax, the defaults and the compatibility note are quoted from Microsoft’s own function pages, with the date each was read.

Sources

  1. Microsoft Support — XLOOKUP function: the six arguments, the match and search modes, and the note that XLOOKUP is not available in Excel 2016 and Excel 2019 — support.microsoft.com, read 4 September 2026.
  2. Microsoft Support — VLOOKUP function: the requirement that the lookup value sit in the first column, the col_index_num argument, and the approximate match default — support.microsoft.com, read 6 September 2026.
  3. Exceljet — Excel XLOOKUP function: worked examples of the optional arguments, read as a second and independent reading of the same behaviour — exceljet.net, read 4 September 2026.

Written by Alberto Gulotta

Founder and editor of AI Tools Primer, writing from Palermo, Italy. Thirty-five years of taking computers apart, starting with a Commodore 64 — the long version is on the about page.

Something wrong on this page? Write to aitoolsprimer@gmail.com and it gets fixed.

Written on 4 September 2026 · last checked 7 September 2026.

Independence and limits

No affiliate links and no paid placements anywhere on this site. Nobody pays to appear here, and no company has seen this page before you did.

This is general information, not professional advice. Where a page touches money, health, safety or the law, it names its source and the date it was read — and your situation may still differ. See the privacy page and the cookie policy.