MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### the Last occurance

• Follow

```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.
```
 0

```On Thu, 18 Feb 2010 15:51:01 -0800, peyman <peyman@discussions.microsoft.com>
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
```
 0

```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" <peyman@discussions.microsoft.com> wrote in message
news:9C7C0254-D278-4D88-AA8B-ECECAFBAF8C4@microsoft.com...
> 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.

```
 0

```normal entered

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

--
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.
```
 0

3 Replies
587 Views

Similiar Articles:

7/19/2012 4:49:45 PM