Complex Lookup Formulas

Hi, I am hoping someone can help me with 3 complex look up formulas please.

Attached is a file with 2 active sheets. The are 3 columns I am trying to fill in the 'SUMMARY' sheet based on data in the 'Transactions' sheet.

All lookups are based on the Account # (which is 'Acc #' in the 'SUMMARY' sheet and 'accno' in the 'Transactions' sheet).
1. 'Trans Count' - # of transaction listed in the 'Transactions' sheet (so in the case of account 35 there is 3 transactions)
2. 'Last Trans Date' - date of the most recent transaction listed in the 'Transactions' sheet (so in the case of account 35 it is 1st Aug 2016 - this is column D)
3. 'Last Trans Value' - value of the most recent transaction listed in the 'Transactions' sheet (so in the case of account 35 it is $569.8 - this is column N)

If someone is able to put these calculations directly into the spreadsheet I have attached I would be extremely grateful!

Thanks a lot
Troy
Amalgam-separator-rebate-informatio.xlsx
recycleausAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Please try this....

On Summary Sheet,
In H2
=COUNTIF(Transactions!$E$2:$E$302,G2)

Open in new window

and copy down.

In I2
=MAX(INDEX((Transactions!$E$2:$E$302=G2)*Transactions!$D$2:$D$302,))

Open in new window

and copy down. (format as date)

In J2
=IFERROR(INDEX(Transactions!$N$2:$N$302,MATCH(1,INDEX((Transactions!$E$2:$E$302=G2)*(Transactions!$D$2:$D$302=I2),),0)),"")

Open in new window

and copy down.
0
 
recycleausAuthor Commented:
Thanks a lot
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Troy! Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.