Hi
can anyone help me with this formula please.
its just not doing what i want it to do.=VLOOKUP(D9,Agency_Contract!C9:Y11,22,IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,0))
vlookup references based on a D9
IF is what i want it to do
ive even tried it like this but this one just returns the value in J9 but i dont want that to happen
=IF(J10>Agency_Contract!X10,J10-Agency_Contract!X10,VLOOKUP(D10,Agency_Contract!C10:Y12,22))
Last edited by LearningEXL; 04-14-2015 at 11:14 PM.
So to verify what your function reads to me:
look up D9 in range c9 to y11
and display a cell 22 spaces to the right if a match is found
else
display either j9-x9 if j9 is bigger
or 0 if x9 is bigger
if I am on the right track I would put the if at the begining
not tested I'm away from my pc, but looks like it might be closerif (j9 > agency_contract!x9, vlookup(d9, Agency_contract!c9:y11,22,j9-agency_contract!x9), 0)
ok so i thought id try and find where the error lays, and this is what i got so far
if Agency_Contract X10 has an 8 in it then the formula works, however if there is a 0 in the same cell then all it does it enters the figure that in the cell next to where this formula lays
which is on Agency_Daily_Input I10=IF(J10>Agency_Contract!X10,J10-Agency_Contract!X10,"")
so...if J10 has 10.5 (which is number of hours done) in it and Agency_Contract!X10 has an 8 in it then the formula returns 2, which is correct, but.....
if Agency_Contract!X10 has an 0 in it then 10.5 is returned instead of a Blank cell or 0. i hope this has helped in helping me.
as i plan to add a vlookup function to this as well
i tried that but all that does is return the value of Agency_Contract X9 which is either 8 or 0Originally Posted by mperrah
How about posting sample before and after data or upload your workbook
under post reply click go advanced and click the paperclip
https://www.dropbox.com/s/hni5spbtti...orum.xlsm?dl=0
If we know the ranges of the data source and the function to manipulate it and the destination, this can be done in VBA or formulas. Excel has some powerful tools built that can work super fast. But sometimes a little finese is required for more complex manipulating
I'll run some test first thing in the morning.
i cant put a range name in (well i dont think i cant) as Agency Contract sheet has an advanced filter on it
You seem to be chasing this one around a bit
Instead of this - per post#01:
=VLOOKUP(D9,Agency_Contract!C9:Y11,22,IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,0))
Try
=IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,1))
Why?
If you translate the formula into what it is being asked to do
=VLOOKUP(D9,Agency_Contract!C9:Y11,22,IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,0))
tells Excel to
VLookUp.jpg
lookup cell D9[COMMA] in Agency_Contract Range C9:Y11 (ColC is the match)[COMMA] value we want is in column X [COMMA] HERE we expect a TRUE or FALSE argument BUT there is an if statement here
i tried that but it still returns a value from j9 of Agency contract X9 is 0 if X9 is 8 it works fine but not if X9 is 0
The formula would yield that result if J9 is blank
So try
=IF(J9="","XXX",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,1)))
still returns value of J9 which i dont want to happen, J9 shows hours worked, J9 will never be blank at it shows hours worked the problem is if Agency Contract X9 is 0 thats the part of the formula that has the issue. if the cell X9 on Agency contract is 8 everything works great, but if 0 is entered the thats where its going wrong.
if agency Contract X9 has 8 (example) in it the do the calculation which it does,
if the X9 has 0 or is empty then do no calculations and leave the cell that has this formula in blank
So change the condition to reflect that
FROM:
=IF(J9="","XXX",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,1)))
TO:
=IF(Agency_Contract!X9=0,"XXX",IF(J9>Agency_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract,22,1)))
and then alter the "XXX" to whatever you want the cell to show
Our posts just crossed.
The above will work if the cell contains a zero.
But now you have added another condition. You also want it to check if the cell is blank.
If we sort that are we done?
thank you sooooooooo much sir i really mean it.....thank you
ok another problem now the vlookup part is not working, referencing D9 or D10 or what ever is in Col D
if the company changes then it needs to take that into account, so if i change D9 from Nampak1 which has 8 in X9 to Nampak2 which has 0 in it, then this should be picked up and and the formula change accordingly
it needs to look up the company and say oh yes X Blah is paying overtime after 8 or what ever is in Col X.
How about you attach a sample file that we can work on. At the moment we seem to chasing shadows on a dark night. In your sample file give a number of possible scenarios so that we can test any proposal
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Posting Permissions
Reply With Quote
