Vlookup for a transposed list or Multiple data validation lists

[exelnube]

Hi attached is a worksheet which is part of a much larger workbook.
Col B: Brand Name
Col C: Subscription Tier (gold, silver, platinum which corresponds to states.
Col: E-BD : 51 states.

Data sheet consists of tables for each tier to state.

1. I'd like to be able to pick tier in dropdown ( already enabled via data validation).
2. Id like to be able to display an icon under the states ( Green for Yes, Red for NO)
3. there is a possibility that someone might have a state outside of the tier.. so I should be able to manually edit that one cell for that state to display another ICON so we know it's A'La Carte.

any help is appreciated. If's and Vlookups didnt make sense. Iconsets are all based on 0's and 1's

A125 is the last row for the formula as i have 125 brands.

thank you again for your help!

[ChemistB]

So there is only 1 table in Data. I do not see how tiers are related to product. Are they? If not, then CA, for example, is Green for every tier, for every product. Yes?

[exelnube]

that is correct.. there are overlaps for some states in the tiers.

CA will be green in all tiers.

[exelnube]

that is correct.. there are overlaps for some states in the tiers.

CA will be green in all tiers.

[ChemistB]

I used this formula in E5 copied right and down

=--(ISNUMBER(MATCH(E$4, INDEX(tbl_states, ,MATCH($C5, tbl_states[#Headers],0)),0)))

This puts either a 1 (for true) or 0 (for false in each cell. Then I used the Icon Conditional Formatting to replace the number with a Green or Red light. Is this what you are looking for?

As far as manually changing things (like adding that a specific state meets "Annual $1500 tier"). Just enter a 1 into the appropriate cell. However, that does remove the formula from that cell and it would need to be changed manually from that point onward.

[exelnube]

this looks like it'll work. I'll plug this into my workbook and see if it will operate the way I want it to.

Thank you for your help. I appreciate your time.