Lookup/index match?

[BradleyN1]

Hi,

I have a worksheet which I want to display the comments only from a questionnaire but based on whatever is selected from a list on data validation.

How would I achieve this? Vlookup obviously just picks out the first but I need something so the formula makes it drop a row each time to populate the rest for the unique comments.

My spreadsheet isn't exactly as depicted below but this is the best way I can explain it

Comments sheet
A1 Drop down list of names
B1 to B(dependent on number of comments) displays lists of comments based on selection

Another sheet (all the data)
A1 list of names
B1 comments

Thank you!

 

[RasGhul]

Hi Bradley,

You can drag this down to as many comments you think you will return and extend the ranges as needed for your data;

Book1
	A	B
1	Name	Comments
2	Amber	Its too hot
3	Jason	Its too cold
4	David	I like it
5	Amber	I hate it
6	Jason	thats awesome
7	David	Its too hot
8	Amber	Its too cold
9	Jason	I like it
10	David	I hate it
11	Amber	thats awesome
12	Jason	Its too hot
13	David	Its too cold
14	Amber	I like it
15	Jason	I hate it
16	David	thats awesome
Data

Book1
	A	B
1	Name	Comments
2	Amber	Its too hot
3		I hate it
4		Its too cold
5		thats awesome
6		I like it
Comments

Cell Formulas
Range	Formula
B2	{=IF(ROWS(Comments!$B$2:B2)>COUNTIF(Data!$A$2:$A$16,Comments!$A$2),"",INDEX(Data!$B$2:$B$16,SMALL(IF(Data!$A$2:$A$16=Comments!$A$2,ROW(Data!$A$2:$A$16)-ROW(Data!$A$2)+1),ROWS(Comments!$B$2:B2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[BradleyN1]

Hi Bradley,

You can drag this down to as many comments you think you will return and extend the ranges as needed for your data;

Book1
	A	B
1	Name	Comments
2	Amber	Its too hot
3	Jason	Its too cold
4	David	I like it
5	Amber	I hate it
6	Jason	thats awesome
7	David	Its too hot
8	Amber	Its too cold
9	Jason	I like it
10	David	I hate it
11	Amber	thats awesome
12	Jason	Its too hot
13	David	Its too cold
14	Amber	I like it
15	Jason	I hate it
16	David	thats awesome
Data

Book1
	A	B
1	Name	Comments
2	Amber	Its too hot
3		I hate it
4		Its too cold
5		thats awesome
6		I like it
Comments

Cell Formulas
Range	Formula
B2	{=IF(ROWS(Comments!$B$2:B2)>COUNTIF(Data!$A$2:$A$16,Comments!$A$2),"",INDEX(Data!$B$2:$B$16,SMALL(IF(Data!$A$2:$A$16=Comments!$A$2,ROW(Data!$A$2:$A$16)-ROW(Data!$A$2)+1),ROWS(Comments!$B$2:B2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

You my friend, are amazing!
Thank you

 

[RasGhul]

You're welcome, thanks for the feedback.