Whenever I try to perform an XLOOKUP using a range for the "lookup_value" and a "return_array" that has multiple columns, Excel will only ever return the first column.
Here’s the formula I’m using:
=XLOOKUP(E2:E6,A2:A11,B2:C11)
If the value is found, it ONLY ever returns the XLOOKUP value in column B. If I change the "lookup_value" to one cell instead of a range, it works.
How do I get it to return multiple columns?
I’ve attached an image here of what happens.
>Solution :
It does so because you referencing an array. The function then simply returns only a 1st column (just like TEXTSPLIT() a.o. would).
To fix this and do this by row, use:
=XLOOKUP(E2,A2:A11,B2:C11)
If you want to do this in a single go, use:
=CHOOSEROWS(B2:C11,XMATCH(E2:E6,A2:A11))