Problem with formulas

[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,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

 

[Bernard Liengme]

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

 

[Roger Govier]

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?

 

[Kim]

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

 

[Roger Govier]

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