1

I have here a sample project, where columns A to C are in FORM RESPONSES TAB/SHEET and columns D and E are in RECORDS TAB/SHEET.

In the RECORDS I have a list of names, in alphabetical order, and a column for BATCHES(E9).

What I wish to have as an output is to AUTOMATICALLY bring/distribute the batches from RESPONSES!B4:B to the respective attendees in RECORD TAB (E10:E).

NOTE: The names are already available in the Google Form(Checkbox type), so the list of names in RECORD TAB are just the same as in the Google Form.

But the problem is, there are responses in C4:C that has multiple names in a single cell, and I don't know what to do anymore. hehe. I have already tried the INDEX and MATCH formulas based on how I understood it as a total beginner in Google Sheet, but it only works on the first name.

I hope that someone could help me achieve the output that I wanted to achieve.

Thank you so much and may God bless everyone! 🙂

Here's the screenshot of my sample project:

Here's the screenshot of my sample project:

Improve this question
3
  • Welcome. Please remember that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well.
    – marikamitsos
    Commented Apr 20, 2021 at 12:18
  • Hi! Thank you for guiding me, I have accepted your answer, and now I am trying to figure out how to upvote it. Your answer really helped me a lot. Thank you so much!
    – ToyDarts
    Commented Apr 21, 2021 at 3:56
  • "I am trying to figure out how to upvote it". Just press the up-arrow once. Although the message will say you don't have enough reputation, it will show in the future when you do.
    – marikamitsos
    Commented Apr 21, 2021 at 21:36

1 Answer 1

Reset to default
1

Please try the following

=INDEX(IFERROR(VLOOKUP(D8:D,
                 QUERY(SPLIT(FLATTEN({Respos!B3:B&"@"&SPLIT(Respos!C3:C,CHAR(10))}),"@"),
                          "select Col2, Col1 where Col2 <>'' "),2,0)))

(please -as always- adjust formula according to your ranges and locale)

enter image description here

Additional info (following OP's comment)

  • @ is just a symbol we use as the identifier for the SPLIT function.
    It could be anything like ♞ or 🐰, NOT present in our text.
  • Col1 and Col2 are the column numbers in our query.
    Be careful because they are case sensitive. That means col OR COL will NOT work.

Functions used:

Improve this answer
1
  • Wow! This is amazing! It worked 100%. Thank you soooo much! May I ask, what are the two "@" and "col1" and "col2" for? and the "2" before the "0"?
    – ToyDarts
    Commented Apr 21, 2021 at 3:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.