Excel Logical operators inside If loop

Question

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

Answer 1

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")

Answer 2

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.