Forum Discussion

PeterR_TS's avatar
PeterR_TS
Copper Contributor
May 28, 2020

XLOOKUP lookup value is formula doesn't work

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 ...
excel
Formulas and Functions
office 365
YeahMan's avatar
YeahMan
Brass Contributor
Mar 10, 2023

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.

terraficke's avatar
terraficke
Copper Contributor
Aug 09, 2023
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.
  • YeahMan's avatar
    YeahMan
    Brass Contributor
    Aug 14, 2023

    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.

  • mathetes's avatar
    mathetes
    Gold Contributor
    Aug 09, 2023

    terraficke 

    In addition to the thoughts by SergeiBaklan, 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 SergeiBaklan that you start a new thread if neither his suggestion nor mine resolves your situation.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 09, 2023

    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.