# Excel Price List With VLOOKUP and MATCH Function

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.

____________

### 14 Responses

1. George says:

Vlookup can only work in forms but cant be used to keep records of sales. This is because whenever there is a price variation, previous records kept with the previous price will also change.
Is there any way one can change the prices in a vlookup table without corresponding changes in the prices of the previous record kept? I will be grateful if an copy of excel worksheet and formula is posted
Thank you in anticipation.

• Shariq says:

Did you find a solution to your problem?

2. Ian says:

Hi,

I have several tables with prices of different flights.
Different tables range from Airline1 – Adult rates, Airline 1 – Child rates, Airline 2 – Adult rates, Airline 2 – Child rates, and so on.
Now, the above doesn’t seem to work as it looks more like a distances table, with prices going horizontally and vertically.
So really I need to have an Index-Match thing but which knows how to choose from which table according to which table you need.
For: instance Airline 3 – Child rates- Mumbasa – Cairo – Result
Can you help ?

• abul hassan says:

Thanks.

• Duncan says:

I am also stuck on this issue, did anyone find a solution ?

3. MITESH says:

IF IHAVE TWO PRODUCT COLUMNS CONTAINING PRODUCT NAME WITH PRICE I.E TOTAL 4 COLUMNS(2 COLUMNS WITH THEIR PRICEINFRONT OF THEM ) HOW TO USE VLOOKUP FORMULA IN INVOICE. THIS BCOZ I AM HAVING MUTILPE ITEM E.G 500 PRODUCT,ITS PRICE LIST.PLZ SUGGEST PROPER FORMULAS FOR THIS. THKS IN ADVANCE

4. Tom Mason says:

I have a (EXCEL 3500 lines) price list for parts on all makes and models of cars.
can i get a database that will allow me to search a particular make, model and part?

I currently have to search through a printed version for the particular part to fit a particular make, model,

5. Suri says:

HI.. I have created a Invoice In excel 2007. My data is Customer name with address & product details. The products are same but the price is different for each customers. My requirement:
When I enter customer code in the invoice it should display the products and its price based on the price set for the customer.

6. Nick says:

I have a price table that has 4 columns in it, but when I select my 3rd pricing type (Sale in the example), the result comes back as #N/A. Whenever I select my other two pricing types, the results come back and are correct.

What can I do to fix this?

7. Savinn says:

worked great but only for the first entry, remaining show #NA error.
here is my example
A B C D
E-10 11000
F-006 1500
F-003 1550
E-15 12500

items formula
E-10 11000
e-15 12500
F-003 1550
e-10 #N/A
F-003 #N/A
e-10 #N/A
e-15 #N/A
FORMULA : =IF(C7=””,””,VLOOKUP(C7,A1:B4,2,FALSE))
THANK YOU
-SAVIN

Hi, I am a transporter , I enter many vehicles price for example \$20000 is price of hino truck. If next time I type hino in cell , is there any way to type \$20000 in price cell? Thank in advance

9. Kailash says:

I have to put a formula for this range
A B
500-750 12
751-1000 18
1001-1500 22
1501-2000 25
2001-2500 30

against these rates
A B
1300
900
1900
550
1400
1750
1070