Index with Drop Down

[bmlaw]

Hello,

I am trying to create a drop down list of ZIP codes and then, depending on whether someone says "yes" or "no" to a question, make a recommendation as to the best organization to contact. This recommendation would change based on the ZIP code.

I have the drop down list set up and have a column for "yes" and one for "no" (the user would check a box, not enter text).

This is my array code for the referral so far:
{=IF(ISERROR(INDEX(Worksheet!$T$1:$S$44,SMALL(IF(Worksheet!$S$1:$S$44=Worksheet!$W$1,ROW(Worksheet!$S$1:$S$44)),ROW(Worksheet!2:2)),2)),"",INDEX(Worksheet!$S$1:$T$44,SMALL(IF(Worksheet!$S$1:$S$44=Worksheet!$W$1,ROW(Worksheet!$S$1:$S$44)),ROW(Worksheet!2:2)),2))}

Column S is the "No" check box and T is the referral.

It works just fine for generating a referral based on yes/no.

How do I incorporate the drop down list information into this code so that the referral organization will change based on the ZIP information?

 

[bmlaw]

anyone?

 

[Mike Szczesny]

Hi bmlaw,
Since you did not show how it would look. So, I am taking a guess as to how this would look like. I am assuming the recommendation would be specific to a zip code and no/yes combination. For my simple example I have a, b, and c. with no or yes. Therefore, I would would have 6 specific recommendations. So I have come up with this. I used data validation to select for a specific criteria. Hope this helps.
Mike Szczesny

Excel 2012
	A	B	C	D	E	F	G
1	Codes	Question	Recommendation			No	a
2	a	Yes	Go Home	Go There		Yes	b
3	a	No	Go Fish				c
4	b	Yes	Go Crazy
5	b	No	Go There
6	c	Yes	Go Here
7	c	No	Go Somewhere
8
9	b	No
Sheet1

Cell Formulas
Range	Formula
D2	{=INDEX($C$2:$C$7,SMALL(IF($A$2:$A$7&$B$2:$B$7=$A$9&$B$9,ROW($C$2:$C$7)-ROW($C$2)+1),1))}
Press CTRL+SHIFT+ENTER to enter array formulas.