Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

XLOOKUP lookup value is formula doesn't work

Copper Contributor

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?

9 Replies

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

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

Hi, did you ever solve this? My lookup value references a cell with =RIGHT(A2,6) but XLOOKUP doesn't recognize it. If I hard-code the value the XLOOKUP works.
Thanks,
--sue

@YeahMan  wrote:  ``My lookup value references a cell with =RIGHT(A2,6) but XLOOKUP doesn't recognize it``

 

 

To paraphrase Uncle Ben's advice to Spider-Man:  "With great details come great answers". (wink)

 

And:  "If a picture is worth 1000 words, an Excel file is worth a 1000 pictures". (wink)

 

Translation:  Provide an Excel file that demonstrates the problem.  The devil might be in details that we can see by staring at it.

 

The Excel file should be redacted of any private information.  Ideally, it should be stripped down to a minimum.  If A2 itself contains private information, perhaps you can change some characters while retaining the ability to duplicate the problem.

 

Ideally, attach the Excel file by clicking "browse files" below near Post button.

 

If you cannot (so some people claim), upload the file that demonstrates the problem to a file-sharing website, and post a download URL that does not require that we log in.  I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum.

 

If you can post actual URLs, edit them to fool the forum.  For example, the URL for this thread is techcommunity dot microsoft dot com /t5/excel/xlookup-lookup-value-is-formula-doesn-t-work/td-p/1425157.

 

PS....  In the future, it is better to start a new topic instead of piggybacking an old question, much less a 3-year-old question that was never resolved.  It is unlikely that your circumstances and the previous one are exactly the same.

@Joe UserThanks for replying to your 3 year old message. I managed to get it to work after I posted this message. I had an error in the formula.

What error did you have? I am running into this exact problem. I am referencing a lookup value that contains a formula (=right(D4,5)) and my xlookup is giving me an error. I am not sure how to be certain that it is searching by the actual cell value.

@terraficke 

As variant you lookup numbers on texts (which looks like numbers) returned by =right(D4,5). If so try

=--right(D4,5)

Whatever, it could be lot of other reasons. It's better to give more details starting the new discussion.

@terraficke 

In addition to the thoughts by @Sergei Baklan, since you're using =RIGHT(D4,5) as the reference in a lookup formula, it's also possible that there's a blank after the text in cell D4, not visible to our human eyes, but very visible to Excel. So that =RIGHT("desiredtext ",5) yields "text " when you think it should be "dtext"

 

I would second the suggestion by @Sergei Baklan that you start a new thread if neither his suggestion nor mine resolves your situation.

Hi @terraficke

The error in my formula was that I had the RIGHT incorrectly set up and wasn't grabbing the correct portion of the string.