Problem with formulas

  • Thread starter Thread starter Kim
  • Start date Start date
K

Kim

Hi,

I've a problem with below formula. I've check a few time and it should be
correct. Could someone please check and let me know if they know of any error
or a simplier way.

=if(and(vlookup(a2,Sheet3!A:B,2,false)<=InDate,vlookup(a2,Sheet3!A:C,3,false)>=InDate,vlookup(a2,Sheet3!A:D,4,false),if(and(vlookup(a2,Sheet3!A:E,5,false)<=Indate,vlookup(a2,Sheet3!A:F,6,false)>=InDate,vlookup(a2,Sheet3!A:G,7,false),if(and(vlookup(a2,Sheet3!A:H,8,false)<=InDate,vlookup(a2,Sheet3!A:I,9,false)>=InDate,vlookup(a2,Sheet3!A:J,10,false),""))))

Thanks.
Kim
 
It seem you have not added a closing parenthesis for each AND
=if(
and(vlookup(a2,Sheet3!A:B,2,false)<=InDate,vlookup(a2,Sheet3!A:C,3,false)>=InDate),vlookup(a2,Sheet3!A:D,4,false),if(and(vlookup(a2,Sheet3!A:E,5,false)<=Indate,vlookup(a2,Sheet3!A:F,6,false)>=InDate),vlookup(a2,Sheet3!A:G,7,false),if(and(vlookup(a2,Sheet3!A:H,8,false)<=InDate,vlookup(a2,Sheet3!A:I,9,false)>=InDate),vlookup(a2,Sheet3!A:J,10,false),""))))but then again, since the formula is no in caps, you clearly did not copyand paste from the worksheet so this could be a typo only in your email--Bernard V LiengmeMicrosoft Excel MVPhttp://people.stfx.ca/bliengmeremove caps from email"Kim" <[email protected]> wrote in messageHi,>> I've a problem with below formula. I've check a few time and it should be> correct. Could someone please check and let me know if they know of anyerror> or a simplier way.>>=if(and(vlookup(a2,Sheet3!A:B,2,false)<=InDate,vlookup(a2,Sheet3!A:C,3,false)>=InDate,vlookup(a2,Sheet3!A:D,4,false),if(and(vlookup(a2,Sheet3!A:E,5,false)<=Indate,vlookup(a2,Sheet3!A:F,6,false)>=InDate,vlookup(a2,Sheet3!A:G,7,false),if(and(vlookup(a2,Sheet3!A:H,8,false)<=InDate,vlookup(a2,Sheet3!A:I,9,false)>=InDate,vlookup(a2,Sheet3!A:J,10,false),""))))>> Thanks.> Kim
 
Hi Kim

There is nothing inherently wrong with your formula.
In order to make it easier to read, I might have been inclined to set up a
named range
Insert>Name>Define>Name Data >Refers to=Sheet3!A:I

There is no need to have different ranges in your formula, the offset deals
with which column you want to consider.
Also, using 0 rather than false, makes it all easier to read (IMO).

=if(and(
vlookup(a2,data,2,0)<=InDate,
vlookup(a2,data,3,0)>=InDate,
vlookup(a2,data,4,0),
if(and(
vlookup(a2,data,5,0)<=Indate,
vlookup(a2,data,6,0)>=InDate,
vlookup(a2,data,7,0),
if(and(
vlookup(a2,data,8,0)<=InDate,
vlookup(a2,data,9,0)>=InDate,
vlookup(a2,data,10,0),""))))

What is the error you are getting?
Or, is it the result you are getting is not what you expect?
What is the value of Indate? Is it an Excel date, or a text representation
of a date?
Are they all Excel dates in columns A, B, C, E, F, H and I?
 
Hi Roger,

Thanks for your advice. Bernard reply had solve my problem but I'll take
your advice in simplified the formula by using name range.

It's just I forget to clode parenthesis for the AND formula.

Thanks.
Kim
 
How stupid of me.
I simplified the layout of the formula to make viewing easier, and still
failed to notice the missing parentheses.
Fortunately for you, Bernard was wide awake!!!
 
Back
Top