0

I have two workbooks and need to extract data from one into the other. The one with the data looks like this:

BE | Account | Site | Major | Sub | Description | Beginning | Ending
02 | 00-345  | 103  | 23    | 12  | Some thing  | $125,000  | $98800
02 | 00-345  | 113  | 23    | 11  | Irrelevant  | $120,000  | $10700
02 | 00-345  | 103  | 22    | 12  | Some thing  | $180,060  | $90000
02 | 00-345  | 113  | 22    | 09  | Irrelevant  | $177,435  | $93400
78 | 00-345  | 222  | 12    | 12  | Some thing  | $129,800  | $40000
78 | 00-345  | 111  | 23    | 12  | Irrelevant  | $620,509  | $90066
78 | 00-345  | 504  | 23    | 12  | Some thing  | $99,000   | $90470
98 | 00-345  | 177  | 23    | 12  | Irrelevant  | $900,000  | $10304
98 | 00-345  | 509  | 23    | 12  | Some thing  | $180,164  | $12904

What I need to do is extract the Value from the "ending" column based on the matching BE, Major, and Sub. For example, given BE: 02, Major: 22, Sub: 12 I would want the cell to return the value $90000. Is there a way to do this without using a lengthy Index/match formula? Perhaps through the use of some level of filtering or pivot tables? I can't seem to get past a formula approach but want to avoid the use of a ridiculously long formula if possible.

0

Since it is numbers, and if the combination of those three are unique as in your example you can use SUMIFS():

=SUMIFS(H:H,A:A,2,D:D,22,E:E,12)

enter image description here

  • Thank you! This works great. So much better than the messy, complex plan I was concocting in my head. – Eleyna Jun 22 '17 at 15:10

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.