0

I work in a territory based position, and so I need to separate a list based on UK postcodes

I have a list of my own postcodes and need a formula to check each postcode against my list and give me a true or false value, I have tried 2 variations of 'cell contains one of many things' but I end up with false positives and I am not sure why. The second value should be False.

Formula Trouble

Many Thanks!

OJR
  • 93
  • 1
  • 1
  • 8
  • 2
    how about IFERROR(MATCH(D3,$B$3:$B$60,0)>0,FALSE)? – Rosetta Apr 09 '18 at 10:28
  • 1
    If using VBA is an option, a more robust solution would be to implement a [regex-based postcode validation](https://stackoverflow.com/questions/164979/uk-postcode-regex-comprehensive). – jsheeran Apr 09 '18 at 10:30
  • 1
    @Rosetta that appears on first try to work beautifully, thank you! – OJR Apr 09 '18 at 10:39
  • @jsheeran if these lists become a regular occurrence I will probably look to do just that – OJR Apr 09 '18 at 10:41

1 Answers1

2
IFERROR(MATCH(D3,$B$3:$B$60,0)>0,FALSE)

MATCH()>0 will generate a TRUE if a match found, but generate an error if no match is found, so IFERROR() comes in handily to translate the error to FALSE.

Rosetta
  • 2,665
  • 1
  • 13
  • 29