Lookup based on Date Range - Multiple Lines per Lookup_Value

[smas_tx]

Hi All,

I'm trying to pull employee rates into an hour report - the hour report is by day, however, the rate table is summarized into date ranges with multiple lines per employee.

I'm hoping there is a way to do this without having to rearrange the rate table format - see below, thanks in advance for your time.

Sample date set

Rate table ->

Emp #	Beg Date	End Date	Rate
123	01/27/11	06/26/11	25.00
123	06/27/11	09/03/12	26.00
123	09/04/12	08/15/15	27.00
123	08/16/15	12/31/99	28.00
231	01/31/07	04/01/07	15.00
231	04/02/07	03/31/11	17.00
231	04/01/11	05/16/11	20.00
231	05/17/11	09/03/12	25.00
321	10/31/05	12/18/05	19.00
321	12/19/05	07/16/06	17.00
321	07/17/06	09/17/06	18.00
321	09/18/06	02/04/07	19.00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Hour Report ->

Emp #	Date Worked	Rate?	Hours Worked
123	06/26/11		5.00
123	09/03/12		10.00
123	08/15/15		10.00
123	12/31/99		10.00
231	04/01/07		10.00
231	03/31/11		10.00
231	05/16/11		8.00
231	09/03/12		10.00
321	12/18/05		10.00
321	07/16/06		8.00
321	09/17/06		10.00
321	02/04/07		5.00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

If there isn't a way to do it with the format as is, what about a slick way to convert the rate table to this format without doing it manually?

Emp #

Beg Date 1

End Date 1

Rate 1

Beg Date 2

End Date 2

Rate 2

Beg Date 3

End Date 3

Rate 3

Beg Date 4

End Date 4

Rate 4

123

01/27/11

06/26/11

25.00

06/27/11

09/03/12

26.00

09/04/12

08/15/15

27.00

08/16/15

12/31/99

27.00

<colgroup><col><col><col><col><col span="9"></colgroup><tbody>
</tbody>

Any input would be greatly appreciated, thanks.

 

[MichaelSchulz]

Thought I had an answer. But there is an error.

 

[MichaelSchulz]

Okay, here it is. You need an index–match formula with nested if-statements entered as an array formula.

=INDEX( Rate Table, MATCH(1, IF( Emp # on Hour Report = Emp # column on Rate Table, IF( Date Worked >= Beg Date column, IF( Date Worked <= End Date column, 1))), 0), COLUMN( Rate column on Rate Table )

To enter the formula as an array formula, press Ctrl + Shift + Enter.

Incidentally, this will reveal an error in the data in the Rate Table—for Emp # 123 with a Beg Date of 8/16/15, the End Date is 99. Unless this is to mean 2099, something is amiss.

 

[smas_tx]

Wow! That is freaking awesome! Saved me a lot of time and introduced me to a whole new level of excel wizardry. Thank you!

And yea, that is actually 9999, our HR dept plugs it in as an open ended date - didn't notice it when I copied the 'end dates' over for the work day in the sample data set.

Anyways, thank you again! Take it easy!