Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. ## 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.

Excel Video Tutorials / Excel Dashboards Reports

2. Super Moderator
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. ## 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

Excel Video Tutorials / Excel Dashboards Reports

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.

Excel Video Tutorials / Excel Dashboards Reports

5. Super Moderator
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. ## 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.

Excel Video Tutorials / Excel Dashboards Reports

7. Super Moderator
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. ## Re: Match Multiple Criteria

Thanks anyway BTC!

Excel Video Tutorials / Excel Dashboards Reports

9. Super Moderator
Join Date
18th November 2004
Location
God's Own Country
Posts
4,648

## 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. Super Moderator
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

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

#### 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