Lookup values from a table based on two criteria

Hi

I'm new to this forum but not Excel but there's a problem I seem unable to solve. I have put together a small spreadsheet for overtime payments. Depending on the person's pay rate and the time of day, the hourly rate for overtime varies.

I want to use the value in column A and the time in column D to then lookup into the table of rates and put that back in column E. I hope this picture clarifies what I'm after

\1


Thanks in advance of any replies
Alun

use index - is the table of rates on a seperate page?
something like =index(table_of_rates,match($a1,Table_of_rates1,0),match($D1,Table_of_Rates2,0)

where Table_of_rates is named range Table_of_rates1 is the named range's first column and Table_of_rates2 is the named range's header row.
of course D1 would need to be 1.5x or 2x

post the example as spreadsheet

Example Spreadsheet.xlsx

I'm sorry, I didn't realise I could attach a file - it makes it much easier!

Alun

Anyone else have a suggestion on my file?

Hi

How does one know when these rates kick in!

IE: Are they after a Time
Anything over a "X" amount of hours
Sat = 1.5x

Thanks for the reply Kevin

If the cell in column "Left Site" says "20:30" then the 1.5x rate applies and if any cell in column "Arrived on Site" says "20:31" then the 2x rate applies

Hi Alun

Try in F2 and copy down.

Formula:

Please Login or Register  to view this content.