Forum Discussion
Multi Column Return Array in Xlookup
@Sergei Baklan @Peter Bartholomew @lori_m @Chris_Gross
When the Lookup value is a single cell, the Return_Array is returning two columns as desired in the following formula
=XLOOKUP(C2,TAN!$B$1:$B$188,TAN!$D$1:$E$188,,0,1)
But when the Lookup_Value is a Range, the Return_Array is returning only the 1st Column.
=XLOOKUP(C2:C7,TAN!$B$1:$B$188,TAN!$D$1:$E$188,,0,1)
Is it a Bug or I am missing something !
IMHO, that is correct behaviour. In second case you try to return array of array which is not supported.
3 Replies
IMHO, that is correct behaviour. In second case you try to return array of array which is not supported.
- Ahh ! That is the Case ! I am amazed at how easily we can assume the things, which are yet to come into foray. May be Microsoft has given wings to our thoughts ! Thanks Sergei ! You have my admiration !
How do you learn and go about the things ? Will you share the secret recipe !If you are trying to return a two dimensional array of values, you can use INDEX/XMATCH/XMATCH or, if you have no need to look up the column indices, a hard-wired array {1,2} will return the first two columns.
It is a nuisance that, though arrays of arrays, are normal as input (essentially that is what Tables are) it is such hard work to get a 2D array returned.
