# Multiple IF condition with text in CALC

Hello!

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.

Thanks!

edit retag close merge delete

Sort by » oldest newest most voted

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

VlookupInnerArray.ods

Edited 20170927

Sample with the function.

BuscarV_matrizInterna.ods

more

You only need to put the string between quotes like

=BUSCARV(A8;{"z"."#N/A";"a"."ultraviolett";"b"."violett";"c"."blau";"d"."grün";"f"."gelb";"g"."orange";"h"."rot";"i"."infrarot"};2;1)

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

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

( 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!

( 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

( 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...)

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

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

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

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.

more

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.

more