If cell contains text1, text2 or text 3 return this text, otherwise return X

**bukmanodrama** · 03-26-2013, 08:40 AM

Hi all,

I desperately need a formula using which I could differentiate two categories of countries..
So there are two categories of countries, reliable ones and non-reliable ones. All of them are in one column, let's say Column A
Reliable countries: Italy, USA, France, Spain, etc. Non-reliable countries China, Turkey, Egypt.

I need a formula using which I could get value equal to the value in column A when it is a reliable country and value "non-reliable" when the country is non-reliable

It would be great to get a result similar to this one:

| A | B |
---------------------------
1 | Italy | Italy |
---------------------------
2 | China | Non-reliable |
---------------------------
3 | USA | USA |
---------------------------
4 | France | France |
---------------------------
5 | Turkey | Non-reliable |
---------------------------
6 | Spain | Spain |
---------------------------
7 | Egypt | Non-reliable |
-----------------------------

Can anyone please advice me on this?

Thanks in advance

**Jonmo1** · 03-26-2013, 08:49 AM

Make a list of your "Reliable" contries, in say E1:E20

Then you can use
=IF(ISNUMBER(MATCH(A1,E$1:E$20,0)),A1,"Non-reliable")

**benishiryo** · 03-26-2013, 08:50 AM

hi bukmanodrama, welcome to the forum. if you only have 3 countries which are unreliable, then:
=IF(OR(A1={"China","Turkey","Egypt"}),"Unreliable",A1)

otherwise, you can have the list somewhere else, say in F1:F3, then:
=IF(COUNTIF($F$1:$F$3,A1),"Unreliable",A1)

**bukmanodrama** · 03-26-2013, 09:24 AM

Thank you so much!

I've been trying to make something like this for a while..

I've tried the method provided by Jonmo1 , and it worked great!

I have almost whole world in my list, so I believe that the second formula provided by benishiryo will work better. I'll try both of it a bit later and will let you know how it will work for me.

Thanks again!