Hello,

I have one excel sheet containing dates of M&A transactions. Another sheet contains yearly data on all the acquiring companies.

I want to construct a Vlookup function based on the date of acquisition.
Say I have the date of the acquisition (e.g. 05/30/2006).
The acquiring company should add the revenue of the acquired company in its first annual report AFTER the transaction.
Difficulty is that not all companies publish their financial report on December 31.

Therefore I want to construct a vlookup function giving two values: with the value from the financial report before AND after the acquisition.

Example:
Acquisition: On May 31st 2006
Annual report after acquisition: On July 31st, 2006
Annual report before acquisition: On July 31st 2005
Conditions: annual report for this specific company

Thanks for thinking with me.

Link to the file (Box): https://app.box.com/s/i2otul0mpeah0rz818nnatpaud1m510f