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.
Posted by Mark W. on July 11, 2001 8:50 AM
Try using an Advanced AutoFilter with Criteria that
Extracts its results to your desired target cell
range.
Posted by IML on July 11, 2001 8:57 AM
I'm probably reading to little into this, but how about,
=IF(B1=43,A1,"")
and copying it down?
good luck
Posted by stevie windows on July 11, 2001 9:00 AM
I'm doing it with a filter at the moment, it's a shame there isn't something like a MATCH type that returns ALL values, rather than just largest, smallest and exact.
Posted by stevie windows on July 11, 2001 9:03 AM
Cheers
But I need to find all duplicate values for all figures, not just 43.
Posted by Mark W. on July 11, 2001 10:18 AM
I'm sorry, I don't understand your comment. If you're
filtering on those records with 43 then all of those
records will be displayed. Also, do you undestand
the distinction between an AutoFilter (with its drop
down arrows) and an Advanced AutoFilter?
