XLOOKUP Error - Need Help

New Contributor



I have two different sheets. On Sheet A I want to look up and return a value from Sheet B. The unique identifier exists within both sheets. I can literally see the value I want to reference in both sheets, but the return value is not returning from Sheet B to Sheet A.


I did a little test where I copied the reference cell in Sheet A and pasted over the existing lookup cell in Sheet B (they were the same exact value), and when I looked back at Sheet A, the formula worked for that particular cell. I'm not sure what's wrong. It seems to be a formatting issue maybe? I removed any formatting on both sheets but still no luck.


My formula looks like this: [for cell Z2]

=XLOOKUP('Sheet A'!J2,'Sheet B'!F:F,'Sheet B'!J:J,"Not Found",0,1)



Sheet A J2 is my reference/lookup value (unique identifier)

Sheet B F:F is the lookup column where the unique identifier exists

Sheet B J:J is the return column that contains the information I want in Sheet A


I've also substituted F:F/J:J for a defined range like F2:F2500/J2:J2500

I've also tried VLOOKUP and INDEX/MATCH--still doesn't work


Any tips will help!

2 Replies


All lookup functions require the same datatype for matching.

Use ISNUMBER() or ISTEXT() to verify that the cells have the same datatype.


@ACGG12340 So I figured it out. The reference values were a series of numbers but I guess the person who created the sheet used the apostrophe (') to prevent Excel from manipulating them as "numbers" (decimals, etc.,). I removed them by highlighting them, going to Data, and then clicking on 'Text to Column'. After that, the formula read everything as it should have.