Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Match Multiple Criteria

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Match Multiple Criteria

    I need a little help with a formula that will take the entries made by users and lookup those values on a table and then return the correct pay rate. I took a look at INDEX and VLOOKUP, but I got lost.

    Any help is appreciated.
    Attached Files

  • #2
    Re: Match Multiple Criteria

    So is the $8.00 supposed to be returned as $12.00? The $17.33 looks right - is that to be returned too? In other words it is not clear what is an input and what is to be pulled from the table.
    .

    Comment


    • #3
      Re: Match Multiple Criteria

      I'm sorry..
      The 17.33 is would be output of the formula. since they are in Texas (44) their normal pay would be 13.62, but since they are coded to overtime (1) that puts them at 17.33

      I confused myself for a minute
      Attached Files

      Comment


      • #4
        Re: Match Multiple Criteria

        I know why I got confused. If you notice on the TRANSCODES page, You have a Pay Rate Column. That is the basic pay rates for workers. The other rates to the right are the rates per state after an additive has been added for a labor source. So If you have an employee who's rate is 8.00 per hour that works in Texas straight time, their final rate would be 13.62, if they work overtime their rate would be 12.00 and after the additive it would be 17.33.

        Comment


        • #5
          Re: Match Multiple Criteria

          Yes, I can see that, but I still don't know which are inputs and which are outputs. I THINK you are inputting the 1 and 44. Are you inputting the $13.62? If not, how does one know which double row of the table to go to?
          .

          Comment


          • #6
            Re: Match Multiple Criteria

            The input cells are 8.00, 1, and 44. 8.00 is the flat rate for that employee. They work in Texas and they worked overtime so after figuring in overtime fee plus the additive for Texas the formula would give 17.33. Take a look at my updated sample.

            Sorry for the confusion.
            Attached Files

            Comment


            • #7
              Re: Match Multiple Criteria

              No probs, JMAN. I'm off now, but it is nice and clear for someone to do this for you before I'm back in the morning, I'm sure.
              .

              Comment


              • #8
                Re: Match Multiple Criteria

                Thanks anyway BTC!

                Comment


                • #9
                  Re: Match Multiple Criteria

                  Hi,

                  =INDEX(TRANSCODES!H4:O9,MATCH(DETAIL!I8,TRANSCODES!G4:G9,0)+DETAIL!G8,MATCH(DETAIL!F8,TRANSCODES!H3:O3,0))

                  HTH
                  Kris

                  ExcelFox

                  Comment


                  • #10
                    Re: Match Multiple Criteria

                    Brilliant, Kris! I must get these mastered; I did one once and then forgot how...
                    .

                    Comment


                    • #11
                      Re: Match Multiple Criteria

                      Thanks a lot that is exactly what I wanted. I need to sharpen up on my formula knowledge.

                      Thanks again!

                      JMAN

                      Comment


                      • #12
                        Re: Match Multiple Criteria

                        Can someone explain something for me? I can't see where the formula Kris came up with refrences the overtime (1) or straight time (0) code on the TRANSCODES worksheet (column F). I've stared at that formula for an hour now trying to figure that part out b/c I made a change to my worksheet layout and I can't see it.

                        BTW, it Kris's formula works fine, I just can't see where it references the actual time code.

                        JMAN

                        Comment


                        • #13
                          Re: Match Multiple Criteria

                          Nevermind, I see it now it's in the :

                          +DETAIL!G8

                          sorry for the post.

                          Comment

                          Trending

                          Collapse

                          There are no results that meet this criteria.

                          Working...
                          X