IF logic only works 7 times

C

ChrisPrather

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"
 
B

Biff

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" <[email protected]>
wrote in message
news:[email protected]...
 
C

ChrisPrather

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
 
C

ChrisPrather

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
 
B

Biff

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?
Click to expand...

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
 
Top