How to list ingredients for a product if one product can have another product as ingredient

Question

Ok, so here is my simple table, P1-P4 = Product, Ix = Ingredient

+---------------------+
|   A    B    C    D  |
+---------------------+
| 1 P1   I1   I2   I3 |
| 2 P2   I4   I5   I6 |
| 3 P3   I7   I8   P4 |
| 4 P4   I10  I11     |
+---------------------+

Now what I'm trying to accomplish is, that I can list all Ingredients for example for P3 so I will get a list that looks like that

I7
I8
I10
I11

Is that even possible via a formula in Excel without using VBA? Thanks in advance

Answer 1

Here is a recursive UDF that does what you want. I do not believe that a formula will do it.

Dim cnt as long

Function viceversa(lkup As Variant, rng As Range, nmbr As Long, Optional rec As Boolean) As String
Dim rngArr
Dim temp as string
If rec = False Then cnt = 1

rngArr = rng.Value
For i = LBound(rngArr, 1) To UBound(rngArr, 1)
    If rngArr(i, 1) = lkup Then
        For j = LBound(rngArr, 2) + 1 To UBound(rngArr, 2)
            If Left(rngArr(i, j), 1) <> "P" Then
                If cnt = nmbr Then
                    viceversa = rngArr(i, j)
                    Exit Function
                Else
                    cnt = cnt + 1
                End If
            Else
                temp = viceversa(rngArr(i, j), rng, nmbr, True)
                If temp <> "" Then
                    viceversa = temp
                    Exit Function
                End If
            End If
        Next j
    End If
Next i
viceversa = ""

End Function

Put this in in a module attached to the workbook. cnt is a public variable and theDim cnt as Long` needs to be at the top of the module.

Then you can call it like this:

=viceversa($A$3,$A$1:$D$4,1)

$A$3 is the Product, $A$1:$D$4 is the Range including the first column of products. 1 is the first ingredient. I used a helper column with numbers:

It does not matter if you have 1 or more products as ingredients it will continue till all are accounted for.

Note

One thing that will need to change is the test of whether it is a product or not. So change this line If Left(rngArr(i, j), 1) <> "P" Then to something that works to denote that the ingredient being tested is actually a product.