Creating a calculated column from another table based on date criteria

Example file with Sales and Mailers Tables

Example End Product Conversion Chart by Campaign

Background:

We help boat dealers sell boats, we do marketing for them.

I have 2 tables:

• Mailer table – telling us who we physically mailed a marketing campaign to (as well as details on the mailer)
• Sales Table – telling us who actually bought our widget

Problem:

I'm looking to visualize conversions by different physical mail "Campaign" we send out. Right now there’s no column in our Sales table that says which campaign resulted in a sale.

I need to add a “Last attributed Campaign” column in my Sales Table so I can look at (Count of campaign type mailed [Mailers Table]) /(Count of campaign type resulting in a sale [Sales Table]) – to get conversion by campaign. All I need is the column in the sales table to do this.

Complex If / Vlookup type request

• How can we add a “Last Attributed Campaign” column into the Sales table with the below conditions?
1. Look at the “Deal Date” in the Sales Table, and if it is on the 13th of the month or before, then the column should return the Campaign from the previous month or earlier, whatever is latest.
1. For example, if someone bought a boat on May 5th,  we want to attribute that Boat sale to the "Campaign" that was sent before May.
2. If the deal date is on the 14th of the month or after, simply return the most recent "Campaign" sent – could be the same month
3. Every row in the Sales table should be filled in based on what’s in the Mailer table.