Lookup to return multiple values
Posted by Stevie Windows on July 11, 2001 8:46 AM
I want to do a lookup on a cell that returns all of the values in the lookup table that match the one that I'm looking up, rather than the first occurrence. If possible I would like to do this given some conditions, eg.
col A1:10 contains the CR/OW refs and B1:B10 contains mileages...
1 CR/OW/00015 12
2 CR/OW/00021 43
3 CR/OW/00074 56
4 CR/OW/00045 32
5 CR/OW/00080 43
6 CR/OW/00015 43
7 CR/OW/00030 40
8 CR/OW/00090 43
9 CR/OW/00045 32
10 CR/OW/00075 86
I want to populate the cells on the right with all the values in column A that where col B is 43. So rows 2,5,6,and 8 would contain CR/OW/00021, CR/OW/00080, CR/OW/00015, CR/OW/00090. Standard lookup functions, as far as I can see, only return one value. Anybody help? I think this might call for a clever formula rather than any VB.