Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Can you post your lookup table please.
"HOUA-CARLOS" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
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. Toppers wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
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(A280000,0.05,IF(A239999,0.03, IF(A219999,0.02,IF(A29999,0.01,0)))) "HOUA-CARLOS" wrote: 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. Toppers wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
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) "HOUA-CARLOS" wrote: 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. Toppers wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Thank you.
tim m wrote: 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(A280000,0.05,IF(A239999,0.03, IF(A219999,0.02,IF(A29999,0.01,0)))) Here is part of my data for Contract Discounts sheet 1 Contract Amount [quoted text clipped - 24 lines] Thanks. Houa -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200606/1 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Thank you so much.
Toppers wrote: 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] Thanks. Houa -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200606/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |