XLOOKUP function returning #VALUE inaccurately.

Question

I am running macOS Sonoma and excel version 16.78. Recently my Excel has stopped performing the xlookup function properly and returns #VALUE when it shouldn't. All other functions seem to be working properly. I have already tried the following things... (1) Ensuring that the lookup value is present in the lookup array (and formatted the same), even in some cases referencing the lookup value inside the lookup array to ensure there is a match. (2) Ensuring that the lookup array is the same size as the return array. (3) Trying multiple worksheets, workbooks, and versions of Excel. I tried the web and the desktop version of Excel... Same result.

*Note I was unable to attach screenshots as the 'File/s failed to upload' in this query box... I tried lowering the quality and changing the file type to no avail.

Answer 1

It seems you may have been misinformed about how XLOOKUP works.

In your first example the 2nd argument refers to an array which isn't shown [D5:E6]

In the 2nd example your first argument is a cell [G5] which is contained in the array referred to in the 2nd argument [G5:H6]

In the 3rd example you've specified the entire data range rather than just the column to be searched

Have a look at the information provided in the Microsoft Support article XLOOKUP function

Answer 2

=xlookup(g5,g5:g9,h5:h9)

Answer 3

Here are three examples - there are no spaces and all data is formatted as 'General'.

Answer 4

Please post a copy of the formula as well as some sample data. You can copy and paste a range into the forum message or paste an image of the range.

Thanks

Answer 5

Could you 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.