Retun value from a column based on a lookup value falling between a range

[freeb1893]

I have the following table, 3 columns:

Low End	High End	Priority Level
prospects	$0	0
$1	$499	1
$500	$999	2
$1,000	$1,999	3
$2,000	$2,999	4
$3,000	$4,999	5
$5,000	$9,999	6
$10,000	$14,999	7
$15,000	$29,999	8
$30,000	$99,999	9
$100,000	+	10

<tbody>
</tbody><colgroup><col><col span="2"></colgroup>

Then I have a number of numerical values in another column, such as below:

$37,686.95
$37,590.34
$36,183.91
$33,878.85
$30,264.94
$28,492.51
$27,903.74
$26,777.97
$25,964.11

<tbody>
</tbody><colgroup><col></colgroup>

I want to be able to lookup the values listed just above here, and check and see which ranges they fall into with my first table up top of this post, and return the value in the "Priority Level" column for that range it falls in. Can someone help me put that into a formula?

 

[Trebor76]

Hi there,

You can use this - just change the cell reference from F8 to suit:

=LOOKUP(F8,{0,1,499,1000,2000,3000,5000,10000,15000,30000,100000},{0,1,2,3,4,5,6,7,8,9,10})

HTH

Robert