XLOOKUP lookup value is formula doesn't work

%3CLINGO-SUB%20id%3D%22lingo-sub-1425157%22%20slang%3D%22en-US%22%3EXLOOKUP%20lookup%20value%20is%20formula%20doesn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425157%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI%20have%20an%20xlookup%20formula%20with%20lookup%20value%20to%20a%20cell%20that%20has%20a%20formula%20in%20it.%26nbsp%3B%20The%20xlookup%20returns%20%23N%2FA%20when%20looking%20at%20the%20formula%2C%20but%20if%20I%20type%20a%20value%20in%20the%20cell%20it%20works...%26nbsp%3B%20why%20won't%20the%20xlookup%20work%20when%20lookup%20value%20cell%20contains%20a%20formula%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1425157%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1425167%22%20slang%3D%22en-US%22%3ERe%3A%20XLOOKUP%20lookup%20value%20is%20formula%20doesn't%20work%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1425167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347141%22%20target%3D%22_blank%22%3E%40PeterR_TS%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAre%20the%20values%20really%20identical%3F%20Xlookup%20returns%20%23N%2FA%20if%20the%20value%20cannot%20be%20found.%20If%20you%20manually%20type%20the%20value%20and%20that%20one%20can%20be%20found%2C%20then%20the%20initial%20value%20is%20not%20the%20same%20as%20what%20you%20typed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELeading%2Ftrailing%20blanks%20can%20be%20one%20reason%20for%20the%20problem.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20post%20an%20example%20for%20inspection%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hello

I have an xlookup formula with lookup value to a cell that has a formula in it.  The xlookup returns #N/A when looking at the formula, but if I type a value in the cell it works...  why won't the xlookup work when lookup value cell contains a formula?

2 Replies
Highlighted

@PeterR_TS 

Are the values really identical? Xlookup returns #N/A if the value cannot be found. If you manually type the value and that one can be found, then the initial value is not the same as what you typed.

 

Leading/trailing blanks can be one reason for the problem.

 

Can you post an example for inspection? 

Highlighted

@PeterR_TS 

Better to check the sample, as variant formula could return text "1" and you type number 1; formula could return text with extra space or other symbol "abc " and you type "abc", etc.