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?
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.
JoeUser2004Thanks 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.
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?
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.
