not sure which formula to use for this ...

[twiley23]

Hello all, I'm stuck trying to figure out if this is even possible in excel. Basically what im trying to set up is a chart that will give me a yes or no answer. sounds simple enough but where i'm struggling is this. in cell A1 there will be a drop down list of all 50 states. same goes in cell B1. What i would like to do is show some states will show a yes or no for different state combinations, for example , California and Arizona would get a "yes", where as California and New York would get a "No" any ideas ?? Thanks

 

[Peter_SSs]

So you would have 2,450 possible outcomes in those 2 cells (or 2,500 if the same state can be selected in both cells).
Do you have a list of all those outcomes and what the expected result for each is somewhere in your workbook?

Also, does CA & AZ always get the same result as AZ & CA?

 

[twiley23]

yes, CA and AZ would get the same result as AZ and CA, I'm working on a list of which states will work with each other , and which states wont work together, i haven't quite put all the info together yet, but i have some of it

 

[Peter_SSs]

Then you could set up something like this.
- Lookup table in columns E:F. If you have Ohio|Utah, there is no need to also have Utah|Ohio as the C2 formula checks both. If you want, this table could go on another sheet or be hidden on this sheet.
- Drop-down choices in A1 and B1 as you described.

Excel Workbook
	A	B	C	D	E	F
1	California	Arizona	Yes		Alaska|California	No
2					Ohio|Utah	Yes
3					Arizona|California	Yes
4					Ohio|New York	No
5
Yes No

 

[twiley23]

Thank you so much for your help , i was also wondering what if some didnt match , example az to ca is fine but ca to az is no good