help needed with Vlookup and IF function

[Yongle]

To also check for blanks try

=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"XXX",IF(J9>Agency_C ontract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,1)))


Explanation
The bit inside the OR statement returns a TRUE value if both conditions are met, and FALSE otherwise
To turn that into something we can use we multiply by 1 and TRUE becomes the number 1 and FALSE becomes the number 0

[LearningEXL]

To also check for blanks try

=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"XXX",IF(J9>Agency_C ontract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,1)))


Explanation
The bit inside the OR statement returns a TRUE value if both conditions are met, and FALSE otherwise
To turn that into something we can use we multiply by 1 and TRUE becomes the number 1 and FALSE becomes the number 0

=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)

i need the below code to do what the above code does with regards to looking up who the contract is

=IF(Agency_Contract!X9=0,"",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract!C9:Y10,22,1)))

here it he link to my file

ive highlight the cell im talking about in yellow

https://www.dropbox.com/s/hni5spbtti...orum.xlsm?dl=0

[Yongle]

This should sort the Look up error. My apologies. I was using a named range and forgot to convert it back after testing

=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"XXX",IF(J9>Agency_C ontract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract!C9:Y11,22,1)))

[LearningEXL]

This should sort the Look up error. My apologies. I was using a named range and forgot to convert it back after testing

=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"XXX",IF(J9>Agency_C ontract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract!C9:Y11,22,1)))

its not looking up the name, if i change Nampak1 to nampak2 col I does not change

[Yongle]

in the ....VLOOKUP(D9,Agency_Contract!C9:Y11,22,1)))
Try changing the last 1 to a 0

[LearningEXL]

in the ....VLOOKUP(D9,Agency_Contract!C9:Y11,22,1)))
Try changing the last 1 to a 0

does not work.

are you in the uk btw

[Paul_Hossler]

https://www.dropbox.com/s/hni5spbtti...orum.xlsm?dl=0

PLEASE don't use Dropbox - I don't want to join

Click [Advanced] and use the paperclip icon to attach a file

(BTW - I've always found it a bit finicky so you might have to play around with it a bit)

[Yongle]

It is very sunny here in Cornwall today!

Now - I am puzzled. The Vlookup is as per your original formula in post#01

There are 2 things you can try next
1 Remove the 1 or 0 and the preceding comma (which sets the argument to default)
changing the formula to
...VLOOKUP(D9,Agency_Contract!C9:Y11,22)))

If that changes nothing then we must test the original Vlookup
2 Can you take one step back and verify the VLookup as a standalone function?
and confirm that it works

thanks

[Yongle]

If you cannot make VLookup work :

Attach a workbook that only contains the 2 sheets we need:
- sheet "Agency_Contract" and if its values are dependent on other sheets please paste values over all cells in the VLookUp table (- so that we can see the actual table of values and not a bagfull of errors.)
- sheet where all this work is going to be placed

And as requested by Paul in post#27 click on advanced and the paperclip to attach the file

thanks
Yon

[mperrah]

I pulled his file from DropBox and Deleted the Forms (17 of them)
his file was over 4mbvbae52297_noforms.xlsm
also made all sheets visible.
the "Invoice" sheet is where the formula are.
Good Luck, there is a lot going on

[LearningEXL]

***Solved**** many thanks for all your help i really mean it.

solution here

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

[LearningEXL]

I pulled his file from DropBox and Deleted the Forms (17 of them)
his file was over 4mbvbae52297_noforms.xlsm
also made all sheets visible.
the "Invoice" sheet is where the formula are.
Good Luck, there is a lot going on

Invoice sheet was hidden as there was not work needed on that sheet sir

[Yongle]

@LearningEXL
I am glad that we were able to answer your questions. Can you now click on Thread Tools and mark the thread as solved

thanks
Yon