#NAME? Excel error only occurring on one computer

We are using Excel 365. I was teaching my colleague XLOOKUP yesterday but she kept getting #NAME?.

What she entered was correct. She redid it in a new column in case any odd formatting was causing issues but the error occurred again. To speed things up I did the lookup on my PC and it worked fine. When I emailed her the document she opened it and my perfect results turned to #NAME?.

I'm aware of the option to hide/display formulae but what setting could have been turned on her machine to turn a perfect XLOOKUP into a #NAME? error just be opening a document? That's the most baffling thing I've seen.

2 Answers

XLOOKUP is only supported in the following versions

Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016, Excel for iPad, Excel for iPhone, Excel for Android tablets & Excel for Android phones

If you're using a different version of Excel, then XLOOKUP isn't available.

Oddly, Microsoft then go on to say

Note: XLOOKUP is not available in Excel 2016 and Excel 2019, however, you may come across a situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it created by someone else using a newer version of Excel.

Another couple surefire tests of whether this is it or not are:

1. Have her begin typing XLOO... in a cell while watching the Formula Editor. If the function IS available to her, Intellisense will kick in. If not, the cell will just be taking it as text, not recognizing a function name is being typed (so no Intellisense).

2. Send her a file with a single formula, the XLKOOKUP() formula in cell A1. Set a value in B1 and another in C1. Have the function lookup the B1 value, in B1 (can't fail, eh?), and return C1's content. If she has the function or not, when she opens the spreadsheet she should see one of two formulas in cell A1. Either way the result in A1 will be the C1 cell's contents. She may see:

   a. The exact formula you typed. As typed, no changes, nothing unusual. In that case she has the function available.

   b. A modified formula with xlfn. preceding the function name. In this case she does NOT have the function available.

Note that the instant she recalculates cell A1, however amongst the many ways that could happen (the most obvious being pressing F2, then Enter), the value in A1 will change to the error. If the cell is never recalculated, it will not, but that'd be mostly pointless...

Other ways to tell surely exist too. The first one above is probably the quickest and best way to tell.

The mention MS makes about it being in a spreadsheet created by someone with a more recent version of Excel refers to the second way above. It has been so for decades and the modified function (the preceding xlfn.) has been their process for the same time.

(Interestingly, they do the xl plus two more letters for other similar issues as well. Recently the site here saw a question that related (probably) to Data Types and using a slightly different xlxx. prefix. Since the xl part seems to clearly relate to Excel (=xl), the implied thought is that other of their programs use different first two letters for similar things, when applicable, followed by two "explanatory" letters for the exact item of concern. One could picture that, for example, with the functions available in Word Fields.)