EXCEL 2003 and VLOOKUP help in calculating percentage in third cell
Home > IT Answers > Microsoft Windows > EXCEL 2003 and VLOOKUP help in calculating pe...
Ianmax49
65 pts.
0
Q:
EXCEL 2003 and VLOOKUP help in calculating percentage in third cell
VLOOKUP, excel, Excel 2003, VLOOKUP function, Percentage
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 dependant 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: Aug 8 2009  3:31 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
MarkW
15 pts.
0
A:
 RATE THIS ANSWER
+1
Click to Vote:
  •   1
  •  0
  • AddThis Social Bookmark Button
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).
Last Answered: Aug 8 2009  12:53 AM GMT by MarkW   15 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Ianmax49   65 pts.  |   Aug 9 2009  11:05PM 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

 

Dgrainge   70 pts.  |   Aug 12 2009  2:13PM 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

 
0