# 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
###### Who is Participating?

x

Commented:
Thanks gowflow

=((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

Author Commented:
See the corrected samole file, I expect Cell I11 should change with the selection of the drop down.
sample.xls
0

Commented:
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

Commented:
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

Author 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

Commented:
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

Commented:
If you need more help pls advise
gowflow
0

Commented:
Barry
Marvellous formulas learned a lot from trying it out !!!
gowflow
0

Author 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

Author 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.