2D array lookup that can return multiple values

[andrewhorne252]

Hello,

I have a table of die numbers that correspond to another table of production numbers. I am looking to see if it is possible to automate the summing of the production numbers based on where the die numbers are. I will continue to use this in the future and don't want to have to sum the numbers up manually every time I update the table.

I have looked at multiple examples, but can't find anything like this. I have been trying to find an function that would return an array of numbers, maybe 1's and 0's, that give the location of the die number I am looking for. Then I could use that array to pull the corresponding production numbers.

For example: If i wanted to find the total for die 1E. I would add all of the production numbers in red. I would get 646,326.

I don't know any formula/formula combinations that can pull multiple values out of a 2D array.

Any suggestions? Anything would be greatly appreciated.

[TABLE="class: grid"]
<tbody>[TR]
[TD]Die Positions[/TD]
[TD]11/20/2015[/TD]
[TD]1/21/2016[/TD]
[TD]4/14/2016[/TD]
[TD]4/25/2016[/TD]
[TD]6/20/2016[/TD]
[TD]8/24/2016[/TD]
[TD]10/27/2016[/TD]
[TD]1/20/2017[/TD]
[TD]3/2/2017[/TD]
[TD]5/9/2017[/TD]
[TD]→ → →[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10012[/TD]
[TD]110513[/TD]
[TD]9551[/TD]
[TD]71723[/TD]
[TD]91425[/TD]
[TD]85557[/TD]
[TD]99993[/TD]
[TD]48888[/TD]
[TD]72516[/TD]
[TD]57167[/TD]
[TD]This[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10184[/TD]
[TD]110869[/TD]
[TD]9577[/TD]
[TD]71449[/TD]
[TD]91436[/TD]
[TD]85882[/TD]
[TD]99143[/TD]
[TD]49040[/TD]
[TD]72582[/TD]
[TD]57209[/TD]
[TD]will[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10159[/TD]
[TD]111117[/TD]
[TD]9756[/TD]
[TD]71268[/TD]
[TD]91148[/TD]
[TD]86392[/TD]
[TD]99892[/TD]
[TD]49031[/TD]
[TD]73507[/TD]
[TD]57270[/TD]
[TD]expand[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10107[/TD]
[TD]111464[/TD]
[TD]9675[/TD]
[TD]71417[/TD]
[TD]91108[/TD]
[TD]85226[/TD]
[TD]100119[/TD]
[TD]49452[/TD]
[TD]73577[/TD]
[TD]57448[/TD]
[TD]outward[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10207[/TD]
[TD]110921[/TD]
[TD]9593[/TD]
[TD]71086[/TD]
[TD]90946[/TD]
[TD]85690[/TD]
[TD]99919[/TD]
[TD]48666[/TD]
[TD]72236[/TD]
[TD]56662[/TD]
[TD]with each[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10216[/TD]
[TD]111293[/TD]
[TD]9666[/TD]
[TD]71218[/TD]
[TD]90926[/TD]
[TD]85209[/TD]
[TD]99556[/TD]
[TD]48529[/TD]
[TD]72195[/TD]
[TD]57159[/TD]
[TD]die[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]10115[/TD]
[TD]110523[/TD]
[TD]9613[/TD]
[TD]70188[/TD]
[TD]90992[/TD]
[TD]85794[/TD]
[TD]99854[/TD]
[TD]48921[/TD]
[TD]72340[/TD]
[TD]57477[/TD]
[TD]change[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]10225[/TD]
[TD]110877[/TD]
[TD]9836[/TD]
[TD]71394[/TD]
[TD]90797[/TD]
[TD]85214[/TD]
[TD]99250[/TD]
[TD]49112[/TD]
[TD]72419[/TD]
[TD]57270[/TD]
[TD]→ → →[/TD]
[/TR]
</tbody>[/TABLE]

<table class="wysiwyg_dashes wysiwyg_cms_table_grid" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"="" width=""><tbody>[TR="class: grid"]
[TD]Die[/TD]
[TD="colspan: 2"]Total Produced / die[/TD]
[/TR]
[TR="class: grid"]
[TD]1E[/TD]
[TD="colspan: 2"]646,326[/TD]
[/TR]
[TR="class: grid"]
[TD]2E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]3E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]4E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]5E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]6E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]7E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]8E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]9E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]1J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]2J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]3J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]4J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]5J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]6J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]7J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]8J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]4F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]5F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]6F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]7F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]8F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]1G[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]2G[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]3G[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]4C[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR="class: grid"]
[TD]2F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
</tbody></table>
Here is a link to an image of the data if the data above does not turn out: Imgur: The most awesome images on the Internet

Right now I am going in and summing up the cells by clicking on them.

I am using excel 2010 on windows.

Thank you,
Andrew

 

[andrewhorne252]

Here is the data again:
[TABLE="class: grid"]
<tbody>[TR]
[TD]Die Positions[/TD]
[TD]11/20/2015[/TD]
[TD]1/21/2016[/TD]
[TD]4/14/2016[/TD]
[TD]4/25/2016[/TD]
[TD]6/20/2016[/TD]
[TD]8/24/2016[/TD]
[TD]10/27/2016[/TD]
[TD]1/20/2017[/TD]
[TD]3/2/2017[/TD]
[TD]5/9/2017[/TD]
[TD] → → →[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]6E[/TD]
[TD]1E[/TD]
[TD]1E[/TD]
[TD]1E[/TD]
[TD]1E[/TD]
[TD]1E[/TD]
[TD]1E[/TD]
[TD]1G[/TD]
[TD]2E[/TD]
[TD]5J[/TD]
[TD]This[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1G[/TD]
[TD]3E[/TD]
[TD]3E[/TD]
[TD]3E[/TD]
[TD]3E[/TD]
[TD]3E[/TD]
[TD]3E[/TD]
[TD]3E[/TD]
[TD]3E[/TD]
[TD]3E[/TD]
[TD]will[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2G[/TD]
[TD]6E[/TD]
[TD]6E[/TD]
[TD]6E[/TD]
[TD]6E[/TD]
[TD]6E[/TD]
[TD]6E[/TD]
[TD]4J[/TD]
[TD]2G[/TD]
[TD]2G[/TD]
[TD]expand[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]8[/TD]
[TD]7E[/TD]
[TD]7E[/TD]
[TD]3G[/TD]
[TD]5F[/TD]
[TD]5F[/TD]
[TD]8F[/TD]
[TD]5F[/TD]
[TD]8F[/TD]
[TD]6F[/TD]
[TD]outward[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3E[/TD]
[TD]1G[/TD]
[TD]1G[/TD]
[TD]7F[/TD]
[TD]1G[/TD]
[TD]1G[/TD]
[TD]1G[/TD]
[TD]1E[/TD]
[TD]1E[/TD]
[TD]1E[/TD]
[TD]with each[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4F[/TD]
[TD]4F[/TD]
[TD]5E[/TD]
[TD]5E[/TD]
[TD]5E[/TD]
[TD]5E[/TD]
[TD]5E[/TD]
[TD]5E[/TD]
[TD]5E[/TD]
[TD]4C[/TD]
[TD]die[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]5F[/TD]
[TD]5F[/TD]
[TD]5F[/TD]
[TD]7E[/TD]
[TD]7E[/TD]
[TD]7E[/TD]
[TD]7E[/TD]
[TD]7E[/TD]
[TD]7E[/TD]
[TD]4E[/TD]
[TD]change[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]7F[/TD]
[TD]7F[/TD]
[TD]1P[/TD]
[TD]1P[/TD]
[TD]1P[/TD]
[TD]4J[/TD]
[TD]4J[/TD]
[TD]6E[/TD]
[TD]6E[/TD]
[TD]8E[/TD]
[TD] → → →[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid"]
<tbody>[TR]
[TD]Die Positions[/TD]
[TD]11/20/2015[/TD]
[TD]1/21/2016[/TD]
[TD]4/14/2016[/TD]
[TD]4/25/2016[/TD]
[TD]6/20/2016[/TD]
[TD]8/24/2016[/TD]
[TD]10/27/2016[/TD]
[TD]1/20/2017[/TD]
[TD]3/2/2017[/TD]
[TD]5/9/2017[/TD]
[TD] → → →[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10012[/TD]
[TD]110513[/TD]
[TD]9551[/TD]
[TD]71723[/TD]
[TD]91425[/TD]
[TD]85557[/TD]
[TD]99993[/TD]
[TD]48888[/TD]
[TD]72516[/TD]
[TD]57167[/TD]
[TD]This[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10184[/TD]
[TD]110869[/TD]
[TD]9577[/TD]
[TD]71449[/TD]
[TD]91436[/TD]
[TD]85882[/TD]
[TD]99143[/TD]
[TD]49040[/TD]
[TD]72582[/TD]
[TD]57209[/TD]
[TD]will[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10159[/TD]
[TD]111117[/TD]
[TD]9756[/TD]
[TD]71268[/TD]
[TD]91148[/TD]
[TD]86392[/TD]
[TD]99892[/TD]
[TD]49031[/TD]
[TD]73507[/TD]
[TD]57270[/TD]
[TD]expand[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10107[/TD]
[TD]111464[/TD]
[TD]9675[/TD]
[TD]71417[/TD]
[TD]91108[/TD]
[TD]85226[/TD]
[TD]100119[/TD]
[TD]49452[/TD]
[TD]73577[/TD]
[TD]57448[/TD]
[TD]outward[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10207[/TD]
[TD]110921[/TD]
[TD]9593[/TD]
[TD]71086[/TD]
[TD]90946[/TD]
[TD]85690[/TD]
[TD]99919[/TD]
[TD]48666[/TD]
[TD]72236[/TD]
[TD]56662[/TD]
[TD]with each[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10216[/TD]
[TD]111293[/TD]
[TD]9666[/TD]
[TD]71218[/TD]
[TD]90926[/TD]
[TD]85209[/TD]
[TD]99556[/TD]
[TD]48529[/TD]
[TD]72195[/TD]
[TD]57159[/TD]
[TD]die[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]10115[/TD]
[TD]110523[/TD]
[TD]9613[/TD]
[TD]70188[/TD]
[TD]90992[/TD]
[TD]85794[/TD]
[TD]99854[/TD]
[TD]48921[/TD]
[TD]72340[/TD]
[TD]57477[/TD]
[TD]change[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]10225[/TD]
[TD]110877[/TD]
[TD]9836[/TD]
[TD]71394[/TD]
[TD]90797[/TD]
[TD]85214[/TD]
[TD]99250[/TD]
[TD]49112[/TD]
[TD]72419[/TD]
[TD]57270[/TD]
[TD] → → →[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid"]
<tbody>[TR]
[TD]Die[/TD]
[TD="colspan: 2"]Total Produced / die[/TD]
[/TR]
[TR]
[TD]1E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]2E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]3E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]4E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]5E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]6E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]7E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]8E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]9E[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]1J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]2J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]3J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]4J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]5J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]6J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]7J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]8J[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]4F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]5F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]6F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]7F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]8F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]1G[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]2G[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]3G[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]4C[/TD]
[TD="colspan: 2"][/TD]
[/TR]
[TR]
[TD]2F[/TD]
[TD="colspan: 2"][/TD]
[/TR]
</tbody>[/TABLE]

Thanks,
Andrew

 

[AhoyNC]

Try SUMPRODUCT function.

Excel Workbook
	A	B	C	D	E
1
2		1e	6t	8i	1e
3		i8	6y	1e	g7
4		8i	p0	8i	1e
5
6		1	2	3	4
7		5	6	7	8
8		9	10	11	12
9
10	item	sum
11	1e	24
12	8i	23
Sheet

 

[andrewhorne252]

That works perfectly.

Thank you so much,
Andrew

 

[AhoyNC]

You're welcome. Thanks for the feedback.