digest
Short Summary
Main recommendation: To extract multiple columns from an Excel table with a formula, use INDEX with MATCH for compatibility with any Excel version, or use dynamic array formulas (XMATCH, SEQUENCE, and the spill operator) if Office 365 is available. The video covers six examples across these approaches, all built around locking and unlocking cell references correctly when copying formulas.
Key Points
- The INDEX method works in any version of Excel and uses a row counter plus a MATCH-based column number to pull the requested columns.
- MATCH with zero as the match type returns the relative position of a field name, such as 2 for the email column in the example.
- Dynamic array versions in Office 365 spill results automatically, so a single formula can return a whole column or a full block of rows and columns.
- A single formula can use SEQUENCE for rows and XMATCH with an array of field names to return several columns at once (positions 2, 5, and 6 in the example).
- The spill operator (#) referenced to a table name keeps results fully dynamic, so adding rows or inserting columns in the source table updates the output.
Details Worth Noting
- Concrete instructions:
- Select the table's interior by pointing to its upper left corner and clicking when the black diagonal arrow appears.
- For the INDEX method, lock the first row reference with a dollar sign (for example, $ on the first row) and leave the second reference unlocked so the row counter becomes 1, 2, 3, and so on as the formula copies down.
- For the column number, use MATCH with the field name as the lookup value, the header range as the lookup array, and 0 as the match type.
- Lock the lookup value for copying down, but let it move to the next field name when copied sideways: press F4 twice to set the correct reference.
- Enter the INDEX formula with Control and Enter, copy it down, then copy it to the side. Press F2 in the last cell to confirm all references are working.
- For the Office 365 single-column method, leave the row number argument empty, use XMATCH for the column number, and press Control and Enter so the column spills.
- For the multi-column Office 365 method, enter SEQUENCE for the rows (using the ID column, default start 1, step 1) and XMATCH with an array such as {"Employee","#","Headers"} for the columns, then press Enter. Press F9 to inspect the array values before entering.
- To grab a whole table or specific fields dynamically, type the table name, followed by the field names in square brackets and the spill operator (#).
- If you need one specific column accessed directly, place the formula in that cell and use the spill operator; a formula that lives only in the upper left corner cannot spill from a table reference in the same way.
- The video demonstrates that F2 is used to verify cell references, and that grayed-out formulas in spilled cells are only spilled results, not stored formulas.
- The example data uses an employee table with columns including ID, Employee, #, and Headers.
Bottom Line
Use INDEX with MATCH for maximum compatibility, and switch to XMATCH, SEQUENCE, and the spill operator when Office 365 is available to build fully dynamic extractions.
