Lookup in a Range

[Eskypades]

I am working with a file (“Address”) that lists addresses by house numbers and street names. I need to compare this information to another sheet (“Route) that has lower and upper ranges of addresses along with the route number that this range is assigned to. For example, in the Address file, I might have the following:


<table class="MsoTableGrid" style="border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt; mso-yfti-tbllook:1184;mso-padding-alt:0in 5.4pt 0in 5.4pt" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes"> <td style="width:81.9pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="109"> Column A
</td> <td style="width:85.5pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="114"> Column B
</td> </tr> <tr style="mso-yfti-irow:1"> <td style="width:81.9pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="109"> House #
</td> <td style="width:85.5pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="114"> Street Name
</td> </tr> <tr style="mso-yfti-irow:2;mso-yfti-lastrow:yes"> <td style="width:81.9pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="109"> 1234
</td> <td style="width:85.5pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="114"> Main St
</td> </tr> </tbody></table>
The Route file would have the following setup:
<table class="MsoTableGrid" style="border-collapse:collapse;border:none;mso-border-alt:solid windowtext .5pt; mso-yfti-tbllook:1184;mso-padding-alt:0in 5.4pt 0in 5.4pt" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes"> <td style="width:119.7pt;border:solid windowtext 1.0pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> Column A
</td> <td style="width:119.7pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> Column B
</td> <td style="width:119.7pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> Column C
</td> <td style="width:119.7pt;border:solid windowtext 1.0pt; border-left:none;mso-border-left-alt:solid windowtext .5pt;mso-border-alt: solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> Column D
</td> </tr> <tr style="mso-yfti-irow:1"> <td style="width:119.7pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> Lower house range
</td> <td style="width:119.7pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> Upper house range
</td> <td style="width:119.7pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> Street Name
</td> <td style="width:119.7pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> Route
</td> </tr> <tr style="mso-yfti-irow:2"> <td style="width:119.7pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> 100
</td> <td style="width:119.7pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> 999
</td> <td style="width:119.7pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> Main St
</td> <td style="width:119.7pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> 012302
</td> </tr> <tr style="mso-yfti-irow:3;mso-yfti-lastrow:yes"> <td style="width:119.7pt;border:solid windowtext 1.0pt; border-top:none;mso-border-top-alt:solid windowtext .5pt;mso-border-alt:solid windowtext .5pt; padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> 1000
</td> <td style="width:119.7pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> 1599
</td> <td style="width:119.7pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> Main St
</td> <td style="width:119.7pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; mso-border-top-alt:solid windowtext .5pt;mso-border-left-alt:solid windowtext .5pt; mso-border-alt:solid windowtext .5pt;padding:0in 5.4pt 0in 5.4pt" valign="top" width="160"> 012304
</td> </tr> </tbody></table>
The challenge I’m having is being able to look at the house number in the Address file as well as the Street Name and then return the corresponding Route number that it falls into. House number 1234 Main St falls into the range for Route 012304. However, an address with the house number of 543 would fall into the range of Route 012302. I’m not sure how to go about formulating this. If it were a matter of using only the Street name, this would be very simple. However, because a street name can sometimes be split across multiple routes, this is where I need help in figuring out how to handle it.



As always, any and all help is greatly appreciated.

 

[sahild05]

how about a custom function that checks if your house is within the range of the lower and upper house numbers AND the street name is the same.

basically just a 3-condition function that returns the route number when true.

 

[Eskypades]

how about a custom function that checks if your house is within the range of the lower and upper house numbers AND the street name is the same.

basically just a 3-condition function that returns the route number when true.

How would that work with looking up a single cell across multiple rows? It may be that I've been staring at these files now I'm looking right past a simple solution.

 

[jim may]

Maybe,,,

Excel Workbook
	A	B	C
1	House #	Street Name	Route
2	1234	Main St	12304
3	150	Main St	12302
Address

Excel 2007

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(--(Route!$A$2:$A$3),--(Route!$B$2:$B$3>Address!A2),Route!$D$2:$D$3)
C3	=SUMPRODUCT(--(Route!$A$2:$A$3),--(Route!$B$2:$B$3>Address!A3),Route!$D$2:$D$3)

Excel Workbook
	A	B	C	D
1	Lower house range	Upper house range	Street Name	Route
2	100	999	Main St	12302
3	1000	1599	Main St	12304
Route

Excel 2007

 

[Eskypades]

Hi Jim,

Thanks for the help. I'll continue testing, but so far that seems to be doing the trick. The only thing I needed to add was the lookup for Column B (the street name) and match it to Column C in the Route file.

And small world, I'm in Roanoke, VA as well!

 

[jim may]

Just add the Street Name in as follows..

=SUMPRODUCT(--(Route!$A$2:$A$3<Address!A2),--(Route!$B$2:$B$3>Address!A2),--(Route!$C$2:$C$3 = $B2),Route!$D$2:$D$3)

 

[Eskypades]

Thanks Jim. I meant to say that I had already added the Street lookup. I've also added a City part to the lookup as well since there are several streets that are the same in different cities (such as Main St). But so far, your formula is working beautifully.

I've seen the SUMPRODUCT formula used elsewhere with the (--), but I've never been able to figure out how the (--) affects the formula. How does that work?

Thanks again!

 

[T. Valko]

Thanks Jim. I meant to say that I had already added the Street lookup. I've also added a City part to the lookup as well since there are several streets that are the same in different cities (such as Main St). But so far, your formula is working beautifully.

I've seen the SUMPRODUCT formula used elsewhere with the (--), but I've never been able to figure out how the (--) affects the formula. How does that work?

Thanks again!

See this...

http://xldynamic.com/source/xld.SUMPRODUCT.html

 

[Eskypades]

Thank you very much for the link. Learned a new term today - "double unary."

 

[T. Valko]

Thank you very much for the link. Learned a new term today - "double unary."

Good deal. Thanks for the feedback!