XLOOKUP Error - Need Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1777352%22%20slang%3D%22en-US%22%3EXLOOKUP%20Error%20-%20Need%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1777352%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20different%20sheets.%20On%20Sheet%20A%20I%20want%20to%20look%20up%20and%20return%20a%20value%20from%20Sheet%20B.%20The%20unique%20identifier%20exists%20within%20both%20sheets.%20I%20can%20literally%20see%20the%20value%20I%20want%20to%20reference%20in%20both%20sheets%2C%20but%20the%20return%20value%20is%20not%20returning%20from%20Sheet%20B%20to%20Sheet%20A.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20a%20little%20test%20where%20I%20copied%20the%20reference%20cell%20in%20Sheet%20A%20and%20pasted%20over%20the%20existing%20lookup%20cell%20in%20Sheet%20B%20(they%20were%20the%20same%20exact%20value)%2C%20and%20when%20I%20looked%20back%20at%20Sheet%20A%2C%20the%20formula%20worked%20for%20that%20particular%20cell.%20I'm%20not%20sure%20what's%20wrong.%20It%20seems%20to%20be%20a%20formatting%20issue%20maybe%3F%20I%20removed%20any%20formatting%20on%20both%20sheets%20but%20still%20no%20luck.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20formula%20looks%20like%20this%3A%20%5Bfor%20cell%20Z2%5D%3C%2FP%3E%3CP%3E%3DXLOOKUP('Sheet%20A'!J2%2C'Sheet%20B'!F%3AF%2C'Sheet%20B'!J%3AJ%2C%22Not%20Found%22%2C0%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%3A%3C%2FP%3E%3CP%3ESheet%20A%20J2%20is%20my%20reference%2Flookup%20value%20(unique%20identifier)%3C%2FP%3E%3CP%3ESheet%20B%20F%3AF%20is%20the%20lookup%20column%20where%20the%20unique%20identifier%20exists%3C%2FP%3E%3CP%3ESheet%20B%20J%3AJ%20is%20the%20return%20column%20that%20contains%20the%20information%20I%20want%20in%20Sheet%20A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20also%20substituted%20F%3AF%2FJ%3AJ%20for%20a%20defined%20range%20like%20F2%3AF2500%2FJ2%3AJ2500%3C%2FP%3E%3CP%3EI've%20also%20tried%20VLOOKUP%20and%20INDEX%2FMATCH--still%20doesn't%20work%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20tips%20will%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1777352%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1777369%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20Error%20-%20Need%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1777369%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F831160%22%20target%3D%22_blank%22%3E%40ACGG12340%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20lookup%20functions%20require%20the%20same%20datatype%20for%20matching.%3C%2FP%3E%3CP%3EUse%20ISNUMBER()%20or%20ISTEXT()%20to%20verify%20that%20the%20cells%20have%20the%20same%20datatype.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted

@ACGG12340 

All lookup functions require the same datatype for matching.

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

 

Highlighted

@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.