Logical AND and OR with IF condition ..

[Analyst_Kumar]

Is it not possible to use AND / OR with IF condition?

I have a spreadsheet with following data.
Column A has city names such as Delhi, Mumbai, Chennai, Bangalore and Hyderabad.
For column B, I want to set value
to 2 if column A has either Chennai or Bangalore,
to 5 if column A has either Delhi or Mumbai
to 6 if column A has Hyderabad
to 0 if column A has any other value.

I use, If(A2="Chennai" or A2="Bangalore", 2, 99) It is throwing error

 

[Khalid NGO]

Hi Kumar,

Try the following:

=IF(A2=0,"any other value",IF(A2="Hyderabad",6,IF(OR(A2={"Chennai","Bangalore"}),2,IF(OR(A2={"Delhi","Mumbai"}),5,"Not specified"))))

Regards,

 

[Asheesh]

Hi,

The construct give by you is incorrect...try the below

=IF(OR(A2="Chennai",A2="Bangalore"),2,...

 

[Khalid NGO]

Or may be with lookup:

=IF(A2=0,"any other value",LOOKUP(A2,{"Bangalore","Chennai","Delhi","Hyderabad","Mumbai"},{2,2,5,6,5}))

Regards,

 

[Analyst_Kumar]

Thank you @Khalid NGO , @Asheesh for your quick responses.