Lookup a number within a range then return a value

[yichuansancun]

I have the following table:

<table border="0" cellpadding="0" cellspacing="0" width="340"><col style="width: 40pt;" span="2" width="53"> <col style="width: 29pt;" span="6" width="39"> <tbody><tr style="height: 19.5pt;" height="26"> <td colspan="2" class="xl68" style="border-right: 0.5pt solid black; height: 19.5pt; width: 80pt;" align="center" height="26" width="106">Range</td> <td colspan="6" class="xl68" style="border-right: 0.5pt solid black; border-left: medium none; width: 174pt;" align="center" width="234">Family Size</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" align="center" height="20">low</td> <td class="xl71" align="center">high</td> <td class="xl70" style="border-left: medium none;" align="center">1</td> <td class="xl75" align="center">2</td> <td class="xl75" align="center">3</td> <td class="xl75" align="center">4</td> <td class="xl75" align="center">5</td> <td class="xl71" align="center">6</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl72" style="height: 15.75pt;" align="right" height="21">40,000</td> <td class="xl73" align="right">40,999</td> <td class="xl72" style="border-left: medium none;" align="right">1,322</td> <td class="xl76" align="right">1,302</td> <td class="xl76" align="right">1,282</td> <td class="xl76" align="right">1,262</td> <td class="xl76" align="right">1,242</td> <td class="xl73" align="right">1,222</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" align="right" height="20">41,000</td> <td class="xl73" align="right">41,999</td> <td class="xl72" style="border-left: medium none;" align="right">1,380</td> <td class="xl76" align="right">1,360</td> <td class="xl76" align="right">1,340</td> <td class="xl76" align="right">1,320</td> <td class="xl76" align="right">1,300</td> <td class="xl73" align="right">1,280</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" align="right" height="20">42,000</td> <td class="xl73" align="right">42,999</td> <td class="xl72" style="border-left: medium none;" align="right">1,438</td> <td class="xl76" align="right">1,418</td> <td class="xl76" align="right">1,398</td> <td class="xl76" align="right">1,378</td> <td class="xl76" align="right">1,358</td> <td class="xl73" align="right">1,338</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" align="right" height="20">43,000</td> <td class="xl73" align="right">43,999</td> <td class="xl72" style="border-left: medium none;" align="right">1,496</td> <td class="xl76" align="right">1,476</td> <td class="xl76" align="right">1,456</td> <td class="xl76" align="right">1,436</td> <td class="xl76" align="right">1,416</td> <td class="xl73" align="right">1,396</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" align="right" height="20">44,000</td> <td class="xl73" align="right">44,999</td> <td class="xl72" style="border-left: medium none;" align="right">1,554</td> <td class="xl76" align="right">1,534</td> <td class="xl76" align="right">1,514</td> <td class="xl76" align="right">1,494</td> <td class="xl76" align="right">1,474</td> <td class="xl73" align="right">1,454</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" align="right" height="20">45,000</td> <td class="xl73" align="right">45,999</td> <td class="xl72" style="border-left: medium none;" align="right">1,612</td> <td class="xl76" align="right">1,592</td> <td class="xl76" align="right">1,572</td> <td class="xl76" align="right">1,552</td> <td class="xl76" align="right">1,532</td> <td class="xl73" align="right">1,512</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" align="right" height="20">46,000</td> <td class="xl73" align="right">46,999</td> <td class="xl72" style="border-left: medium none;" align="right">1,670</td> <td class="xl76" align="right">1,650</td> <td class="xl76" align="right">1,630</td> <td class="xl76" align="right">1,610</td> <td class="xl76" align="right">1,590</td> <td class="xl73" align="right">1,570</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" align="right" height="20">47,000</td> <td class="xl73" align="right">47,999</td> <td class="xl72" style="border-left: medium none;" align="right">1,728</td> <td class="xl76" align="right">1,708</td> <td class="xl76" align="right">1,688</td> <td class="xl76" align="right">1,668</td> <td class="xl76" align="right">1,648</td> <td class="xl73" align="right">1,628</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl72" style="height: 15pt;" align="right" height="20">48,000</td> <td class="xl73" align="right">48,999</td> <td class="xl72" style="border-left: medium none;" align="right">1,786</td> <td class="xl76" align="right">1,766</td> <td class="xl76" align="right">1,746</td> <td class="xl76" align="right">1,726</td> <td class="xl76" align="right">1,706</td> <td class="xl73" align="right">1,686</td> </tr> </tbody></table>

Say if the base salary is 40,000 and the family size is 5, how can I make excel to return the corresponding number? In this case, the excel should return "1,242".

Tried this but doesn't work:
=IF(AND(K3>=$A$3:$A$146,K3<=$B$3:$B$146),INDEX($C$3:$H$146,,MATCH(L3,$C$2:$H$2)),"n/a")

 

[Yahya]

try this

Excel Workbook
	A	B	C	D	E	F	G	H
2	low	high	1	2	3	4	5	6
3	40,000	40,999	1,322	1,302	1,282	1,262	1,242	1,222
4	41,000	41,999	1,380	1,360	1,340	1,320	1,300	1,280
5	42,000	42,999	1,438	1,418	1,398	1,378	1,358	1,338
6	43,000	43,999	1,496	1,476	1,456	1,436	1,416	1,396
7	44,000	44,999	1,554	1,534	1,514	1,494	1,474	1,454
8	45,000	45,999	1,612	1,592	1,572	1,552	1,532	1,512
9	46,000	46,999	1,670	1,650	1,630	1,610	1,590	1,570
10	47,000	47,999	1,728	1,708	1,688	1,668	1,648	1,628
11
12
13		Income	40,000		1242
14		Family Size	5
Sheet8

Excel 2007

Cell Formulas
Range	Formula
E13	=INDEX(C$3:H$10,MATCH(C13,A3:A10,1),MATCH(C14,C2:H2,0))

 

[yichuansancun]

It doesn't work

<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="xl66" style="height: 15pt; width: 45pt;" height="20" width="60">low</td> <td class="xl66" style="border-left: medium none; width: 45pt;" width="60">high</td> </tr> <tr style="height: 15.75pt;" height="21"> <td class="xl67" style="height: 15.75pt; border-top: medium none;" align="right" height="21">40,000</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">40,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" align="right" height="20">41,000</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">41,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" align="right" height="20">42,000</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">42,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" align="right" height="20">43,000</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">43,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" align="right" height="20">44,000</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">44,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" align="right" height="20">45,000</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">45,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" align="right" height="20">46,000</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">46,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" align="right" height="20">47,000</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">47,999</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; border-top: medium none;" align="right" height="20">48,000</td> <td class="xl67" style="border-top: medium none; border-left: medium none;" align="right">48,999</td> </tr> </tbody></table>
I guess the key step here to be able to look up a value to see if it falls within a range then return the row number .

For example, if the number is 40,555, it should return row #1.

Will match works here?

Thanks!