SOLVED

Multi Column Return Array in Xlookup

%3CLINGO-SUB%20id%3D%22lingo-sub-2991197%22%20slang%3D%22en-US%22%3EMulti%20Column%20Return%20Array%20in%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2991197%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F288074%22%20target%3D%22_blank%22%3E%40lori_m%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584627%22%20target%3D%22_blank%22%3E%40Chris_Gross%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20the%20Lookup%20value%20is%20a%20single%20cell%2C%20the%20Return_Array%20is%20returning%20two%20columns%20as%20desired%20in%20the%20following%20formula%3C%2FP%3E%3CP%3E%3DXLOOKUP(C2%2CTAN!%24B%241%3A%24B%24188%2CTAN!%24D%241%3A%24E%24188%2C%2C0%2C1)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22KanwalNo1_1-1637548373843.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328486i469E567BE963713C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22KanwalNo1_1-1637548373843.png%22%20alt%3D%22KanwalNo1_1-1637548373843.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CBR%20%2F%3EBut%20when%20the%20Lookup_Value%20is%20a%20Range%2C%20the%20Return_Array%20is%20returning%20only%20the%201st%20Column.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3DXLOOKUP(C2%3AC7%2CTAN!%24B%241%3A%24B%24188%2CTAN!%24D%241%3A%24E%24188%2C%2C0%2C1)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22KanwalNo1_0-1637548328882.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F328485i7DF7278BCB6384DD%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22KanwalNo1_0-1637548328882.png%22%20alt%3D%22KanwalNo1_0-1637548328882.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EIs%20it%20a%20Bug%20or%20I%20am%20missing%20something%20!%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%3CLINGO-SUB%20id%3D%22lingo-sub-3008887%22%20slang%3D%22en-US%22%3ERe%3A%20Multi%20Column%20Return%20Array%20in%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3008887%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F537595%22%20target%3D%22_blank%22%3E%40KanwalNo1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIMHO%2C%20that%20is%20correct%20behaviour.%20In%20second%20case%20you%20try%20to%20return%20array%20of%20array%20which%20is%20not%20supported.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3009507%22%20slang%3D%22en-US%22%3ERe%3A%20Multi%20Column%20Return%20Array%20in%20Xlookup%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3009507%22%20slang%3D%22en-US%22%3EAhh%20!%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

=XLOOKUP(C2,TAN!$B$1:$B$188,TAN!$D$1:$E$188,,0,1)

KanwalNo1_1-1637548373843.png


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)

KanwalNo1_0-1637548328882.png

Is it a Bug or I am missing something !

 

3 Replies
best response confirmed by KanwalNo1 (Occasional Contributor)
Solution

@KanwalNo1 

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 !

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