You can create order forms and price lists in Excel, and automatically show a price when a product is selected in the order form. But what happens if you want to give some customers special pricing, or offer sales pricing occasionally?
Using the VLOOKUP Function
With a simple VLOOKUP function in Excel, you can pull a product price from a lookup table. For example, if a customer orders a jacket, the price is 25, based on this lookup table. The lookup table is a named range, ProductLookup.
To show the price after a product is selected in an order form, use a VLOOKUP formula to find that product in the lookup table. For example:
=IF(B11="","",VLOOKUP(B11,ProductLookup,2,FALSE))
If cell B11 is empty (no product has been selected), the formula result is an empty string, and cell C11 will appear empty.
If a product has been selected in cell B11, the VLOOKUP formula finds the price in column 2 of the ProductLookup table.
Select a Variable Column
In the simple example shown above, the price will always come from the second column of the lookup table. To use variable pricing, you could create a lookup table with two or more columns of prices, instead of just one. In this example, the ProductLookup table has four columns, instead of two.
On the order form, add a drop down list where you can select one of the pricing types – Price, Promo or Sale.
Then, in the VLOOKUP formula, replace the column number with a MATCH formula.
=IF(B11="","",VLOOKUP(B11,ProductLookup,MATCH($E$4,Pricing,0)+1,FALSE))
The MATCH formula finds the pricing type selected in cell E4, in a range named Pricing (cells B1:D1 in the lookup table).
If Promo is selected, the MATCH formula returns a 2, because Promo is the second item in the Pricing range.
You’ll add 1 to that number, because the ProductLookup table has one column to the left of the pricing columns.
So, 2 + 1 = 3, and the promo pricing will come from the 3rd column of the ProductLookup table.
Watch the VLOOKUP / MATCH Video
To see another example of using MATCH with VLOOKUP, watch this short video.
____________
