How can I combine a series of dates? VLOOKUP can only work with one unique identity.

I have 2 worksheets I am working from.

The first contains a list of date ranges. See the table below for examples, the data could be a single date range (which would be easy in the end), or consecutive dates ranges in the example of identifier 222222, or non consecutive date ranges as in the example of identifier 333333.


The second worksheets contains a single date with corresponding identifiers

What I would like to do is check the date by the first worksheet and if it is within the date range specified I want to have another column(In Red) that will tell me if it is on the list or not.

This would be easy if there was only 1 date range; I could use VLOOKUP and an IF(AND()) equation to work it out
This would still be within my limits if all the dates were consecutive; that way I could do a pivotable and take the minimum of the start dates and max of the end dates to create one date range.
But with non-consecutive date ranges I am stumped at where to go to next.

HELP...

To get the results you show, try:

=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!B:B,"<="&B2,Sheet2!C:C,">="&B2)=0,"Yes","No")

copied down

where Sheet2 is the database,

Thanks NBVC for the advice and the introduction to a newequation.
I simplified my worksheets for the question. My start dates are in column E so I changedyour equation from B:B to E:E and the end date is in column G so I also changed C:C to G:G to no avail. All 44K lines of my formula come out as Yes.
What does the &B2 do? I tried playing with that as well to A2, and then E2 and G2 respectively but that doesn’t make any “No” as well.

the &B2 is your criteria to search against. It refers to 14-Feb-2014 in the following table. where B1 is "Date"


so your formula using the above table, cell c2 should contain, =IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!E:E,"<="&B2,Sheet2!G:G,">="&B2)=0,"Yes","No")

Thanks Simi and NBVC,

It all worked out in the end. I had one set of unique identifiers with leading zeroes which was fine when I conevrted them all over to numbers.

Works like a gem!

Let's push this a step further.

Is there a way to retrieve a fourth column of data from the first worksheet of "identifier", "startDate", "end date", and for argument sake lets say "colour" and get the equation to return the colour instead of a "yes"?

Assumint same structure, with the addition of column D in Sheet2 that contain color name strings... Try something like:

=IFERROR(INDEX(Sheet2!$D$2:$D$100,MATCH(1,INDEX((Sheet2!$A$2:$A$100=A2)*(Sheet2!$B$2:$B$100<=B2)*(Sheet2!$C$2:$C$100>=B2),0),0)),"")

Notice: This is an array formula and is less efficient than COUNTIFS, so it is recommended to use defined ranges that don't exceed maximum rows required.