IF with multiple formula/variable

[Marcus Tavares]

I'm trying to use IF with other variable but is not working.

What I have:

Colum G12 - NET Sallary
Colum I12 - 3 possibilities (MRM, ME, AB)
Colum J12 where I will have my formula.

I have another table with 3 datas:

MRM = 1,709 (P3)
ME = 1,544 (P4)
AB = 1,630 (P5)

What I need to do is the following:

J12 = IF I12 = "MRM" THAN G12*P3; IF I12 = "ME" THAN G12*P4; IF I12 = "AB" THAN G12*P5

I need to multiply the sallary to one of the 3 variable depending on the category: MRM, ME and AB.

Anybody knows if this is possible?

Thanks in advance.

Marcus

 

[Andrew Poulsom]

Set out your table in 2 columns:

MRM 1,709
ME 1,544
AB 1,630

If that table is in the range O3:P5:

=G12*VLOOKUP(I12,O3:P5,2,FALSE)

 

[Jonmo1]

Where "Another Table" is a range that has the MRM = 1,709 etc..

Say for example
<TABLE style="WIDTH: 68pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=90 border=0 x:str><COLGROUP><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1426" width=39><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 11pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=14 height=17> </TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 28pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=37>L</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 29pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=39>M</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>1</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">MRM

</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1709">1,709</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>2</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">ME</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1544">1,544</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17 x:num>3</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">AB

</TD><TD class=xl30 id=td_post_1582460 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="1630">1,630</TD></TR></TBODY></TABLE>

=G12*VLOOKUP(I12,L1:M3,2,FALSE)

 

[Stormseed]

Formula in J12 to be entered:

=IF((I12="MRM"),G12*P3,IF((I12="ME"),G12*P4,IF((I12="AB"),G12*P5,"")))

 

[Marcus Tavares]

I did exactly what you said. I made a table as

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl97 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17> </TD><TD class=xl99 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>O</TD><TD class=xl99 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>P</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl100 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>3</TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">MRM</TD><TD class=xl97 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right>1,709</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl100 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>4</TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">ME</TD><TD class=xl97 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right>1,544</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl100 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>5</TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">AB</TD><TD class=xl97 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right>1,63</TD></TR></TBODY></TABLE>

and on the cell J12 I add the following formula

=G12*VLOOKUP(I12,O3:P5,2,FALSE)

Where G12 is where the sallary is and I12 where the variables (MRM, ME or AB) are and O3:P5 where the multiply variable are.

BUT when I past the above formula it pops up a error message.

Do you know what might be happening? It seems logical.

Thanks again

Marcus

Set out your table in 2 columns:

MRM 1,709
ME 1,544
AB 1,630

If that table is in the range O3:P5:

=G12*VLOOKUP(I12,O3:P5,2,FALSE)

 

[Marcus Tavares]

I have tryied this already but it pops up an excel formula error message.

Thanks a lot.

marcus

Formula in J12 to be entered:

=IF((I12="MRM"),G12*P3,IF((I12="ME"),G12*P4,IF((I12="AB"),G12*P5,"")))

 

[Jonmo1]

What error message?

Make sure your values (G12 and P3:P5) are actually numbers...

Use
=ISNUMBER(G12)
to test
repeat for each of P3 - P5

do they return TRUE or FALSE?

 

[Stormseed]

What is the Excel Formula Error Message that pops up ?

 

[Marcus Tavares]

NOW IS WORKING! I changed FALSE per 0 and works.

THANKS A LOT!

Marcus

I did exactly what you said. I made a table as

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl97 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=64 height=17></TD><TD class=xl99 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>O</TD><TD class=xl99 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" width=64>P</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl100 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>3</TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">MRM</TD><TD class=xl97 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right>1,709</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl100 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>4</TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">ME</TD><TD class=xl97 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right>1,544</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl100 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>5</TD><TD class=xl98 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white">AB</TD><TD class=xl97 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: white" align=right>1,63</TD></TR></TBODY></TABLE>

and on the cell J12 I add the following formula

=G12*VLOOKUP(I12,O3:P5,2,FALSE)

Where G12 is where the sallary is and I12 where the variables (MRM, ME or AB) are and O3:P5 where the multiply variable are.

BUT when I past the above formula it pops up a error message.

Do you know what might be happening? It seems logical.

Thanks again

Marcus

 

[Marcus Tavares]

THANKS now is working! I just changed the "FALSE" per "0".

What error message?

Make sure your values (G12 and P3:P5) are actually numbers...

Use
=ISNUMBER(G12)
to test
repeat for each of P3 - P5

do they return TRUE or FALSE?