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,D25130,F37=102.00,VLOOKUP(D 25,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.

Hi Pank,

Try this:

=IF(D25<110,86.49,IF(D25130,102,VLOOKUP(D25,B28:C 50,2)))

Regards,
KL

"Pank" wrote in message
...
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,D25130,F37=102.00,VLOOKUP(D 25,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.

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.

"Pank" wrote in message
...
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,D25130,F37=102.00,VLOOKUP(D 25,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.

For 130 read 131, sorry

"Jack Sheet" wrote in message
...
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.

"Pank" wrote in message
...
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,D25130,F37=102.00,VLOOKUP(D 25,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.

Try following formula :

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

Regards
Jed.


"Pank" wrote in message
...
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,D25130,F37=102.00,VLOOKUP(D 25,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.

Hi Jed,

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

110D25130

or

F37=86.49=102

KL


"Jed" wrote in message
...
Try following formula :

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

Regards
Jed.


"Pank" wrote in message
...
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,D25130,F37=102.00,VLOOKUP(D 25,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.

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

--
paul
remove nospam for email addy!



"Pank" wrote:

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,D25130,F37=102.00,VLOOKUP(D 25,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.

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



"paul" wrote:

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

--
paul
remove nospam for email addy!



"Pank" wrote:

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,D25130,F37=102.00,VLOOKUP(D 25,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.

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(D2 5,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.



"paul" wrote:


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



"paul" wrote:

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

--
paul
remove nospam for email addy!



"Pank" wrote:

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,D25130,F37=102.00,VLOOKUP(D 25,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.

Hi Pank,

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

=IF(D25<110,86.49,IF(D25130,102,VLOOKUP(D25,B28:C 50,2)))

If yes, what was the problem?

Regards,
KL


"Pank" wrote in message
...
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(D2 5,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.



"paul" wrote:


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



"paul" wrote:

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

--
paul
remove nospam for email addy!



"Pank" wrote:

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,D25130,F37=102.00,VLOOKUP(D 25,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,

I tied your formula and it worked a treat.

Can you plese explain the following to me:-

=IF(D25<110,86.49,IF(D25130,102,VLOOKUP(D25,B28:C 50,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" wrote:

Hi Pank,

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

=IF(D25<110,86.49,IF(D25130,102,VLOOKUP(D25,B28:C 50,2)))

If yes, what was the problem?

Regards,
KL


"Pank" wrote in message
...
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(D2 5,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.



"paul" wrote:


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



"paul" wrote:

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

--
paul
remove nospam for email addy!



"Pank" wrote:

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,D25130,F37=102.00,VLOOKUP(D 25,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.

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

KL,

Once again Thanks.

Regards

Pank

"KL" wrote:

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