dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
1178
share rss forum feed


Grey Area

join:2003-11-18
UK

[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?



Grey Area

join:2003-11-18
UK

I really should do more thorough searches before I post...found it;

=MATCH("START", A:A,0)

Simples!



Cudni
La Merma - Vigilado
Premium,MVM
join:2003-12-20
Someshire
kudos:13
reply to Grey Area

Thanks for posting what you found

Cudni



Grey Area

join:2003-11-18
UK

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