return row # of a certain cell

C

Chris

Is there a way to return the row # of a cell without using the cell's
location but rather its content? For example, I would like to return the the
row # of a cell that contains the word "Run".
 
R

Ron Coderre

Try something like this:

If the search word will only be in ONE column:
Example: Find the row containing "Run" in Col_F
=MATCH("Run",F:F,0)

If the search word may be in a range:
Example: Find the row containing "Run" in E1:G1000
=SUMPRODUCT((E1:G1000="Run")*ROW(E1:G1000))

Note: Those formulas presume the search term will only occur once.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
S

starguy

try following function

=ADDRESS(MATCH("Run",A1:A10,0),2,4)

you can set cell reference instead of including the word "Run" in you
formula.
e.g if word "Run" is in cell B1 your formula would be:

=ADDRESS(MATCH(B1,A1:A10,0),2,4)
change the last digit i.e 4 if you want cell reference to be absolute
or mix (see help of ADDRESS function)

hope this would serve your purpose.
 
C

Chris

Ah yes, appreciate it

Ron Coderre said:
Try something like this:

If the search word will only be in ONE column:
Example: Find the row containing "Run" in Col_F
=MATCH("Run",F:F,0)

If the search word may be in a range:
Example: Find the row containing "Run" in E1:G1000
=SUMPRODUCT((E1:G1000="Run")*ROW(E1:G1000))

Note: Those formulas presume the search term will only occur once.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
Click to expand...
 
Top