Best Microsoft Excel Bloggers

Monday, January 13, 2014

JOINING VLOOKUP FUNCTIONS TOGETHER

JOINING VLOOKUP FUNCTIONS TOGETHER


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