Forum Discussion

Asa94's avatar
Asa94
Copper Contributor
Feb 09, 2021

Using IF for multiple values

Hey there! I'm afraid I got stuck at a point I need your expert advice.

I want to use the function IF so as to state 3 different options and retrieve the value associated each one.

 

Here it goes the relation stated in a separate sheet:

 

Local=100%

Regional=120%

Iberian=95%

 

So, what I would like to get is, assuming that column C contains the first value (local, regional or iberian). The exact % for each one.

 

I tried to achieve so by using the following expression: 

 

=IF(W2="Local";Sheet3!B4;)IF(W2="Ibérico";Sheet3!B5;)IF(W2="Regional";Sheet!B6;"")))

 

However, excel is not retrieving the value for each, it's only capable of retrieving the first set (local).

 

It would be great if excel understood boolean operators, for instance: =IF(W2="Local";Sheet3!B4;)ELSE(W2="Ibérico";Sheet3!B5;)ELSE(W2="Regional";Sheet!B6;"")))

 

How should I tweak it? Any thoughts?

 

Thanks a lot!

Formulas and Functions

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 09, 2021

    Asa94 

     

    You have too many parentheses:

    =IF(W2="Local";Sheet3!B4;IF(W2="Ibérico";Sheet3!B5;IF(W2="Regional";Sheet3!B6;"")))

     

    Another way is a lookup table:

     

     

    The formula can then be

     

    =IFERROR(VLOOKUP(W2; Sheet3!$A$4:$B$6; 2; FALSE); "")

     

    If your Excel is in Spanish:

     

    =SI.ERROR(BUSCARV(W2; Sheet3!$A$4:$B$6; 2; FALSE); "")

     

Resources