# Need Help Summing Two Way Lookups from Multiple Tables

#### dwgnome

##### Active Member
Looking to sum the total emissions from several tables based on two criteria – the engine type and pollutant. However, each emission table is “labeled” by the engine type from a drop down (at C15, H15, Q15, V15 etc) and can change based on user input.

I was able to develop a formula to capture the correct emission (tons column) on the Engine Evaluation worksheet for pollutant NOX and engine type PW4090 at the first table approx C15:G26, but do not know how to extend to sum from all tables.

So at J17 on the Evaluation Summary I came up with:

Code:
``[COLOR=#222222][FONT=Calibri][SIZE=3]=INDEX('Engine Evaluation'!B15:L26,MATCH(J15,'Engine Evaluation'!B15:B26,0),MATCH(I17,'Engine Evaluation'!B15:H15,0)+4)[/SIZE][/FONT][/COLOR]``

Alternatively:
Code:
``[COLOR=#222222][FONT=Calibri]=SUM(IF(('Engine Evaluation'!\$B\$22:\$B\$26=J15)*('Engine Evaluation'!\$C\$15:\$F\$15=I17),'Engine Evaluation'!\$G\$22:\$G\$26,0))[/FONT][/COLOR]``
as
Cntrl+Shft+Enter

Both give the correct answer but again, I don’t know how to extend the formulas to the other tables. Below the table with the one formula at J17 is an image of the table with a few of the expected results.

Your help would be most appreciated. Excel 2003.

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Still need help!

Bump.

Bump.

Bump.

#### dwgnome

Bump.

##### MrExcel MVP

Arrange the tables in a vertical area one after the other, each having the same header structure. Once that done, summing across all of them would be an easy and reliable job.

#### ififthelement

##### New Member
Hi, I found an example on the internet for tables. Sorry I couldnt find upload button. but here is the example as is :

Use Col A1 to B and enter the Product table 1 and using similar structure create Product Table 2 and 3... one below the other

 on Row 1 Product 1 \$0.00 0.00% \$500.00 1.00% \$1,500.00 2.00% \$5,000.00 3.00%

<colgroup><col><col></colgroup><tbody>
</tbody>

Product table 2 - on Row 9
Product table 3 - on Row 17

towards the end of the 3 tables in Col A& B ..in row 23 create the table
 Product 1 1 Product 2 2 Product 3 3

<colgroup><col><col></colgroup><tbody>
</tbody>

Now Create this table below in col D1 to H
 Products Amount of Sale Table Discount ? Discount ? Product 1 \$3,225.00 1 64.5 64.5 Product 3 \$2,014.00 3 20.14 20.14 Product 1 \$1,264.00 1 12.64 12.64 Product 2 \$1,434.00 2 21.51 21.51 Product 1 \$2,295.00 1 45.9 45.9

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>
Formula for
F2 is =VLOOKUP(D2,\$A\$23:\$B\$25,2,0)
G2 is =E2*INDEX((\$B\$2:\$B\$5,\$B\$10:\$B\$13,\$B\$18:\$B\$21),MATCH(E2,\$A\$2:\$A\$5),,F2)
H2 is =E2*VLOOKUP(E2,CHOOSE(F2,\$A\$2:\$B\$5,\$A\$10:\$B\$13,\$A\$18:\$B\$21),2)

similarly drag it downwards

hope that helps..

#### dwgnome

##### Active Member

I rearranged the tables as suggested. Please take a look at the updated file (link below) and let me know if this is what you had in mind. I have attempted to use a variety of approaches to capture and sum but to no avail.

Thank you ififthelement, but I am unable to understand the example you referenced. Maybe if you look at my file, you can work from that instead.

See updated file at: https://dl.dropboxusercontent.com/u/95483456/Excel/DWGN Evaluation7b.xls

#### dwgnome

##### Active Member
Bump.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,629
Messages
5,838,454
Members
430,549
Latest member
jayjay2022

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back