Having trouble coming up with a formula using multiple IF/AND functions. What I need to do: Column A data is all state abbreviations. Based upon which state is selected and dependent upon the word "Separate" being selected in Column D, I need to have either "Consolidated" or "Proforma" appear in Column E. If "Separate" is NOT selected in Column D, then there should be a blank in Column E. I've gotten the single argument to work; i.e. "AK" + "Separate" = "Consolidated". How do I add the argument for my other states (such as CA, DE, IL) + "Separate" = "Proforma"?
Here's the formula as it currently is:
=IF(AND(D3="Separate",OR(A3="AK",A3="AR",A3="CT",A3="ID",A3="IA",A3="KS",A3="ME",A3="MN",A3="MO",A3= "MT",A3="NE",A3="ND",A3="OK",A3="OR",A3="WV",A3="WI")),"Consolidated","")
Any help is greatly appreciated.
Last edited by swhiteley; 11-04-2011 at 09:17 PM. Reason: solved
Try this version with shortened OR
=IF(D3="Separate",IF(OR(A3={"AK","AR","CT","ID","IA","KS","ME","MN","MO","MT","NE","ND","OK","OR","W V","WI"}),"Consolidated","Proforma"),"")
That won't actually check for the other states - just assumes there must be one of the others if it isn't one of those specified.
A simpler way might be to construct a list of all states in Y2:Y50 and the relevant category (proforma or consolidated) in Z2:Z50 then you could use this version
=IF(D3="Separate",VLOOKUP(A3,Y$2:Z$50,2,0),"")
Audere est facere
Actually the vlookup is a much better option! Don't know why I didn't consider it earlier... as my 2 options of proforma or consolidated have the potential of becoming 4 options down the road and this will make it so much easier to adjust when I need to.
Works perfectly!! Thank you!!
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
“To sin by silence when they should protest makes cowards of men.” ~ Abraham Lincoln
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks