Skip to content

Excel function · Lookup & reference

Excel COLUMN function

COLUMN returns the column number of a cell: 1 for A, 4 for D. Use it when a formula copied to the right needs a number that grows by itself, such as the column index of VLOOKUP.

In Turkish Excel: SÜTUN

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

English Excel=COLUMN([reference])
Turkish Excel=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

ArgumentRequiredDescription
referenceOptionalThe 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.

G2fx=VLOOKUP($F2,$A$2:$D$6,COLUMN(B1),FALSE)
Let COLUMN count VLOOKUP's column index: example table
ABCDEFGHI
1CodeProductUnitPriceCodeProductUnitPrice
2KR-110A4 NotebookEach42.50KR-140Sticky NotesPack27.50
3KR-120Ballpoint PenBox165.00
4KR-130Ring BinderEach38.90
5KR-140Sticky NotesPack27.50
6KR-150StaplesBox19.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.

B2fx=DATE(2026,COLUMN()-1,1)
Write the first day of each month across a row: example table
ABCDE
1ItemMonth 1Month 2Month 3Month 4
2Period Start1-Jan-20261-Feb-20261-Mar-20261-Apr-2026
3Rent25,00025,00025,00025,000
4Electricity3,8504,1203,6403,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 of COLUMN(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

Type this in Nexell

Add the product name, unit and price from the price list to the order list by product code

Convert to table

Bring 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

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free