IF logic only works 7 times

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ChrisPrather - 18 Aug 2006 22:27 GMT

Hello,

I have the following IF function that works great on the first seve
rows. Starting with row 8, every row until the end of the colum
evalautes to false and outputs the false statement incorrectly on th
sheet. Please help by telling me what I am doing wrong or may no
understand. Thank you in advance.

=IF(A1=Sheet2!A:A,"True","False"

Reply to this Message

Dave F - 18 Aug 2006 22:43 GMT

IF functions can only be nested 7 times.

Try a lookup table.

Signature

Brevity is the soul of wit.

> Hello,
>
[quoted text clipped - 5 lines]
>
> =IF(A1=Sheet2!A:A,"True","False")

Reply to this Message

Biff - 18 Aug 2006 22:46 GMT

Hi!

The way your formula is working is it's testing the same cell on both
sheets:

A1=Sheet2A1
A2= Sheet2A2
A3=Sheet2A3
etc

Is that really what you want to do?

Try this instead:

=ISNUMBER(MATCH(A1,Sheet2!A:A,0))

Biff

> Hello,
>
[quoted text clipped - 5 lines]
>
> =IF(A1=Sheet2!A:A,"True","False")

Reply to this Message

ChrisPrather - 18 Aug 2006 23:31 GMT

The function you provided will work just fine except that I want t
paste some text in a cell if there is a match ("Same") and if ther
isn't a match ("New To The Report"). I don't know how your functio
will accomplish that.

=ISNUMBER(MATCH(A1,Sheet2!A:A,0))

Here is my goal if I wasn't clear earlier. I have a set of text name
in column A. I want to compare each cell in column A from sheet1 t
each cell in column A from sheet2. IF there is any match, I want t
print "Same" or "New To The Report" in Column B to the right of eac
cell. I hope that makes more sense.

Sheet1

abcd Same
aecd New To The Report
acfe Same
akjllk Same

Sheet2

abcd
acde
akjllk
acf

Reply to this Message

Biff - 19 Aug 2006 02:08 GMT

Ok......

Use this:

=IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),"Same","New To The Report")

Biff

> The function you provided will work just fine except that I want to
> paste some text in a cell if there is a match ("Same") and if there
[quoted text clipped - 22 lines]
> akjllk
> acfe

Reply to this Message

ChrisPrather - 19 Aug 2006 14:23 GMT

Excellent work Biff! Can you explain how the function you just gave m
works more than 7 times since it is still an IF worksheet function o
tell me where to go read if you don't have the time

Reply to this Message

Biff - 20 Aug 2006 04:12 GMT

"ChrisPrather" wrote...

> Excellent work Biff! Can you explain how the function you just gave me
> works more than 7 times since it is still an IF worksheet function or
> tell me where to go read if you don't have the time?

Ok.....

=IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),"Same","New To The Report")

The Match function looks for the lookup_value (A1) in the specified
lookup_array (Sheet2!A:A). If a match is found Match returns a number that
is the relative position in the lookup_array of the matched lookup_value.
For example, suppose the lookup_value in A1 is 10. The lookup_array is the
range A5:A10.

A5:A10 = 5;7;3;4;10;1

MATCH(A1,A5:A10,0) will return 5 because the lookup_value 10 is a match and
is in the 5th position relative to the lookup_array. If the lookup_value was
7 then Match would return 2 because 7 is in the 2nd position relative to the
lookup_array. If no match is found then #N/A is returned.

The result of the Match function is then passed to the Isnumber function.
The Isnumber function evaluates this result and returns a logical value,
either TRUE or FALSE. TRUE = it is a number. FALSE = it is not a number. So,
any number evaluates to TRUE and #N/A evaluates to FALSE.

This logical value is then passed to the IF function. If TRUE returns
"Same", If FALSE returns "New To The Report".

Biff

Reply to this Message