Oct 13 2020 02:32 PM
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!
Oct 13 2020 02:44 PM - edited Oct 13 2020 02:45 PM
All lookup functions require the same datatype for matching.
Use ISNUMBER() or ISTEXT() to verify that the cells have the same datatype.
Oct 13 2020 02:46 PM
@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.