Help with IF statement

[Gordon]

I have two lists of names, in vertical columns. One list is smaller than the
other, but consists of names to be found in the larger list. I want to use
an IF statement against the names in the larger list in order to find out
which names are in the smaller list.

I've tried IF(cell in large list=Range in small list, print cell, if not
print space) and I get a #VALUE result. What am I doiung wrong?

(I've done this years ago but can't remember!)

Thanks

 

[macropod]

Hi Gordon,

Try a COUNTIF statement, eg:
=COUNTIF(A$1:A$20,B1)
where A$1:A$20 is the range being tested and B1 is the name searched for.
Once you've got the formula set up correctly, copy down as far as needed.
Each matched value will return 1 (or more if there are duplicates) and 0
otherwise.

Cheers

 

[Gordon]

Hi Gordon,

Try a COUNTIF statement, eg:
=COUNTIF(A$1:A$20,B1)
where A$1:A$20 is the range being tested and B1 is the name searched for.
Once you've got the formula set up correctly, copy down as far as needed.
Each matched value will return 1 (or more if there are duplicates) and 0
otherwise.

Cheers

Thanks! That works!

(I still need to find out why the IF statement doesn't though - the strange
thing is that when I select the logical test and the Value if true, the
dialog box says that the formula result is correct! If I then hit "OK" I
get the #VALUE answer. Strange)

 

[Max]

... (I still need to find out why the IF statement doesn't though - the strange
thing is that when I select the logical test and the Value if true, the
dialog box says that the formula result is correct! If I then hit "OK" I
get the #VALUE answer. Strange)

One guess is that you probably didn't *array-enter* the IF formula

For example: assuming the shorter list is in say, B1:B3,
and the longer list is in A1:A10

Put in C1: =IF(OR(A1=$B$1:$B$3),A1,"")

*Array-enter* the formula,
i.e. press CTRL + SHIFT + ENTER
[ instead of just pressing ENTER ]

Done correctly, Excel will wrap curly braces around the formula:
{=IF(OR(A1=$B$1:$B$3),A1,"")}

Don't type-in the curly braces { } yourself !

Copy C1 down to C10

Entries in the longer list which match the ones in the shorter list
will be returned in the corresponding row in C1:C10, unmatched
ones will return blanks.