lookup returning multiple values

[oshlonger]

I have Sheet1 with the following:

Name Product
--------- -------
Bill Smith A
Jim Dean B
Jack Daniels A
Joe Rogers C

I want to create a formula in sheet2 that will return all the names for the product i specify. I want the following results:

Product A:
Bill Smith
Jack Daniels

Product B:
Jim Dean

Product C:
Joe Rogers

any suggestions? I tried the lookup function but that will only return a single value, and I need all values

 

[macropod]

Hi,

Take a look at the solutions posted in the thread at:

http://www.tek-tips.com/viewthread.cfm?qid=910617

Cheers

 

[byundt]

In addition to those methods, here is an array formula that returns all the names:
=IF(COUNTIF(Sheet1!B$1:B$100,A$1)<ROW()-ROW(A$1),"",INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!B$1:B$100=A$1,ROW(Sheet1!B$1:B$100),65536),ROW()-ROW(A$1)),1))
This is an array formula, so remember to hold Control + Shift down while hitting Enter. Excel will respond by adding curly braces { } surrounding the equation.

The formula may be copied down. Once you have exhausted the list of Names, the formula returns blanks.

In this equation, cell A$1 contains the name of the Product--which the formula assumes is in the row above the first cell with the formula. Sheet1 column A contains the Names, and column B contains the Product names.

 

[oshlonger]

Thanks byundt - The formula works great!