Function that can return the row number

[QuietMan]

Can someone help....I need to know if there is a function in excel that can
look through a column of data, find a specific cost center and return the
first instance/row that costcenter shows and also the last instance/row

Thanks

 

[Pete_UK]

The MATCH function will return the relative position of a cell within
a range which matches another cell, eg:

=MATCH(sought_value,range,0)

where range can be a full-column reference, eg D, and sought_value
can be a specific value or a reference to a cell which contains that
value (your cost centre). If your range is, for example, D1024, then
you will have to add 9 onto the result to get the actual row number of
the matched item.

Hope this helps.

Pete

 

[Bob Bridges]

If, as I understand it, you're looking for the number of the row on which a
certain value appears, you want MATCH. For example, if you have a list of
cost centers, like this:

A
101355
102183
502721
500003
313099


....and so on, and you want to discover that 502721 first appears on row 4,
then =MATCH(502721,A1:A999,FALSE) will return the value 4.

(I think it's FALSE; maybe it's 0. You'd better look it up.)

Now, that gets you the first occurrence of the desired string. How to get
the last occurrence...hmm.

 

[QuietMan]

Thanks, but dosen't help me find the last instance...any ideas on that

 

[Peo Sjoblom]

This will give you the row number counted starting with row 1

=MAX(IF(A1:A2000=C1,ROW(A1:A2000)))

where C1 holds the costcenter name/number/string


It needs to be entered with ctrl + shift & enter


=CELL("address",INDEX(A1:A2000,MAX(IF(A1:A2000=C1,ROW(A1:A2000)))))

also entered the same way


will give you the absolute cell reference

--


Regards,


Peo Sjoblom

 

[QuietMan]

Thanks you very much....would have had to write vba to do this.....