Jan 18 2022 03:27 PM
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!
Jan 18 2022 05:15 PM
=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.
Jan 18 2022 10:38 PM
Solution@bmaroni As a variant, using FILTER in stead of XLOOKUP
=FILTER(Domains!K:K,(Domains!A:A=E6)+(Domains!B:B=E6))
Jan 19 2022 12:40 AM
One more variant
=XLOOKUP( 1, (Domains!$A:$A=E6)*(Domains!$B:$B=E6), Domains!$K:$K, "nothing")
Jan 19 2022 01:11 PM
Jan 19 2022 01:11 PM - edited Jan 19 2022 01:12 PM
Jan 19 2022 01:12 PM
Jan 19 2022 02:01 PM
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.
Jan 18 2022 10:38 PM
Solution@bmaroni As a variant, using FILTER in stead of XLOOKUP
=FILTER(Domains!K:K,(Domains!A:A=E6)+(Domains!B:B=E6))