IF & VLOOKUP on time value

[salianne]

On the following sheet. I have times formatted in hh:mm in column F. I want an IF and VLOOKUP function that states if the Paid Hrs (Column F) is <8, lookup the Dept Mo (Column D) and get the figure from Basic Rate (Colum M) and Multiply by Paid Hrs Mo; but if Paid Hrs Mo is more than 8 then lookup Dept Mo and get figure from OT rate column (Column N) multiply by the Paid Hrs Mo. Hope this makes sense. Am missing one part of my formula each time I do it. Any help appreciated.

 

[etaf]

where do you want the answer put

=if( f3 <8 , index( $M$3:$M$21, match ( d3, $L$3:$L$2, 0 )) , iF( f3 > 8 , index( $N$3:$N$21, match ( d3, $L$3:$L$2, 0 )) , "paid hours = 8" ))

=IF(F3<8,INDEX($M$3:$M$21,MATCH(D3,$L$2:$L$3,0)),IF(F3>8,INDEX($N$3:$N$21,MATCH(D3,$L$2:$L$3,0)),"paid hours = 8"))

what to do if paid hrs = 8

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

 

[salianne]

where do you want the answer put

=if( f3 <8 , index( $M$3:$M$21, match ( d3, $L$3:$L$2, 0 )) , iF( f3 > 8 , index( $N$3:$N$21, match ( d3, $L$3:$L$2, 0 )) , "paid hours = 8" ))

=IF(F3<8,INDEX($M$3:$M$21,MATCH(D3,$L$2:$L$3,0)),IF(F3>8,INDEX($N$3:$N$21,MATCH(D3,$L$2:$L$3,0)),"paid hours = 8"))

what to do if paid hrs = 8

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Thanks for your very detailed response. I want to multiply the Paid Hours by the the Basic Rate if the hours are <8(we can include the = here as well). If the hours are above 8, I want to multiply the OT Rate (Both based on the department). Does that make sense?

 

[etaf]

=IF(F3<=8,INDEX($M$3:$M$21,MATCH(D3,$L$2:$L$3,0))*F3,IF(F3>8,INDEX($N$3:$N$21,MATCH(D3,$L$2:$L$3,0))*F3,"paid hours = 8"))

added the =
that returns the rate Basic Rate or OT rate - then i have added *F3
does that work now

so we can simplify now to
=IF(F3<=8,INDEX($M$3:$M$21,MATCH(D3,$L$2:$L$3,0))*F3,INDEX($N$3:$N$21,MATCH(D3,$L$2:$L$3,0))*F3)
as if its NOT <=8 - then assume its more

you could add ignore if employee blank
=IF(A3="","",IF(F3<=8,INDEX($M$3:$M$21,MATCH(D3,$L$2:$L$3,0))*F3,INDEX($N$3:$N$21,MATCH(D3,$L$2:$L$3,0))*F3))

 

[salianne]

=IF(F3<=8,INDEX($M$3:$M$21,MATCH(D3,$L$2:$L$3,0))*F3,IF(F3>8,INDEX($N$3:$N$21,MATCH(D3,$L$2:$L$3,0))*F3,"paid hours = 8"))

added the =
that returns the rate Basic Rate or OT rate - then i have added *F3
does that work now

so we can simplify now to
=IF(F3<=8,INDEX($M$3:$M$21,MATCH(D3,$L$2:$L$3,0))*F3,INDEX($N$3:$N$21,MATCH(D3,$L$2:$L$3,0))*F3)
as if its NOT <=8 - then assume its more

you could add ignore if employee blank
=IF(A3="","",IF(F3<=8,INDEX($M$3:$M$21,MATCH(D3,$L$2:$L$3,0))*F3,INDEX($N$3:$N$21,MATCH(D3,$L$2:$L$3,0))*F3))

Column F is formatted as time (hh:mm)

 

[etaf]

so multiply the time by 24

(F3*24)

But the table - of rates - is that also HH:MM as well -
if so what does that mean

 

[salianne]

so multiply the time by 24

(F3*24)

But the table - of rates - is that also HH:MM as well -
if so what does that mean

The rates table is monetary.

 

[etaf]

=IF(A3="","",IF((F3*24)<=8,INDEX($M$3:$M$21,MATCH(D3,$L$3:$L$21,0))*(F3*24),INDEX($N$3:$N$21,MATCH(D3,$L$3:$L$21,0))*(F3*24)))

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3	fr			dep1		06:30:00	65					Dep1	10	100
4	fr			dep3		10:00:00	3000					Dep2	20	200
5												Dep3	30	300
6
Sheet1

Cell Formulas
Range	Formula
G3:G5	G3	=IF(A3="","",IF((F3*24)<=8,INDEX($M$3:$M$21,MATCH(D3,$L$3:$L$21,0))*(F3*24),INDEX($N$3:$N$21,MATCH(D3,$L$3:$L$21,0))*(F3*24)))

 

[salianne]

Thank you - that works perfectly.

 

[etaf]

you are welcome