XLOOKUP Error - Need Help

New Contributor

Hi,

 

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)

 

Where:

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

@ACGG12340 

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.