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

[davejago]

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.

 

[p45cal]

E18, copied down:

Excel Workbook
	A	B	C	D	E
17	Volume	Price		Volume	Price
18	0	17		0	17
19	50000	19		30	17
20	100000	21		49000	17
21	150000	23		51000	19
22	200000	25		255000	27
23	250000	27		999000	55
24	300000	29		101000	21
25	350000	31		755000	47
26	400000	33		1010000	57
27	450000	35		999999	55
28	500000	37		1000000	57
29	550000	39		1000001	57
30	600000	41
31	650000	43
32	700000	45
33	750000	47
34	800000	49
35	850000	51
36	900000	53
37	950000	55
38	1000000	57
39	1050000	59
40
Sheet

 

[davejago]

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

 

[p45cal]

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
	A	B	C	D	E	F	G
17	Volume
18	0
19	50000
20	100000
21	150000
22	200000		0		17
23	250000		30		17
24	300000		49000		17		Price
25	350000		51000		19		17
26	400000		255000		27		19
27	450000		999000		55		21
28	500000						23
29	550000		755000		47		25
30	600000		1010000		57		27
31	650000						29
32	700000		1000001		57		31
33	750000						33
34	800000						35
35	850000		55	999999			37
36	900000						39
37	950000						41
38	1000000		21				43
39	1050000		101000				45
40							47
41							49
42		1000000					51
43		57					53
44							55
45							57
46							59
Sheet

 

[davejago]

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!

 

[davejago]

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