0

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.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Tim Comer
  • 19
  • 2
  • 1
    Please, demonstrate your desired outout. you want to lookup all nubers in the cell or the fist one. – Osm Oct 12 '22 at 02:13
  • Do you need a script or a formula? – Daniil Loban Oct 12 '22 at 04:41
  • 1
    Kindly add input table and expected output table as **text table** (NOT as IMAGE) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). [Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 12 '22 at 13:01
  • Hi Osm. Thanks for your attention to this. Daniil and player0 hooked it up. You guys rock! – Tim Comer Oct 12 '22 at 21:01
  • Hey -TheMaster. Thanks for the heads up on all of this! – Tim Comer Oct 12 '22 at 21:03

3 Answers3

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:

enter image description here

Daniil Loban
  • 4,165
  • 1
  • 14
  • 20
  • Hi Daniil Loban... Thanks for looking into this for me. This is a great community. I searched for a long time and wasn't able to find this specific hack. Thanks also for the expaination! So awesome! – Tim Comer Oct 12 '22 at 20:37
  • Daniil, did I say thanks yet? I know I did but to have two responses in less than 24 hours and have two different approaches to solving a problem is really something. This is my first time posting in the community. Maybe someday I can give back a bit. – Tim Comer Oct 12 '22 at 21:00
  • @TimComer I'm glad that I could help, but you still have to choose one of the answers, it's supposed to be here) Good luck with your studies! – Daniil Loban Oct 13 '22 at 05:33
1

use:

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Hey Player0! Thanks! I legit just looked at the sheet and got the idea to nest a bunch of IfNas and vlookups * 100 on the off chance that someone would select 100 numbers and saw your formula in the sheet. Thanks so much! – Tim Comer Oct 12 '22 at 20:36
  • 1
    I am looking at this one and like . I was trying something similar using helper columns that this formula seems to do with split and flatten. People in my circle think I am a genius because I can do Vlookups and then I see this wizardry. Thanks again! – Tim Comer Oct 12 '22 at 20:59
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!

Tim Comer
  • 19
  • 2