Multi Column Return Array in Xlookup!%24B%241%3A%24B%24188%2CTAN!%24D%241%3A%24E%24188%2C%2C0%2C1)!%24B%241%3A%24B%24188%2CTAN!%24D%241%3A%24E%24188%2C%2C0%2C1)!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2991197%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLI!%20That%20is%20the%20Case%20!%20I%20am%20amazed%20at%20how%20easily%20we%20can%20assume%20the%20things%2C%20which%20are%20yet%20to%20come%20into%20foray.%20May%20be%20Microsoft%20has%20given%20wings%20to%20our%20thoughts%20!%20Thanks%20Sergei%20!%20You%20have%20my%20admiration%20!%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20do%20you%20learn%20and%20go%20about%20the%20things%20%3F%20Will%20you%20share%20the%20secret%20recipe%20!%3C%2FLINGO-BODY%3E
Occasional Contributor

@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



But when the Lookup_Value is a Range, the Return_Array is returning only the 1st Column.



Is it a Bug or I am missing something !


3 Replies
best response confirmed by KanwalNo1 (Occasional Contributor)


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.