Range lookup problem

peterinaudo

New Member
Joined
Oct 4, 2012
Messages
25
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
 
Last edited:

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi,
given:

Desired Result
Price RangePercentagePricePercentagePrice
$0.20 25% $0.56
$0.40 50% $1.13
$0.6025% $0.5675% $1.69
$0.80 100% $2.25
$1.00 125% $2.81
$1.2050% $1.13150% $3.38
$1.40
$1.60
$1.8075% $1.69
$2.00
$2.20
$2.40100% $2.25
$2.60
$2.80
$3.00125% $2.81
$3.20
$3.40150% $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

Well-known Member
Joined
Mar 1, 2010
Messages
644
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
ABCDEF
3Price RangePercentagePricePercentagePrice
4$0.20  0%$0.00
5$0.4025%$0.56
6$0.6025%$0.5650%$1.13
7$0.8075%$1.69
8$1.00100%$2.25
9$1.2050%$1.13125%$2.81
10$1.40150%$3.38
11$1.6075%$1.69
12$1.80
13$2.00
14$2.20100%$2.25
15$2.40
16$2.60
17$2.80125%$2.81
18$3.00
19$3.20
20$3.40150%$3.38
Sheet1
 

peterinaudo

New Member
Joined
Oct 4, 2012
Messages
25
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

New Member
Joined
Oct 4, 2012
Messages
25
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,918
Messages
5,525,625
Members
409,657
Latest member
19JimRon72

This Week's Hot Topics

Top