Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: Match Multiple Criteria

  1. #1
    Join Date
    22nd November 2005
    Posts
    894

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    22nd November 2005
    Posts
    894

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    22nd November 2005
    Posts
    894

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    22nd November 2005
    Posts
    894

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    22nd November 2005
    Posts
    894

    Re: Match Multiple Criteria

    Thanks anyway BTC!

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    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

  10. #10
    Join Date
    7th December 2005
    Location
    Hampshire, England
    Posts
    4,898

    Re: Match Multiple Criteria

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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Index & Match Multiple Items/Criteria
    By rkunzel in forum Excel General
    Replies: 2
    Last Post: June 25th, 2008, 04:08
  2. Index & Match Formula: Multiple Row Criteria?
    By peter0200 in forum Excel General
    Replies: 7
    Last Post: June 23rd, 2007, 06:14
  3. Match multiple criteria from different arrays
    By EmmaFairclough in forum Excel General
    Replies: 2
    Last Post: April 19th, 2006, 18:10

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno