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)

 

VIDEO TRAINING - LEARN AT YOUR OWN PACE

DEEP DIVE INTO LOOKUP FUNCTIONS - XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, MATCH, INDEX, IFERROR, ISNA, IFNA, LEFT, RIGHT, MID, FIND, SEARCH, LEN, SUBSTITUTE, REPLACE, TRIM, CLEAN & INDIRECTClick here to enrol

THE MS ACCESS FOR BEGINNERS' COURSE - Click here to enrol


FREE STUFF
70+ MUST KNOW EXCEL SHORTCUT KEYS: Download the pdf
UNDERSTAND & FIX EXCEL ERRORS: Download the pdf 

 

 

Please download the featured file here.

Posted by Blue Pecan Computer Training