PDA

View Full Version : [SOLVED] help needed with Vlookup and IF function



LearningEXL
04-14-2015, 10:46 PM
Hi

can anyone help me with this formula please.


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

its just not doing what i want it to do.

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

mperrah
04-14-2015, 11:32 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



if (j9 > agency_contract!x9, vlookup(d9, Agency_contract!c9:y11,22,j9-agency_contract!x9), 0)

not tested I'm away from my pc, but looks like it might be closer

LearningEXL
04-14-2015, 11:47 PM
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


=IF(J10>Agency_Contract!X10,J10-Agency_Contract!X10,"")

which is on Agency_Daily_Input I10

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

LearningEXL
04-14-2015, 11:51 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 beggining



if (j9 > agency_contract!x9, vlookup(d9, Agency_contract!c9:y11,22,j9-agency_contract!x9), 0)

not tested I'm away from my pc, but looks like it might be closer

i tried that but all that does is return the value of Agency_Contract X9 which is either 8 or 0

mperrah
04-14-2015, 11:55 PM
How about posting sample before and after data or upload your workbook
under post reply click go advanced and click the paperclip

LearningEXL
04-14-2015, 11:59 PM
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/hni5spbttin260s/for%20the%20forum.xlsm?dl=0

mperrah
04-14-2015, 11:59 PM
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

mperrah
04-15-2015, 12:04 AM
I'll run some test first thing in the morning.

LearningEXL
04-15-2015, 12:05 AM
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 cant put a range name in (well i dont think i cant) as Agency Contract sheet has an advanced filter on it

Yongle
04-15-2015, 02:17 AM
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


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


13176

LearningEXL
04-15-2015, 02:48 AM
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


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


13176
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

Yongle
04-15-2015, 03:50 AM
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)))

LearningEXL
04-15-2015, 04:00 AM
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.

LearningEXL
04-15-2015, 04:37 AM
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

Yongle
04-15-2015, 04:39 AM
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

Yongle
04-15-2015, 04:42 AM
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?

LearningEXL
04-15-2015, 04:43 AM
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

thank you sooooooooo much sir i really mean it.....thank you

LearningEXL
04-15-2015, 04:47 AM
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?


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
ok another problem now the vlookup part is not working, referencing D9 or D10 or what ever is in Col D

LearningEXL
04-15-2015, 04:54 AM
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.

Aussiebear
04-15-2015, 04:59 AM
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

Yongle
04-15-2015, 05:04 AM
To also check for blanks try

=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"XXX",IF(J9>Agency_Contract!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
04-15-2015, 05:08 AM
To also check for blanks try

=IF(OR(Agency_Contract!X9=0,Agency_Contract!X9="")*1=1,"XXX",IF(J9>Agency_Contract!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/hni5spbttin260s/for%20the%20forum.xlsm?dl=0

Yongle
04-15-2015, 05:17 AM
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_Contract!X9,J9-Agency_Contract!X9,VLOOKUP(D9,Agency_Contract!C9:Y11,22,1)))

LearningEXL
04-15-2015, 05:23 AM
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_Contract!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
04-15-2015, 05:37 AM
in the ....VLOOKUP(D9,Agency_Contract!C9:Y11,22,1)))
Try changing the last 1 to a 0

LearningEXL
04-15-2015, 05:49 AM
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
04-15-2015, 06:28 AM
https://www.dropbox.com/s/hni5spbtti...orum.xlsm?dl=0 (https://www.dropbox.com/s/hni5spbttin260s/for%20the%20forum.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
04-15-2015, 07:08 AM
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
04-15-2015, 09:54 AM
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
04-15-2015, 10:14 AM
I pulled his file from DropBox and Deleted the Forms (17 of them)
his file was over 4mb13181
also made all sheets visible.
the "Invoice" sheet is where the formula are.
Good Luck, there is a lot going on

LearningEXL
04-15-2015, 12:08 PM
***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_Contrac t!$C$9:$Y$10,22,FALSE))))

LearningEXL
04-15-2015, 12:09 PM
I pulled his file from DropBox and Deleted the Forms (17 of them)
his file was over 4mb13181
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
04-15-2015, 12:49 PM
@LearningEXL (http://www.vbaexpress.com/forum/member.php?56336-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