Help with Match Function

[yanf7]

I have a list of names in column.

1 A
2 B
3 A
4 C
5 A

I need the match function to bring the location of the second A (3) and the last A (5)

[Sandy Mann]

For the last A in A1:A10 try:

=MAX(--(A1:A10="A")*(ROW(1:10)))
entered as an array formula with Ctrl + Shift + Enter

for the second A try:

=SMALL(--(A1:A10="A")*(ROW(A1:A10)),ROW(A10)-SUM(--(A1:A10="A"))+2)
Again entered as an array formula.

But watch this space, there will be someone anong in a minute with a more
elegant solution.

--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk
"yanf7" <[email protected]> wrote in
message news:[email protected]...
>
> I have a list of names in column.
>
> 1 A
> 2 B
> 3 A
> 4 C
> 5 A
>
> I need the match function to bring the location of the second A (3) and
> the last A (5)
>
>
> --
> yanf7
> ------------------------------------------------------------------------
> yanf7's Profile:
> http://www.excelforum.com/member.php...info&userid=19
> View this thread: http://www.excelforum.com/showthread...hreadid=517508
>

[Sandy Mann]

Woh!
Not only is it not very elegant but I changed the constant number 10 to
Row(A10) at the last minute before posting because I thought that it would
proof it against adding rows above the data but it does not. Use:

=SMALL(--(A1:A10="A")*(ROW(A1:A10)),10-SUM(--(A1:A10="A"))+2)
or
=SMALL(--(A1:A10="A")*(ROW(A1:A10)),COUNT(ROW(A1:A10))-SUM(--(A1:A10="A"))+2)

But still keep watching this space.

--
HTH

Sandy
[email protected]
[email protected] with @tiscali.co.uk


"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...
> For the last A in A1:A10 try:
>
> =MAX(--(A1:A10="A")*(ROW(1:10)))
> entered as an array formula with Ctrl + Shift + Enter
>
> for the second A try:
>
> =SMALL(--(A1:A10="A")*(ROW(A1:A10)),ROW(A10)-SUM(--(A1:A10="A"))+2)
> Again entered as an array formula.
>
> But watch this space, there will be someone anong in a minute with a more
> elegant solution.
>
> --
> HTH
>
> Sandy
> [email protected]
> [email protected] with @tiscali.co.uk
> "yanf7" <[email protected]> wrote in
> message news:[email protected]...
>>
>> I have a list of names in column.
>>
>> 1 A
>> 2 B
>> 3 A
>> 4 C
>> 5 A
>>
>> I need the match function to bring the location of the second A (3) and
>> the last A (5)
>>
>>
>> --
>> yanf7
>> ------------------------------------------------------------------------
>> yanf7's Profile:
>> http://www.excelforum.com/member.php...info&userid=19
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=517508
>>
>
>