In this example a merchant is offering discount on large orders. The Unit Cost Table holds the cost of 1 unit of Brick, Wood and Glass.
|Unit Cost Table|
The Discount Table holds the various discounts for different quantities of each product.
The Orders Table is used to enter the orders and calculate the Total. All the calculations take place in the Orders Table.
The name of the Item is typed in column C of the Orders Table.
The Unit Cost of the item is then looked up in the Unit Cost Table.
The FALSE option has been used at the end of the function to indicate that the product names down the side of the Unit Cost Table are not sorted. Using the FALSE option forces the function to search for an exact match. If a match is not found, the function will produce an error.
The discount is then looked up in the Discount Table If the Quantity Ordered matches a value at the side of the Discount Table the =VLOOKUP will look across to find the correct discount.
The TRUE option has been used at the end of the function to indicate that the values down the side of the Discount Table are sorted. Using TRUE will allow the function to make an approximate match. If the Quantity Ordered does not match a value at the side of the Discount Table, the next lowest value is used. Trying to match an order of 125 will drop down to 100, and the discount from the 100 row is used.
You can download the excel source file with all the tables and formulas here vlookupPracticalExamle1.xls