Help with Match Function
 

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

Help with Match Function
 

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

with @tiscali.co.uk
"yanf7" wrote in
message ...

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

Help with Match Function
 

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

with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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

with @tiscali.co.uk
"yanf7" wrote in
message ...

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