How to Return Several Columns with XLOOKUP in Excel

Return a whole record in one formula, a slice of adjacent columns, or an entire column chosen by its header.

You will learn to

• Return several adjacent columns that spill to the right
• Return a slice of a record by name
• Look up a header to return a whole column

How it works

XLOOKUP’s return_array can be wider than one column. =XLOOKUP(H1,A2:A10,B2:E10) finds the employee ID in H1 and returns name, department, extension and office together; the results spill into the cells to the right. One formula replaces four separate lookups.

It works sideways too. Search the header row instead of a column — =XLOOKUP("Office",A1:E1,A2:E10) — and XLOOKUP returns the entire Office column, spilling down. Keep the spill area clear, or Excel shows #SPILL!.

=XLOOKUP(H1,A2:A10,B2:E10)