Best Microsoft Excel Bloggers

Monday, January 13, 2014



Between the holidays, Ice Storms and dealing with my daughter's wisdom teeth removal, it has been a little while but I am still working away on my VLookup Ebook and I have to tell you I have learned some cool tricks. 

This tip is a bit basic but I am willing to bet some of you have never considered it.  It is a really simple idea but one that never really occurred to me until I started really looking at Vlookups.

Instead of creating two Lookup functions and then creating a 3rd column to multiply or add the numbers together – you can do it all in one.
In the example below, my Order information is in Columns C through H and the lookup table containing my data is in Columns K through R.

My lookup value is Product ID which is in Column E. I want to find the Price in Column Q and the Shipping Cost in Column R for each product and then multiply it by the quantity of product being shipped.

In other words, I want to add together the Price (Column Q) and the Shipping Costs (Column R) for each Product ID in Column E and then multiply it by the Quantity in Column G.

To do this, I clicked in cell H3, Total Price,  and created a Price Vlookup  to look up the Price in Column Q, typed a plus sign and then created a second Vlookup to retrieve the information in Column R. Then I put parentheses around the entire equation and multiplied it by G3 (Quantity)and then copied it down.

The equation is: =(VLOOKUP(E3,$K$3:$R$30,7,FALSE)+VLOOKUP(E3,$K$3:$R$30,8,FALSE))*G3

Just a little faster way to do the calculation. Have fun.

Ms. Excel- Resident Excel Geek