Multiply numbers using VLOOKUP

Question

I have a case of 22 lines where a user can enter in the amount needed for each item. Each item has a price associated with it. So I need the cell to lookup the price for each item and multiply it by the amount. I've used VLOOKUP and it doesn't appear to work. Here's the data:

and the vlookup is:

I need column 3 from the last table to be multiplied by "1" in the top table and then summed everything up to give me a total. I don't know if this will make a difference but the "1" column is not adjacent to the description. It's three columns over.

Answer 1

I have a case of 22 lines where a user can enter in the amount needed for each item. Each item has a price associated with it. So I need the cell to lookup the price for each item and multiply it by the amount. I've used VLOOKUP and it doesn't appear to work. Here's the data:

 

Dgst 4 pg cover	1
Dgst 8 pg cover	1
Dgst 16 pg cover	1
Dgst 24 pg mixed cover	1
Dgst 32 pg mixed cover	1
Dgst 32 pg body	1
Dgst 24 pg body	1
Dgst 16 pg body	1
Dgst 8 pg body	1

 

and the vlookup is:

 

Dgst 16 pg body		$                                3,034.60
Dgst 16 pg cover		$                                3,034.60
Dgst 24 pg body		$                                3,034.60
Dgst 24 pg mixed cover		$                                3,034.60
Dgst 32 pg body		$                                3,034.60
Dgst 32 pg mixed cover		$                                3,034.60
Dgst 4 pg cover	as 4, 4 out - 4/4	$                                2,399.62
Dgst 8 pg body		$                                3,034.60
Dgst 8 pg cover		$                                3,034.60

I need column 3 from the last table to be multiplied by "1" in the top table and then summed everything up to give me a total. I don't know if this will make a difference but the "1" column is not adjacent to the description. It's three columns over.

This will work but has a couple of conditions attached.

Your lookup table *MUST BE SORTED IN ASCENDING ORDER BY PRODUCT*.

For each lookup value *THERE MUST BE AN EXACT MATCH IN THE LOOKUP TABLE*.

Lookup values in the range A2:A10

Quantity in the range G2:G10

Lookup table (sorted in ascending order!!!) in the range A13:B21

Then:

=SUMPRODUCT(LOOKUP(A2:A10,A13:B21),G2:G10)

--

Biff

Microsoft Excel MVP

Answer 2

They are numbers.

Answer 3

OK, so the value is coming back as just a sum of the values I have in G2:G10. What it's not doing is multiplying those values against column C of my 2nd table that I posted.

Make sure the numbers in column G are really numeric numbers and not TEXT numbers.

I don't have access to Excel 2007 right at this very moment so here are some generic instructions on how to do that.

Make sure the cell format for the cells in question in column G is General. Change the format to General then double click within the cell then hit Enter.

--

Biff

Microsoft Excel MVP

Answer 4

To illustrate: if I have 10 "Dgst 4 pg cover" and 10 "Dgst 8 pg cover", my end result should be $54,342.20 (which is the sum of 10 x 2399.62 and 10 x 3034.60). Thanks for your help!

Answer 5

OK, so the value is coming back as just a sum of the values I have in G2:G10. What it's not doing is multiplying those values against column C of my 2nd table that I posted.