Vlookup using a comma separated search key in Google Sheets

Question

I am trying to use a vlookup (or some other function) to populate a list of numbers with usernames based on comma-separated data appearing in a single cell. I have attempted wild cards, and regmatch functions but can't seem to get the syntax correct (see the development tab of the linked spreadsheet for my efforts). Essentially, I'd like to populate column B of the "columns" tab with usernames from column E of the All tab that corresponds to the numbers in column A derived from the comma-separated input in column D of the "all" Tab.

https://docs.google.com/spreadsheets/d/1Zebrp15784rtKb8obSr1ohWbjwm28owVTLSf1cBixzs/edit#gid=556664780

Thanks in advance for any support.

Answer 1

=INDEX(IFNA(VLOOKUP(A2:A*1, SPLIT(FLATTEN(IF(IFERROR( SPLIT(ALL!D2:D, ","))="",,SPLIT(ALL!D2:D, ",")&"​"&ALL!E2:E)), "​"), 2, )))

This is the formula that worked the best. It treated each of the comma separated values separately and allowed me to pull in the names from the appropriate column. Thanks for the support!

Answer 2

Solution without VLOOKUP:

=IFERROR(
  INDEX(E:E, 
    MAX(
      IFERROR(MATCH((ROW ()-1) & ",*",D:D,0), -1),          
      IFERROR(MATCH("* " & (ROW ()-1) & ",*",D:D,0), -1),
      IFERROR(MATCH(", " & (ROW ()-1),D:D,0), -1)
    ),
  1), 
"")

Explain:

• MATCH((ROW ()-1) & ",*",D:D,0) - the first number (9, 15, 21)

• MATCH("* " & (ROW ()-1) & ",*",D:D,0) - a middle number (9, 15, 21)

• MATCH(", " & (ROW ()-1),D:D,0) - the last number (9, 15, 21)

• if no match ROW()-1 with data value will be -1, then it fires ERROR

  (when we try to get aname by row == -1)

• if we have ERROR we show an empty string

Result:

Answer 3

use:

=INDEX(IFNA(VLOOKUP(A2:A*1, SPLIT(FLATTEN(IF(IFERROR(
 SPLIT(ALL!D2:D, ","))="",,SPLIT(ALL!D2:D, ",")&"​"&ALL!E2:E)), "​"), 2, )))