Somekind of lookup?

[Guest]

I have a sheet which is a list of clients addresses, and I wish to categorise
each address as being inside or outside a geographical region that I have
defined.
I have a separate sheet with a list of streets within the region.

A typical address would be "11 Cumberland Road"
An entry in the list for the region would be "Cumberland"

What I want to do is write a function that returns True if any of the
entries in the region list appears within the client's address.

 

[Bernie Deitrick]

Adrian,

Name the list of allowed street names "List", and use this formula

=IF(SUMPRODUCT(ISNUMBER(FIND(List,E2))*1)>=1,"In List","")

Where cell E2 has the address of the client.

Note that it will find Cumberland in Cumberland Road, but will also find Cumberland Street, which
may not be in your area, so be as specific as possible when making up your list.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Great stuff Bernie! Many thanks.

Adrian,

Name the list of allowed street names "List", and use this formula

=IF(SUMPRODUCT(ISNUMBER(FIND(List,E2))*1)>=1,"In List","")

Where cell E2 has the address of the client.

Note that it will find Cumberland in Cumberland Road, but will also find Cumberland Street, which
may not be in your area, so be as specific as possible when making up your list.

HTH,
Bernie
MS Excel MVP