Ask Your Question

Multiple IF condition with text in CALC

asked 2017-08-07 13:34:18 +0200

kiko35 gravatar image


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?


edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2017-08-07 16:12:08 +0200

m.a.riosv gravatar image

updated 2017-09-27 22:34:40 +0200

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


Edited 20170927

Sample with the function.


image description

edit flag offensive delete link more


You only need to put the string between quotes like

m.a.riosv gravatar imagem.a.riosv ( 2017-08-11 01:25:48 +0200 )edit

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

kiko35 gravatar imagekiko35 ( 2017-09-05 17:02:21 +0200 )edit

Please can copy your formula to take a look.

m.a.riosv gravatar imagem.a.riosv ( 2017-09-05 22:36:29 +0200 )edit

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

kiko35 gravatar imagekiko35 ( 2017-09-06 10:37:15 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2017-09-06 15:41:53 +0200 )edit

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

kiko35 gravatar imagekiko35 ( 2017-09-27 12:55:24 +0200 )edit

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

kiko35 gravatar imagekiko35 ( 2017-09-29 16:13:34 +0200 )edit

answered 2017-08-07 14:00:26 +0200

robleyd gravatar image

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

edit flag offensive delete link more

answered 2017-08-10 23:32:56 +0200

kiko35 gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-08-07 13:34:18 +0200

Seen: 2,383 times

Last updated: Sep 27 '17