VLOOKUP

[HOUA-CARLOS]

Hi.
I'm trying to enter the VLOOKUP function for a discount on contract amount.
This table has two sheet. The first sheet is the "Contract Discount". The
second sheet is the "Lookup table". The discount total is in the cell C4.
which is located in sheet 1 "Contract Discount". The contract amount is in
cell A4 ($60,000) Sheet 1"Contract Discount". The discount is in sheet 2
which is the "Lookup table". Cell A2 should be: For Contracts between $40,000
and $80,000 there is a 3% discount. I try to use the VLOOKUP formula and the
IF formula and could not come up with the discount total in cell C4, sheet 1
"Contract Discount".
Any suggestions for a formula to do this?
Thanks.
Houa

 

[Guest]

Can you post your lookup table please.

 

[HOUA-CARLOS]

Here is part of my data for Contract Discounts sheet 1
Contract Amount
$15,000
$4,000
$60,500
$73,235
$8,025
$68,135
$12,000
$45,000
$82,002
$35,258

Here is the date for Lookup Table

If the contracts is under $10,000 there are no discount. Between $10,000 and
$20,000 there is a 1% discount, for $20,000-$40,000 there is a 2% dicounts,
between $ 40,000 and $80,000 there is a 3% discount and contract over $80,
000 there is a 5% discount.

Hopefull this will help you.

Can you post your lookup table please.

Hi.
I'm trying to enter the VLOOKUP function for a discount on contract amount.

[quoted text clipped - 9 lines]

Thanks.
Houa

 

[Guest]

I don't think you want to use a Vlookup for this as it is going to look for
an exact match rather than a range of values. You could try this formula
below. Format a column as % and enter this formula. (In the formula A2 is
the start of your contract amounts.)

=IF(A2>80000,0.05,IF(A2>39999,0.03, IF(A2>19999,0.02,IF(A2>9999,0.01,0))))

Here is part of my data for Contract Discounts sheet 1
Contract Amount
$15,000
$4,000
$60,500
$73,235
$8,025
$68,135
$12,000
$45,000
$82,002
$35,258

Here is the date for Lookup Table

If the contracts is under $10,000 there are no discount. Between $10,000 and
$20,000 there is a 1% discount, for $20,000-$40,000 there is a 2% dicounts,
between $ 40,000 and $80,000 there is a 3% discount and contract over $80,
000 there is a 5% discount.

Hopefull this will help you.

Can you post your lookup table please.

Hi.
I'm trying to enter the VLOOKUP function for a discount on contract amount.

[quoted text clipped - 9 lines]

Thanks.
Houa

 

[Guest]

Try:

Lookup table on Sheet2 (A1:B5)

£0.00 0.00%
£10,001.00 1.00%
£20,001.00 2.00%
£40,001.00 3.00%
£80,001.00 5.00%

In your discount cell:

=VLOOKUP(A4,Sheet2!$A$1:$B$5,2,TRUE)

Here is part of my data for Contract Discounts sheet 1
Contract Amount
$15,000
$4,000
$60,500
$73,235
$8,025
$68,135
$12,000
$45,000
$82,002
$35,258

Here is the date for Lookup Table

If the contracts is under $10,000 there are no discount. Between $10,000 and
$20,000 there is a 1% discount, for $20,000-$40,000 there is a 2% dicounts,
between $ 40,000 and $80,000 there is a 3% discount and contract over $80,
000 there is a 5% discount.

Hopefull this will help you.

Can you post your lookup table please.

Hi.
I'm trying to enter the VLOOKUP function for a discount on contract amount.

[quoted text clipped - 9 lines]

Thanks.
Houa

 

[HOUA-CARLOS via OfficeKB.com]

Thank you.

I don't think you want to use a Vlookup for this as it is going to look for
an exact match rather than a range of values. You could try this formula
below. Format a column as % and enter this formula. (In the formula A2 is
the start of your contract amounts.)

=IF(A2>80000,0.05,IF(A2>39999,0.03, IF(A2>19999,0.02,IF(A2>9999,0.01,0))))

Here is part of my data for Contract Discounts sheet 1
Contract Amount

[quoted text clipped - 24 lines]

 

[HOUA-CARLOS via OfficeKB.com]

Thank you so much.

Try:

Lookup table on Sheet2 (A1:B5)

£0.00 0.00%
£10,001.00 1.00%
£20,001.00 2.00%
£40,001.00 3.00%
£80,001.00 5.00%

In your discount cell:

=VLOOKUP(A4,Sheet2!$A$1:$B$5,2,TRUE)

Here is part of my data for Contract Discounts sheet 1
Contract Amount

[quoted text clipped - 24 lines]