EXCEL 2003 and VLOOKUP help in calculating percentage in third cell

75 pts.
Tags:
Microsoft Excel
Microsoft Excel 2003
Percentage
VLOOKUP function
Hi all, I have three cells in an Excel 2003 worksheet. Cell one is a drop down list containing: 01/07/2009 – 30/06/2010 01/01/2009 – 31/12/2009 Cell 2 is a dollar amount which is manually entered Cell 3 will calculate a dollar percentage of Cell 2 dependent on which date range is chosen in Cell 1 I have created a VLOOKUP table as: COLUMN 1 COLUMN 2 01/07/2009 – 30/06/2010 2.5% 01/01/2009 – 31/12/2009 3.5% A sample result would be: Cell 1 01/07/2009 – 30/06/2010 Cell 2 \$1,000.00 (manually entered) Cell 3 \$25.00 (provided from VLOOKUP table) How can I get this to work correctly? At the moment it will not provide the answer in Cell 3 and only provides a #N/A Hope someone can assist with this!!!

Thanks. We'll let you know when a new response is added.

Hi Ianmax49

You don’t show your code/formula to comment on so the best way seemed to be to re-create your requirement to demonstrate the solution, which I have done as follows:

(sorry I am using the ‘code’ feature, but the columns still get messed up)

```(Col A) (Col B) (Col C)
(Row 1)
(Row 2) Dropdown Input Result

(Row 4) 01/07/2009 – 30/06/2010 1000.00 35.00

(Row 9) Lookup table
(Row 10) 01/07/2009 – 30/06/2010 3.50%
(Row 11) 01/01/2009 – 31/12/2009 2.50%```

The formula in cell C4 would then be “=VLOOKUP(A4,A10:B11,2,FALSE)*B4”.
Both the dropdown and lookup table were cut and pasted from your question, to ensure that the text for both is identical. The ‘FALSE’ parameter in the formula then ensures an exact match is needed to determine a value – which seems appropriate considering the nature of the match. Cells B10 and B11 were formatted as Percentage, although you can just type in the ‘%’ symbol. If you are still getting a problem, then there is possibly a mismatch between the text in the lookup table vs the dropdown – remove any excess spaces, or even better, cut and paste from one to create the other.

Hope this helps

Mark

(PS – I am using Excel 2000, but I don’t expect that to make a difference in this case).

Discuss This Question: 3 Replies

Thanks. We'll let you know when a new response is added.