I am looking for either an index and match formula or Vlookup formula to return only the providers name based on a date range. For example, I need the provider's name for the ID's DOS that falls within the provider's start date and end date range. Even if the provider's end date is blank/NULL.
The table on the left is missing the provider assigned to that Member's ID. The DOS represents the date of service a member received a service.
The table on the right is the history of a member's providers. For example, ID 1234 was assigned to Bob for during the date range 1/1/2016 (start date) to 2/13/2016 (End date).
The table on the left is when the member received a service. For instance, Member ID 1234 had a Date of Service (DOS) on 7/2/2016. The provider, Alan, is what I need returned since he was the provider assigned to that member (ID) during that DOS. Alan's start date: 2/14/2016 And End Date: 9/30/2016. So Alan should be returned as the provider the Member ID 1234 with DOS of 7/2/2016.
I am working with Excel 2010.
Try this array formula at
A2 then copy/paste it all the way down