How to Get Row and Column Numbers in Excel

Use ROW and COLUMN to find which sheet row holds a record, which column holds a field and where a list ends.

You will learn to

• Return the sheet row of a matched record
• Return the column number of a header
• Find the last used row of a list

How it works

ROW(reference) returns the row number of a cell and COLUMN(reference) its column number: =ROW(B7) is 7 and =COLUMN(D1) is 4. On their own they look trivial, but combined with INDEX they report where a lookup landed: =ROW(INDEX(A2:A11,MATCH("Mei Tanaka",A2:A11,0))).

Given a whole range, ROW returns an array of row numbers. =MAX((A2:A11<>"")*ROW(A2:A11)) multiplies each row number by 1 or 0 depending on whether the cell is filled, so the largest result is the last used row — handy for growing lists.

=MAX((A2:A11<>"")*ROW(A2:A11))