Lookup & Reference
COLUMN and COLUMNS: build dynamic horizontal indexes and count table width
Generate auto-incrementing VLOOKUP column numbers across a report with COLUMN or COLUMNS, and count total cells in a 2D block.
Eliminate hardcoded 2, 3, 4 column indexes when dragging right
COLUMN(reference) returns the 1-based column number. COLUMNS($A$1:A1) starts at 1 and grows to 2, 3 and so on when copied right because only the ending reference moves.
Using COLUMNS($A$2:C2) as VLOOKUP's col_index_num pulls column 3 automatically without editing numbers column by column.
| SKU (A) | Q1 (B) | Q2 (C) | Q3 (D) |
|---|---|---|---|
| P-100 | 120 | 145 | 160 |
| P-200 | 85 | 90 | 110 |
Dynamic VLOOKUP column index with COLUMNS($A$2:D2)
COLUMNS($A$2:D2) equals 4, so VLOOKUP("P-200", A2:D3, COLUMNS($A$2:D2), FALSE) retrieves Q3 (110).
Keep the lookup table and the first endpoint of COLUMNS absolute when copying. Copy only while the selected column index remains inside the lookup table width.
=VLOOKUP("P-200", $A$2:$D$3, COLUMNS($A$2:D2), FALSE)Total cells in a rectangular matrix
Multiplying ROWS(A2:D3) * COLUMNS(A2:D3) gives total cell capacity (2 * 4 = 8 cells).
=ROWS(A2:D3) * COLUMNS(A2:D3)Try the example
Use the original values or change them to test your understanding.