Excel365 XLookup and return multiple in non-adjacent columns
The formula =XLOOKUP(J2, A:A, HSTACK(B:B, G:G)) works because it uses the XLOOKUP function to search for a value in column A (Employee IDs) and returns corresponding data from the array created by HSTACK(B:B, G:G).
XLOOKUP Function:
• J2 is the lookup value (Employee ID you want to search for).
• A:A is the lookup array (the range where Employee IDs are located).
• HSTACK(B:B, G:G) is the return array (contains columns B and G stacked horizontally).
HSTACK:
• Combines the B:B (Full Name) and G:G (Department) columns into a single array horizontally.
• This results in an array where the first column corresponds to B:B (Full Name) and the second column corresponds to G:G (Department).
Why HSTACK is Needed:
• Normally, XLOOKUP only allows you to return values from one column (or row) at a time.
• By using HSTACK, you create a combined array that allows XLOOKUP to return multiple values (e.g., Full Name and Department) in one step.
• Without HSTACK, you would need two separate XLOOKUP formulas to get values from B:B and G:G.