getting a formula to do a similar job as another

[LearningEX]

i would like the following formula (1) and (2) to work in the same way as formula (3)

formula (3)

=VLOOKUP(Agency_Daily_Input!D9,Agency_Contract!$C$9:$T$10000,IF(AND(WEEKDAY(Agency_Daily_Input!B9)>1,WEEKDAY(Agency_Daily_Input!B9)<7),13,IF(WEEKDAY(Agency_Daily_Input!B9)=7,15,IF(WEEKDAY(Agency_Daily_Input!B9)=1,17)+IF(Agency_Daily_Input!G9>0.708333333333333,1))),FALSE)

the above one return a value based on cell c9 on Agency Daily Input

id like formula (2) to do the same

=IF(Agency_Contract!X9:X100000=0,0,IF(J9>Agency_Contract!X9:X100000,J9-Agency_Contract!X9:X100000,0))

formula (3)

=T9*Agency_Contract!$Y$9:$Y$100000*I9

 

[gaz_chops]

Then simply add them, where it says U2 paste the vlookup (as below), I wouldn't! As I said why do the same lookup multiple times?

=IF(VLOOKUP(D9,Agency_Contract!$C$9:$Y$10,21,FALSE)=0,"",IF(J9>VLOOKUP(D9,Agency_Contract!$C$9:$Y$10,21,FALSE),J9-VLOOKUP(D9,Agency_Contract!$C$9:$Y$10,21,FALSE),VLOOKUP(D9,Agency_Contract!$C$9:$Y$10,21,FALSE)))

 

[LearningEX]

nampak2 should return a blank cell and its not so its not looking up the company name

 

[LearningEX]

Then simply add them, where it says U2 paste the vlookup (as below), I wouldn't! As I said why do the same lookup multiple times?

=IF(VLOOKUP(D9,Agency_Contract!$C$9:$Y$10,21,FALSE)=0,"",IF(J9>VLOOKUP(D9,Agency_Contract!$C$9:$Y$10,21,FALSE),J9-VLOOKUP(D9,Agency_Contract!$C$9:$Y$10,21,FALSE),VLOOKUP(D9,Agency_Contract!$C$9:$Y$10,21,FALSE)))

have you downloaded the workbook?

its not looking up the company in col D (Daily Input) and returning the value in col X (Agency contract) for that company

 

[gaz_chops]

Change the col from 21 to 22, think i changed it by mistake.

 

[LearningEX]

Change the col from 21 to 22, think i changed it by mistake.

lol now its adding the 8 lol

 

[LearningEX]

Change the col from 21 to 22, think i changed it by mistake.

ok ok got it all working thank you very much for everybodies help and yours Gaz thank you

 

[gaz_chops]

What do you mean? It returns Blank, 1.75, Blank on the 3 examples, is that wrong? If so what should it be?

 

[gaz_chops]

Lol you're welcome.

 

[LearningEX]

lol now its adding the 8 lol

Change the col from 21 to 22, think i changed it by mistake.

have you downloaded the workbook?

its not looking up the company in col D (Daily Input) and returning the value in col X (Agency contract) for that company

noticed that if hours worked is less than 8 i get 8 showing in overtime col. lol

 

[gaz_chops]

I don't! Check your formula gain, did you change all the 21's to 22?