As VLOOKUP only match the first row I will show who to use arrays, FILTER, JOIN and ARRAY_CONSTRAIN to get around this limitation.

Lets assume we have a table of things like this where needs specifies the ID number needed by the thing. (Ignore the fact that we would like to have multiple needs, but feel free to comment how to solve this!)

How to follow the needs backwards, ie how do we see that cups are needed both by Coffee and Tea? Lets try VLOOKUP!

We want to find the ID of everything that needs the current ID. Lets try:

=VLOOKUP(A2,{C:C,A:A}, 2, false)

• A:A means the entire column A
• { } is the array syntax
• {C:C, A:A} creates a range with column C then column A
• VLOOKUP searches the first column in the range
• #N/A error does not look very pretty
• Wrong! Cup are needed by both Tea and Coffee. VLOOKUP only returns the first result, 1 in this case.

After googling and and looking at stackoverflow I managed to come up with this solution:

= JOIN(
   ",", 
   ARRAY_CONSTRAIN(
     IFERROR(
        FILTER({A:A,C:C}, C:C = A2), 
        {""}), 
     ROWS(A:A), 1))

• FILTER to find the rows I want
• IFERROR to control the error message, in this case by creating an empty array
• ARRAY_CONSTRAIN to only keep the first column in the range
• JOIN to concatenate the array into a string