# Range lookup problem

#### peterinaudo

##### New Member
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:

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

##### Well-known Member
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
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
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

##### Well-known Member
Thank you so much!! Kind regards, Peter R
You are welcome.

Cheers.

Replies
8
Views
518
Replies
1
Views
1K
Replies
3
Views
323
Replies
4
Views
57
Replies
4
Views
295

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