Multiple matches

moccasinus

New Member
Joined
Aug 8, 2017
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm having hard time trying to figure out how to match multiple criteria, that is both vertical and horizontal, and so on.
I've concacenated with transpose, and so on, and so on, in this forum also found AND(EXACT(... that doesn't work either.
The aim is to have AB2:AD4 formatted in color if they match all the criteria, which is based on info in second picture (can I bake all products with a particular raw material instead of the original one)
RLxLGTc.jpg


If all products can be baked with a "match" sugar or flour, etc, then I'd like either to format AB2:AD4 in green color, or add thin columns next to Code1 P/N for "true" or whatever.
I can have the same table of B2:M4 next to the matching product below, but with that still couldn't figure out a way to do it.
VVLeTFR.jpg


I know it sounds really incoherent, hope you get the idea, if not please ask
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
I don't get it, what do you want to match?
SUMPRODUCT is probably what u want to use. But can't give you an example because I'm not sure which criteras to match against
 
Upvote 0
For example: IF( "Flour 1kg" in 'Products, second img' contains "Pie2/Pie3/Cookies3/Bread1/Bread2/Pretzel1" (all products containing Flour003, from 1st img) ; "TRUE" (or whatever) ; "FALSE" (or whatever) )
 
Upvote 0
The main issue I'm having is the: contains "Pie2/Pie3/Cookies3/Bread1/Bread2/Pretzel1" (all products containing Flour003, from 1st img)
Because for a formula to lookup the needed values, if it contains Pie2 etc. I need to use transpose so it lookups vertically I guess.I've tried several options, definitely I'm missing something
 
Upvote 0
If you transpose you can do something like this:
bUYXiFB.png

Each match returns a 1 and the others return a 0, they're are then summarized. (in this case the answer was 4)
 
Upvote 0
I've narrowed down my problem to this: how do I get excel to do these things in a sequence (not necessarily as listed)
1. Recognize same pair in Table 2 as in Table 1
2. Recognize Conclusion "Match" for the pair
3. Check whether all the same products (Pie/Bread1,2,3 etc.) are listed in Table 2.
Meaning that if 'Flour003' is in all pies, breads, pretzels and one cookie, the analog in Table 2 has to list all of these products as well.
In this example below, the only missing product in Table 2 is 'Cookies2', so I'd want a "FALSE" return, because not all products can be baked with 'Brown Flour 500g'.
Also, Table 1 indicates the quantities needed below a certain product, and in Table 2 I've set up those one's so the lists are separated, but that didn't work out either

Table 1.
ZuaxHWu.jpg


Table 2.
d6Bx9Qc.jpg
 
Upvote 0
1. Recognize same pair in Table 2 as in Table 1 (Original PN & Analog PN)
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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
Back
Top