Hello,

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

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


--
ChrisPrather
------------------------------------------------------------------------
ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743
View this thread: http://www.excelforum.com/showthread...hreadid=573274

IF functions can only be nested 7 times.

Try a lookup table.
--
Brevity is the soul of wit.


"ChrisPrather" wrote:


Hello,

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

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


--
ChrisPrather
------------------------------------------------------------------------
ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743
View this thread: http://www.excelforum.com/showthread...hreadid=573274

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

"ChrisPrather"
wrote in message
news:ChrisPrather.2cr8s7_1155936616.6879@excelforu m-nospam.com...

Hello,

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

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


--
ChrisPrather
------------------------------------------------------------------------
ChrisPrather's Profile:
http://www.excelforum.com/member.php...o&userid=37743
View this thread: http://www.excelforum.com/showthread...hreadid=573274

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
isn't a match ("New To The Report"). I don't know how your function
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 names
in column A. I want to compare each cell in column A from sheet1 to
each cell in column A from sheet2. IF there is any match, I want to
print "Same" or "New To The Report" in Column B to the right of each
cell. I hope that makes more sense.

Sheet1

abcd Same
aecd New To The Report
acfe Same
akjllk Same

Sheet2

abcd
acde
akjllk
acfe


--
ChrisPrather
------------------------------------------------------------------------
ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743
View this thread: http://www.excelforum.com/showthread...hreadid=573274

Ok......

Use this:

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

Biff

"ChrisPrather"
wrote in message
news:ChrisPrather.2crbsi_1155940513.7047@excelforu m-nospam.com...

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
isn't a match ("New To The Report"). I don't know how your function
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 names
in column A. I want to compare each cell in column A from sheet1 to
each cell in column A from sheet2. IF there is any match, I want to
print "Same" or "New To The Report" in Column B to the right of each
cell. I hope that makes more sense.

Sheet1

abcd Same
aecd New To The Report
acfe Same
akjllk Same

Sheet2

abcd
acde
akjllk
acfe


--
ChrisPrather
------------------------------------------------------------------------
ChrisPrather's Profile:
http://www.excelforum.com/member.php...o&userid=37743
View this thread: http://www.excelforum.com/showthread...hreadid=573274

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?


--
ChrisPrather
------------------------------------------------------------------------
ChrisPrather's Profile: http://www.excelforum.com/member.php...o&userid=37743
View this thread: http://www.excelforum.com/showthread...hreadid=573274

"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