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]

We are comparing F9:F17. I haven't added a "Cream" row that would match with "Cow Cream" in AromaComination yet.
Also if it's not too complicated to explain, why would partial matches both ways not work?

 

[jasonb75]

why would partial matches both ways not work?

It would work but would be complicated and unreliable. You would need two separate formulas, one for each way. The problem with that would be that any exact matches would be counted by both formulas, so you would then need a third formula to count the exact matches and deduct that from the total of the other two.

I haven't added a "Cream" row that would match with "Cow Cream" in AromaComination yet.

As I mentioned earlier in the thread, the example needs to be accurate. If there are errors and / or omissions then it leads to a lot of confusion.

 

[Margooox]

Sorry, I just assumed that the formula would return a 0 since there wouldn't be a match for this one, didn't realize it would pose a problem.

I'll send you a different version later that only uses ingredients referenced in AromaCombination.

 

[jasonb75]

On the count side of things it would return 0, but because a match is required on part of it there will be errors.

This formula will work for partial matches with the shorter version in the planner and the longer version in the combination sheet.

It looks for whole words only, so 'Black' will match to 'Black Currant' or 'Black Pudding' but not 'Blackberry'. This only works for the list in F9:F17, the entry in F7 is exact match only. You could use wildcards with match as you did before, but as I mentioned much earlier, MATCH only returns the first thing it finds which may not be the correct one in the event of a partial match.

I don't think that it will be possible to use the methods in this formula to allow for partial matches there as well in order to overcome the problem that I mention above.

=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"))

 

[Margooox]

Now it seems to be counting all the "G"s for one ingredient. I get really high numbers like 490 or 186 when I should get maybe as high as a 10.

I've been stuck on this for a week now and I think I'm going to scrap it and restructure my data list. I didn't realize that this would be this difficult / not really possible and I guess it was a good learning experience nonetheless.
If you come up with something else that could work, feel free to let me know. However I feel like you've already given me a lot of your time which I greatly appreciate so feel free to drop the problem.

I'll still update this thread when / if I find a workaround though!

 

[jasonb75]

Now it seems to be counting all the "G"s for one ingredient. I get really high numbers like 490 or 186 when I should get maybe as high as a 10.

Could you post a file where it is showing those results?
With the last file that you attached, I'm getting single digit results using an exact matching entry in F7.

 

[Margooox]

I have made a couple changes to the structure of the data which should simplify everthing. Here is the latest version.

I have manually added each ingredient's aroma in my "AllData" tab. That means that we can drop the partial match entirely.

This is what the "AromaCombo Score" tab looks like now.

I would need this formula to be usable for all the cells in column D:

=COUNTIF(INDEX(AromaCombinations!$B$2:$IQ$250,MATCH(D20,AromaCombinations!$B$3:$B$250,0),MATCH($D$21, AromaCombinations!$B$2:$IQ$2)),"G")

Currently it only checks D20 and D21, but I would need to compare each cell in column D to each other.

 

[jasonb75]

Could you check and confirm what the correct result should be if the formula is only looking at D21:D25 in the screen capture above.

I'm using that small group of items for testing the formula before expanding it to the full column.

Edit:- I think this is right based on the full column, it returns a total of 88 (or 5 with the smaller group mentioned above).

=SUM(IFERROR(COUNTIFS(AromaCombinations!$C$2:$FZ$2,TRANSPOSE(D20:D119),OFFSET(AromaCombinations!$C$3,MATCH(D20:D119,AromaCombinations!$B$3:$B$201,0)-1,,1,COLUMNS(AromaCombinations!$C$2:$FZ$2)),"G"),0))

 

[Margooox]

IT WORKS!

Thank you so so much for your help!!