If, Lookup

[Guest]

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

 

[Bernard Liengme]

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

 

[Pete_UK]

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