Dynamic Vlookups?

Okay so I have two sheets that look like this:


Edit: Okay so the formatting was screwed there, fixing for a table:

Location	Location A	Location B	Location C
Total Price	???	???	???
Item A	500	0	20
Item B	50	20	30
Item C	0	0	10
Item D	12	15	10

Sheet2 (Price List)

Item	Price
Item A	3.14
Item B	12
Item C	4.57
Item D	2

How do I get a formula that looks up the price of ITEMA...B...C... multiplies that value by the value of the corresponding item on the Price List, then sums them altogether.

I know that I can use a bunch of VLOOKUPS to do this and then sum them one by one. However, my sheet actually has well over 50 values and those kinds of formulas will take forever to update.

I believe the answer lies somewhere with a SUMPRODUCT array formula, I am just not seeing it right now.

Any help would be appreciated.

Thank You!

This is an Excel Forum, so posting a sample workbook demonstrating the layout of your data and a manually mocked set of "results" you want to achieve by formula is the way to get this right on the first try.

Click GO ADVANCED and use the paperclip icon to post up a copy of your sample workbook. Make sure the workbook demonstrates your desired results.

The way you present it with two lists with the same records in would be easily be solved with an ordinary SUMPRODUCT.

I then realized that the first list probably can be scrambled and have multiple Item A, multiple Item B etc. and/or items that are not in the second list. In that case I don't know how to solve it without involving VLOOKUP.

I was surprised of how easy it was to put a VLOOKUP in an array formula. It worked on the first try, almost.

Note to self, VLOOKUP does not work with arrayformulas, LOOKUP does.

Formula:

Please Login or Register  to view this content.