Embedded Formulas -- PLEASE HELP!

[icount]

Hi Guys!

I have been racking my brain trying to create formulas for the following situation ....

Our company has employees across the nation, of course due to their levels of management/non-management, their pay dates vary, i.e. bi-monthly, bi-weekly, and weekly.

I have a list of former employees and their effective date of unemployment.
I need to figure out a formula(s) that (1st) searches for the pay group and (2nd) then returns the pay period prior to their last day.

I've tried:
1. VLookUp and HLookUp
2. Index and Match

The number of pay periods are not the same and I've created a spreadsheet vertically & horizontally.

Help! I'm stuck!

Thanks in advance!
Veronica
#icount (and I thought #iEXCELed)

[JBeaucaire]

If the goal is lookup the pay date prior to the termination date, then this formula in E2:

=INDEX('Payroll Dates'!$1:$6, MATCH(D2, 'Payroll Dates'!$A$1:$A$6, 0), MATCH(INDEX('Payroll Dates'!$1:$1, MATCH(B2,OFFSET('Payroll Dates'!$1:$1, MATCH(D2, 'Payroll Dates'!$A$1:$A$6,0)-1, , , ), 1)), 'Payroll Dates'!$1:$1, 0))

...copied down.

The formula in F2 will always be "-" because this formula ALWAYS brings back a date prior to B2.

[icount]

Thanks!!!!!!!! You're awesome!