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.
No comments:
Post a Comment