Lookup between Values no VBA (1 Viewer)

[clive2002]

Hi,

Hope someone can assist with this problem, which i need to solve without using VBA.


I have the below table of events, From - to dates and event description.

From To Event
07/01/2012 09/01/2012 Contract1
11/01/2012 14/01/2012 Contract2

I also have a straight list of days, and i need to look up the events covering each. As demonstrated below.

Date Event
01/01/2012
02/01/2012
03/01/2012
04/01/2012
05/01/2012
06/01/2012
07/01/2012 Contract1
08/01/2012 Contract1
09/01/2012 Contract1
10/01/2012
11/01/2012 Contract2
12/01/2012 Contract2
13/01/2012 Contract2
14/01/2012 Contract2
15/01/2012

Thanks
Clive

 

[Brianwarnock]

Finding the start and ends would be easy with a few nested Vlookups and the use of ISNA, but how you would fill in the middle eludes me.

Brian

 

[NBVC]

Assuming your table of events is in A1:C3 (incl. headers) and you have your dates listed in column F with first date in F2, then in G2 try:

=IF(ISNUMBER(MATCH(1,INDEX(($A$2:$A$3<=F2)*($B$2:$B$3>=F2),0),0)),INDEX($C$2:$C$3,MATCH(1,INDEX(($A$2:$A$3<=F2)*($B$2:$B$3>=F2),0),0)),"")

copied down.

I forgot to mention, that if you are using Excel 2007 or later, you can use IFERROR function to shorten this:

=IFERROR(INDEX($C$2:$C$3,MATCH(1,INDEX(($A$2:$A$3<=F2)*($B$2:$B$3>=F2),0),0)),"")

 

[Brianwarnock]

Finally got a chance to look at this today, I had wondered about Index Match but I would never have come up with that. I can't comment on the 2007 version but I do like the other, I've never seen Index like that and I think that the use of Isnumber in that way is cool, is a 69 year old allowed to say that. Live long and learn that's my motto.

Brian