will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
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.
____________
February 8th, 2010 at 5:27 pm
Thanks Debra - this is a better solution to mine (where I had the user type the column number in a cell). This is more intuitive!
One question, I notice you use FALSE at the end of your VLOOKUP function. I have been using 0 for years (because I’m lazy, and I save 4 keystrokes). Is this just a preference, or is there another reason?
February 8th, 2010 at 7:15 pm
Thanks David, glad you liked this solution. Usually I just type a zero at the end of a VLOOKUP function, but with the popup choices in Excel 2007 functions, sometimes I click on the FALSE, instead of typing the last argument.
February 8th, 2010 at 7:23 pm
Thanks - I’m still stuck with Excel 2003, so didn’t know about the pop-ups when you enter a function in Excel 2007.
February 9th, 2010 at 4:49 am
I use this one also.
It’s limited to 7 columns of course.
Cells are relative to your sample
=VLOOKUP(J2,D:G,IF(K1=”Jan”,2,IF(K1=”Feb”,3,IF(K1=”Mar”,4))),FALSE)
also works in Hlookup if that fits the table better
=HLOOKUP(K1,D:G,IF(J2=”East”,2,IF(J2=”West”,3,IF(J2=”Central”,4))),FALSE)
February 9th, 2010 at 5:56 am
I shall be highly obliged to the person who would be ready to teach me microsoft excel very easily. thanks.
February 11th, 2010 at 4:27 am
David, Debrah,
my preference for the last argument of Vlookup is always to spell out TRUE or FALSE, depending on what I want the Vlookup to do. Even though 1 or 0 may be faster to type, in a complex formula with nested functions, I find the TRUE or FALSE much easier to spot, so when testing or trouble-shooting a formula that involves a Vlookup, it makes my life a bit easier.
The formula entry functionality in 2007 and later actually offers TRUE and FALSE as options when you type your Vlookup function into a cell after you’ve typed the third comma, although you can override it with 1 or 0. This parameter validation is one of the better features of Excel 2007 and may make up for a lot of the drawbacks in this version. Unfortunately, in Excel 2003 you still have to do the typing all by yourself.
cheers
teylyn
February 11th, 2010 at 4:26 pm
Thanks teylyn, you’re right — the new formula features in Excel 2007 are very helpful. And that’s a good point about TRUE/FALSE making it easier to find things in a complex formula.
February 15th, 2010 at 2:43 am
David, Debra
Thank you for your sharing experience with “VLOOKUP and MATCH Function”
In my opinion the first VLOOKUP function can also be replaced by:
=IF(B11=”",”",VLOOKUP(B11,ProductLookup,2,))
And in the second VLOOKUP function,if you define the area A1:D1 as Pricing,it can save “+1″ as
=IF(B11=”",”",VLOOKUP(B11,ProductLookup,MATCH($E$4,Pricing,0),))