Using Excel to create a record of my paychecks

Question

Forgive me if this is newbie question, but I am creating a excel document that records the information on my pay stubs. I am trying to calculate the dollar amount for the hours worked during that pay period, all the while using a second table for the rate which has all of my pay increases.

Table #1 has the following fields:

Period Beginning || Period Ending || Hours Worked || Total $ ...........

Table #2:

Start Date || End Date || Pay Rate

Essentially I want to figure out the Total $ using table 2, but I want to make sure that it is choosing the right rate for that time period.

Answer 1

It cannot really be done this way, as periods defined by Period Beginning and Period Ending will not necessarily be the same as the ones defined by Start Date and End Date — which means, you need to settle for something less.

As of the actual lookup, check out this article: the example on the right is a date lookup, exactly what you need. If you change the lookup formula to INDIRECT("G"&SUMPRODUCT(--(E6:E15<=F3)*(F6:F15>=F3),ROW(E6:E15))) and add rates to the column G, you'll have the lookup part done.