Check each comma-separated value in a cell if it matches a certain number

Question

In Google Sheets:

I have 2 sheets, "Sheet A" and "Sheet B".

"Sheet A" has two columns, Column A, whose cells contain comma-separated values like the following:

• A2: 1,3,45
• A3: 33,44,12, 125
• A4: 9
• A5:
• A6: 19,21

AND Column B, whose cells have names, like:

• B2: Hello
• B3: World
• B4: John
• B5: Doe
• B6: Volcano

"Sheet B", Column A has values like:

• A2: 9
• A3: 2
• A4: 3
• A5: 1

Within "Sheet B", Column B: I need to fetch the value contained in the "Sheet A", corresponding cell in Column B if it matches with "Sheet B", Column A:

So, based upon the above hypothetical values, it would be as follows: (in Sheet B, Column B)

• B2: John
• B3:
• B4: Hello
• B5: Hello

Any idea how I can do this? If it were not for the comma-separated values, I would have used VLOOKUP directly. I am sure there would be some simple way to do this. Looking forward to your help.

Thanks and regards,

Chintan.

P.S. Sorry for the terrible formatting of this question. Promise to do better on my next one once I learn more about formatting on stackoverflow.

Answer 1

See if this works ?

=iferror(ArrayFormula(vlookup(A2:A, {transpose(split(substitute(query(Sheet1!A2:A,,rows(Sheet1!A2:A)), " ", ","),",")), transpose(split(query(rept(Sheet1!B2:B&" ", if(len(Sheet1!A2:A), len(Sheet1!A2:A)-len(substitute(Sheet1!A2:A, ",",""))+1,0)),,50000)," "))}, 2, 0)))

Change sheet names and ranges to suit.

Example spreadsheet