How to map a date to a range of dates in excel?

I have 2 sheets in excel.

One field has date fields.

for example A-1 Date1

2nd sheet I have a date ranges with Ids.

A-1 StartDate A2- EndDate A3-Date Range ID

I need to create a new field on the first sheet that will contain Range ID of the Date1 .

For Example on 2nd sheet I have range A1-May 1 2017 A2-May 30 2017 A3-7

On the first sheet A1-May 3 2017. So I want to have a new column on my first sheet that will have value 7.

How can I do it in excel?

Answer

As already stated by @Ron Rosenfeld if range in Sheet2 is inclusive of all dates in Sheet1 and there's no overlap, simple VLOOKUP should work. However if your Date RAnge in Sheet2 is not nessasrily in ascending order and with gaps in between and possibly overlap. Try this solution. The limitation is that it shall return the first Range ID for the dates range where data from Sheet1 is found to be within range.

In this example Sheet1 has Dates in Column A1:A10. Sheet2 has Start Date in A1:A7 End Date in B1:B7 and Range ID in C1:C7.

In Sheet1 Cell B1 put the following Array Formula.

=IF(ISERROR(INDEX(Sheet2!$C$1:$C$7,MATCH(1,IF(A1>=Sheet2!$A$1:$A$7,IF(A1<=Sheet2!$B$1:$B$7,1,0)),0))),"Not Found",INDEX(Sheet2!$C$1:$C$7,MATCH(1,IF(A1>=Sheet2!$A$1:$A$7,IF(A1<=Sheet2!$B$1:$B$7,1,0)),0)))

After this Press CTRL+SHIFT+ENTER Key to make it an array formula. You shall now see the formula wrapped in curly braces in the address bar. Now Drag it down up to desired rows. Now Column B shall fetch the respective Range IDs from Sheet2 where the date in Column A falls in the range.

The formula is based on checking nested IFs to return an array of TRUE,FALSE based on the matching range and use an outer INDEX-MATCH to fetch the first position of matching 1 and return the value at that position from Range ID column.