Excel 2007 : Return Max Date in column based on lookup in adjacent column

I have a spreadsheet where I would like to lookup a vendor # (ex. 20831) in another spreadsheet where there are several entries for that vendor # (A/P invoices). Once the lookup finds the vendor #, I would like it to search an adjacent column containing invoice dates and return the maximum date value. Anyone know the best way to go about this?

Thanks in advance for any help you can lend.

Originally Posted by JGradowitz

I have a spreadsheet where I would like to lookup a vendor # (ex. 20831) in another spreadsheet where there are several entries for that vendor # (A/P invoices). Once the lookup finds the vendor #, I would like it to search an adjacent column containing invoice dates and return the maximum date value. Anyone know the best way to go about this?

Thanks in advance for any help you can lend.

I've attached the workbook if this helps. The date fields already filled in on the MIR spreadsheet, I would like to automatically fill based on a lookup formula that matches the vendor # in the 4-1-2-18 All spreadsheet and then return the invoice with the most recent date.