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

 

[LearningEX]

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

yes sir, but if hours are under the figure in X which we know at this time to be 8 then 8 is displayed in Col if J is under that figure (8). everything else works great btw

 

[gaz_chops]

So you want to add if J9 < 8 then blank!

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

 

[LearningEX]

So you want to add if J9 < 8 then blank!

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

you my friend are a genius now for my auto fill issue that im having when i add a new shift ty very kindly sir.

 

[gaz_chops]

You're welcome mate.