COUNTIF + INDEX MATCH with partial match

[Margooox]

Hello everyone!

I have this intersectionnal grid which shows me all the harmonious flavor combinations you can make while cooking.
"G" stands for "Good" and "AT" stands for "Acquired Taste".

I have written this formula to be able to check the value from the intersection of the row of Ingredient1 with the column of Ingredient 2.
I need the match to be partial as I will have different types of ingredients. Example: green apple, red apple, candied apple, applesauce.... which all contain the word "apple" and share the same flavor as a result.

This formula currently returns #N/A errors. Any idea why?

Thank you so much in advance!

 

[jasonb75]

You could probably do it with SUM(COUNTIFS(

I can't copy and paste a formula from a screen capture and I don't have time to mess about retyping it, if you post it properly then I'll edit it for you.

 

[Margooox]

You could probably do it with SUM(COUNTIFS(

I can't copy and paste a formula from a screen capture and I don't have time to mess about retyping it, if you post it properly then I'll edit it for you.

So I got it working with this formula after a lot of trial and error:

=COUNTIF(


INDEX(AromaCombinations!$B$2:$FY$181,
MATCH("*"&RecipePlanification!F7&"*",  AromaCombinations!$B$2:$B$181,0),
MATCH("*"&RecipePlanification!$F$8&"*", AromaCombinations!$B$2:$FY$2,0)),
"G") +

COUNTIF(INDEX(AromaCombinations!$B$2:$FY$181,
MATCH("*"&RecipePlanification!F7&"*",  AromaCombinations!$B$2:$B$181,0),
MATCH("*"&RecipePlanification!$F$9&"*", AromaCombinations!$B$2:$FY$2,0)),
"G") +

COUNTIF(INDEX(AromaCombinations!$B$2:$FY$181,
MATCH("*"&RecipePlanification!F7&"*",  AromaCombinations!$B$2:$B$181,0),
MATCH("*"&RecipePlanification!$F$10&"*", AromaCombinations!$B$2:$FY$2,0)),
"G") +

COUNTIF(INDEX(AromaCombinations!$B$2:$FY$181,
MATCH("*"&RecipePlanification!F7&"*",  AromaCombinations!$B$2:$B$181,0),
MATCH("*"&RecipePlanification!$F$11&"*", AromaCombinations!$B$2:$FY$2,0)),
"G") +

COUNTIF(INDEX(AromaCombinations!$B$2:$FY$181,
MATCH("*"&RecipePlanification!F7&"*",  AromaCombinations!$B$2:$B$181,0),
MATCH("*"&RecipePlanification!$F$12&"*", AromaCombinations!$B$2:$FY$2,0)),
"G") +

COUNTIF(INDEX(AromaCombinations!$B$2:$FY$181,
MATCH("*"&RecipePlanification!F7&"*",  AromaCombinations!$B$2:$B$181,0),
MATCH("*"&RecipePlanification!$F$13&"*", AromaCombinations!$B$2:$FY$2,0)),
"G") +

COUNTIF(INDEX(AromaCombinations!$B$2:$FY$181,
MATCH("*"&RecipePlanification!F7&"*",  AromaCombinations!$B$2:$B$181,0),
MATCH("*"&RecipePlanification!$F$14&"*", AromaCombinations!$B$2:$FY$2,0)),
"G") +

COUNTIF(INDEX(AromaCombinations!$B$2:$FY$181,
MATCH("*"&RecipePlanification!F7&"*",  AromaCombinations!$B$2:$B$181,0),
MATCH("*"&RecipePlanification!$F$15&"*", AromaCombinations!$B$2:$FY$2,0)),
"G")

It's pretty awful though and ideally it would have to be longer to take into account all of the cells of each column (this is just the first column and I have ten!)

Thanks for the help!

 

[jasonb75]

Try

=SUM(COUNTIFS(AromaCombinations!$B$2:$FY$2,"*"&RecipePlanification!F8:F15&"*",
INDEX(AromaCombinations!$B$2:$FY$181,MATCH("*"&RecipePlanification!F7&"*",AromaCombinations!$B$2:$B$181,0),0),"G"))

 

[Margooox]

So I removed the SUM from your function to see the value breakdown.

The left column is the correct answer (total of 11), and the right column is with your formula without the SUM. With the SUM, it would return 287.

 

[jasonb75]

The left column is the correct answer (total of 11)

Without seeing the full table and the current criteria in F7 and F8:F15 I would question whether the results are correct, or correct by chance.

If you had "Black" as one of the criteria then yours would only ever count 'Black Currant'. IT would ignore 'Black Pudding' and 'Blackberry'. My formula would work slightly differently but is likely to still be incorrect in such cases. Without more details and a sample that we can work with, we could spend weeks getting nowhere.

It is preferable that you upload samples of your sheet using XL2BB, however, I think that we would need the full sheet for clarity so I would suggest uploading it to a file sharing service then posting the link here.

Please use the same criteria as you have for the test in your last reply so that we can have some consistency.

 

[Margooox]

There you go!

The green tabs are the tabs that have data/formulas relevant to this specific issue.

And I'm really thankful for the help by the way!

 

[jasonb75]

The formula is counting the blanks in error, something that I had not allowed for.

Before correcting the formula, there is still one point that needs addressing.

If you had "Black" as one of the criteria then yours would only ever count 'Black Currant'. IT would ignore 'Black Pudding' and 'Blackberry'.

Using the above as an example, what would you want to be counted in the case of any similar scenario?

Should it count one, or all matching records? If only one, how would you determine which is correct?

If all, should the correct result be 3 or 9? (3 matching rows and 3 matching columns means 9 matching records).

 

[Margooox]

So because the ingredients that the player can pick are already pre-determined from the drop-down list, that problem should never really arise as the word "Black" can't be put there on its own. Currently, the AromaCombinaiton tab keeps track of all the flavor combinations by using very general aroma name. I assumed that the formula would look for the closest match so it would be able to tell thet "Black Pudding" and "Black Currant" aren't the same, but maybe I was mistaken.

Ideally the correct result should return 3 (to avoid counting duplicates) but if it doesn't it would be fine. I'm simulating a crafting system for a game and this number will never be showned to anyone. It will be used for the overall scoring system of the game. So whether it returns 3 or 9, this can be balanced out by determining a max and low score and turning it into percentages based of highest and lowest score to determine a success range (hope that makes sense to you).

 

[jasonb75]

I assumed that the formula would look for the closest match so it would be able to tell thet "Black Pudding" and "Black Currant" aren't the same, but maybe I was mistaken.

The formula will only look for what it is told to look for, if you look for 'Black' then they are the same, if you look for 'Pudding' or 'Currant' then they are different.

In such situations the 'closest match' will either be the first thing found that contains the keyword, the last thing found that contains the keyword, or everything that contains the keyword depending on the method used.

Things that might be less obvious, but would be a problem are Apple and Pineapple.

For some reason the formula that should work, is not working. I'll have another look at it later and hopefully get a working formula posted later this evening.

 

[Margooox]

So if you go into the AllData I have a separate column for the name of the ingredient (the string displayed to the user) and the internal name. Could we workaround this issue by making intentionnal typos on the words belonging to the "Internal Name" column. It would require an extra layer of INDEX/MATCH or VLOOKUP but it could avoid having false duplicates.