2D array lookup that can return multiple values

A

andrewhorne252

New Member
Joined
Jun 27, 2017
Messages
3
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
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
 
Upvote 0
Try SUMPRODUCT function.
Excel Workbook
ABCDE
1
21e6t8i1e
3i86y1eg7
48ip08i1e
5
61234
75678
89101112
9
10itemsum
111e24
128i23
Sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Can I combine MIN with an Index Match array?
Replies
3
Views
2K
Eric W
Eric W
L
  • Question Question
How can I return the most recent account balance from a chart of multiple accounts
Replies
2
Views
826
bosco_yip
B
T
  • Question Question
Locating moving cell value in an array, and returning the date form the top of the array.
Replies
1
Views
424
Peter_SSs
Peter_SSs
T
  • Question Question
Multiple Criteria INDEX and MATCH CSE Array Formula
Replies
1
Views
2K
Marcelo Branco
M
A
  • Question Question
Return a text value that is not equal to a text value with a lookup or array
Replies
4
Views
742
Allkair
A

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top