What it does
=COLUMN(D1) returns 4; without an argument you get the column number of the formula's own cell. The row does not matter: COLUMN(D1) and COLUMN(D50) give the same result.
Its best-known use is to avoid typing the column index in a lookup formula that is copied across. As =VLOOKUP($F2,$A$2:$D$6,COLUMN(B1),FALSE) is copied to the right, COLUMN(B1) becomes 2, 3, 4 and each cell returns the next column of the table.
To count the columns of a range use COLUMNS, and for row numbers use ROW. In Microsoft 365, CHOOSECOLS picks columns and XLOOKUP looks up without counting columns at all.
Syntax
=COLUMN([reference])=SÜTUN([başvuru])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
reference | Optional | The cell whose column number you want, e.g. D1. If omitted, the cell that holds the formula is used. |
Examples
1Let COLUMN count VLOOKUP's column index
The formulas in G2, H2 and I2 are the same; as the first one is copied right, the reference COLUMN(B1) becomes C1 and D1. The column index therefore grows 2, 3, 4 by itself.
=VLOOKUP($F2,$A$2:$D$6,COLUMN(B1),FALSE)| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Code | Product | Unit | Price | Code | Product | Unit | Price | |
| 2 | KR-110 | A4 Notebook | Each | 42.50 | KR-140 | Sticky Notes | Pack | 27.50 | |
| 3 | KR-120 | Ballpoint Pen | Box | 165.00 | |||||
| 4 | KR-130 | Ring Binder | Each | 38.90 | |||||
| 5 | KR-140 | Sticky Notes | Pack | 27.50 | |||||
| 6 | KR-150 | Staples | Box | 19.75 |
- G2
=VLOOKUP($F2,$A$2:$D$6,COLUMN(B1),FALSE) - H2
=VLOOKUP($F2,$A$2:$D$6,COLUMN(C1),FALSE) - I2
=VLOOKUP($F2,$A$2:$D$6,COLUMN(D1),FALSE)
Write one formula and copy it right: KR-140's name (Sticky Notes), unit (Pack) and price (27.50) appear side by side.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Write the first day of each month across a row
Row 2 of a budget table should show the first day of the month for each column. All four cells hold the same formula: COLUMN() gives 2 in B and 5 in E, so subtracting 1 makes the month run from 1 to 4.
=DATE(2026,COLUMN()-1,1)| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Month 1 | Month 2 | Month 3 | Month 4 |
| 2 | Period Start | 1-Jan-2026 | 1-Feb-2026 | 1-Mar-2026 | 1-Apr-2026 |
| 3 | Rent | 25,000 | 25,000 | 25,000 | 25,000 |
| 4 | Electricity | 3,850 | 4,120 | 3,640 | 3,310 |
- B2
=DATE(2026,COLUMN()-1,1) - C2
=DATE(2026,COLUMN()-1,1) - D2
=DATE(2026,COLUMN()-1,1) - E2
=DATE(2026,COLUMN()-1,1)
B2 shows 1-Jan-2026 and E2 shows 1-Apr-2026. To add another month, copy the formula one more column to the right.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Number does not grow
Cause:
COLUMN($B$1)was written instead ofCOLUMN(B1), so the number stays the same when the formula is copied right.Fix: Do not put $ before the column letter; to lock only the row, write
COLUMN(B$1).- #REF!
Cause: The formula was copied too far right and the position from COLUMN is larger than the number of columns in VLOOKUP's table range.
Fix: Copy the formula across only as many columns as the table has, or widen the table range.
Good to know
- To get the distance from a starting column, subtract:
=COLUMN()-COLUMN($A$1)tells you how many columns to the right of A the formula is. - The same idea works for formulas copied down with ROW.
In Nexell
Get Nexell to do it from a sentence
Add the product name, unit and price from the price list to the order list by product code
Convert to tableBring both lists into the tool and type this. Nexell builds the match with real Excel formulas and shows it in the preview; in the downloaded file, the order lines update when the price list changes.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I convert a column letter to a number in Excel?
Turn the letter into a cell address and pass it to COLUMN: =COLUMN(INDIRECT(A2&"1")). If A2 holds "AB", it returns 28 (INDIRECT).
Related functions
- ROWSATIR · Row number (the header is row 1).
- COLUMNSSÜTUNSAY · Number of columns in a range.
- VLOOKUPDÜŞEYARA · Looks up a value in the first column and returns a value from the same row. Use 0 as the last argument for exact match.
- INDEXİNDİS · Returns the value at a given row and column of a range.
- CHOOSECOLSSÜTUNSEÇ · Returns chosen columns of an array.