sum of multiple Vlookups...

Listed are the two tables that I am trying to join using a Vlookup. However, I was wondering if there was a better way to possibly get the same result using array functions in Excel.

Name Item-1 Item-2 Item-3 Item-4 Vlookup-Item-1 Vlookup-Item-2 Vlookup-Item-3 Vlookup-Item-4 Total
a 1 3 5 7 5 12 22 22 61
b 2 4 6 7 17 27 51
c 1 5 5 22 27
d 2 4 8 7 17 17 41


Item Price
1 5
2 7
3 12
4 17
5 22
6 27
7 22
8 17


I want to end up with the Total column without having to do all the different Vlookup's (Vlookup-Item-1 thru Vlookup-Item-4) and summing up the result.

Vlookup is "IF(B2>0,VLOOKUP(B2,$A$10:$B$17,2,FALSE),"")"



Any insight would be greatly appreciated.

Sudheer

Thank you Martin & DonkeyOte:

Only problem with your solution is it fails to error out if there is an entry in Column B through E of table-1 without an entry in table-2. Try deleting ITEM 4 from table 4. How do we handle such exceptions?

I also have reservations using Martin's solution in this particular problem because in the real world I have to work with 12 columns from table-1 (as opposed to 4 in this example) and the formula gets a little unwieldy.

Once again, thanks for all your help!

I am sorry about not being clear. Attached is the sample sheet with the various options. What I am looking to get is the result in the "Total" column. All the options included give the correct result as far as every item listed in column B through E in table-1 has a corresponding entry in Table-2.

Try deleting the row corresponding to item-4 in table-2. The "Total" column displays "#NA" indicating an error, whereas option-1 through ignore the missing entry from table-2.

In such situations, I would like to be notified about the missing entry.

I hope I was clear. Thank you all for your help.

Hi Martin:

Thx for that solution. However, given that there may be 1000's of rows involved and several different tabs, I still have to look for a solution that would result in a severe error in calculation thereby alerting the necessary party.

I truly appreciate your help.

Thx
Sudheer