# 2 Dimensional vlookup + match, pulling from two different data sets?

here's a tricky one for me, hopefully not for you.

I have a data table in one sheet which lists product numbers in the column, and materials used to make said product in the top row, with corresponding quantities where they meet.

In a second sheet, I have a daily list that consists what products were produced with a corresponding quantity produced in another column.

In my third sheet, I'd like to be able to see the totals of what materials were used that day depending on what products were produced

Ex. Daily sheet shows out of the list that 80 units of product 1 was produced. It takes 1 unit of material x to produce the product. Therefore Id like a formula to show me I used 80 units of material x, showing up under that column header.

Any help is appreciated, I feel like Im close but making it way too complicated and cant get it to work...

Thanks!

• show us some of what you've tried. Will a sumif do the trick here? – Kevin Mar 7 '13 at 3:54
• OK, it's easy if your sheet two has all the same product numbers, and if they weren't produced has a zero (or is blank) for a quantity. Is that the case? – Lance Roberts Mar 7 '13 at 4:59
• sumif alone didnt work, a response below using sumif and sumproduct did the trick, guess two dimensional lookup wasnt the right path. – user2142544 Mar 7 '13 at 20:54

For the solution I assume the following, slightly simplified setup: To get to this result, do the following:

• Add an new column next to your table with the quantities (here it is column E). In here you sum the amount of products sold. I used this formula:
`=SUMIFS(I:I,H:H,A4)`
. Depending if you want the sales for a specific date, you can of course adjust this formula, as at the moment it sum the products from all dates.
• Add a table with all the materials
• Calculate the the sum with the following formula
`=SUMPRODUCT((\$B\$4:\$D\$6*\$E\$4:\$E\$6)*(\$B\$3:\$D\$3=\$K4))`
• This works great! Different idea from what I was going for but awesome thank you. – user2142544 Mar 7 '13 at 20:52

Heres a strategy that uses a combination of Excel `Tables` and a `Pivot Table` to report the results

(Developed in Excel 2010)

• Configure your Product/material usage data as a `Table`: I've called it `tblQuantities`
• Configure your Daily Production data as a `Table`: I've called it `tblProduction`
• Extend `tblProduction` with formulas to get the material consumption per Product per Material ID
• Set `tblProduction` to auto extend the formulas as you add new rows
• Add a `Pivot Table` to get the summed daily consumtion data

tblQuantities tblProduction Formula for `tblProduction` `Material` columns

``````=INDEX(tblQuantities,MATCH([@Product],tblQuantities[ProductID],0),  