Look Up and Return Multiple Columns

Excel 365Excel 2021+Spills

To pull a whole record with one lookup — name, department, and salary for an ID, not just one field — point XLOOKUP’s return at several columns. The result spills across the row in a single formula.

Quick formula: to return columns B:D for the ID matched in E2:

=XLOOKUP(E2, A2:A8, B2:D8)

Make the return array more than one column wide and XLOOKUP returns the whole matching row, spilling into adjacent cells.

Functions used (tap for the full reference guide):

XLOOKUP INDEX MATCH

The example

Look up an ID and return name, department, and salary together.

	A	B	C	D
1	ID	Name	Dept	Salary
2	E-1042	James	Sales	$71,200
3	E-1077	Priya	Finance	$82,100
4	E-1090	Alex	IT	$79,500

The formula

One formula returns the whole record (it spills across three cells):

=XLOOKUP("E-1077", A2:A4, B2:D4) // → Priya | Finance | $82,100

How it works

A wide return array is the whole trick:

The lookup works as usual — find E-1077 in the ID column.
The return array B2:D4 is three columns wide, so XLOOKUP returns all three values from the matching row.
Enter the formula once; the result spills right into the next two cells automatically.
Reorder or pick specific columns by wrapping in CHOOSECOLS (see variations).

Classic INDEX/MATCH does it too, by lookup-ing the row once and letting INDEX return the whole row: =INDEX(B2:D4, MATCH(E2, A2:A4, 0), 0) — the 0 column argument means “all columns.”

Try it: interactive demo

Live demo

Pick an ID; the whole record is returned at once.

ID (E2)

Variations

INDEX/MATCH, whole row

Works in every version (column arg 0):

=INDEX(B2:D4, MATCH(E2, A2:A4, 0), 0)

Pick or reorder columns

Wrap in CHOOSECOLS for just name and salary:

=CHOOSECOLS(XLOOKUP(E2, A2:A4, B2:D4), 1, 3)

Return a vertical block

If the data runs in columns, return multiple rows the same way.

Pitfalls & errors

#SPILL! The cells to the right of the formula aren’t empty. Clear them so the record can spill.

XLOOKUP multi-column needs Excel 365/2021. Use the INDEX/MATCH version for older Excel.

The return block must align with the lookup. B2:D4 has to cover the same rows as the lookup array A2:A4.

Practice workbook

📊

Download the free Look Up and Return Multiple Columns practice workbook

An employee table with the XLOOKUP multi-column return and the INDEX/MATCH whole-row version (results shown), plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I return multiple columns from a lookup in Excel?

Point XLOOKUP's return array at several columns: =XLOOKUP(E2, A2:A8, B2:D8). The matching row spills across adjacent cells in one formula (Excel 365/2021).

How do I return a whole row with INDEX/MATCH?

Use 0 as the column argument: =INDEX(B2:D8, MATCH(E2, A2:A8, 0), 0) returns the entire matching row and works in every version.

How do I return only some of the columns?

Wrap the lookup in CHOOSECOLS: =CHOOSECOLS(XLOOKUP(E2, A2:A8, B2:D8), 1, 3) returns just the 1st and 3rd fields, in any order.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: XLOOKUP · INDEX · MATCH