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

freeb1893

Board Regular
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?

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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

Replies
3
Views
656
Replies
5
Views
875
Replies
1
Views
254
Replies
1
Views
277
Replies
3
Views
392

1,220,965
Messages
6,157,122
Members
451,399
Latest member
alchavar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back