| #### [Excel] Formula to return row number? I want to put a certain word into column A somewhere, and have a formula in another cell that tells me what row number contains that word.
So for example if I put my formula in cell B1 and put the word "START" in Cell A1, the formula for B1 would return "1". If I put the word "START" in Cell A12, the formula in B1 to return "12"
Is there some way to do this? |

2 recommendations | Never know when it might help others.
The full picture of what I was trying to do might help too. I wanted to perform operations (sums, averages) on a selected portion of a column of figures. I figured if I put "Start" and "End" somewhere in the list I'd be able to set it on the fly without editing the formula every time I changed something (like for instance adding new data to the bottom of the table.
The full formula I used is;
=SUMIF((OFFSET($B$15,(MATCH("START",$H:$H,0)-15),0,1,1)):(OFFSET($B$15,(MATCH("END",$H:$H,0)-15),0,1,1)),$B5,(OFFSET(C$15,(MATCH("START",$H:$H,0)-15),0,1,1):(OFFSET(C$15,(MATCH("END",$H:$H,0)-15),0,1,1))))
Breaking this down into chunks;
The (MATCH("START",$H:$H,0)-15) part returns the row number that contains the word "Start" in column H. The -15 is the adjustment in my table of data because the header of my rows doesn't start until row 15 (I always put the totals at the top of the page rather than the bottom so they are easier to read, plus they remain more "static" that way.
(MATCH("END",$H:$H,0)-15) returns the row that contains the word "End" in Column H in exactly the same way.
In the SUMIF command I have replaced the normal cell reference using the OFFSET command, which in turn use the row number given by the MATCH command.
The same method also works for various of the "SUBTOTAL" functions as well as "COUNT" and "COUNTA".
Quite elegant, and works rather well...you just have to watch out in long columns that "Start" and "End" are only entered once, as the system will only return the first match it comes to. (I guess I could use a "IF COUNTIF >1" type function as an error trapper for this if I really wanted to). |