当前位置: 动力学知识库 > 问答 > 编程问答 >

Excel - Assign vlookup value based on date range

问题描述:

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

=INDEX(F:F,MATCH(1,IF((H:H<=C2)*OR(I:I>=C2,ISBLANK(I:I))*(G:G=B2),1)))  Ctrl+Shift+Enter
分享给朋友:
您可能感兴趣的文章:
随机阅读: