• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using VLOOKUP to find value to the left

K

kdriggers

New Member
I am lost on this one and have searched and found several solutions to use the VLOOKUP idea to find something to the left, but not one that will work for me (i.e. INDEX + MATCH, VLOOKUP + CHOOSE, and a few others).


Here's the situation: I have a debt amortization table and would like to use a formula to look up the date when the end balance is less than or equal to zero, so that if I change my payment, it will change the payoff date.


In Column A, I have months (Aug 2012 - Mar 2026). I have some other information in Columns B-D. In Column E, I have the End Balance. So, I want to find a value in Column A (the month and year), when the corresponding cell in the same row in Column E is less than or equal to zero.


Any ideas?
 
Good day kdriggers

Have you used the offset function, it can return negative column values.
 
Thanks for the responses.


I looked into the OFFSET function and it doesn't appear to be what I'm looking for. Neither does sorting. If I wanted to find the payoff date, then I could simply scroll down and find where the End Balance is less than or equal to zero. However, what I'm trying to do is put in a formula, so I can see how my payoff date changes if my payment changes.


Hope that makes sense.
 
Thw two parts of the problem are that a) you need to do a lookup on value less than or equal to 0 on a list that is in descending order, and b) you need to return a value from column to the left of searched column.


This should work:

=INDEX(A:A,MATCH(0.01,E:E,-1)+1)


I'm using 0.01 as the find value because the last argument in MATCH lets us find the closest value equal to or greater than search value. Thus, we'll either be right on the 1 cent mark, or the next closest thing. By putting the +1 in the formula, we then look at 1 cell lower. By definition, this will have to be cell less than 1 cent, aka, equal to or less than 0.
 
kdriggers


On it's own offset is limited in it's functionality, but when combined with other functions it has real strength. You may wish to luck at the following link


http://www.myonlinetraininghub.com/excel-offset-function-explained
 
That did it Luke. Thank you. I swear I tried the Index + Match combo, but, obviously, I was doing it incorrectly. Good day.
 
No problem kdriggers. I admit, I had to look at the built-in XL amortization template to see how they were counting number of payments in order to get the idea for the MATCH function. =P
 
You must log in or register to reply here.
Back
Top