Formula that pulls across a value based on the which date range the given date falls into.

Hello!

I feel like I'm close to a solution, but I can't seem to figure out an efficient solve to this. I feel that an INDEX solution may be needed, but I'm not very well-versed in INDEXes. I don't think vlookup will be helpful here either, since this only pulls the first match.

What I'm looking to do is create a formula that exists on tab A (not shown) that works as a helper column next to raw data, that says "when employee matches column 1 and date falls between the dates in the attached table (columns 3 & 4), pull across the team name from column 2.

To put more in context, we have sales teams where the employees transition teams every so often.
However, we want to make sure we are assigning "credit" for sales metrics achieved by employees fairly.
For example, if an employee just transitioned from team Thompson to team Holbrook, we want to make sure the sales metrics achieved prior to this transition are credited to team Thompson. This will be handled by assigning the correct "Team" when a metric date falls between the date range, which is essentially the dates that the employee was a part of that team.

I'm happy to provide more details if needed.

Thanks ahead of time!

Chris


tableexample.png

It would help if you attached a sample Excel workbook, as it is difficult to advise on a formula for tab A when we can't see how the data on that tab is laid out.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it is broken on this forum.

Hope this helps.

Pete

Thanks for the feedback Pete. I've attached a workbook so you can see the layout that I'm referring to. Once again, the raw data will be on the "data" tab, and the table I'm referencing will be on the "table" tab.

Let me know if you have additional questions.

Thanks!

Put this array* formula in cell C2 of the Data sheet:

=IFERROR(INDEX(table!$B$2:$B$7,MATCH(1,(table!$A$2:$A$7=A2)*(B2>=table!$C$2:$C$7)*(B2<=table!$D$2:$D$7),0)),"")

*Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter, rather than the usual < Enter >.

Then you can copy it down as far as you need to using your usual method(s).

Hope this helps.

Pete

Pete,

You are the man. Thanks so much, this works perfectly!

Chris

You're welcome, Chris - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

Also, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete