Index match to find unique values

[BobTheRocker]

I have three columns of data:

A
Car A A
Bus B A
Van C C
Dog A A
Cat A C

I want to INDEX MATCH columns C and D to find unique values in column A. In the example I would like a formula to look at A1 then in instance return Car, Bus, Dog, Cat.

Using the following formula I can change the last number in the formula ,1 to ,2 then ,3 etc. This returns the next occurrence of A but using the example it would return Car, Car, Bus, Dog, Dog, Cat

Please Login or Register  to view this content.

Any ideas gratefully received.

[XOR LX]

Hi.

Array formula**:

=INDEX($A$2:$A$6,SMALL(IF(FREQUENCY(IF($B$2:$C$6=$A$1,MATCH($A$2:$A$6,$A$2:$A$6,0)),ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6))+1),ROW($A$2:$A$6)-MIN(ROW($A$2:$A$6))+1),ROWS($1:1)))

Regards

[BobTheRocker]

Almost there, thank you XOR LX that works a treat for returned values in a list in one column going down. What if I wanted a returned list in one row going across?

[XOR LX]

Just change ROWS($1:1) to COLUMNS($A:A).

Regards