Range lookup problem

[peterinaudo]

Hi, I am having trouble working out this range lookup problem and would really appreciate some help. Still trying to get the hang of Excel.

I am using Excel 2007, my computer runs on Windows Vista.



Excel 2007

	A	B	C	D	E	F
1		Initial Table
2
3	Price Range	Percentage	Price		Percentage	Price
4	$ 0.20				25%	$ 0.56
5	$ 0.40				50%	$ 1.13
6	$ 0.60				75%	$ 1.69
7	$ 0.80				100%	$ 2.25
8	$ 1.00				125%	$ 2.81
9	$ 1.20				150%	$ 3.38
10	$ 1.40
11	$ 1.60
12	$ 1.80
13	$ 2.00
14	$ 2.20
15	$ 2.40
16	$ 2.60
17	$ 2.80
18	$ 3.00
19	$ 3.20
20	$ 3.40

<tbody>
</tbody>

Sheet1​

What I would like to achieve-

1/ Cell C4 requires a function which will look at the value in A4, i.e. either the Range between $0.00 to $0.20 , OR $0.20 to $0.40, then search down the cells F4 to F9 and return the value in that Range only if there is one, otherwise return a blank.
2/ Cell B4 requires a function which will return the corresponding Percentage value of C4 if it is populated with a value, otherwise it is to remain blank.
3/ The function inserted in cell C4 will be copied down to cell C20.


Below is the desired result.


Excel 2007

	A	B	C	D	E	F
1		Desired Result
2
3	Price Range	Percentage	Price		Percentage	Price
4	$ 0.20				25%	$ 0.56
5	$ 0.40				50%	$ 1.13
6	$ 0.60	25%	$ 0.56		75%	$ 1.69
7	$ 0.80				100%	$ 2.25
8	$ 1.00				125%	$ 2.81
9	$ 1.20	50%	$ 1.13		150%	$ 3.38
10	$ 1.40
11	$ 1.60	75%	$ 1.69
12	$ 1.80
13	$ 2.00
14	$ 2.20	100%	$ 2.25
15	$ 2.40
16	$ 2.60
17	$ 2.80	125%	$ 2.81
18	$ 3.00
19	$ 3.20
20	$ 3.40	150%	$ 3.38

<tbody>
</tbody>

Sheet1​

I hope this is easy for you to solve, thank you for your help.
regards, Pete

 

[cyrilbrd]

Hi,
given:

	Desired Result
Price Range	Percentage	Price		Percentage	Price
$0.20				25%	$0.56
$0.40				50%	$1.13
$0.60	25%	$0.56		75%	$1.69
$0.80				100%	$2.25
$1.00				125%	$2.81
$1.20	50%	$1.13		150%	$3.38
$1.40
$1.60
$1.80	75%	$1.69
$2.00
$2.20
$2.40	100%	$2.25
$2.60
$2.80
$3.00	125%	$2.81
$3.20
$3.40	150%	$3.38

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

In Cell B4 =IFERROR(IF(INDEX($F$4:$F$9,MATCH(A4,$F$4:$F$9,1+1))=MEDIAN(INDEX($F$4:$F$9,MATCH(A4,$F$4:$F$9,1+1)),A3:A4),INDEX($E$4:$E$9,MATCH(A4,$F$4:$F$9,1+1)),""),"") copied down till needed
In cell C4 =IFERROR(IF(INDEX($F$4:$F$9,MATCH(A4,$F$4:$F$9,1+1))=MEDIAN(INDEX($F$4:$F$9,MATCH(A4,$F$4:$F$9,1+1)),A3:A4),INDEX($F$4:$F$9,MATCH(A4,$F$4:$F$9,1+1)),""),"")

Note:
Column B formatted as percentage
Column C formatted as currency
Make sure that values in Column A and F are numbers formatted as currency and not a mix of text and integer.

Would that work for you?

 

[Marcol]

If you change your lookup table slightly, i.e. start with 0% $0.00, then you can avoid error handling

Drag B2:C2 Down

Excel Workbook
	A	B	C	D	E	F
3	Price Range	Percentage	Price		Percentage	Price
4	$0.20				0%	$0.00
5	$0.40				25%	$0.56
6	$0.60	25%	$0.56		50%	$1.13
7	$0.80				75%	$1.69
8	$1.00				100%	$2.25
9	$1.20	50%	$1.13		125%	$2.81
10	$1.40				150%	$3.38
11	$1.60	75%	$1.69
12	$1.80
13	$2.00
14	$2.20	100%	$2.25
15	$2.40
16	$2.60
17	$2.80	125%	$2.81
18	$3.00
19	$3.20
20	$3.40	150%	$3.38
Sheet1

 

[peterinaudo]

Hi,
given:

	Desired Result
Price Range	Percentage	Price		Percentage	Price
$0.20				25%	$0.56
$0.40				50%	$1.13
$0.60	25%	$0.56		75%	$1.69
$0.80				100%	$2.25
$1.00				125%	$2.81
$1.20	50%	$1.13		150%	$3.38
$1.40
$1.60
$1.80	75%	$1.69
$2.00
$2.20
$2.40	100%	$2.25
$2.60
$2.80
$3.00	125%	$2.81
$3.20
$3.40	150%	$3.38

<tbody>
</tbody>

In Cell B4 =IFERROR(IF(INDEX($F$4:$F$9,MATCH(A4,$F$4:$F$9,1+1))=MEDIAN(INDEX($F$4:$F$9,MATCH(A4,$F$4:$F$9,1+1)),A3:A4),INDEX($E$4:$E$9,MATCH(A4,$F$4:$F$9,1+1)),""),"") copied down till needed
In cell C4 =IFERROR(IF(INDEX($F$4:$F$9,MATCH(A4,$F$4:$F$9,1+1))=MEDIAN(INDEX($F$4:$F$9,MATCH(A4,$F$4:$F$9,1+1)),A3:A4),INDEX($F$4:$F$9,MATCH(A4,$F$4:$F$9,1+1)),""),"")

Note:
Column B formatted as percentage
Column C formatted as currency
Make sure that values in Column A and F are numbers formatted as currency and not a mix of text and integer.

Would that work for you?

Hi cyrilbrd
thank you for working on this problem for me. Your solution works well for me and I really appreciate your efforts and the amazing speed of your response, I did get a little green error icon in the corner of the cells, but I dont think this is giving me any problem, although I did spend some time on the help screen trying to work it out. I am not very good with excel yet. I did get another response to this problem from Marcol who has thought of an interesting way of averaging the search range, I hadnt imagined showing the data like this so didnt ask for it in the original request, but now that I see it can be done I think it gives a better way to display the data for my purpose. Thank you so much!! Kind regards, Peter R

 

[peterinaudo]

Hi Marcol, I spent quite a bit of time (many many nights) trying to work out how to write a function to do this and also how to separate the range divisions. Your solution using averages is so much better than I could even imagine. Thank you for sharing your brilliance with me, I appreciate your help very much. Kind regards, Peter R

 

[cyrilbrd]

Thank you so much!! Kind regards, Peter R

You are welcome.
Glad you found a solution.

Cheers.