← All Kits · Excel Kit

XLOOKUP Is Not Available: Why It Is Missing and What to Use Instead

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

You write =XLOOKUP(...), it works, you send the file, and a colleague replies with a screenshot of #NAME? and a formula that reads =_xlfn.XLOOKUP(...). Nothing is broken. Their Excel does not contain that function.

What you do: use INDEX and MATCH for anything that will be opened by somebody whose version you do not control. It does the same jobs and it has worked since the nineties.

The short version. _xlfn. in front of a function name means "this version has never heard of this function".

Which versions have XLOOKUP

To check your own: File, Account, and read the line under About Excel. If it says Microsoft 365, you have it. If it names a year, look that year up in the table.

The same rule applies to the other new functions people hit this with: XMATCH, FILTER, UNIQUE, SORT, SEQUENCE, LET, TEXTSPLIT, TEXTBEFORE. All of them fail the same way on Excel 2019 and earlier, with _xlfn. in front.

The five jobs, translated

The MATCH third argument is the one to be careful with. 0 means exact and is what you want almost always. 1 means largest value less than or equal, requires the list sorted ascending, and is how banded lookups such as tax brackets are built. That case is worked in approximate match lookups.

Sending a file to a mixed-version team

1. Ask which version the recipients run, once, and write it down. It rarely changes.
2. Build with INDEX MATCH if any of them is 2019 or earlier.
3. If the file is a report rather than a model, paste values before sending. Nobody needs your formulas to read a number.
4. Avoid dynamic array functions in shared files entirely. Even where they open, a spilled range behaves differently, and older versions will not spill at all.
5. Test by opening your own file in Excel on the web, which is free and enforces its own function set.

If you have already been sent one

You cannot make the function work, so you have three choices, in order of preference:

1. Ask the sender for a values-only copy. Fastest, and usually all you needed.
2. Open the file in Excel on the web with a free Microsoft account. It supports XLOOKUP and will calculate the results, which you can then copy out.
3. Rewrite the formulas as INDEX MATCH. Find and Replace does most of the work if the pattern is consistent, but check every one: an XLOOKUP with a fourth argument has a not-found behaviour your replacement has to reproduce.

Why INDEX MATCH is worth learning anyway

It is not a downgrade. It looks in any direction, it does not break when a column is inserted in the middle of the table, and the MATCH can be reused: compute the row number once in its own cell and have twelve INDEX formulas read it, which is faster than twelve independent lookups. The full treatment is in INDEX MATCH.

How to apply this to your own work

1. Check File, Account today so you know what you are building on.
2. Ask your two most frequent recipients what version they run.
3. Search your shared workbooks for XLOOKUP, FILTER and UNIQUE, and convert the ones that leave your machine.
4. Keep one scratch sheet with your standard INDEX MATCH pattern in it, so writing it is copy and edit rather than recall.
5. Make paste-values the default for anything you send as a finished report.

The one habit to keep

Build for the oldest version that will open the file. It costs a slightly longer formula and it removes an entire category of "it works on mine" from your week.

Do you know which Excel version the person who opens your files most often is running?