Problem with formulas

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
 
B

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: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
 
R

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?
 
K

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
 
R

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top