In this video, I demonstrate how to create a non-contiguous return array when using XLOOKUP. The CHOOSE function does the trick here as you will see in the formula below.  I created an array of index numbers {1,2,3} which correspond to the columns I have selected in the value 1, value 2 and value 3 arguments.

=XLOOKUP(O4&"*",$E$4:$E$47,CHOOSE({1,2,3},$E$4:$E$47,$F$4:$F$47,$K$4:$K$47),,2,-1)

Please download the featured file here.

Posted by Blue Pecan Computer Training