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? Heres how to customize your Excel price list with VLOOKUP and MATCH.
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 Product, Price, Promo and Sale.
Select a Pricing Type
On the order form, add a drop down list where you can select one of those pricing types Price, Promo or Sale.
Change the VLOOKUP
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].
How It Works
If Promo is selected, the MATCH formula returns a 2, because Promo is the second item in the Pricing range.
Youll 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.
____________