Looking up last entry before certain date

[fbert]

Hi,

my data looks like this:


img upload

Downloadable here: Sample data.xlsx - Speedy Share - upload your files here

I'm trying to get a formula that does what I did manually in Table 2, Column E.

In words: The first table gives me dates of yearly financial reportings per company. The second table gives me dates for M&A transactions these companies were involved in. In Column E in Table 2 I want to mark the financial reporting date that was the last one before the M&A transaction date.

I have no idea how to get there!

Thanks for your help.

 

[Jonmo1]

Try this array formula entered with CTRL + SHIFT + ENTER

=MAX(IF((B$2:B$16=B21)*(D$2:D$16 < D21),D$2:D$16))

 

[fbert]

Try this array formula entered with CTRL + SHIFT + ENTER

=MAX(IF((B$2:B$16=B21)*(D$2:D$16 < D21),D$2:D$16))

Awesome, thank you very much.

 

[Jonmo1]

You're welcome.