Forum Discussion
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!
bmaroni As a variant, using FILTER in stead of XLOOKUP
=FILTER(Domains!K:K,(Domains!A:A=E6)+(Domains!B:B=E6))
7 Replies
- SergeiBaklanDiamond Contributor
One more variant
=XLOOKUP( 1, (Domains!$A:$A=E6)*(Domains!$B:$B=E6), Domains!$K:$K, "nothing")
- bmaroniCopper ContributorThank you for your reply Sergei but for some reason this variant did not work for me.
- Riny_van_EekelenPlatinum Contributor
bmaroni As a variant, using FILTER in stead of XLOOKUP
=FILTER(Domains!K:K,(Domains!A:A=E6)+(Domains!B:B=E6))
- PeterBartholomew1Silver 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.
- bmaroniCopper ContributorLoved how clean this was Riny! I will definitely find myself using this for future iterations.
- OliverScheurichGold Contributor
=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.
- bmaroniCopper ContributorThank you for your reply Quadruple_Pawn your solution did work for me as well.