digest
Short Summary
Main recommendation: To return an entire record with VLOOKUP, generate the column index numbers with the COLUMNS function (or ROWS for vertical copying), or use the SEQUENCE function in Office 365 to spill the column numbers automatically. The presenter compares these three options and argues that SEQUENCE is the most elegant because one formula can return the full record.
Key Points
- VLOOKUP's third argument (column index number) must increase by one for each column of the record, so the numbers 2, 3, 4, 5, 6 need to be generated inside the formula.
- COLUMNS works when copying across columns: an expanding range like $C$10:C10 is used, with the C column locked by a dollar sign, and one is added to the COLUMNS result to yield the needed sequence (2, 3, 4...).
- ROWS handles vertical copying (down rows) in the same way, counting rows from a locked starting cell.
- SEQUENCE, a dynamic array function in Office 365, spills an array of numbers across or down cells without copying formulas, and it can feed VLOOKUP directly so the full record spills at once.
Details Worth Noting
- Concrete instructions:
- For a horizontal record, in the first result cell enter
=VLOOKUP(lookup_value, table_array, COLUMNS($C$10:C10)+1, FALSE), using the lookup value locked with F4 and the table as an Excel table range, then copy across with Control-Enter. Adjust the locked and unlocked references so the range expands one column at a time. - For a vertical record, use the same structure with ROWS instead of COLUMNS, locking the row number (e.g., $10) and copying down so the count increases by one each row.
- In Office 365, use
=VLOOKUP(lookup_value, table_array, SEQUENCE(1,5,2), FALSE): SEQUENCE(rows, columns, start, step) with start 2 and default step 1 produces 2 through 6 and spills the whole record. To spill vertically, use SEQUENCE(5,1,2) instead. - Use COLUMNS(C3:G3) in place of a hard-coded 5 so the number of columns is calculated from the range.
- Apply currency or other number formatting to the returned cells as needed.
- For a horizontal record, in the first result cell enter
- The presenter notes that dynamic array results live in the top-left cell only; the other spilled cells are grayed out and cannot be edited individually.
- COLUMNS and ROWS work in any version of Excel, while SEQUENCE requires Office 365.
- Changing the lookup value updates the entire returned record in every method.
Bottom Line
The choice comes down to Excel version and layout: COLUMNS/ROWS work everywhere and suit copied formulas, while SEQUENCE inside VLOOKUP is the single-formula option for Office 365 users.
