VLOOKUP addresses and their numbers and give them best return value

[tasic89]

Hi,

I have a specific problem, and I need help.

I will be getting a list of customers like this

NAME	SURNAME	PLACE	STREET	STREET NUMBER	DELIVERY POINT
MIKE	JONES	LOS ANGELES	PASADENA	11	LA 1
GEORGE	MATIK	LOS ANGELES	PASADENA	9	LA 2
DERRICK	SHAWN	LAS VEGAS	PASADENA	33	LAS VEGAS
SASHA	FINE	SACRAMENTO	GEORGE	44	SAC 3

I don't want to do it manually to add the "delivery point" to specific adresses.

I want to use some fixed table like this one

PLACE	STREET	STREET NUMBER	DELIVERY POINT
LOS ANGELES	PASADENA	1	LA 1
SACRAMENTO	GEORGE		SAC 3			* PASADENA 1-8 LA 1, PASADENA 9-30 LA 2, PASADENA 31-90 LA 3…
LOS ANGELES	NEW ONE		LA 5
LOS ANGELES	PASADENA	6	LA 1
LOS ANGELES	PASADENA	7	LA 1
LOS ANGELES	PASADENA	8	LA 1
LOS ANGELES	PASADENA	9	LA 2
LOS ANGELES	SHAQ		LA 3
LAS VEGAS	GAMBLE		LAS VEGAS
LAS VEGAS	PASADENA		LAS VEGAS

Thing is that I have several places like in this "fixed" one. And I have some rules. Like in Los Angeles and Las Vegas I have a street with the same names. But Pasadena in LA or LV will have different delivery points. Also I have additional problem in the rules that Pasadena in LA is a very long street, so 1-9 is in one store, while 10-30 is in another one. Because its better that way. In the fixed one, I have some streets without any numbers, just because the whole street is "dedicated" to one delivery point.

Is there any vlookup formulas that will bring me the desired result and that will successfully give me the right delivery point ?

p.s. Variable one is the variable, but the layout of informations is always the same. If there e.g. is some bad typed street it will give me N/A result which is fine, or maybe if I don't have that street inserted in fixed table.

 

[jasonb75]

Looking at the cell to the right of the bottom table, PASADENA 1-8 LA 1, PASADENA 9-30 LA 2, PASADENA 31-90 LA 3 shouldn't the first one in the top table be LA 2 instead of LA 1?

I've made a few changes to the bottom table, hopefully I've followed the requirement correctly. For L.A./ Pasadena the street numbers are the lowest for each delivery point. Street numbers for any single Place / Street combination must be in ascending order, if they are not then you could see incorrect delivery points for some entries.

Book1
	A	B	C	D	E	F
1	NAME	SURNAME	PLACE	STREET	STREET NUMBER	DELIVERY POINT
2	MIKE	JONES	LOS ANGELES	PASADENA	11	LA 2
3	GEORGE	MATIK	LOS ANGELES	PASADENA	9	LA 2
4	DERRICK	SHAWN	LAS VEGAS	PASADENA	33	LAS VEGAS
5	SASHA	FINE	SACRAMENTO	GEORGE	44	SAC 3
6
7	PLACE	STREET	STREET NUMBER	DELIVERY POINT
8	SACRAMENTO	GEORGE		SAC 3
9	LOS ANGELES	NEW ONE		LA 5
10	LOS ANGELES	PASADENA	1	LA 1
11	LOS ANGELES	PASADENA	9	LA 2
12	LOS ANGELES	PASADENA	31	LA 3
13	LOS ANGELES	SHAQ		LA 3
14	LAS VEGAS	GAMBLE		LAS VEGAS
15	LAS VEGAS	PASADENA		LAS VEGAS
Sheet4

Cell Formulas
Range	Formula
F2:F5	F2	=LOOKUP(2,1/($A$8:$A$15=C2)/($B$8:$B$15=D2)/IF(COUNTIFS($A$8:$A$15,C2,$B$8:$B$15,D2,$C$8:$C$15,""),1,$C$8:$C$15<=E2),$D$8:$D$15)

 

[tasic89]

Hi,

Just want to say big thanks.

Your formula works perfectly.

I tested it in this few days, I have over 4000 adresses in fixed list.

I just want to add that I needed to ascend all street numbers (in "fixed" list) and in that variable list.

And also i needed to press ctrl+shift+enter in that formula. And like out of 1030 addresses, he automatically done it for 900. 130 are typos or some adresses that are "older" street names. But that's super okay, especially when I know that 900 are recognized.

 

[jasonb75]

also i needed to press ctrl+shift+enter in that formula.

Sorry, I keep forgetting that. I'm using a newer version of excel that no longer needs Ctrl Shift Enter (with a couple of very rare exceptions on obscure formulas).

Glad you figured it out and got it working as needed