Assistance with Date Range Formula

[ShannieD]

Trying to create a formula that will select the correct pay date based on a particular date.

Column A1:A26 = the start of each pay period
Column B1:B26 = the end of each pay period
Column C1:C26 = the pay date

Date in column D represents the effective date. For example, cell D1 equals April 12th. I would like the formula to find the pay period range that April 12th falls in and the output will be the actual pay date.

I've tried a number of formula but have not been successful.

 

[AlphaFrog]

If you have Excel 2003 or earlier, you could use the SUMPRODUCT function...
=SUMPRODUCT(--(D1>=A1:A26),--(D1<=B1:B26),C1:C26)

If you have Excel 2007 or later, you could use the SUMIFS function...
=SUMIFS(C:C,A:A,">="&D1,B:B,"<="&D1)

Remember to format the cell with the formula as a Date.
This assumes no pay period date ranges overlap.

 

[ShannieD]

Thanks! I used the first formula and it worked perfectly.