software

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: www.excelforum.com/member.php...infoamp;userid=19
View this thread: www.excelforum.com/showthread...hreadid=517508For the last A in A1:A10 try:

=MAX(--(A1:A10=quot;Aquot;)*(ROW(1:10)))
entered as an array formula with Ctrl Shift Enter

for the second A try:

=SMALL(--(A1:A10=quot;Aquot;)*(ROW(A1:A10)),ROW(A10)-SUM(--(A1:A10=quot;Aquot;)) 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
with @tiscali.co.uk
quot;yanf7quot; gt; wrote in
message ...
gt;
gt; I have a list of names in column.
gt;
gt; 1 A
gt; 2 B
gt; 3 A
gt; 4 C
gt; 5 A
gt;
gt; I need the match function to bring the location of the second A (3) and
gt; the last A (5)
gt;
gt;
gt; --
gt; yanf7
gt; ------------------------------------------------------------------------
gt; yanf7's Profile:
gt; www.excelforum.com/member.php...infoamp;userid=19
gt; View this thread: www.excelforum.com/showthread...hreadid=517508
gt;
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=quot;Aquot;)*(ROW(A1:A10)),10-SUM(--(A1:A10=quot;Aquot;)) 2)
or
=SMALL(--(A1:A10=quot;Aquot;)*(ROW(A1:A10)),COUNT(ROW(A1:A10))-SUM(--(A1:A10=quot;Aquot;)) 2)

But still keep watching this space.

--
HTH

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