I need a formula to match-up two datasets. I’m using excel 2010.
The 1st dataset is my base data & the 2nd provides additional details. The difficulty is that the 2nd dataset has multiple entries for a given name & I just need one record for each match to the 1st dataset based on the below criteria.

1. Name: (1st dataset - col. X to 2nd dataset - col. AB)
2. Product Name: “Widget” above all others (2nd dataset - col. AO)
3. Product Type - 1st choice being “Sales” (2nd dataset - col. AF)
4. If a Type of “Sales” entry isn’t available my 2nd choice would be the most recent Type, regardless of what it is, based on a Date column (2nd dataset - col. AH)

The 2nd dataset will have multiple possible matches, but I need to separate those that match the above criteria from those that do not. I’d like to use a vlookup to a reference # (1st dataset - col. Y) that can be used for a sort; sorting to the bottom those that do not meet the above criteria.

Any help would be appreciated