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

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

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