Having difficulty using XLOOKUP on a Mac

Question

I have a two column list of text values, one for lookup array and one for return array.

If the lookup value is found in the lookup array, the value in the return array is returned, but if not found, it returns a 0, not the value ("not found") that I placed in the function. Sometimes it doesn't return 0 but instead shows the =XLOOKUP....formula.

Am I missing something?

Answer 1

Make sure that the cell with the modified formula is not formatted as Text but as General. Then edit and confirm.

This is what I see:

The formulas in B2:B9 return "not found" because 8574969388000 and 8584061446000 do not occur in column A on he MBPOwned sheet:

Answer 2

I have created a "stripped down" version of the file.

https://1drv.ms/x/s!AtqkRymf79Clo1O3EcgoFGr4msRK?e=dHgrWp

Advise me if you have trouble accessing.

Answer 3

If you wish, you can create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Answer 4

Double checked my data.

The value that I was looking up does not exist in my lookup and return array, so it should not be returning 0.

I think I have more serious issues with my current installation of Excel for Mac, and will reach out to MS Support.

Thanks for your consideration.

Regards.

JP

Answer 5

If the formula is displayed in the cell, that cell might be formatted as Text.

A return value of 0 indicates that XLOOKUP did find a match in lookup_array, but the corresponding value in return_array was blank.

You might try

=XLOOKUP(...)&""

or

=LET(s, XLOOKUP(...), IF(s="", "not found", s))