Posted to microsoft.public.excel.worksheet.functions
|
|
Address Range
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.
|