return row # of a certain cell
 

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".

return row # of a certain cell
 

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


"Chris" wrote:

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".

return row # of a certain cell
 

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.

Chris Wrote:
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".


--
starguy
------------------------------------------------------------------------
starguy's Profile: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=569875

return row # of a certain cell
 

Ah yes, appreciate it

"Ron Coderre" wrote:

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


"Chris" wrote:

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".