How to lookup a number that falls into a range and return the row numbe of that range by using match function?

[yichuansancun]

Hello -

I have the following tables:

<table border="0" cellpadding="0" cellspacing="0" width="120"><col style="width: 45pt;" span="2" width="60"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl68" style="height: 15pt; width: 45pt;" align="center" height="20" width="60">low</td> <td class="xl68" style="border-left: medium none; width: 45pt;" align="center" width="60">high</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl69" style="height: 15.75pt; border-top: medium none;" align="right" height="21">40,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">40,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">41,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">41,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">42,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">42,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">43,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">43,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">44,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">44,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">45,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">45,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">46,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">46,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">47,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">47,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">48,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">48,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">49,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">49,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">50,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">50,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">51,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">51,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt; border-top: medium none;" align="right" height="20">52,000</td> <td class="xl69" style="border-top: medium none; border-left: medium none;" align="right">52,999</td> </tr> </tbody></table>
and I need to look up a number to see what Low-High range it falls within, then use match to return the row number.

For example
A1 = 41,500
Then match A1 should return row number of 2

What will be the actual formula?

Thanks!

 

[Andrew Poulsom]

Like this?

=MATCH(A1,B2:B14)

where your table is in the range B1:C14.

 

[Jonmo1]

If the table begins in B2 (B2=40,000)

=MATCH(A1,B2:B14,1)

However, it should be understood that Match DOES NOT return Row# per-sey
It returns the position # within the Array.
The array is B2:B14
41,000 is in B3 (which is the 2nd postion of the array B2:B14)
So the match actually returns 2.

Hope that helps.

 

[p45cal]

Excel Workbook
	A	B	C	D	E	F
1	41000	2			low	high
2					40,000	40,999
3					41,000	41,999
4					42,000	42,999
5					43,000	43,999
6					44,000	44,999
7					45,000	45,999
8					46,000	46,999
9					47,000	47,999
10					48,000	48,999
11					49,000	49,999
12					50,000	50,999
13					51,000	51,999
14					52,000	52,999
mySheet

 

[markmzz]

Another formula (array formula - use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">low</td><td style="text-align: center;;">high</td><td style="text-align: right;;"></td><td style="text-align: center;;">Number</td><td style="text-align: center;;">Row</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">40,000</td><td style="text-align: center;;">40,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">41,500</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">41,000</td><td style="text-align: center;;">41,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">52,753</td><td style="text-align: center;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">42,000</td><td style="text-align: center;;">42,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">52,975</td><td style="text-align: center;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">43,000</td><td style="text-align: center;;">43,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">51,835</td><td style="text-align: center;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">44,000</td><td style="text-align: center;;">44,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">52,041</td><td style="text-align: center;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">45,000</td><td style="text-align: center;;">45,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">43,041</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">46,000</td><td style="text-align: center;;">46,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">40,999</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">47,000</td><td style="text-align: center;;">47,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">44,745</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">48,000</td><td style="text-align: center;;">48,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">52,482</td><td style="text-align: center;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">49,000</td><td style="text-align: center;;">49,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">52,621</td><td style="text-align: center;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">50,000</td><td style="text-align: center;;">50,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">45,755</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">51,000</td><td style="text-align: center;;">51,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">51,754</td><td style="text-align: center;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">52,000</td><td style="text-align: center;;">52,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">43,886</td><td style="text-align: center;;">4</td></tr></tbody></table><p style="width:1.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Ale</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">{=MAX(<font color="Blue">(<font color="Red">D2>=A$2:A$14</font>)*ROW(<font color="Red">$1:$13</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[T. Valko]

Another formula (array formula - use Ctrl+Shift+Enter and not only Enter):


{=MAX((D2>=A$2:A$14)*ROW($1:$13))}

Markmzz

That's not very intuitive in addition to not being robust against row insertions.

 

[markmzz]

Another formula:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">low</td><td style="text-align: center;;">high</td><td style="text-align: right;;"></td><td style="text-align: center;;">Number</td><td style="text-align: center;;">Row</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">40,000</td><td style="text-align: center;;">40,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">41,500</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">41,000</td><td style="text-align: center;;">41,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">42,010</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">42,000</td><td style="text-align: center;;">42,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">49,464</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">43,000</td><td style="text-align: center;;">43,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">52,943</td><td style="text-align: center;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">44,000</td><td style="text-align: center;;">44,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">43,585</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">45,000</td><td style="text-align: center;;">45,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">45,791</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">46,000</td><td style="text-align: center;;">46,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">44,798</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">47,000</td><td style="text-align: center;;">47,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">42,366</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">48,000</td><td style="text-align: center;;">48,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">41,865</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">49,000</td><td style="text-align: center;;">49,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">45,169</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">50,000</td><td style="text-align: center;;">50,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">47,453</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">51,000</td><td style="text-align: center;;">51,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">51,155</td><td style="text-align: center;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">52,000</td><td style="text-align: center;;">52,999</td><td style="text-align: right;;"></td><td style="text-align: center;;">47,004</td><td style="text-align: center;;">8</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E2</th><td style="text-align:left">=MATCH(<font color="Blue">LOOKUP(<font color="Red">D2,A$2:A$14</font>),A$2:A$14,0</font>)</td></tr></tbody></table></td></tr></table><br />
Note: this isn't the most smart formula but solve the problem of the user. The most important thing is to solve the user problem.

Markmz

 

[yichuansancun]

Thanks for all the input, the formulas worked, but may I ask by column B is not in the equation? I am trying to understand the logic behind it. I thought you would need both column A and B to see if the number falls within the range.

Thanks again for all the help!

 

[Jonmo1]

We've basically made a very reasonable assumption:
That there are no gaps between your ranges..

Range1 40,000 - 40,999
Range2 41,000 - 41,999

So we're assuming you are dealing with whole numbers only, and there is no number between 40,999 and 41,000 to be considered.

So with the formula
=MATCH(A1,B2:B14,1)
It looks for the closest match (notated by the 1 in the 3rd argument).
With the 1, it means that Match will find the LARGEST # in B2:B14 that is LESS than or EQUAL to A1.
It requires that the data in column B be sorted Ascending, smallest to largest.

That argument can be -1,0 or +1
-1 it looks for smallest number that is greater than or equal to A1 - data must be sorted DEscending
0 it looks for exact match - data does not need to be sorted
+1 it looks for largest number that is less than or equal to A1 - data must be sorted AScending.


Hope that helps.

 

[yichuansancun]

Got it

Thanks!