The COLUMN function
Returns the column number of a cell, 1 for A, 2 for B and 27 for AA. Left empty, it returns the column of the cell the formula is in.
Syntax
COLUMN([reference])- reference (optional)
- The cell to get the column number of. Left out, the formula's own cell.
Examples
B1=COLUMN(D5)
| A | B | |
|---|---|---|
| 1 | 4 |
B1=COLUMN(AA1)
| A | B | |
|---|---|---|
| 1 | 27 |
E1=VLOOKUP("Ben",A2:C4,COLUMN(C1),FALSE)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Department | Salary | 61000 | |
| 2 | Ann | Sales | 58000 | ||
| 3 | Ben | Finance | 61000 | ||
| 4 | Cy | Sales | 54000 |
Questions
How do I turn a column letter into a number?
Build a reference from the letter and ask for its column, as in =COLUMN(INDIRECT("AB1")), which gives 28.
Why use COLUMN inside VLOOKUP?
When you copy =VLOOKUP($G2,$A$2:$C$4,COLUMN(B1),FALSE) to the right, COLUMN(B1) becomes COLUMN(C1), so the column index goes from 2 to 3 by itself.