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!

 

[JamesCanale]

It looks like it should work. unless whatever in cells F5 or F7 doesn't match at all (some spelling mistake).

Here is something that will output a filtered array:

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F	G	H
1		Apple	Bean	Curry	Dry Apple	Egg	Eggplant	Fried Egg
2	Apple			G		G
3	Bean	G		G			G	G
4	Curry	G				G		G
5	Dry Apple		G	G		AT	G
6	Egg				G
7	Eggplant	G	G
8	Fried Egg		G	G
9
10
11		Item 1	Apple
12		Item 2	Egg
13
14			Egg	Eggplant	Fried Egg
15
16		Apple	G
17		Dry Apple	AT	G
18
19
20
21
22
Sheet12

Cell Formulas
Range	Formula
B14:E14	B14	=SORT(UNIQUE(IF(IFERROR(SEARCH(C12,B1:H1),"")<>"",B1:H1,""),TRUE))
B15:B17	B15	=SORT(UNIQUE(IF(IFERROR(SEARCH(C11,A2:A8),"")<>"",A2:A8,"")))
C15:H22	C15	=IF(IFNA(INDEX($B$2:$H$8,MATCH($B15,$A$2:$A$8,0),MATCH(C$14,$B$1:$H$1,0)),"")=0,"",IFNA(INDEX($B$2:$H$8,MATCH($B15,$A$2:$A$8,0),MATCH(C$14,$B$1:$H$1,0)),""))
Dynamic array formulas.

 

[jasonb75]

Looks like the wildards should be on the aroma combinations, not the recipe plans.

 

[Margooox]

It looks like it should work. unless whatever in cells F5 or F7 doesn't match at all (some spelling mistake).

Here is something that will output a filtered array:

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F	G	H
1		Apple	Bean	Curry	Dry Apple	Egg	Eggplant	Fried Egg
2	Apple			G		G
3	Bean	G		G			G	G
4	Curry	G				G		G
5	Dry Apple		G	G		AT	G
6	Egg				G
7	Eggplant	G	G
8	Fried Egg		G	G
9
10
11		Item 1	Apple
12		Item 2	Egg
13
14			Egg	Eggplant	Fried Egg
15
16		Apple	G
17		Dry Apple	AT	G
18
19
20
21
22
Sheet12

Cell Formulas
Range	Formula
B14:E14	B14	=SORT(UNIQUE(IF(IFERROR(SEARCH(C12,B1:H1),"")<>"",B1:H1,""),TRUE))
B15:B17	B15	=SORT(UNIQUE(IF(IFERROR(SEARCH(C11,A2:A8),"")<>"",A2:A8,"")))
C15:H22	C15	=IF(IFNA(INDEX($B$2:$H$8,MATCH($B15,$A$2:$A$8,0),MATCH(C$14,$B$1:$H$1,0)),"")=0,"",IFNA(INDEX($B$2:$H$8,MATCH($B15,$A$2:$A$8,0),MATCH(C$14,$B$1:$H$1,0)),""))
Dynamic array formulas.

Thanks for the help!

What I'm trying to achieve is a way to calculate how many "G" are found within my recipe as a whole by comparing all the ingredients used as shown in the example below:

 

[Margooox]

So I would need a number to be returned for each "G" found, hence my use of the COUNTIF formula.
I'm new at Excel so I'm not even sure if this is the best method to achieve something like that.

 

[Margooox]

Looks like the wildards should be on the aroma combinations, not the recipe plans.

It was worth a try but it didn't change anything sadly.

 

[jasonb75]

One observation, F5 is empty so the wildcard match will always return the first item in the criteria range, in the example provided that is B2, so it will be counting in row 2 where there are no "G"'s to count.

 

[Margooox]

One observation, F5 is empty so the wildcard match will always return the first item in the criteria range, in the example provided that is B2, so it will be counting in row 2 where there are no "G"'s to count.

That was actually what was causing the problem!

Now I gotta figure out a way of comparing each cell to the group so that I don't need to copy paste the same line 50 times!

 

[jasonb75]

Now I gotta figure out a way of comparing each cell to the group so that I don't need to copy paste the same line 50 times!

Not following exactly what you mean by 'the group' when looking at your images but I'm sure it should be something fairly simple.

 

[Margooox]

Not following exactly what you mean by 'the group' when looking at your images but I'm sure it should be something fairly simple.

I need to compare F5 with F7, F8, F9, etc... Essentially run COUNTIF for my entire ingredients list.