Hi all,
I'm having trouble with a lookup-type function returning the wrong values.
We have our base sheet called the "Deal Tracker" where we keep tabs on the various deals that we consider (we're an investment firm). When entering a new deal, we always collect a Contact Name of the person who brought us the deal. We often receive multiple deals from the same person.
We want to leverage this data to make sure we're consistently keeping in touch with valuable contacts who bring us deals. The end result should be the following columns: Contact Name | Most Recent Deal from Contact | Date of Most Recent Deal | # Deals 2018 | # Deals 2019
We're using the Pivot app to collect # deals per contact as well as the comprehensive list of all contacts.
The problem I'm having is finding the most recent deal for each contact. It works fine for many of the contacts, but it pulls incorrect deals for other contacts and I don't know how to begin to figure out why 
The data in the base sheet is not sorted and we really do not want a solution that requires a particular sort order in the base sheet.
This is the formula used to try to pull that data:
=INDEX({Deal Name}, MATCH(MAX(COLLECT({First Date}, {Contact Name}, [Contact Name]@row)), {First Date}, 0))
Could anyone help??
Thank you!