CODEWITHSUNDEEP
excelloopslogical-operators
Amatya
Amatya

Reputation: 1243

Excel Logical operators inside If loop

So this works:

=IF(OR(D2="MEXICO",D2="TURKEY",D2="CHINA",D2="BRAZIL",D2="INDIA",D2="INDONESIA",D2="POLAND",D2="COLOMBIA",D2="ARGENTINA",D2="PHILIPPINES"),D2,"Others")

But this doesn't

=IF(D2=OR("MEXICO","TURKEY","CHINA","BRAZIL","INDIA","INDONESIA","POLAND","COLOMBIA","ARGENTINA","PHILIPPINES"),D2,"Others")

Is there a way to get around writing D2= inside every single time? I am looking to see if the criteria can be used in multiple places .. say in a different sheet I also have country names, but just not in column "D".

Upvotes: 4

Views: 312

Answers (4)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

You can use something like this:

=IF(ISERROR(VLOOKUP(D2,{"MEXICO","TURKEY"},1,0)),"Others",D2)

Futhermore, as @barryhoudini suggest, in excel 2007 or later you can use:

=IFERROR(VLOOKUP(D2,{"MEXICO","TURKEY"},1,0),"Others")

Upvotes: 4

barry houdini
barry houdini

Reputation: 46361

It's possible to use OR but you need to change the syntax a little - like this

=IF(OR(D2={"MEXICO","TURKEY","CHINA","BRAZIL","INDIA","INDONESIA","POLAND","COLOMBIA","ARGENTINA","PHILIPPINES"}),D2,"Others")

Upvotes: 3

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103135

I would suggest that you create a list of country names in a column in a sheet somewhere and use that as a look up where ever you need it. You can create a separate sheet with this data, say datasheet.

Let's say you have a list of countries in A1:A25 of datasheet, you can then do something like this:

    =IF(COUNTIF(datasheet!A1:datasheet!A25, D2) <> 0, D2, "Other")

Additionally, I would create a named range for the set of country names if I expect them to change in the future. This way my formulas will refer to the name and if I add countries I do not have to change the formulas.

Upvotes: 3

aaroncatlin
aaroncatlin

Reputation: 3271

Alternatively, this should work for you:

=IF(ISNA(MATCH(D2, {"MEXICO","TURKEY","CHINA","BRAZIL","INDIA","INDONESIA","POLAND","COLOMBIA","ARGENTINA","PHILIPPINES"}, 0)), "Others", D2)

Upvotes: 2

Related Questions