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!!!
Asked: Last updated:
Related Questions
1

Answer Wiki

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

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

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

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

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: