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.