EDIT: A better solution that doesn't require an extra column
You can use the functionCOUNTIFS
to check if your first range is equal to something and if your second range is equal to something.
I used the function: =COUNTIFS(B3:B8, "Tonic Water", C3:C8, "Gin")
To count how many combinations of Tonic Water + Gin where in the two ranges.
You could also see the function like this: =COUNTIFS(RANGE1, RECIPE, RANGE2, INGREDIENT)
Here is an image explaining it a bit better:
I checked your "input and wanted output" image and the data in the output table doesn't match up with whats in the input ranges. However if I look at your question it seems that my answer will work for that.
EDIT: My comment answer:
Put the ingredients on the left side and the ingredients at the top. Then use COUNTIFS
to check how many times an ingredient is in a recipe. From this table you can easily check if and what ingredients share a recipe.
In the image you can see Ginger Ale and Vodka both share the recipe Moscow Mule