MATCHING A VALUE IN A CELL TO A COLUMN OF VALUES IN ANOTHER SHEET

[carrach]

Hi,
I am haviang a problem with my workbook
My workbook contains several sheets
in the first sheet 'new clients' I have a column containing postcodeswhich
is added to daily
in the last sheet 'postcodes' I have a column containing a set list of
postcodes for certain areas of the country.
I want to check if the postcode entered in 'new clients' is listed in
'postcodes' and if it is put a 'yes' in the same row in 'new clients'.
I have tried lots of different formulas today but none are working.
help please

 

[Mike H]

Hi

=IF(COUNTIF(postcodes!$A$1:$A$20,B1)>0,"Yes","No")

This assumes the postode you have entered is in B1. Drag down as required
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[David Biddulph]

=IF(ISNUMBER(MATCH(A1,postcodes!A:A,0)),"yes","no")

 

[David Biddulph]

But Mike's COUNTIF solution makes more sense. I must have been asleep. :-(