Help with IF then ELSE and VLOOKUP

[Guest]

I have the following dilemma.

I have a Vlookup, which tests for values between 110 and 131 and allocates
an appropriate bonus that works fine. If the values are less than 110 and
greater then 130 the resultant cell displays #N/A as expected.

However I have now been advised that if I have a value that is less than or
equal to 110 I need to allocate a bonus of 86.49 and if it is greater than or
equal to 131 I need to allocate a bonus of 102.00. Any value between 110 and
131 should invoke the vlookup to allocate the appropriate bonus.

I was hoping to using an ‘if then else’ statement to set the low and
high-end bonus prior to the vlookup.

I have keyed the formula in as

=IF(D25<110,F37=86.49,D25>130,F37=102.00,VLOOKUP(D25,B28:C50,2,TRUE))

However, I get a message that says ‘the formula you typed contains an
error’. I have tried to go through the help but I keep hitting a brick wall.

Any help would be appreciated.

 

[KL]

Hi Pank,

Try this:

=IF(D25<110,86.49,IF(D25>130,102,VLOOKUP(D25,B28:C50,2)))

Regards,
KL

 

[Jack Sheet]

Assuming that your formula resides in cell F37, try
=IF(D25<=110,86.49,IF(D25>=130,102,VLOOKUP(D25,B28:C50,2,TRUE)))

Or, better still, include values for <110 and >130 in the lookup table.

 

[Jack Sheet]

For 130 read 131, sorry

Assuming that your formula resides in cell F37, try
=IF(D25<=110,86.49,IF(D25>=130,102,VLOOKUP(D25,B28:C50,2,TRUE)))

Or, better still, include values for <110 and >130 in the lookup table.

than

 

[Jed]

Try following formula :

=IF(AND(D25<110,F37=86.49,D25>130,F37=102),VLOOKUP(D25,B28:C50,2,TRUE))

Regards
Jed.

 

[KL]

Hi Jed,

Your formula makes no sense. Can you imagine a number which is:

110>D25>130

or

F37=86.49=102

KL

 

[Guest]

=IF(D25<=110,86.49,IF(D25<131,VLOOKUP(D25,B28:C50,2,TRUE),140))

 

[Guest]

-- sorry for 140 in formula read 102....
paul
remove nospam for email addy!

 

[Guest]

Apologies to all for not explaining my problem correctly.

Second attempt:-


I have the following Vlookup formula in cell F37
=VLOOKUP(D25,B28:C50,2,TRUE), which tests for values between 110 and 131 and
allocates an appropriate bonus that works fine. If the values are less than
110 and greater then 130 the resultant cell displays #N/A as expected.

However I have now been advised that if I have a value that is less than or
equal to 110 I need to allocate a bonus of 86.49 and if it is greater than or
equal to 131 a bonus of 102.00. Any value between 110 and 131 should invoke
the vlookup to allocate the appropriate bonus.

I was hoping to using an ‘if then else’ statement (in F37) to set the low
and high-end bonus prior to the vlookup (which is currently in F37).

I have keyed the formula in as: -
=IF(D25<110,F37=86.49,D25130,F37=102.00,VLOOKUP(D25,B28:C50,2,TRUE))

However, I get a message that says ‘the formula you typed contains an
error’. I have tried to go through the help but I keep hitting a brick wall.

Any help would be appreciated.

 

[KL]

Hi Pank,

Your formula is incorrect. Have you tried the one I suggested earlier?

=IF(D25<110,86.49,IF(D25>130,102,VLOOKUP(D25,B28:C50,2)))

If yes, what was the problem?

Regards,
KL

 

[Guest]

KL,

I tied your formula and it worked a treat.

Can you plese explain the following to me:-

=IF(D25<110,86.49,IF(D25>130,102,VLOOKUP(D25,B28:C50,2)))

I am assuming that as the formula is in F38 the first if would insert a
value of 86.49 in F38 if the result is <110 and the second if would insert a
value of 102.00 in F38 if the result is >130?. If both these test fail then
the lookup is executed?

Can you please also explain why you have dropped the TRUE statement from the
lookup.

I would like to thank all the people who have offered a solution.

Regards

Pank

 

[KL]

Hi Pank,

I am assuming that as the formula is in F38 the first if would insert a
value of 86.49 in F38 if the result is <110 and the second if would insert
a
value of 102.00 in F38 if the result is >130?. If both these test fail
then
the lookup is executed?

Yes that's exactly it.

Can you please also explain why you have dropped the TRUE statement from
the
lookup.

if the fourth argument is ommited VLOOKUP assumes TRUE. Also, the short way
of putting TRUE and FALSE as argument is 1 and 0.

Regards,
KL

 

[Guest]

KL,

Once again Thanks.

Regards

Pank

Hi Pank,


Yes that's exactly it.


if the fourth argument is ommited VLOOKUP assumes TRUE. Also, the short way
of putting TRUE and FALSE as argument is 1 and 0.

Regards,
KL