Returning Whole Rows or Columns

So far XLOOKUP has returned a single value. But it can also return a whole row or column of data at once.

When a formula returns many values, they spill into the neighboring cells automatically. This lets one XLOOKUP fill an entire mini-record.

The trick is to make the return array span several columns. Instead of returning just B2:B20, return B2:E20.

XLOOKUP finds the matching row, then hands back every column in that row of the return array. One formula, four results.

Type the formula in a single cell, say F2, and press Enter. The values appear across F2, G2, H2, and I2.

A faint blue border outlines the spill range. You only edit the top-left cell; the rest are filled by the spill and cannot be changed directly.

An employee table has ID in column A and name, department, role, and salary in columns B through E.

Type an ID into D2 and one XLOOKUP returns the entire record. Change the ID and the whole row updates instantly - a tiny lookup tool in a single formula.

The same idea works vertically. If you search across a row of headers, you can return an entire column of results.

Here XLOOKUP searches the header row B1:E1 for a label in D2 and spills down the full column B2:E50 beneath the matching header.

Once a formula spills, you can refer to the entire spilled range using the cell plus a hash, like F2#.

This is powerful: a SUM over a spilled row stays correct even if the row's width changes, because F2# always means "the whole spill from F2".

Because the result is an array, you can feed it directly into functions that accept ranges.

For example, wrap the lookup in SUM to total a returned row of monthly figures, all in one formula with no helper cells.

A spilling formula needs empty cells to fill. If any cell in the spill path already contains data, XLOOKUP returns a #SPILL! error.

The fix is simple: clear the blocking cells, or move the formula to a clear area. The spill range must be completely empty.

For a polished lookup card, you can spill the field headers as well.

Place one XLOOKUP that returns the data row, and above it reference the header range. When the spill widens or narrows, your labels still line up with the returned columns.

You can even nest one XLOOKUP inside another. The inner one returns a whole column, and the outer one picks a single cell from it.

This produces a true two-way lookup - matching both a row and a column - entirely with XLOOKUP. It is a neat alternative to INDEX-MATCH-MATCH.

You now know XLOOKUP can return more than one value:

• A multi-column return array spills a whole row
• A multi-row return array spills a whole column
• Reference the spill with the # suffix
• Clear blocking cells to avoid #SPILL!

This turns a single formula into a complete record viewer.

Test your understanding of spilling results from XLOOKUP.

You finished the XLOOKUP course by learning to spill results:

• Widen the return array to spill a full row or column
• Spills fill empty neighboring cells and show a blue border
• Refer to a spill with the # suffix, like F2#
• Avoid #SPILL! by keeping the spill area clear

With syntax, fallbacks, direction, and spilling mastered, XLOOKUP can replace nearly every legacy lookup you write.