I'm writing an IF function that checks a column of dates (Sheet1!$A$2:$A$366)
to find the row with a particular date (C3) and do something based on other
values in that row. (The LOOKUP, MATCH, and INDEX functions aren't quite
right for what I need.)

My IF function looks something like this:
IF(Sheet1!$A$2:$A$366=C3,TRUE,FALSE)...For clarity, I'm omitting the actual
TRUE / FALSE results because my testing shows that, for whatever reason, my
conditional only tests TRUE for 1/3/07. If C3 is any other date, this
function returns false, even though every date in 2007 is represented in the
range Sheet1!$A$2:$A$366.

I've never encountered a problem like this, as it seems pretty
straightforward. Any ideas why this conditional is only true for one date in
the range?

Try entering the formula as an array by hitting CTRL+SHIFT+ENTER
--
Brevity is the soul of wit.


"Rob J" wrote:

I'm writing an IF function that checks a column of dates (Sheet1!$A$2:$A$366)
to find the row with a particular date (C3) and do something based on other
values in that row. (The LOOKUP, MATCH, and INDEX functions aren't quite
right for what I need.)

My IF function looks something like this:
IF(Sheet1!$A$2:$A$366=C3,TRUE,FALSE)...For clarity, I'm omitting the actual
TRUE / FALSE results because my testing shows that, for whatever reason, my
conditional only tests TRUE for 1/3/07. If C3 is any other date, this
function returns false, even though every date in 2007 is represented in the
range Sheet1!$A$2:$A$366.

I've never encountered a problem like this, as it seems pretty
straightforward. Any ideas why this conditional is only true for one date in
the range?

Thanks for the reply.

Doing this only seems to have changed the value that it returns TRUE
for...now it only returns true for 1/1/07.

"Dave F" wrote:

Try entering the formula as an array by hitting CTRL+SHIFT+ENTER
--
Brevity is the soul of wit.


"Rob J" wrote:

I'm writing an IF function that checks a column of dates (Sheet1!$A$2:$A$366)
to find the row with a particular date (C3) and do something based on other
values in that row. (The LOOKUP, MATCH, and INDEX functions aren't quite
right for what I need.)

My IF function looks something like this:
IF(Sheet1!$A$2:$A$366=C3,TRUE,FALSE)...For clarity, I'm omitting the actual
TRUE / FALSE results because my testing shows that, for whatever reason, my
conditional only tests TRUE for 1/3/07. If C3 is any other date, this
function returns false, even though every date in 2007 is represented in the
range Sheet1!$A$2:$A$366.

I've never encountered a problem like this, as it seems pretty
straightforward. Any ideas why this conditional is only true for one date in
the range?

So what are you trying to do? It sounds like you want Excel to search for a
value in a column, and, if that value appears, then do a calculation. Is
that correct? If it is, try something like =IF(COUNTIF([range],"[your
criteria]")0,[do your calculation],[do nothing?]

Essentially: "IF the count of your criteria in the given range is greater
than one, THEN do the calculation, ELSE [not sure what your else clause would
be.]"

Does that do what you're trying to do?

Dave
--
Brevity is the soul of wit.


"Rob J" wrote:

Thanks for the reply.

Doing this only seems to have changed the value that it returns TRUE
for...now it only returns true for 1/1/07.

"Dave F" wrote:

Try entering the formula as an array by hitting CTRL+SHIFT+ENTER
--
Brevity is the soul of wit.


"Rob J" wrote:

I'm writing an IF function that checks a column of dates (Sheet1!$A$2:$A$366)
to find the row with a particular date (C3) and do something based on other
values in that row. (The LOOKUP, MATCH, and INDEX functions aren't quite
right for what I need.)

My IF function looks something like this:
IF(Sheet1!$A$2:$A$366=C3,TRUE,FALSE)...For clarity, I'm omitting the actual
TRUE / FALSE results because my testing shows that, for whatever reason, my
conditional only tests TRUE for 1/3/07. If C3 is any other date, this
function returns false, even though every date in 2007 is represented in the
range Sheet1!$A$2:$A$366.

I've never encountered a problem like this, as it seems pretty
straightforward. Any ideas why this conditional is only true for one date in
the range?

If you're trying to find the row, maybe you could use:

=index(sheet1!$a$2:$a$366,c3)+row($a$2)-1

It kind of sounds like you'll want to use =index(match()).

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions03.html (for =index(match()))



Rob J wrote:

I'm writing an IF function that checks a column of dates (Sheet1!$A$2:$A$366)
to find the row with a particular date (C3) and do something based on other
values in that row. (The LOOKUP, MATCH, and INDEX functions aren't quite
right for what I need.)

My IF function looks something like this:
IF(Sheet1!$A$2:$A$366=C3,TRUE,FALSE)...For clarity, I'm omitting the actual
TRUE / FALSE results because my testing shows that, for whatever reason, my
conditional only tests TRUE for 1/3/07. If C3 is any other date, this
function returns false, even though every date in 2007 is represented in the
range Sheet1!$A$2:$A$366.

I've never encountered a problem like this, as it seems pretty
straightforward. Any ideas why this conditional is only true for one date in
the range?

--

Dave Peterson