Excel Logical operators inside If loop

By | December 17, 2017

So this works:


But this doesn’t


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


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.


You can use something like this:


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



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



Alternatively, this should work for you:


Leave a Reply

Your email address will not be published. Required fields are marked *