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