How to return the values from a list while ignoring blanks?

[stlpony]

Hello!

Have a question ... Here is my table
[TABLE="width: 500"]
<tbody>[TR]
[TD]Christmas
[/TD]
[TD]EggNog[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Christmas[/TD]
[TD]Spaghetti[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Christmas[/TD]
[TD]Meatballs[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanksgiving[/TD]
[TD]Turkey[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanksgiving[/TD]
[TD]MashedPotatoes[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Holiday?[/TD]
[TD]Options?[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanksgiving[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl65, width: 64"]=IFERROR(INDEX($A$1:$B$5,MATCH(ROW()-ROW($A$8),$A$1:$A$5,0)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'm trying to make a simple survey ... If cell A8=Thanksgiving, I would like "Turkey" listed in cell B8, and Mashed Potatoes listed in B9. Thought I had the formula correct, but appears not ... Any idea??

 

[Fluff]

Try

Excel 2013/2016
	A	B
1	Christmas	EggNog
2	Christmas	Spaghetti
3	Christmas	Meatballs
4	Thanksgiving	Turkey
5	Thanksgiving	MashedPotatoes
6
7	Holiday?	Options?
8	Thanksgiving	Turkey
9		MashedPotatoes
Offer Received

Cell Formulas
Range	Formula
B8	=IFERROR(INDEX($B$1:$B$5,AGGREGATE(15,6,(ROW($B$1:$B$5)-ROW($B$1)+1)/($A$1:$A$5=$A$8),ROWS($1:1))),"")

 

[stlpony]

This is great! Now expanding this a little ... (I'm actually trying to create a dinner survey)...

 

[stlpony]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Holiday[/TD]
[TD]First Course[/TD]
[TD]Second Course[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Christmas[/TD]
[TD]EggNog[/TD]
[TD]Cookies[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Christmas[/TD]
[TD]EggNog[/TD]
[TD]Milk[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Christmas[/TD]
[TD]Spaghetti[/TD]
[TD]Bread[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Christmas[/TD]
[TD]Spaghetti[/TD]
[TD]Salad[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Christmas[/TD]
[TD]Meatballs[/TD]
[TD]Sauce[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanksgiving[/TD]
[TD]Turkey[/TD]
[TD]Corn[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanksgiving[/TD]
[TD]Turkey[/TD]
[TD]Veggies[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanksgiving[/TD]
[TD]MashedPotatoes[/TD]
[TD]Gravy[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanksgiving[/TD]
[TD]MashedPotatoes[/TD]
[TD]Stuffing[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Which Holiday?[/TD]
[TD]First Course Options?[/TD]
[TD]Second Course Options?[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Christmas[/TD]
[TD]EggNog[/TD]
[TD]Cookies[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]EggNog[/TD]
[TD]Milk[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Spaghetti[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl65, width: 142"]=IFERROR(INDEX($C$2:$C$10,AGGREGATE(15,6,(ROW($C$2:$C$10)-ROW($C$2)+1)/($B$2:$B$10=$B15),ROWS($2:4))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Spaghetti[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl65, width: 142"]=IFERROR(INDEX($C$2:$C$10,AGGREGATE(15,6,(ROW($C$2:$C$10)-ROW($C$2)+1)/($B$2:$B$10=$B15),ROWS($2:4))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Meatballs[/TD]
[TD][TABLE="width: 142"]
<tbody>[TR]
[TD="class: xl65, width: 142"]=IFERROR(INDEX($C$2:$C$10,AGGREGATE(15,6,(ROW($C$2:$C$10)-ROW($C$2)+1)/($B$2:$B$10=$B15),ROWS($2:4))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dropdown #1 [/TD]
[TD]Dopdown #2 [/TD]
[TD]Dopdown #3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



So, I'm trying to design a survey, by which DropDown #1 will show the holiday (either Christmas or Thanksgiving). If Christmas is selected, dropdown #2 will have EggNog, Spaghetti, or Meatballs. Depending on what is picked in #2 , the third option will show what is left based on the database up top.

Questions:
1) How do I make a dropdown that only shows the unique values in cells B13:B17?
2) My formula in Column C is not working ... If Spaghetti is selected, I want the options to show Bread or Salad ... So how do I add this third question / layer to my formula?

Thanks!

 

[Fluff]

This is now a completely different question, so you will need to start a new thread.