Excel Price List With VLOOKUP and MATCH Function

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. 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.

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.

VLookupMatch01

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))

VLookupMatch02

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.

VLookupMatch03

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.

VLookupMatch04

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).

VlookupMatch05

How It Works

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.

____________