Index/Match with closest date greater than value

[weeble33]

Hello,

I have a data set of sales employees that shows the history of their job titles along with an effective date for each title. I'm trying to match that up with a list of sales based on executed date. Each sale has the sales rep name and the executed date. I need to bring in the job title for that rep at the time of the sale.

I've got two formulas right now that I think can get me what I need, but I can't seem to combine them:

Column BE: Last Name
Column BD: First Name
Column M: Executed Date

This first one is just a match up of first and last name along with trying to grab effective dates that occur after any title change.

Please Login or Register  to view this content.

However, the above is bringing in the very first title, I need the title associated with the max effective date that occurs before the executed date, so I found the formula below:

Please Login or Register  to view this content.

The problem with the above is that it's looking at the entirety of the employee based. I need it refined down to only be looking at the set of dates associated with the particular sales rep on that row (in this case row 5).

Any ideas?

[FDibbins]

Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[weeble33]

Here's a small set of data.example.xlsx

[weeble33]

Hmm, Is it as easy as changing my MATCH statement in the first formula from match type = 0 to =1?

EDIT: no, no it is not that easy.

[FDibbins]

I will take a look at your file, but try that and see

[weeble33]

I've got it working (at least for everything I've checked so far), but I don't fully understand my solution.

Please Login or Register  to view this content.

Essentially not only does the match type change to 1, but changing the string of the conditional if statements to return a -1 for a true path. This requires the Staffing table to be sorted oldest to newest, but it's at least a workaround. I'd still appreciate any solutions that wouldn't involve a sort order on the source data.