Ask Your Question
0

Multiple IF condition with text in CALC

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

kiko35 gravatar image

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!

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

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.

VlookupInnerArray.ods

Edited 20170927

Sample with the function.

BuscarV_matrizInterna.ods

image description

edit flag offensive delete link more

Comments

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)
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
0

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
0

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

Stats

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

Seen: 133 times

Last updated: Sep 27