# DYNAMIC PRICING - Lookup volume. Which predetermined range does it fall in? Price is \$X

#### davejago

##### Board Regular
I have an empty cell which will only ever be populated with a number (may be decimal) between 1-1million. I have a range of prices below which says if the value in the cell above is between 0-50k then price is X, 51-100k then price is Y, 101-150k then price is Z and so on.

How can I set up a formula which takes the value in the cell, identifies in which range the number falls, then pulls the appropriate price?

Seems so easy but I'm pulling my hair out.

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
E18, copied down:
Excel Workbook
ABCDE
17VolumePriceVolumePrice
18017017
1950000193017
20100000214900017
21150000235100019
222000002525500027
232500002799900055
243000002910100021
253500003175500047
2640000033101000057
274500003599999955
2850000037100000057
2955000039100000157
3060000041
3165000043
3270000045
3375000047
3480000049
3585000051
3690000053
3795000055
38100000057
39105000059
40
Sheet

P45cal, THANKS!!! The formula scares me though but it works a treat. Interestingly, I have my volume and price ranges in one area of the sheet, my 'blank user entry cell' in another part, and my formula in another area and the formula returns NA. When I lay out my data in a clean area of the sheet, side by side the formula works. Could you comment on why that's the case?

Thanks again, this is a real life saver for me!
Dave

Shouldn't matter where things are too much. The data in A18:A39 should be sorted ascending and G24:G26 does not need to be sorted (obviously):
Excel Workbook
ABCDEFG
17Volume
180
1950000
20100000
21150000
22200000017
232500003017
243000004900017Price
25350000510001917
264000002550002719
274500009990005521
2850000023
295500007550004725
3060000010100005727
3165000029
3270000010000015731
3375000033
3480000035
358500005599999937
3690000039
3795000041
3810000002143
39105000010100045
4047
4149
42100000051
435753
4455
4557
4659
Sheet

Last edited:
I actually think it MIGHT have been because in my working spreadsheet trial, I neglected to add the volume = 0 row with an associated price!

You clearly are an Excel God. Thank you so very much...
David

Replies
7
Views
475
Replies
0
Views
244
Replies
6
Views
348
Replies
1
Views
193
Replies
0
Views
241

1,206,808
Messages
6,074,985
Members
446,111
Latest member
trduy1908

### 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.

### Which adblocker are you using?

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