EXCEL 2003 and VLOOKUP help in calculating percentage in third cell

Ianmax49

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

Asked: August 8, 2009 3:31 AM Last updated: September 16, 2013 2:51 AM

Answer Wiki

Last updated: September 16, 2013 2:51 AM GMT

Michael Tidmarsh64,400 pts.
History
Contributors
- Ordered by most recent
- Michael Tidmarsh64,400 pts.
- MarkW15 pts.

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

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)
<pre>(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%</pre>
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).

Send me notifications when members answer or reply to this question.

Discuss This Question: 3 Replies

There was an error processing your information. Please try again later.

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

Send me notifications when members answer or reply to this question.

Ianmax49 Aug 9, 2009 11:05 PM GMT

Hi Mark, Thanks for that! worked a treat. I have psoted a further addition to my original question regarding the addtion of a new cell to the formula. any help would be appreciated! Regards Ian

75 pointsBadges:

report
Dgrainge Aug 12, 2009 2:13 PM GMT

A couple of further suggestions: Use insert --> name to give your lookup table of dates and percentages a name. Suppose you call it datelist and it refers to A10:B11 The formula then becomes VLOOKUP(A4,namelist,2,FALSE)*B4 If you add a third (or more) date to your list, then redefine the cells namelist refers to e.g. A10:B14 or whatever. Secondly to guarantee that your drop-downs correspond to the lookup table, I assume you're using data validation, but base your selection on A10:A11, rather than typing in values into the validation list. The dropdown and the vlookup then share the same source data. And if you add to the list, you can change this to A10:A14 or whatever. Derek

70 pointsBadges:

report
SbElectric Mar 22, 2010 2:39 AM GMT

I am looking at an old question/request - this may have been properly answered. Looking now it seems that that the date ranges are not properly set - it is in dd/mm/yyyy format. There is an overlap - is this intended?

2,540 pointsBadges:

report