vlookup + array formula producing #Value!

Hello,
I have a workbook that if I use just the array formula for the totals it works fine and if I use data validation and vlookup and the array formula for the totals it is giving me a #Value! instead.
I have tried a couple of things and did find that as long as I do not use the vlookup in the one column (column d) the array formula at the bottom works but as soon as I add the vlookup to column D I got the error.

I want to use the vlookup to get the values for items on a different page - along with the data validation and then use the array formula to get a total for multiple items in the above columns.

I hope this makes sense. I have attached a workbook to show what I am talking about.

Try:

Please Login or Register  to view this content.

with ctrl+shift+enter