Return info based on 1 criteria

[John Moore]

Hi guys, small query. Is there a way to return every occurence of a part
number from looking at one cell ... e.g. Cell C1 contains the one cell that I
want to use and cells D14 contain the result I want to see, gained from the
data in A1:B6 .... so I want to be able to return the info from A1:B6 based
on C1 and input the data into cells D14.

A B C D
1 Bob Car Bob Car
2 Bob Bus Bus
3 Bob Moped Moped
4 Mike Car Boat
5 Mike Bus
6 Bob Boat

 

[Mike H]

Hi,

Base on yor posted data this works. Out it in D1 and drag down to d4.

=VLOOKUP($C$1,$A1:$B$6,2,FALSE)

Mike

 

[John Moore]

Hi Mike ,,, this doesn't return what I'm looking for. I am looking for the
results in D14 to show Car, Bus , Moped and Boat respectively based on cell
C1 being "Bob" using the range A1:B6.

 

[Mike H]

What does it show in D1 - D4 for you? Because on my Machine it returns this

ColA Col B Col C Col D
Bob Car Bob Car
Bob Bus Bus
Bob Moped Moped
Mike Car Boat
Mike Bus
Bob Boat

Mike

 

[John Moore]

It's ok Mike ,, I found a solution using SMALL, INDEX and MATCH .... thanks
anyway

 

[John Moore]

Mike, it seems that this will work, but only if the data A1:B6 begins with
Bob , if I try to do Mike it returns Car only. But I have a solution that
works well enough using the INDEX and MATCH functions.