Looking up a value, depending on which set of dates another date falls between

Question

and apologies if this is a duplicate - if you could point me in the direction of any existing answers, that would be great.

I have a set of date ranges in Excel, each of which has some kind of label. e.g.

LabelA  01/01/10  31/01/10
LabelB  01/02/10  28/02/10
LabelC  01/03/10  31/03/10

If I have another date, I would like to look up the label relating to the date range within which this date falls. e.g. For 15/02/10 I would like to return LabelB. I know that the date ranges will not overlap, although there could be a gap between the end date of one, and the start date of the next.

Answer 1

You could do it by creating an array formula, which compares each date range with the required date. If the date is the range, it returns the end date.

Then do a match on on this end date to find the appropriate row.

This can all be done in a single cell as needed.

If the data is in A1:C3, and the required date is in B4 then this formula:

=INDEX(A1:A3,MATCH(SUM(IF(B1:B3<B4,1,0)*IF(C1:C3>=B4,C1:C3,0)),C1:C3,0))

Should do what you want. This needs to be entered as an array formula.

If the date is not covered in any of the ranges it will return #N/A

Answer 2

Can you add a (maybe hidden) duplicate of columnA on the right hand side (or move column A) and are the dates in order?

If so, the VLOOKUP function may be the answer. It looks up a value in the first column, and looks across a number of columns. Here is an example.

I realise that this may be a bit of a nasty way of doing it but it is easy and as long as the data is in order, should give you what you want.

Answer 3

You could do a multiple condition sum using sumproduct. You would use (lets say Date1 is B:B, Date2 is C:C, E1 is the date you want to find)

sumproduct(--(B1:B50<=E1),--(C1:C50>=E1),Row(B1:B50))

This will give you the row number, since this multiple condition sum is only true when E1 is >= B and <= C, and will sum up the row number of all rows where this is true (which you said is at most 1). To then lookup the label you can:

index(A1:A50,sumproduct(--(B1:B50<=E1),--(C1:C50>=E1),Row(B1:B50)))

Also if you think your number might be in one of the Gaps you could do:

if(sumproduct(--(B1:B50<=E1),--(C1:C50>=E1),Row(B1:B50))=0,"Date Not Found",index(A1:A50,sumproduct(--(B1:B50<=E1),--(C1:C50>=E1),Row(B1:B50))))

Answer 4

To me this sounds exactly what Conditional Formatting was created for. You can set up to 3 distinct conditions for a cell or range of cells and when one of the conditions matches the cell gets the formatting applied by that conditions.

It's how a lot of the "traffic light" type spreadsheets are made.