HLOOKUP #N/A only on specific values

[ChabiP]

I have an issue here, I've linked a table on another sheet to calculate NPV Values on a sheet...but, for specific values, it doesn't work (5%, 6%, 7%, 10%, 17%, 21% and 24%)

These are the formulas:

...and this is the source data for the HLOOKUP (Sheet Name PVF2)...it goes up to column AE 

...what's weird is, when entered manually, DiscountFactor2 calculates correctly, but when done as per the formula screenshot, it gives #N/A. DiscountFactor1 & DiscountFactor2 formulas are exactly alike, except for the lookup value. I need to use this sheet to generate values for another set of functions, so trying to limit the amount of user input to the gray blocks only. Same problem occurs when I switch rows/columns on the lookup data and use VLOOKUP.

Please help!!!!!

[#2 · Accepted solution]

@ChabiP Such things happen sometimes. When you notice that a calculated number like you have in K71 (=J71+1%) is not regarded equal to a constant, wrap the calculation in ROUND.

Change K71 to =ROUND(=J71+1%,2) and it will work.

[#3]

@Riny_van_Eekelen you're a Godsend!!!! Thank you! it works brilliantly :)