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?

  • Is there a possibility that the Date in column A in first Sheet1 in fact appears or falls in multiple rows/ranges in Sheet2 and thus has multiple Range IDs in Sheet2? If yes what value it should return? – patkim Sep 2 '17 at 18:42
  • 1
    It depends on how Sheet2 is set up. If the date ranges are inclusive of all the dates on Sheet1, and there is no overlap, and the date ranges are in ascending order, then a simple =VLOOKUP(A1,Sheet2!$A$1:$C$nnn,3) will work. – Ron Rosenfeld Sep 2 '17 at 18:54

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.

enter image description here

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.