Return table headers based on multiple selection criteria (Index?)

[olga6542]

I need to figure out which option is applicable based on my selection criteria. So, if I select NY, options 1, 3 and 4 are applicable. Once add NJ, only options 3 and 4 are applicable. After I add 1 more selection like TN, I only get option 3 and so on. Is it an Index function? Can you please help?

[AliGW]

Where would the selections be made and in what way?

[olga6542]

The selection would be made on the same worksheet or multiple worksheets. So for each row selection that I make, so it should return the header based if that row is applicable.

[AliGW]

Sorry, I am not visualising what you are describing.

Can you provide a sample that shows (mocked up manually) the results you are looking for?

[olga6542]

I don't know if I should be using formulas or pivot tables. Basically I need to filter by the states. A stands for applicable and NA stands for not applicable. So if I select NY, I want cell N6 to read Option 1, Option 3 and Option 4 but I need cell N6 to be updated as I select multiple options. So if NY has option 1, 3 and 4 applicable, then let's say I select NJ. Then it looks for the applicability for Option 1, 3 and 4 as NY was previously selected. Out of 3 options, applicability for NJ are options 3 and 4. SO, cell N6 should now read Options 3 and Options 4. Next if I select TN, out of 2 options only 1 option is applicable. Now cell N6 reads Option 3. So, A is a common factor between the states for each option. Hope it clarifies what I am trying to do.

[JeteMc]

Perhaps this will help.
1. Populate C15:G15 using: =IF(COUNTIFS(C4:C14,"A",$H4:$H14,"x")=COUNTA($H4:$H14),C2,"")
2. Populate C16:G16 using: =IF(AND(B16<>"",C15<>""),B16&", "&C15,IF(C15<>"",C15,B16))
3. Populate B16 using: =""
4. Populated N6 using: =G16
Make selections in column H (as AliGW's question was never addressed I chose this method)
Please update your profile. The 2003 version did not support .xlsx files.
Let us know if you have any questions.