title: Excel - Assign vlookup value based on date range
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>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). </p>
  
  <p>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.</p>
  
  <p>I am working with Excel 2010.</p>
  
  <p><a href="https://i.stack.imgur.com/0MxYE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0MxYE.png" alt="Reference"></a></p>