How can I find out the row number in an Excel spreadsheet?
2 Answers
=ROW(C5)
gives the row number of cell C5
, i.e 5
. This can be useful for making sure that you have a fixed starting point for some other function like an OFFSET
. If someone adds or deletes rows which affect that C5
cell, your formula would update to C4
, C6
or whatever, and you still have a correct starting row.
ROW()
gives the row number of the cell you use that formula in. I often use this with the MOD()
function in conditional formats to stripe sets of rows, or underline every 5th one say to make it easier to follow rows across a printed page.
As your question in the title you want to search by row number; So, you need INDIRECT()
function; like this:
=INDIRECT("A"&1)
If your specific row number is in cell B1
you can change it to:
=INDIRECT("A"&B1)
This formula will give you the value of column A
and row as value that stored in B1
.
As your question in the body of question you need to use ROW()
function that will returns the current row number.
As another view, you can use MATCH()
function; that can give you the row number of a search result, like this:
MATCH( Lookup_value, Lookup_array, Match_type )
- Lookup_value; is the value you want to find.
- Lookup_array; is where you want to search in it.
- Match_type; is
0 - Exact match
,1 - Less than
and-1 - Greater than
Use it like this:
=MATCH(B1; A:A; 0)