Solved

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

Posted on 2012-03-23
6
376 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
6 Comments
 
LVL 40

Assisted Solution

by:als315
als315 earned 50 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 50 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 81

Accepted Solution

by:
byundt earned 400 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Web Query 1 20
VBA or Script to identify files which are duplicate in a folder 6 32
How do you check for multiple possibilities in Excel 7 26
Clear a Text Box 7 23
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

856 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