lookup a value in date range

[alex73]

Hi everbody,


My question is about looking up a value in a table using date periods. I have two sheets in a workbook: A en B. Sheet A contains the location code for a employee during different periods, for example


Empno DateBegin DateEnd LocationCode

1215 01052009 28022010 1050

1215 01032010 31032010 1275

1215 01042010 31122010 1325


Sheet B contains the same employee but different timeperiods:


Empno DateBegin DateEnd LocationCode

1215 01062009 31122009

1215 01012010 30042010

1215 01052010 31072010

1215 01082010 31122010


Now I need a formule on sheet B in the collumn LocatioCode which looks up the active code for that period.


Can someone help me with this?

Thanks for your help.

Alex

 

[Luke M]

The biggest key here is making sure yout dates are truly dates. If they are, you could use something like this:

=SUMPRODUCT(--(A!$A$1:$A$8=A1),--(A!$B$1:$B$8<=B1),--(A!$C$1:$C$8>=C1),A!$D$1:$D$8)


But then, what happens if your look-up time period covers a range of more than 1 time period from first sheet?

 

[keymaster]

Also refer this.. http://chandoo.org/wp/2010/06/30/range-lookup-excel/