lookup and vlookup or index with combined with a nested IF?

Dear all,

I am back with a new challenge. I am not sure how to go around this in the most efficient way.
I need to run a mapping based on matching three columns. This mapping is driven by the existence or not of country ES. Please check the file attached, it is easier to understand and then read the below:

Basically
1. Product number
2. Each product number has 1 to * manufacturer roles. We hold a standard list with them
3. Each manufacturer role can be located in either 1 to many countries
4. For each manufacturer role and country I need a classification:
4.1 - Mapping1 (Insert value): 'included': Country ES occurs at least once, but there are other countries for the same product and manufacturer role different that country ES
4.2 - Mapping2 (Insert value): 'not included': Country ES does not occur at all
4.3 -Mapping3 (Insert value): 'only': Country ES is the only one occurring.
5. Some manufacturer roles need grouping under 1 single category (see the example with Primary and Secondary Packaging into just Packaging)

many thanks for helping

Hi

I1->ES

Try this in D3:F11

Formula:

Please Login or Register  to view this content.

and this in G3:G11

Formula:

Please Login or Register  to view this content.

I do not know if G8 is correct.
See the file

Hi,

For your uploaded sample, formula in D3 copied down and across to G11:

Formula:

Please Login or Register  to view this content.

G8 in your sample is Blank, so I too am not sure if my result "included" is correct.

See attached.

Hi Jtakw,

I think that's a good start point. However I have many more products and I need to included them within the criteria. How would you go around it? See for example if there was 30 more products (all grouped by product, as the example).
I have uploaded a new file, so you have a better understanding of the scope.

A million thanks for your support.

Try this formula in D3:G59

Formula:

Please Login or Register  to view this content.

Note: $I$1 <-- ES

Originally Posted by siroco79

Hi Jtakw,
I think that's a good start point. However I have many more products and I need to included them within the criteria. How would you go around it? See for example if there was 30 more products (all grouped by product, as the example).
I have uploaded a new file, so you have a better understanding of the scope.
A million thanks for your support.

Just need to add Column A as an additional criteria to my formula above, updated formula:

Formula:

Please Login or Register  to view this content.

Change/adjust Column A, B, C ranges to suit your actual data, See attached.

nnnnnnnnnnnnn