Help with If Statement

[mithil1]

If there are several 10 Cities in a single column. Newyork,LA, SF, Orlando, Chicago, Washington, London, Tokyo, Pretoria, Nairobi and if these cities matches with a particular criteria i.e. Metropolitan then value for true should be 1000 or else blank

Thanks

 

[bosco_yip]

Maybe......

=IF(OR(A1={"Newyork";"LA";"SF";"Orlando";"Chicago";"Washington";"London";"Tokyo";"Pretoria";"Nairobi"},A1="Metropolitan"),1000,"")

Regards
Bosco

 

[mithil1]

Hey Bosco,

Thanks. I will try if that works. By the way, how are you able to figure the formulas out in excel. Have you done taken any formal training ?

Mithil

 

[mithil1]

Can I use the same syntax if the critera is "Newyork 1" or "Newyork AB"

 

[mithil1]

Hi Bosco,

This is not working out. Because it is also pulling the data for the A1= Metropolitan.

What I need to do is A1 = Metropolitan only and the several city criterias

Mithil

 

[bosco_yip]

Can I use the same syntax if the critera is "Newyork 1" or "Newyork AB"

…..This is not working out. Because it is also pulling the data for the A1= Metropolitan. What I need to do is A1 = Metropolitan only and the several city criterias

Hi,

It is not clear of your requirement.

Could you upload a sample data file together with the expected result.

Regards
Bosco

 

[mithil1]

Sorry bosco couldn't upload. I will try to do it as soon as I can. I was travelling.

 

[Khalid NGO]

Hi Mithil,
Good Day...

Another way is to make a simple table, and then use a simple VLOOKUP:
=IFERROR(IF(VLOOKUP(A1,$F$1:$G$14,2,0)="Metropolitan",1000,""),"")

See the attached sample.

Regards,

 

[mithil1]

hi Khalid,

Thanks for the reply. This is not working out because I just don't have "Metropolitan" but for example :Metropolitan, Metropolitan 1, Metropolitan 2 etc.

Hi Mithil,
Good Day...

Another way is to make a simple table, and then use a simple VLOOKUP:
=IFERROR(IF(VLOOKUP(A1,$F$1:$G$14,2,0)="Metropolitan",1000,""),"")

See the attached sample.

Regards,[/quote

 

[Khalid NGO]

This is not working out because I just don't have "Metropolitan" but for example :Metropolitan, Metropolitan 1, Metropolitan 2 etc.

Hi,

Try this then:

=IFERROR(IF(SEARCH("Metropolitan",VLOOKUP(A1,$F$1:$G$14,2,0)),1000,""),"")

Regards,