I have a VLOOKUP function embedded into a payment calculation that is working for 9 out of 10 drop down options. For some reason, the 10th doesn't work and I cannot figure out why.
In the attached workbook, Closing Worksheet (tab 2), Cell C39 is a drop down TEXT list for the number of years/months someone will be making a loan payment, from 1-10 years. In certain instances the selection of term affects the interest rate (C38), but term obv always has a bearing on the monthly payment calculation in C40. The reference table for numerical months and interest rates is in the third tab.
If you run through the drop down list of potential terms, years 1-10 all successfully yield the appropriate interest rate from the VLOOKUP table. BUT, the related =PMT calculation with the same VLOOKUP only yields an amount for years 1-9. Year 10 gets me #N/A.
The VLOOKUP function should ultimately produce the number 120 from the "10yr/120mo*" option, and if you plug 120 into the formula directly, it gives me the correct payment amount...so why doesn't VLOOKUP yield 120 from within the formula? It works for everything else.
I'm stumped. I thought that maybe there could be a character limit, but that's not it either...I deleted the asterisk to shorten the phrase and that didn't help.
Bookmarks