Function that can return the row number
 

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
--
Helping Is always a good thing

Function that can return the row number
 

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: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, D10:D24, then
you will have to add 9 onto the result to get the actual row number of
the matched item.

Hope this helps.

Pete

On Aug 14, 12:15*am, QuietMan
wrote:
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
--
Helping Is always a good thing

Function that can return the row number
 

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" wrote:
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

Function that can return the row number
 

Thanks, but dosen't help me find the last instance...any ideas on that
--
Helping Is always a good thing


"Pete_UK" wrote:

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: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, D10:D24, then
you will have to add 9 onto the result to get the actual row number of
the matched item.

Hope this helps.

Pete

On Aug 14, 12:15 am, QuietMan
wrote:
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
--
Helping Is always a good thing

Function that can return the row number
 

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" wrote in message
...
Thanks, but dosen't help me find the last instance...any ideas on that
--
Helping Is always a good thing


"Pete_UK" wrote:

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: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, D10:D24, then
you will have to add 9 onto the result to get the actual row number of
the matched item.

Hope this helps.

Pete

On Aug 14, 12:15 am, QuietMan
wrote:
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
--
Helping Is always a good thing

Function that can return the row number
 

Thanks you very much....would have had to write vba to do this.....
--
Helping Is always a good thing


"Peo Sjoblom" wrote:

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" wrote in message
...
Thanks, but dosen't help me find the last instance...any ideas on that
--
Helping Is always a good thing


"Pete_UK" wrote:

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: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, D10:D24, then
you will have to add 9 onto the result to get the actual row number of
the matched item.

Hope this helps.

Pete

On Aug 14, 12:15 am, QuietMan
wrote:
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
--
Helping Is always a good thing