Address Range

[pkaraffa]

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

 

[T. Valko]

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?

 

[pkaraffa]

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







- 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.

 

[T. Valko]

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)

 

[pkaraffa]

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.

 

[T. Valko]

Biff,

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

You're welcome. Thanks for the feedback!