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!

 

[Margooox]

Also I worked on a different formula that but everything it returns is wrong. What's more, if one of the Steps in the RecipePlanification tab is empty, none of them work.

Here is an updated version of the file if you want to look into it.

 

[jasonb75]

I'm not seeing that intentional typos in the internal names would work, it would just move the problem of ambiguous keywords to a different part of a more complex formula.

I think that the only way to do what you want to accurately is to have the full name in the 'Choice' columns rather than a shorter version.

 

[Margooox]

I think that the only way to do what you want to accurately is to have the full name in the 'Choice' columns rather than a shorter version.

This should already be the case. I manually inserted some basic ingredient names for testing purposes but normally you'd pick one of the options from the dropdown menu.

 

[jasonb75]

This should already be the case.

It is not the case in the example that you provided. Perhaps you could consider being a bit more accurate with your questions and examples in future, that way we could be more accurate with the answers that we provide.

 

[Margooox]

Sorry if my questions/answers aren't accurate, I'm trying my best and I don't want to be annoying but at the same time English isn't my first language. I'm not sure what was wrong in what I said...

I uploaded this new version which has the full ingredient names as you were suggesting earlier.

 

[jasonb75]

You originally asked for a partial match which was what I was working on, if the drop downs use the proper name then it is an exact match not a partial. Exact matches are much easier to count than partials as they require less manipulation and are not prone to errors.

I think that this is right but F7 is empty in the sample, as is the dropdown that feeds it. I have no idea what should be there so I'm going on the assumption that it will be the same list of exact matching records.

=SUM(COUNTIFS(AromaCombinations!$C$3:$FY$3,RecipePlanification!F8:F15,
INDEX(AromaCombinations!$C$4:$FY$182,MATCH(RecipePlanification!F7,AromaCombinations!$B$4:$B$182,0),0),"G"))

BTW, I would never have guessed that English was not your first language.

 

[Margooox]

Yes, I need a partial match to allow me to easily add ingredients variants.
If it's an exact match, it will only match "Apple" with "Apple", but I would need it to match with "Red Apple" too. With this current formula, "Maple Syrup" doesn't match with "Maple" when it should.

I guess I could always add some extra lines for every single variant but that would make the "AromaCombinations" tab 1000 lines long, if not more, which I'd rather avoid. Hope I'm making sense!

And no worries, it's just that I'm learning as I go and it's already difficult to wrap my mind around complex Excel formulas and on top of that I'm doing it in English so things might get lost in translation haha

 

[jasonb75]

There is a way around it but it starts getting complicated, especially if you need a partial match in both row and column. I'll set up a small example for you when I get home tonight.

 

[Margooox]

Thank you, I appreciate the help very much!

 

[jasonb75]

A little bit of clarification needed, are we comparing the contents of F9:F17 or E9:E17?

If F9:F17 then where does 'Cow Cream' come from? I can't see it in the combination table. Taking a shorter description from the planner and matching it to a longer one in the combinations table can be done, or possibly the reverse. Trying to do partial matches both ways is not going to work.