Index match with multiple criterias and wildcards (not all criterias always used)

[dunder]

Hello,

I have a challenge that I have not been able to solve through googling or any other ways. And I am very grateful if someone could help.

Basically I have 2 outputs from a program (some numbers and a text string). The text string then contains several identification tags. For example the letters CC in one unique place in the string tells you that it is customer Y, XX later in the string tells you that it is a customer buying apples and so on.

I have then several rules to identify and analyse this data. The data can only get 1 tag, so if I put wildcards on 4 categories and XX in the category that matches where the XX should be in the string to identify apples sales then that means all those strings that have XX in that place should be labelled Apple sales.

It would have been easy enough if there always was 5 unique tags (as I could combine to a string or use formula in the example spreadsheet), but the issue is that some strings should be labelled only according to 1 or 2 tags, and the rest of the tags should be ignored (I have put * in the example file).

I have uploaded an example file to identify the issues.

Thank you in advance for any help.

[JohnTopley]

Try

in I3

=INDEX($I$12:$I$14,MATCH(1,INDEX((IF($D$12:$D$14="*",1,(D3=$D$12:$D$14)))*IF($E$12:$E$14="*",1,(E3=$E$12:$E$14))*IF($F$12:$F$14="*",1,(F3=$F$12:$F$14))*IF($G$12:$G$14="*",1,(G3=$G$12:$G$14))*IF($H$12:$H$14="*",1,(H3=$H$12:$H$14)),0,1),0),0)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[johnmpl]

Hi, to both!

You could try (NON-CSE):
[I3] : =INDEX(I$12:I$14,MATCH(5,MMULT(N(COUNTIF(D3:H3,D$12:H$14)>0),{1;1;1;1;1}),))
And drag it down.

Blessings!

[dunder]

Try

in I3

=INDEX($I$12:$I$14,MATCH(1,INDEX((IF($D$12:$D$14="*",1,(D3=$D$12:$D$14)))*IF($E$12:$E$14="*",1,(E3=$E$12:$E$14))*IF($F$12:$F$14="*",1,(F3=$F$12:$F$14))*IF($G$12:$G$14="*",1,(G3=$G$12:$G$14))*IF($H$12:$H$14="*",1,(H3=$H$12:$H$14)),0,1),0),0)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Thank you very much! One question, how do I make the cell references in row 3,4,5,6 follow the formula? Or do I need to enter the array formula 1 by 1 per row?

[dunder]

Hi, to both!

You could try (NON-CSE):
[I3] : =INDEX(I$12:I$14,MATCH(5,MMULT(N(COUNTIF(D3:H3,D$12:H$14)>0),{1;1;1;1;1}),))
And drag it down.

Blessings!

Thank you! I get some false positives with this approach, but I see it is because I have some that are only 1 criteria with 4 wildcards, but then another rule can have the same criteria but also another rule. If that is the case it should be another category..
This is a problem I did not account for in the example. However I have can create a unique string, as I can ignore the wildcards.

Do you have any insight how this could be solved? Have attached another example file.

[johnmpl]

Hi again, dunder!

Check this new approach (NON - CSE):
[I3] : =LOOKUP(,0/FREQUENCY(0,1/(1+(MMULT(N(COUNTIF(D3:H3,D$13:H$16)>0),{1;1;1;1;1})=5)*LEN(J$13:J$16))),I$13:I$16)

or, without taking care about J column (NON - CSE too):
[I3] : =LOOKUP(,0/FREQUENCY(0,1/(1+(MMULT(N(COUNTIF(D3:H3,D$13:H$16)>0),{1;1;1;1;1})=5)*MMULT(LEN(D$13:H$16),{1;1;1;1;1}))),I$13:I$16)

Blessings!

[dunder]

Hi again, dunder!

Check this new approach (NON - CSE):
[I3] : =LOOKUP(,0/FREQUENCY(0,1/(1+(MMULT(N(COUNTIF(D3:H3,D$13:H$16)>0),{1;1;1;1;1})=5)*LEN(J$13:J$16))),I$13:I$16)

or, without taking care about J column (NON - CSE too):
[I3] : =LOOKUP(,0/FREQUENCY(0,1/(1+(MMULT(N(COUNTIF(D3:H3,D$13:H$16)>0),{1;1;1;1;1})=5)*MMULT(LEN(D$13:H$16),{1;1;1;1;1}))),I$13:I$16)

Blessings!

This is amazing and works perfect! Thank you very much for taking the time to help! Now time to try to understand how the formula works