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.

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.

VLookupMatch03

On the order form, add a drop down list where you can select one of the pricing types – Price, Promo or Sale.

VLookupMatch04

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

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.

____________

You may also like...

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.

  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 ?

    Thanks in Advance.

  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))
    PLEASE HELP ME
    THANK YOU
    -SAVIN

  8. sohaib ahmad says:

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

Leave a Reply to Debra Dalgleish Cancel reply

Your email address will not be published. Required fields are marked *