| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Somekind of lookup?
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Somekind of lookup?
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 "Adrian" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Somekind of lookup?
Great stuff Bernie! Many thanks.
"Bernie Deitrick" wrote: 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 "Adrian" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
| LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
| Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Lookup Ques - finding value within a string to lookup | Excel Programming | |||
Linear Mode
