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 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
(PS – I am using Excel 2000, but I don’t expect that to make a difference in this case).