Vlookup or similar to return an array

[DanD]

Afternoon All,

What I'd like to achieve is to use something similar to a vlookup but return multiple results

I have one sheet that contains data about products, the product number, the class it belongs to, and many other bits. The data is sorted by product number.

In a second sheet I have a list of classes in column G, I'd like to return a list of product numbers in columns H to wherever is needed.

For example there might be two products in class one, class one is in G1, with the first product number in H1 and the second in H2.

Any tips?

Thanks in advance

Dan

 

[Partjob]

Dan
forgive me for stating the obvious but you are not going to be able to return both product numbers in to one cell. Although I suppose it is possible with a bit of code.
You can have the Vlookup return a second product number if the first returns nothing by combining it with an IF statement not sure if this is what you want.
You could have two columns of Vlookups one for the 1st Product number and one for the second.

Partjob

 

[DanD]

Except I don't want to return multiple Product numbers in one cell, I want to return them into multiple cells.

 

[jeffreybrown]

DanD,

What about trying advanced auto filter...

http://www.contextures.com/xladvfilter01.html

 

[DanD]

What I'm trying to create is a small table with Class number, either in one row or 1 column, with then a list of Product numbers belonging to that class either below or too the right. The data table that this is coming from is the Product table and is ordered by Product number, which is in column A and it extends all the way over to column UE. Autofilters don't seem to help me unless Im missing something...

 

[GlennUK]

Do a search for VLOOKUPNTH in this forum.

 

[Partjob]

Dan
How many possible product numbers for each Lookup will there be?
You state that you table goes across to Column UE, does that mean in each of these columns there could be a product number? At a quick guess that is close to 400 columns?
Partjob

 

[DanD]

Im presuming that this is still in essence a vlookup? and thus still requires the data to be sorted, by the field being looked up, currently the data isnt sorted at that level. It has many other lookups on the Product code, which is what it is currently sorted by.

 

[DanD]

The source data table is located in A1:UE105. The class numbers are in column E and Product codes in Column A. There are 105 product codes across 12 Classes.

The output I'm looking for would give me for each class, a count of how many products are in that class, simple countif, and the list of the associated item codes.

 

[jeffreybrown]

Dan,

Could a pivot table do this for you?