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 D1:D4 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 D1:D4.

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

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" wrote:

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 D1:D4 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 D1:D4.

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

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

"Mike H" wrote:

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" wrote:

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 D1:D4 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 D1:D4.

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

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" wrote:

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

"Mike H" wrote:

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" wrote:

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 D1:D4 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 D1:D4.

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

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

"John Moore" wrote:

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 D1:D4 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 D1:D4.

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, 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.

"Mike H" wrote:

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" wrote:

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

"Mike H" wrote:

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" wrote:

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 D1:D4 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 D1:D4.

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