How can I search by row number in Excel?

How can I find out the row number in an Excel spreadsheet?

-

migrated from stackoverflow.comOct 12 '09 at 12:02

This question came from our site for professional and enthusiast programmers.

By formula, try Row() – Richard Morgan Oct 12 '09 at 12:05
Could you offer us a bit more information into what you want to achieve? – alex Oct 12 '09 at 12:13

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)
``````
-