Title: Google Sheets: VLOOKUP formula for a multi line cells range
Tags: google-sheets;formulas;google-sheets-query;google-sheets-arrayformula;vlookup
Question: 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:


Comment: Welcome. Please remember that as per [site guidelines](https://webapps.stackexchange.com/help/someone-answers) when an answer addresses your question, you should [accept](https://webapps.stackexchange.com/help/accepted-answer) it and even [upvote](https://webapps.stackexchange.com/help/why-vote) it so others can benefit as well.

Comment: 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!

Comment: *"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.

Here is the accepted answer: Please try the following
```=INDEX(IFERROR(VLOOKUP(D8:D,
                 QUERY(SPLIT(FLATTEN({Respos!B3:B&amp;&quot;@&quot;&amp;SPLIT(Respos!C3:C,CHAR(10))}),&quot;@&quot;),
                          &quot;select Col2, Col1 where Col2 <&gt;'' &quot;),2,0)))
```
(please -as always- adjust formula according to your ranges and locale)


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:

```INDEX```
```IFERROR```
```QUERY```
```VLOOKUP```
```SPLIT```
```FLATTEN```
```CHAR```


Comment for this answer: 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"?


{'source': 'StackExchangeDataset', 'subset': 'webapps.stackexchange.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 3, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2021:00:04/20/21', 'FavoriteCount': 1.0, 'PostTypeId': 1, 'Tags': '<google-sheets><formulas><google-sheets-query><google-sheets-arrayformula><vlookup>', 'Title': 'Google Sheets: VLOOKUP formula for a multi line cells range', 'ViewCount': 180, 'id': 153715, 'user_AboutMe': None, 'user_AccountId': 16531399.0, 'user_DisplayName': 'Totoy Darts', 'user_Reputation': 13.0, 'user_Views': 6.0, 'user_id': 270643.0}}