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.

Could you help me, please?

Thanks!

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.

VlookupInnerArray.ods

Edited 20170927

Sample with the function.

BuscarV_matrizInterna.ods

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)

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.