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...
Excel for web
Excel on Mac
Formulas and Functions
office 365
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Jan 19, 2022

    bmaroni As a variant, using FILTER in stead of XLOOKUP

    =FILTER(Domains!K:K,(Domains!A:A=E6)+(Domains!B:B=E6))
Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Jan 19, 2022

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
    Jan 19, 2022

    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
    Jan 19, 2022
    Loved how clean this was Riny! I will definitely find myself using this for future iterations.

Resources