Forum Discussion

bmaroni's avatar
bmaroni
Copper Contributor
Jan 18, 2022
Solved

XLOOKUP #VALUE! error

I am new to XLOOKUP. I am trying to lookup a value from spreadsheet A on spreadsheet B in order to find a value in a column associated with that value on Spreadsheet B. My current formula that is not working is - =XLOOKUP(E6, Domains!$A:$B, Domains!$K:$K). This is where I get the #VALUE! error. If I change my lookup_array property to only column A or only column B it works perfectly. However, I need it to search for this value in both columns. The value for lookup is a URL name in all text. Both columns A & B are also only text. For some reason the error seems to suggest that my data types are not the same. Has anyone dealt with this before or have any troubleshooting tips for me? Thanks ahead of time!

7 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor

    bmaroni 

    One more variant

    =XLOOKUP( 1, (Domains!$A:$A=E6)*(Domains!$B:$B=E6), Domains!$K:$K, "nothing")
    • bmaroni's avatar
      bmaroni
      Copper Contributor
      Thank you for your reply Sergei but for some reason this variant did not work for me.
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    bmaroni As a variant, using FILTER in stead of XLOOKUP

    =FILTER(Domains!K:K,(Domains!A:A=E6)+(Domains!B:B=E6))
    • PeterBartholomew1's avatar
      PeterBartholomew1
      Silver Contributor

      This is just playing.  Riny_van_Eekelen 's formula followed by a couple of insider beta variants

      =FILTER(K,(A=v)+(B=v))
      
      = FILTER(K, 
          BYROW(AB, 
            LAMBDA(R,OR(R=v))
          )
        )
      
      = BYCOL(AB, 
          LAMBDA(C, XLOOKUP(v,C,K,""))
        )

      My defined names are not too descriptive but there was a reason for that.

    • bmaroni's avatar
      bmaroni
      Copper Contributor
      Loved how clean this was Riny! I will definitely find myself using this for future iterations.
  • bmaroni 

    =XLOOKUP(E6,IF(NOT(ISNA(XLOOKUP(E6,Domains!A:A,Domains!K:K))),Domains!A:A,Domains!B:B),Domains!K:K)

     

    Maybe with this formula as shown in the attached file. It works in my spreadsheet with VLOOKUP as well.

    • bmaroni's avatar
      bmaroni
      Copper Contributor
      Thank you for your reply Quadruple_Pawn your solution did work for me as well.

Resources