I have an excel spreadsheet with one column for ingredients and one column for recipes. (see on the left in the picture below). I would like to create a matrix with all ingredients (as seen in the picture on the right) that shows how many times each pair of ingredients appears in the same recipes.

So far I tried it with combinations of `VLOOKUP`

and `COUNTIFS`

but I did not get a sufficient solution.

The command I am looking for should be something like: "For every time ingredient-x (Gin) appears in a recipe, check which other ingredients appear in the same recipe. And count these co-appearances in the matrix."

My limited knowledge tells me that this is only possible with an algorithm written in VBA because I'd have to tell it to "for every *ingredient* count home often *every other ingredient* appears in the same *recipe*.

I would appreciate your help, and since this is my first question on Superuser please don't hesitate to give me feedback on how to improve my asking!