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.

CodePudding user response：

use:

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