index/match funktion to find nearest value (date) from 2 data sources

dan_web

New Member
Joined
Jul 1, 2014
Messages
6
Hi everyone,

In below data example (data comes from 2 data sources listed in 2 different sheets) how do I find the closest "lead date" to the "opportunity date", where the lead date is equal to or smaller than the opportunity date and when the same company appears in both data sets? The same company can appear many times in both data sets/sheets:


For example

(Sheet1 - leads)

Column A Column B
Account Name Lead Date
Netflix 15.02.2021
Netflix 29.01.2021
Netflix 01.04.2021

(Sheet2 - opportunities)

Column A Column B Column C
Account Name Opportunity Closest lead date to opportunity date (function?)
Netflix 15.02.2021 (must for example return the value 15.02.2021)
Netflix 01.02.2021 (must for example return the value 15.02.2021)
HBO Max 01.02.2021 (must return error bacause no match)
Netflix 01.03.2021 (must for example return the value 15.02.2021)


Thank you in advance.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

dan_web

New Member
Joined
Jul 1, 2014
Messages
6
Sorry here the data sets aremaybe more clear

Account NameLead Date
Netflix15.02.2021
Netflix29.01.2021
Netflix01.04.2021

Account NameOpportunity dateClosest lead date to opportunity date (function?)
Netflix15.02.2021(must for example return the value 15.02.2021)
Netflix01.02.2021(must for example return the value 15.02.2021)
HBO Max01.02.2021(must return error bacause no match)
Netflix01.03.2021(must for example return the value 15.02.2021)
 

Forum statistics

Threads
1,141,049
Messages
5,703,942
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top