Multiple IF condition with text in CALC


I want a cell (B1, in this example) to have different values depending on another cell (A1). I write manually the value for A1, but I want the value of B1 to be automatic. For example:

If I write “London” in A1, B1 will be “UK”; if A1=Paris, B1=France; if A1=Lisbon, B1=Portugal and so on for 5 cities/countries.

I know the function =IF(ISNUMBER(FIND(“London”;A1));“UK”;“XX”), but I want to use 5 conditions in the same cell.

Could you help me, please?


Would VLOOKUP not work for you? It would allow you to fairly simply expand from your current five to a large number of possible options.

If this answer helped you, please accept it by clicking the check mark :heavy_check_mark: to the left and, karma permitting, upvote it. If this resolves your problem, close the question, that will help other people with the same question.

Take a look to the attached sample, with inner arrays it’s easy with VLOOKUP or HLOOKUP.


Edited 20170927

Sample with the function.


You only need to put the string between quotes like


Thanks! But it still does not work… If I modify your sentence with my words, it gives me as answer another word. I mean, if I change “a” for “home”, it shows “rot”.

Please can copy your formula to take a look.

=BUSCARV(A8;{“z”."#N/A";“Paris”.“France”;“London”.“UK”;“Buenos Aires”.“Argentina”;“Lisbon”.“Portugal”;“other_place”.“other_country”};2;1)
thanks again!

Changin the last parameter for zero =BUSCARV(A8;{"z"."#N/A";"Paris"."France";"London"."UK";"Buenos Aires"."Argentina";"Lisbon"."Portugal";"other_place"."other_country"};2;0) it’s enought, so search without considered data sorted. BTW there is a Spanish Ask site

Thank you again!! However, it does not work… The answer is always ‘#NAME?0’
(sorry for the delay, I had some problems lately…)

Now it is working. THANK YOU SO MUCH. GRAZAS.

Thanks both! I know how to use VLOOKUP with numbers, as in the m.a.riosv’s example, but no with words… I’m getting Erro:511 and Erro:512.