Checking a range with IF
 

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?

Checking a range with IF
 

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?

Checking a range with IF
 

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?

Checking a range with IF
 

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?

Checking a range with IF
 

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