Multiple vlookup help?

[MMike]

Hi All,

I have a spreadsheet where I'm using vlookup to calculate the cost of a product for a fundraiser at my sons school.

Right now I'm using chained vlookups like this:

=SUM((C6)*(VLOOKUP(C$5,pricelist,2,FALSE)),(D6)*(VLOOKUP(D$5,pricelist,2,FALSE)),(E6)*(VLOOKUP(E$5,pricelist,2,FALSE)),(F6)*(VLOOKUP(F$5,pricelist,2,FALSE)),(G6)*(VLOOKUP(G$5,pricelist,2,FALSE)) etc.

I did this because I needed it to look up the price of the product in the price list and multiply it by the number of that product sold. Then I need it to do that for each cell on the row so that I have one cell on the row with the total for each row. Each column equals a different product and then each the quantity of each product is entered into the cell. Then it sums all the products on that line to give me a total.

Here's a pic of what I mean.

As you can see its not a great way of doing it and I can do it with all the products because Excel says the formula is too long.

How can I do this in a better way? Help?

Thanks!

Mike

 

[Magriza]

One option would be to insert another row between 5 and 6 and then place the cost under the item (in the new row 6). You could still use VLOOKUP to fetch the cost from elsewhere but then you could use SUMPRODUCT in column X to calculate the cost.

So in X7 (the first name after inserting a new row) you'd have:
=SUMPRODUCT($C$6:$V$6,C7:V7)

You could hide row 6 so that the sheet looks the same (for printing etc.)

 

[Magriza]

Oh, and welcome to the board!

 

[hiker95]

MMike,

Welcome to the MrExcel board.

If the formula in cell X6 is to be copied down the column, then you may have to use the following formula:
=SUM(($C6)*(VLOOKUP($C$5,pricelist,2,FALSE)),($D6)*(VLOOKUP($D$5,pricelist,2,FALSE)),($E6)*(VLOOKUP($E$5,pricelist,2,FALSE)),($F6)*(VLOOKUP($F$5,pricelist,2,FALSE)),($G6)*(VLOOKUP($G$5,pricelist,2,FALSE)) etc.

 

[MMike]

Hi Hiker95,

Yes it does get copied down the column I'll have to correct that thanks.

 

[MMike]

One option would be to insert another row between 5 and 6 and then place the cost under the item (in the new row 6). You could still use VLOOKUP to fetch the cost from elsewhere but then you could use SUMPRODUCT in column X to calculate the cost.

So in X7 (the first name after inserting a new row) you'd have:
=SUMPRODUCT($C$6:$V$6,C7:V7)

You could hide row 6 so that the sheet looks the same (for printing etc.)

Hi Magriza,

Thanks!

Not quite sure I understand this how would this work. I need it to take the number of items sold for each product and multiply that by the price. Then it needs to do that for each product and give me a total in column X.

With your recommendation I would have to use two rows for every person wouldn't I because I would be using the second line to store the result of the first lookup and the sum them?

Or am I totally missing it?

 

[Matty]

Hello MMike,

Magriza's solution was to insert a single Row, and then reference that absolutely in the suggested SUMPRODUCT formula. But this can be down without a helper Row, like:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1		Pizza Base	Cheese	Tomato	Pepperoni				ID	Cost
2	Name	101	102	103	104	Total Items	Total Cost		101	1.00
3	John Doe	1	2	1	1	5	6.00		102	1.50
4									103	0.75
5									104	1.25
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F3	=SUM(B3:E3)
G3	=SUMPRODUCT(SUMIF(I$2:I$5,B$2:E$2,J$2:J$5),B3:E3)

Any good to you?

Matty

 

[MMike]

Ah! yes! perfect and works as advertised!

Matty, Thank you so much! I don't think I would have gotten that.

Thanks again!