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

[TABLE="width: 311"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Emp #[/TD]
[TD]Beg Date[/TD]
[TD]End Date[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]01/27/11[/TD]
[TD]06/26/11[/TD]
[TD]25.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]06/27/11[/TD]
[TD]09/03/12[/TD]
[TD]26.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]09/04/12[/TD]
[TD]08/15/15[/TD]
[TD]27.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]08/16/15[/TD]
[TD]12/31/99[/TD]
[TD]28.00[/TD]
[/TR]
[TR]
[TD]231[/TD]
[TD]01/31/07[/TD]
[TD]04/01/07[/TD]
[TD]15.00[/TD]
[/TR]
[TR]
[TD]231[/TD]
[TD]04/02/07[/TD]
[TD]03/31/11[/TD]
[TD]17.00[/TD]
[/TR]
[TR]
[TD]231[/TD]
[TD]04/01/11[/TD]
[TD]05/16/11[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD]231[/TD]
[TD]05/17/11[/TD]
[TD]09/03/12[/TD]
[TD]25.00[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]10/31/05[/TD]
[TD]12/18/05[/TD]
[TD]19.00[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]12/19/05[/TD]
[TD]07/16/06[/TD]
[TD]17.00[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]07/17/06[/TD]
[TD]09/17/06[/TD]
[TD]18.00[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]09/18/06[/TD]
[TD]02/04/07[/TD]
[TD]19.00[/TD]
[/TR]
</tbody>[/TABLE]

Hour Report ->

[TABLE="width: 339"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Emp #[/TD]
[TD]Date Worked[/TD]
[TD] Rate?[/TD]
[TD]Hours Worked[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]06/26/11[/TD]
[TD] [/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]09/03/12[/TD]
[TD] [/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]08/15/15[/TD]
[TD] [/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]12/31/99[/TD]
[TD] [/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]231[/TD]
[TD]04/01/07[/TD]
[TD] [/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]231[/TD]
[TD]03/31/11[/TD]
[TD] [/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]231[/TD]
[TD]05/16/11[/TD]
[TD] [/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]231[/TD]
[TD]09/03/12[/TD]
[TD] [/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]12/18/05[/TD]
[TD] [/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]07/16/06[/TD]
[TD] [/TD]
[TD]8.00[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]09/17/06[/TD]
[TD] [/TD]
[TD]10.00[/TD]
[/TR]
[TR]
[TD]321[/TD]
[TD]02/04/07[/TD]
[TD] [/TD]
[TD]5.00[/TD]
[/TR]
</tbody>[/TABLE]

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?

[TABLE="width: 1225"]
<colgroup><col><col><col><col><col span="9"></colgroup><tbody>[TR]
[TD]Emp #[/TD]
[TD]Beg Date 1[/TD]
[TD]End Date 1[/TD]
[TD]Rate 1 [/TD]
[TD]Beg Date 2[/TD]
[TD]End Date 2[/TD]
[TD]Rate 2[/TD]
[TD]Beg Date 3[/TD]
[TD]End Date 3[/TD]
[TD]Rate 3[/TD]
[TD]Beg Date 4[/TD]
[TD]End Date 4[/TD]
[TD]Rate 4[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]01/27/11[/TD]
[TD]06/26/11[/TD]
[TD]25.00[/TD]
[TD]06/27/11[/TD]
[TD]09/03/12[/TD]
[TD]26.00[/TD]
[TD]09/04/12[/TD]
[TD]08/15/15[/TD]
[TD]27.00[/TD]
[TD]08/16/15[/TD]
[TD]12/31/99[/TD]
[TD]27.00[/TD]
[/TR]
</tbody>[/TABLE]

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!