PDA

View Full Version : Vlookup with multiple results



nugol
04-05-2016, 02:39 AM
Hy guys,

I want excel to show me which group a product is. The problem with this is, if the vlookup finds or does not find a first result it is not searching anymore.
Now I need to find a way for it to do so. Or is there a better way with a new formula or a vba solution which I always love the most?

15833

That is the table, where I want Column B to show the right Group.
I wrote the vlookup in Row 10, just in case.

Thanks in advance for your time

snb
04-05-2016, 03:18 AM
15834

snb
04-05-2016, 04:03 AM
crossposted:

http://www.office-loesung.de/p/viewtopic.php?f=166&t=715852

Paul_Hossler
04-05-2016, 04:14 AM
I'm confused

a. Groups are made up of products (1 Group - N products) ?

b. Products are made up of Parts (1 Product - N Parts) ??

c. Groups are made up of Parts (1 Group - N Parts) ???


or is it


d. Groups are made up of Products which are made up of Parts ???? and a Part can be used in multiple Products which care in different groups


Is a Product strictly in only 1 Group?

Is a Part used in multiple Products?



I want excel to show me which group a product is.

Columns A-B do that with a simple VLOOKUP

nugol
04-05-2016, 04:26 AM
Hy Paul,

yeah a Product is strictly in only one group.
A part is used in multiiple products.

Colum B can't really do that, since vlookup gives back a #NA if it doesn't find the first part of a product. And not all parts are in the parts/group matrix.

Paul_Hossler
04-05-2016, 08:11 AM
I was going by


I want excel to show me which group a product is.

Parts aren't mentioned, so a VLOOKUP in A:B would do that

So what is it you really want?

1. For each Part
2. Find what Product(s) it is used in
3. Find what Group that Product is in
4. List "Part" -- "Product" -- "Group" or
"Group" -- "Product" -- "Part"


Maybe attaching a WORKBOOK with the before and after will help us to understand