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.

____________

19 thoughts on “Excel Price List With VLOOKUP and MATCH Function”

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

  10. DESCRIPTION QTY UNIT PRICE AMOUNT
    Service Fee 500
    Plastic 500

    Product Name Product Price QTY Discount Product Price QTY Discount
    Plastic 75 250 15% 60 600 25%
    Service Fee 36 200 5% 20 500 10%

    In Above example if customer purchase plastic products 250 qty or less than 250 then price will 75 per qty and discount 15%, and if purchase more than 250 or less than 600 then price should be 60 per qty and discount 25%.
    Please help for formula.

  11. I have a problem, there are 10 branches in each branches there are 6 staff how to extract the data of one branch with 6 staff in another spread sheet.
    For example
    Branch name Staff name
    1 AA
    2 BB
    1 CC
    1 DD
    3 EE

  12. Hello Debra,
    I just want to tell you that I am so proud of myself and Thanks to you because I create this Ordering form following your instructions. This was my first time doing something like this and I am not an expert on Excel, but I did it so Thank you for that.
    Now I have an issue and I’ve been looking around on your web but I haven’t been able to find the information or I would say because I am not an expert on Excel it makes it more difficult to look for something that you don’t know.
    What I am trying to formulate is the following: I have my ordering form, with a customer list sheet, a Product List sheet, and the ordering form sheet. In the Product List, I have the names of my products and there are 3 columns, one with the Price per case which is 12 bottles, another column with the price of Half Case which is 6 bottles, and another column with the prices for single bottles.
    All I need is to tell the formula if a customer order 6 bottles to look for the price in the half case column, or if it only 1 or 2 bottles to look at the column for single bottles and give me the prices correct in the ordering sheet. I have checked INDEX/MATCH but I can’t make it work maybe I am doing something wrong.
    Do you think you can help me with this? I will really appreciate. Thank you so much and Stay Safe.
    NOTE: I sent you an email that has the ordering form that I put together, look in your spam. Thank you!

  13. hi,
    I have two excel sheets,with the same product code and i want to create a lookup and overwrite of the price for sheet one if the product code matches with one in sheet 2
    is this possible?

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.