Help with Match Function

Y

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)
 
S

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
 
S

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
 
Top