Multi Column Return Array in Xlookup

[KanwalNo1]

@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 !

[#2 · Accepted solution]

@KanwalNo1 

IMHO, that is correct behaviour. In second case you try to return array of array which is not supported.

[#3]

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 !

[#4]

@KanwalNo1 

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.