Vlookup or similar to return an array

DanD

Board Regular
Joined
Dec 23, 2008
Messages
124
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
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Partjob

Board Regular
Joined
Apr 17, 2008
Messages
139
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

Board Regular
Joined
Dec 23, 2008
Messages
124
Except I don't want to return multiple Product numbers in one cell, I want to return them into multiple cells.
 

DanD

Board Regular
Joined
Dec 23, 2008
Messages
124

ADVERTISEMENT

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

Partjob

Board Regular
Joined
Apr 17, 2008
Messages
139

ADVERTISEMENT

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

Board Regular
Joined
Dec 23, 2008
Messages
124
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

Board Regular
Joined
Dec 23, 2008
Messages
124
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,187
Messages
5,594,753
Members
413,930
Latest member
Nela817

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top