Search code examples
excelexcel-formulaexcel-2007

Find if date is in range using index match in Excel

I need to find if the id in column ID2 is in ID_Master AND if the corresponding date in col Date3 falls between the dates in Start_Date & End_Date.

I have the following layout:

ID_Master   Start_Date   End_Date   ID2        Date3

testid1         1/1/2016       2/1/2016    testid2   1/3/2016

I tried the index match formula, but have no luck. I feel its something simple, but i dont seem to have any luck :( appreciate the help this forum provides!

Solution

  • You could use a simple COUNTIFS():

    =IF(COUNTIFS($A$2:$A$500,D2,$B$2:$B$500,"<=" & E2,$C$2:$C$500,">=" & E2)>0,"y","n")

    Edit as per you comment.