Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel, IF statement with AND, OR - Two Columns. Nine Combinations

Posted on 2012-03-23
6
Medium Priority
?
382 Views
Last Modified: 2012-03-26
We need to identify NAFTA shipments, with a formula inserted into a spreadsheet.
I have tried a number of IF statements with OR and AND, but I cannot get it to work.

We have:

3 countries = 9 combinations

Column A = Origin Country
Column B = Destination Country

What we are trying to do:

If the value in A1 = USA, MEX, or CAN
and the value in B1 = USA, MEX, or CAN, then C1 returns "NAFTA".


IF(OR(A1="USA",A1="MEX",A1=CAN")

AND

IF(OR(B1="USA",B1="MEX",B1="CAN")

then, "NAFTA"

otherwise, "" )

Any advise that you can provide is appreciated.

Tosagua
0
Comment
Question by:Tosagua
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

Assisted Solution

by:als315
als315 earned 200 total points
ID: 37758176
=IF(AND(OR(A1="MEX",A1 = "CAN",A1="USA"),OR(B1="MEX",B1 = "CAN",B1="USA")),"NAFTA","")
0
 

Author Comment

by:Tosagua
ID: 37758326
als315,

I see now, how this should work, but it doesn't.
It always returns "FALSE", regardless of the combination.
 See attached.

Tosagua
0
 
LVL 1

Assisted Solution

by:coogsirc
coogsirc earned 200 total points
ID: 37758375
I created a range called "nft" with the 3 country codes and plugged this formula into C1.
=IF(AND(MATCH(A1,nft,0),MATCH(B1,nft,0)),"nafta")

If a1 or b1 isn't in the list c1 returns #N/A.
0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
LVL 81

Accepted Solution

by:
byundt earned 1600 total points
ID: 37759603
If both countries are the same, I assume the answer should not be NAFTA. If so, then you might consider a formula like:
=IF((A2<>B2)*(SUMPRODUCT(--(A2:B2={"USA";"MEX";"CAN"}))=2),"NAFTA","")

Brad
NAFTA-Q27646241.xlsx
0
 
LVL 40

Expert Comment

by:als315
ID: 37760075
An example with equation from my comment
NAFTA.xls
0
 

Author Closing Comment

by:Tosagua
ID: 37767044
Another learning experience.

I appreciate everyone's help, however 'byundt' saved me from making a simple mistake (USA to USA is not NAFTA).

Part of the difficuly was that the cells were formatted as text, and when the format was changed to general, there was srtill no change.

Fortunately, I found this comment on EE,

"by: bouscal Posted on 2010-12-13 at 09:34:43ID: 34336238 Rank: Master
 
After you change the formatting to General from Text you may also need to select the cell, hit F2 to activate it then Enter for it to calculate.  Excel doesn't always like to apply formatting.

Thank you for all of your assistance.

Tosagua
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
New style of hardware planning for Microsoft Exchange server.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question