Compare numbers in to raws

  • Thread starter Thread starter Stig LA
  • Start date Start date
S

Stig LA

I'm using Excel 2003
Can anybody help with programming this:
I have a column of numbers in F20:F33 I would like to check against an other
colums of numbers in A20:A23. All numbers are phone numbers - no parentheses.
If a number in F match a number in A, it should return a "Y" (for yes) in
the adressfield to the right for this number (say G28).
I tried to modify the IF COUNT IF in the thread shown in "Programming" on
the 10.02.2010 without succes.
The above ranges are arbitrary. The real ones are much much longer.

Thank you in advance.
 
To just check if one in F is also in A, formula (for cell G20) would be:
=IF(COUNTIF(A$20:A$33)>0,"YES","NO")
but if there is already some text in G20 and you want to add the "YES/NO" to
that text then something like this:
="original text here like street address" & " Phone: " &
IF(COUNTIF(A$20:A$33)>0,"YES","NO")
 
To explain the formula a bit: The heart of it is the COUNTIF() portion.
COUNTIF() needs 2 things; a list of cells to look through, and an indication
of what to look for in that list.

By writing the address for column A as A$20:A$33, it keeps the "cells to
look through" constantly pointing to the entire list no matter where we move
the formula to. And by writing the "what to look for" part as F2 (notice no
$ in it), it allows the 2 to increment as you fill the formula down the sheet.

So in cell G2, the formula would look like
=IF(COUNTIF(A$20:A$33,F2)>1,"YES","NO")
and after you copy/fill it down to F3, it looks like
=IF(COUNTIF(A$20:A$33,F3)>1,"YES","NO")

Of course you can change the row numbers in column A as required; for that
matter, if your list may get longer, you can even write the formula as
=IF(COUNTIF(A:A,F3)>1,"YES","NO")
and never have to worry about which rows in column A are involved at all.
 
I'm using Excel 2003
Can anybody help with programming this:
I have a column of numbers in F20:F33 I would like to check against an other
colums of numbers in A20:A23. All numbers are phone numbers - no parentheses.
If a number in F match a number in A, it should return a "Y" (for yes) in
the adressfield to the right for this number (say G28).
I tried to modify the IF COUNT IF in the thread shown in "Programming" on
the 10.02.2010 without succes.
The above ranges are arbitrary. The real ones are much much longer.

Thank you in advance.
Click to expand...

Another approach

=IF(ISNUMBER(MATCH(F20,$A$20:$A$33,0)),"yes","no")

adjust ranges accordingly.

regards
 
Back
Top