need help to write formula in Excel

The attached Excel file is a simple example for my project with 10K rows.  "WEST ex CA" and "CENTRAL ex AR" in the drop down box is not working.  How to modify the formulas in G4-G9 to make them working?  Help needed!
sample.xls
jjxia2001Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
barry houdiniConnect With a Mentor Commented:
Thanks gowflow

.....how about this version to modify for that last condition

=((LEFT(INDEX(N$3:N$7,N$2),3)="ALL")+COUNTIF(INDEX(N$3:N$7,N$2),D4&"*"))*ISERR(SEARCH(" "&E4&",",INDEX(N$3:N$7,N$2)&","))

regards, barry

0
 
jjxia2001Author Commented:
See the corrected samole file, I expect Cell I11 should change with the selection of the drop down.
sample.xls
0
 
barry houdiniCommented:
Try this formula in G4 copied down

=($N$2=1)+COUNTIF(INDEX($N$3:$N$7,$N$2),"*"&D4&"*")*(D4&" ex "&E4<>INDEX($N$3:$N$7,$N$2))

regards, barry
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
SANTABABYCommented:
Use this formula for G4. Then copy G4 and paste in G5:G9.

=IF(OR($N$2=1,INDEX($N$3:$N$7,$N$2)=LEFT(D4 & " ex " & E4,LEN(INDEX($N$3:$N$7,$N$2)))),1,0)

0
 
jjxia2001Author Commented:
Thanks for the effort!  I forgot to mention that the real case is a little more complicated, some regions had to exclude more than one state, e.g :"WEST ex CA, AZ, WI".  Each region could have up to 15 states.  That is why I was struggling to use a formula to cover all of these.  
0
 
barry houdiniCommented:
Assuming all excluded states are listed in that format (separated by comma and space) then you can modify my suggestion as follows:

=($N$2=1)+COUNTIF(INDEX($N$3:$N$7,$N$2),D4&"*")*ISERR(SEARCH(" "&E4&",",INDEX($N$3:$N$7,$N$2)&","))

regards, barry
0
 
gowflowCommented:
If you need more help pls advise
gowflow
0
 
gowflowCommented:
Barry
Marvellous formulas learned a lot from trying it out !!!
gowflow
0
 
jjxia2001Author Commented:
Barry,  it's working perfectly!  Thanks so much!  Just one last question:  only one selection is "ALL ex CA, WI, AL, MN, CO".  I guess it can be added as a hard copy, such as if $N$2 = 2, if(or(E4<>"CA", E4<>"WI", E4<>"AL", E4<>"MN", E4<>"CO", then include all....  Can you help me on this?          
0
 
jjxia2001Author Commented:
perfect!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.