Excel Query - IF or Lookup?

RedTen

RedTen

RedTen

Associate
Joined
23 Jun 2007
Posts
552
Location
South East
Hello chaps,

I'm attempting to polish my timesheet spreadsheet a little. I work shifts and the rate that I am paid varies depending on that shift.

I would ideally like to enter a start and end time then have the daily rate and hourly rate auto populated.

Is it best to do an IF statement with something like IF Start is 06:00 and Finish is 14:30 then Daily Rate is £ and Hourly Rate is £?

I assume that there is a neat way to use the rate table with a lookup or something similar but this is a bit above my knowledge.

The only problem is that if I work overtime then no value will be found and it will still need to be entered manually. My overtime rate is always the same.

1ETA8va.png


4DezkS9.png


Any thoughts on the best way to approach this?
 
this formula should work

=IFERROR(VLOOKUP(C3,$A$12:$D$21,3,FALSE),"Empty or Non standard start time")

C3 is the first start time cell in the top table, $A$12:$D$21 is the range of the rate table

put this formula into the first blank standard shiftrate cell and copy down once you change the cell locations. It's important to keep the $ signs on the range of the rate table otherwise it won't work

For the hourly rate the formula is exactly the same except it looks at a different column

=IFERROR(VLOOKUP(C3,$A$12:$D$21,4,FALSE),"Empty or Non standard start time")

making the same cell location changes depending on your own sheet


EDIT: as your start times are unique we don't need to look at the finish time as well
 
Last edited:
djferrick said:
this formula should work

=IFERROR(VLOOKUP(C3,$A$12:$D$21,3,FALSE),"Empty or Non standard start time")

C3 is the first start time cell in the top table, $A$12:$D$21 is the range of the rate table

put this formula into the first blank standard shiftrate cell and copy down once you change the cell locations. It's important to keep the $ signs on the range of the rate table otherwise it won't work

For the hourly rate the formula is exactly the same except it looks at a different column

=IFERROR(VLOOKUP(C3,$A$12:$D$21,4,FALSE),"Empty or Non standard start time")

making the same cell location changes depending on your own sheet


EDIT: as your start times are unique we don't need to look at the finish time as well
Click to expand...

Thanks. That's almost where I need to be.

I'm trying to check start and end time as my overtime could be at the beginning or end of my shift so only using start time is unreliable.

I see it can be done with INDEX and MATCH but I'm struggling. :o
 
I have this so far.

=INDEX(Q24:Q33,(MATCH(C31,O24:O33)))

This is returning the daily rate from within Q24:Q33 where start time C31 matches a cell within O24:033.

However I wish to add to that a second match value. Where E24 (end time) matches a value within P24:P33.

So the Start and Finish must match before a value is returned.
 
this will work

{=IFERROR(INDEX($A$12:$D$21,(MATCH(C3&E3,$A$12:$A$21&$B$12:$B$21,0)),3),"")}

Note the curly brackets cannot be entered by hand

copy this forumla into your cell

=IFERROR(INDEX($A$12:$D$21,(MATCH(C3&E3,$A$12:$A$21&$B$12:$B$21,0)),3),"")

don't press enter

press ctrl+shift+enter - this will add the curly bracket ( it's an array formula )

the formula will only return a value if it gets two matches - otherwise the cell will remain blank - the formula is for daily rate - change the the 3 to a 4 for the hourly rate. Make it an array formula first before you copy the formula down in the cells

obviuously again change your table & cell references to your own sheet

IMPORTANT - don't know why but I'm getting spaces in my text when I save the formula on ocuk. Remove the spaces so it looks like the one in the picture below otherwise it won't work
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom