Macro to find telephone number in range

[trovello]

I have to compare a phone number in a cell and return the "base" value from a 2nd table in the same workbook.
Numbers to compare:
Excel 2010<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(187, 187, 187); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(218, 231, 245);" width="25px"><col><col></colgroup><thead><tr style="background-color: rgb(218, 231, 245); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>AD</th><th>AE</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="">DIDRange</td><td style="">DIDNumber</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="text-align: right;">
</td><td style="">15126007128</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="text-align: right;">
</td><td style="">15126007196</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="text-align: right;">
</td><td style="">15126007140</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="text-align: right;">
</td><td style="">1512600545</td></tr></tbody></table>

dbimport​

To this range and find the "base number" that the "range to - from" columns correspond to:
Excel 2010<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(187, 187, 187); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(218, 231, 245);" width="25px"><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(218, 231, 245); text-align: center; color: rgb(22, 17, 32);"><th>
</th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="">Range</td><td style="text-align: center;">Base Number</td><td style="text-align: center;"># of Lines</td><td style="text-align: center;">Range from</td><td style="text-align: center;">Range to</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="">DID Range 1</td><td style="">15126005401</td><td style="text-align: right;">59</td><td style="">15126005401</td><td style="">15126005459</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="">DID Range 2</td><td style="">15126007201</td><td style="text-align: right;">44</td><td style="">15126007201</td><td style="">15126007244</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">7</td><td style="">DID Range 3</td><td style="">15126007104</td><td style="text-align: right;">84</td><td style="">15126007104</td><td style="">15126007187</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">8</td><td style="">DID Range 4</td><td style="">15126007190</td><td style="text-align: right;">10</td><td style="">15126007190</td><td style="">15126007199</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">9</td><td style="">DID Range 5</td><td style="">15126007189</td><td style="text-align: right;">1</td><td style="">15126007189</td><td style="">15126007189</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">10</td><td style="">DID Range 6</td><td style="">15125270543</td><td style="text-align: right;">1</td><td style="">15125270543</td><td style="">15125270543</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">11</td><td style="">DID Range 7</td><td style="">15126005400</td><td style="text-align: right;">1</td><td style="">15126005400</td><td style="">15126005400</td></tr></tbody></table>

Begin Here​

The end result is to insert the Base number into the "DIDRange" column. So if AE2 above is 15126007128, then the "base number" should be 15126007104 from B7 on the second table. As 15126007128 is within the base number 15126007104 and the last number in it's range 15126007187 from E7.

1st sheet name is dbimport (where number will be placed) and 2nd sheet is Begin Here (where the range is held).

I tried to do this with Excel formulas but couldn't get it done! I am using macros in this workbook to do further processing...so maybe a macro?

Hope someone can understand this and can help!

 

[Misca]

Here's a formula solution:

Excel Workbook
	A	B	C	D	E	F	G	H
4	Range	Base Number	# of Lines	Range from	Range to		DIDRange	DIDNumber
5	DID Range 1	15126005401	59	15126005401	15126005459		DID Range 3	15126007128
6	DID Range 2	15126007201	44	15126007201	15126007244		DID Range 4	15126007196
7	DID Range 3	15126007104	84	15126007104	15126007187		DID Range 3	15126007140
8	DID Range 4	15126007190	10	15126007190	15126007199		#LUKU!	1512600545
9	DID Range 5	15126007189	1	15126007189	15126007189		DID Range 1	15126005401
10	DID Range 6	15125270543	1	15125270543	15125270543		DID Range 6	15125270543
11	DID Range 7	15126005400	1	15126005400	15126005400
Sheet

It would've been much easier if you had your lookup range (= the green range) sorted in ascending order.

Because it is not, I started by finding how many numbers there are that are smaller than equal to the number I'm interested in. The next step is using SMALL to find what is the closest smaller (or equal) match. Once I know the actual lookup value I can use the normal MATCH to find the exact match (= gives me the row number) and by using that in the INDEX-function I'll get the DID Range equal to the number I'm interested in.

The G8 returns an error because none of the numbers in the list are smaller than equal to what I'm looking for. A simple IF or IFERROR can take care of that if needed.

 

[trovello]

Thank you sir, I named my ranges and put the formula in the other worksheet and it works perfectly. Yes, the range could have been sorted in ascending order - I can do that with a macro (or by hand) after customer fills in the form. They almost never sort it that way.

final formula was:
=INDEX(DID_Range,MATCH(SMALL(Range_from,COUNTIF(Range_from,"<="&$AE2)),Range_from,FALSE))

Thanks again!