Excel Boolean statements inside an 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 it is not

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

Is there a way around D2 = inside every time? I see if criteria can be used in several places. Say, in another sheet, I also have country names, but just not in the “D” column.

Answer 1

I would suggest that you create a list of country names in a column on a sheet somewhere and use this as a search where you need it. You can create a separate sheet with this data, for example, a specification.

Say you have a list of countries in table A1: A25, you can do something like this:

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

, named range , , . , , , .

Answer 2

- :

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

, @barryhoudini, excel 2007 :

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

Answer 3

It can be used OR, but you need to change the syntax a bit - like this

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

Answer 4

Alternatively this should work for you:

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