If, Lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim - 16 Aug 2006 18:09 GMT

Formula #1 = {=(IF(full_date=($B5),"Yes","No"))}

Formula #2 = {=(IF(full_date=($B5+1),"Yes","No"))}

full_date represents a Vertical table of Dates.

Formula searches full_date and sees if it matches $B5 (also a date), returns
"yes" of there is a match and "No" if there is no match.

Formula # works correctly

Formula #2 is in the next cell (next to Formula #1), It performs the same
search function, except it is looking for the day after $B5 (which is $B5+1).
It determines that there is a "true" match, but for some reason the formula
returns a "No", as if there was no match.

Does anyone understand what is going on?

Thanks

Reply to this Message

Bernard Liengme - 16 Aug 2006 19:29 GMT

I cannot get you first array (or second) formula to work, nor do I see why
it should.
Using the evaluate tool I see as the penultimate step =IF(FALSE, FALSE,
FALSE, TRUE.......
and this will return the first Boolean value i.e. False.
There are some non-array options open to you.
This will work =IF(COUNTIF(full_date,B5),"yes","no") as will
=IF(COUNTIF(full_date,B5+1),"yes","no")
Or you can use =IF(SUMPRODUCT(--(full_date=B5)),"yes","no")
and =IF(SUMPRODUCT(--(full_date=B5+1)),"yes","no")
best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

> Formula #1 = {=(IF(full_date=($B5),"Yes","No"))}
>
[quoted text clipped - 18 lines]
>
> Thanks

Reply to this Message

Pete_UK - 16 Aug 2006 19:34 GMT

Here's another one:

=IF(ISNUMBER(MATCH($B5,full_date,0)),"yes","no")

and

=IF(ISNUMBER(MATCH($B5+1,full_date,0)),"yes","no")

Like Bernard, I couldn't get your Formula#1 to work.

Hope this helps.

Pete

> I cannot get you first array (or second) formula to work, nor do I see why
> it should.
[quoted text clipped - 35 lines]
> >
> > Thanks

Reply to this Message