Lookup value between date range if meeting a criteria

Need help with a formula to look up the stage an activity occurred in for specific clients (example is below). For the activities I have a specific date ranges for each client. I want to avoid having to write specific formulas for each client. Below is an example of what I am working on. Need help with a formula for the right most column… 

 

Client Stage Stage Start Date Stage End Date     Client Activity Date Stage at the time of activity
Client X Stage 5 10/12/2016 12/22/2016     Client X 11/16/2016  NEED HELP WITH A FORMULA HERE – want to pull in the stage from the left based on the date ranges and client
Client Y Stage 1 6/14/2017 7/4/2017     Client Y 11/16/2016  
Client Y Stage 2 6/1/2017 6/14/2017     Client X 11/16/2016  
Client Y Stage 3 5/17/2017 6/1/2017     Client Y 11/17/2016  
Client Y Stage 4 4/23/2017 5/17/2017     Client X 11/17/2016  
Client Y Stage 5 3/14/2017 4/23/2017     Client X 11/18/2016  
            Client X 2/3/2017  
            Client Y 4/5/2017  
            Client Y 4/11/2017  
            Client Y 4/12/2017  
            Client X 4/12/2017  

By: alex

Leave a Reply

Your email address will not be published. Required fields are marked *