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
<tbody>
</tbody>
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
<tbody>
</tbody>
I hope this is easy for you to solve, thank you for your help.
regards, Pete
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: