Hi everybody,

I need to now how I can get the last Row number of last occurance of an item
in a list, for example:

row item
1 A
2 B
3 D
4 A

I want a formula to return me row number 4 after looking up A.
Any hints,
thanks.

On Thu, 18 Feb 2010 15:51:01 -0800, peyman
wrote:

Hi everybody,

I need to now how I can get the last Row number of last occurance of an item
in a list, for example:

row item
1 A
2 B
3 D
4 A

I want a formula to return me row number 4 after looking up A.
Any hints,
thanks.


=LOOKUP(2,1/($A$1:$A$65535="A"),ROW($A$1:$A$65535))

--ron

Try this array formula** :

=MAX(IF(A1:A100="A",ROW(A1:A100)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"peyman" wrote in message
...
Hi everybody,

I need to now how I can get the last Row number of last occurance of an
item
in a list, for example:

row item
1 A
2 B
3 D
4 A

I want a formula to return me row number 4 after looking up A.
Any hints,
thanks.

normal entered

=SUMPRODUCT(MAX(($A$1:$A$1000="A")*ROW($A$1:$A$100 0)))

--
Jacob


"peyman" wrote:

Hi everybody,

I need to now how I can get the last Row number of last occurance of an item
in a list, for example:

row item
1 A
2 B
3 D
4 A

I want a formula to return me row number 4 after looking up A.
Any hints,
thanks.