On the order form, add a drop down list where you can select one of those pricing types – Price, Promo or Sale. In this example, the ProductLookup table has four columns, instead of two – Product, Price, Promo and Sale. To use variable pricing, you could create a lookup table with two or more columns of prices, instead of just one. In the simple example shown above, the price will always come from the second column of the lookup table. If a product has been selected in cell B11, the VLOOKUP formula finds the price in column 2 of the ProductLookup table. If cell B11 is empty (no product has been selected), the formula result is an empty string, and cell C11 will appear empty. 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.
The lookup table is a named range, ProductLookup. For example, if a customer orders a jacket, the price is 25, based on this lookup table.
With a simple VLOOKUP function in Excel, you can pull a product price from a lookup table.
But what happens if you want to give some customers special pricing, or offer sales pricing occasionally? Here’s how to customize your Excel price list with VLOOKUP and MATCH. You can create order forms and price lists in Excel, and automatically show a price when a product is selected in the order form.