I have been trying to work on this for a while. We have certain
addresses that we assign our sales employees to try their sales on,
example in column A 110-120 Union Square . In column B we have the
name of the sales person associated with the range .For this example
Joe would be associated with this range . If I put an address into
cell D1 (114 Union Square) Is there a way to have an address that
falls into the range 110-120 have Joe Returned in E1?

Thank you
PJ

It could be done if you split the address into 3 cells like this:

A1 = 110
B1 = 120
C1 = Union Square
D1 = salesperson name

Then the lookup would be split into 2 cells like:

F1 = 114
G1 = Union Square

However, addresses come in all "shapes and sizes" and unless they all
followed the above format a generic formula to do this might not work for
all addresses. Still interested?


--
Biff
Microsoft Excel MVP


wrote in message
ups.com...
I have been trying to work on this for a while. We have certain
addresses that we assign our sales employees to try their sales on,
example in column A 110-120 Union Square . In column B we have the
name of the sales person associated with the range .For this example
Joe would be associated with this range . If I put an address into
cell D1 (114 Union Square) Is there a way to have an address that
falls into the range 110-120 have Joe Returned in E1?

Thank you
PJ

On Aug 21, 10:29 pm, "T. Valko" wrote:
It could be done if you split the address into 3 cells like this:

A1 = 110
B1 = 120
C1 = Union Square
D1 = salesperson name

Then the lookup would be split into 2 cells like:

F1 = 114
G1 = Union Square

However, addresses come in all "shapes and sizes" and unless they all
followed the above format a generic formula to do this might not work for
all addresses. Still interested?

--
Biff
Microsoft Excel MVP

wrote in message

ups.com...



I have been trying to work on this for a while. We have certain
addresses that we assign our sales employees to try their sales on,
example in column A 110-120 Union Square . In column B we have the
name of the sales person associated with the range .For this example
Joe would be associated with this range . If I put an address into
cell D1 (114 Union Square) Is there a way to have an address that
falls into the range 110-120 have Joe Returned in E1?

Thank you
PJ- Hide quoted text -

- Show quoted text -

Thanks! The addresses all follow the above format. I was hoping that
there would be one formula to do this instead of splitting into
different columns.

wrote in message
oups.com...
On Aug 21, 10:29 pm, "T. Valko" wrote:
It could be done if you split the address into 3 cells like this:

A1 = 110
B1 = 120
C1 = Union Square
D1 = salesperson name

Then the lookup would be split into 2 cells like:

F1 = 114
G1 = Union Square

However, addresses come in all "shapes and sizes" and unless they all
followed the above format a generic formula to do this might not work for
all addresses. Still interested?

--
Biff
Microsoft Excel MVP

wrote in message

ups.com...



I have been trying to work on this for a while. We have certain
addresses that we assign our sales employees to try their sales on,
example in column A 110-120 Union Square . In column B we have the
name of the sales person associated with the range .For this example
Joe would be associated with this range . If I put an address into
cell D1 (114 Union Square) Is there a way to have an address that
falls into the range 110-120 have Joe Returned in E1?

Thank you
PJ- Hide quoted text -

- Show quoted text -

Thanks! The addresses all follow the above format. I was hoping that
there would be one formula to do this instead of splitting into
different columns.


Trust me on this...it would be *much easier* if you split the address and
the lookup values into multiple cells. But, here's the type of formula you
need to do it *as is*.

Each address *MUST* follow this format:

(number)(dash)(number)(space)(text)

110-120 Union Square

The lookup value *MUST* follow this format:

(number)(space)(text)

114 Union Square

Assume data in the range A5:B9
Lookup value in D5

Entere this array formula** (all on one line)

=INDEX(B5:B9,MATCH(1,(--LEFT(D5,FIND(" ",D5)-1)
=--LEFT(A5:A9,FIND("-",A5:A9)-1))*
(--LEFT(D5,FIND(" ",D5)-1)
<=--MID(A5:A9,FIND("-",A5:A9)+1,
FIND(" ",A5:A9)-1-FIND("-",A5:A9)))*
(ISNUMBER(SEARCH(MID(D5,
FIND(" ",D5)+1,255),A5:A9))),0))

Here's a screencap:

http://img514.imageshack.us/img514/1805/addresspu4.jpg

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

On Aug 22, 3:06 pm, "T. Valko" wrote:
wrote in message

oups.com...





On Aug 21, 10:29 pm, "T. Valko" wrote:
It could be done if you split the address into 3 cells like this:

A1 = 110
B1 = 120
C1 = Union Square
D1 = salesperson name

Then the lookup would be split into 2 cells like:

F1 = 114
G1 = Union Square

However, addresses come in all "shapes and sizes" and unless they all
followed the above format a generic formula to do this might not work for
all addresses. Still interested?

--
Biff
Microsoft Excel MVP

wrote in message

roups.com...

I have been trying to work on this for a while. We have certain
addresses that we assign our sales employees to try their sales on,
example in column A 110-120 Union Square . In column B we have the
name of the sales person associated with the range .For this example
Joe would be associated with this range . If I put an address into
cell D1 (114 Union Square) Is there a way to have an address that
falls into the range 110-120 have Joe Returned in E1?

Thank you
PJ- Hide quoted text -

- Show quoted text -

Thanks! The addresses all follow the above format. I was hoping that
there would be one formula to do this instead of splitting into
different columns.

Trust me on this...it would be *much easier* if you split the address and
the lookup values into multiple cells. But, here's the type of formula you
need to do it *as is*.

Each address *MUST* follow this format:

(number)(dash)(number)(space)(text)

110-120 Union Square

The lookup value *MUST* follow this format:

(number)(space)(text)

114 Union Square

Assume data in the range A5:B9
Lookup value in D5

Entere this array formula** (all on one line)

=INDEX(B5:B9,MATCH(1,(--LEFT(D5,FIND(" ",D5)-1)=--LEFT(A5:A9,FIND("-",A5:A9)-1))*

(--LEFT(D5,FIND(" ",D5)-1)
<=--MID(A5:A9,FIND("-",A5:A9)+1,
FIND(" ",A5:A9)-1-FIND("-",A5:A9)))*
(ISNUMBER(SEARCH(MID(D5,
FIND(" ",D5)+1,255),A5:A9))),0))

Here's a screencap:

http://img514.imageshack.us/img514/1805/addresspu4.jpg

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Biff,

Thank you very much. that is exactly what I need. I appreciate it.

wrote in message
oups.com...
On Aug 22, 3:06 pm, "T. Valko" wrote:
wrote in message

oups.com...





On Aug 21, 10:29 pm, "T. Valko" wrote:
It could be done if you split the address into 3 cells like this:

A1 = 110
B1 = 120
C1 = Union Square
D1 = salesperson name

Then the lookup would be split into 2 cells like:

F1 = 114
G1 = Union Square

However, addresses come in all "shapes and sizes" and unless they all
followed the above format a generic formula to do this might not work
for
all addresses. Still interested?

--
Biff
Microsoft Excel MVP

wrote in message

roups.com...

I have been trying to work on this for a while. We have certain
addresses that we assign our sales employees to try their sales on,
example in column A 110-120 Union Square . In column B we have the
name of the sales person associated with the range .For this example
Joe would be associated with this range . If I put an address into
cell D1 (114 Union Square) Is there a way to have an address that
falls into the range 110-120 have Joe Returned in E1?

Thank you
PJ- Hide quoted text -

- Show quoted text -

Thanks! The addresses all follow the above format. I was hoping that
there would be one formula to do this instead of splitting into
different columns.

Trust me on this...it would be *much easier* if you split the address and
the lookup values into multiple cells. But, here's the type of formula
you
need to do it *as is*.

Each address *MUST* follow this format:

(number)(dash)(number)(space)(text)

110-120 Union Square

The lookup value *MUST* follow this format:

(number)(space)(text)

114 Union Square

Assume data in the range A5:B9
Lookup value in D5

Entere this array formula** (all on one line)

=INDEX(B5:B9,MATCH(1,(--LEFT(D5,FIND("
",D5)-1)=--LEFT(A5:A9,FIND("-",A5:A9)-1))*

(--LEFT(D5,FIND(" ",D5)-1)
<=--MID(A5:A9,FIND("-",A5:A9)+1,
FIND(" ",A5:A9)-1-FIND("-",A5:A9)))*
(ISNUMBER(SEARCH(MID(D5,
FIND(" ",D5)+1,255),A5:A9))),0))

Here's a screencap:

http://img514.imageshack.us/img514/1805/addresspu4.jpg

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP- Hide quoted text -

- Show quoted text -

Biff,

Thank you very much. that is exactly what I need. I appreciate it.


You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP