Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

migrated from Dec 4 '11 at 22:59

This question came from our site for professional and enthusiast programmers.

this is not a programming question – Dmitry Beransky Nov 28 '11 at 5:07
@DmitryBeransky Why not? Expressing a reference with Excel's obscure functions seems interesting enough programming task to me. – alf Nov 28 '11 at 5:21
@DmitryBeransky Checked FAQ; looked at the "Related questions" on the right... Not convinced at all: there are 1.6K questions tagged excel on Superuser, and 10K here. By the way, haskell has only got 4K. – alf Nov 28 '11 at 5:36
@DmitryBeransky -Incorrect on "because writing Excel macros is not programming" (which I find bizarre). Excel questions fall into two categories, VBA (programming which belongs at SO) and Formulae (SU). – brettdj Nov 28 '11 at 9:05
There are a great many textbook Excel questions on Stackoverflow. Excel seems to be widely used at a quite simple level as an output or input for programs, which makes it programming related. In addition, it seems likely that a suitable solution for this problem will include VBA. – Remou Nov 28 '11 at 10:16

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.

share|improve this answer

You must log in to answer this question.