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

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.

-

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

-
Am I right in thinking that to enter it as an array formula, I just need to do Ctrl+Shift+Enter? –  Ruffles Jun 1 '10 at 12:54
Yes that is correct –  JDunkerley Jun 2 '10 at 8:01

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.

-
I am happy to assume the dates are in order, and it's not a problem to have a hidden duplicate of column A. I have got something working by using VLOOKUP(E4,B1:D3,3,TRUE), where B has the start dates, C has the end dates, and D is the hidden copy of the labels. However, this is only actually checking against the start dates. This is probably good enough, but is there a way I could adapt this to validate against both? –  Ruffles May 27 '10 at 15:09
You could wrap that VLOOKUP, in an IF statement that includes a similar VLOOKUP comparing the date to the Column 2 value; something like: =if( (VLOOKUP(E4,B1:D3,2,TRUE)>=E4 , VLOOKUP(E4,B1:D3,3,TRUE) , "Error"). I can't help feeling that there is a nicer way, but I can't think of one at the moment. –  Neal May 27 '10 at 16:26

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))))
``````
-

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.

-
Thanks, but I don't think this would work in my case, as I will actually have a series of dates I am wanting to look up in my ranges - but I had not mentioned this, so there was no way for you to know. –  Ruffles Jun 1 '10 at 15:36