+ Reply to Thread
Results 1 to 4 of 4

VLOOKUP working in payment calculation 9/10 times

  1. #1
    Registered User
    Join Date
    02-25-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    11

    VLOOKUP working in payment calculation 9/10 times

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: VLOOKUP working in payment calculation 9/10 times

    Hi BuffaloSS, bet you wish you lived in Hawaii right now. You're formula didn't specify exact match, so VLOOKUP expected sorted data...which this isn't. Try:
    Please Login or Register  to view this content.
    Last edited by leelnich; 01-05-2018 at 07:15 PM.
    Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
    Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee

  3. #3
    Registered User
    Join Date
    02-25-2011
    Location
    Buffalo, NY
    MS-Off Ver
    Excel 2003
    Posts
    11

    Re: VLOOKUP working in payment calculation 9/10 times

    That was definitely it...THANKS!

    ...but I wonder...why did it work for years 1-9? What made that 10th option any different?

    EDIT: Oh, wait...you mentioned "sorted data"...i missed that. So if i moved the 10yr term up between 1yr and 2 yr in the table, that might have worked as well? Or am I off base there?

    (and year, Hawaii's weather would be welcomed last week when we had subzero wind chills.)
    Last edited by BuffaloSS; 01-12-2018 at 03:28 PM.

  4. #4
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: VLOOKUP working in payment calculation 9/10 times

    Quote Originally Posted by BuffaloSS View Post
    ...you mentioned "sorted data"...if i moved the 10yr term up between 1yr and 2 yr in the table, that might have worked as well?
    Yes, you could just sort the list (though "10yr..." is actually FIRST alphabetically), but with such a small list, I suggest exact match as the more robust solution. It's fast enough, allows a logical rather than alphabetical arrangement of data and (as demonstrated) yields more predictable results.

    If you feel this answers your question, please close out your thread by marking it as SOLVED (Thread Tools ,up top). Thanks! - Lee
    Last edited by leelnich; 01-12-2018 at 11:10 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 03-07-2017, 02:52 AM
  2. Excel Formula, Payment calculation
    By TUTORPACE in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-22-2015, 09:27 PM
  3. Payment Calculation with 90 day deferred first payment
    By MSE7 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-09-2015, 07:56 PM
  4. Working hours calculation with 4 times
    By IKZOUHETNIETWETEN in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-10-2013, 07:32 AM
  5. Lease Payment Calculation
    By mikemack79 in forum Excel General
    Replies: 1
    Last Post: 02-16-2010, 11:05 PM
  6. Calculation Of Last Payment Made
    By kanwal_deep in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-19-2007, 06:43 AM
  7. [SOLVED] payment calculation
    By in forum Excel General
    Replies: 3
    Last Post: 11-17-2005, 01:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1