How to make Excel detect the "closest" date to the one I entered and return an value?

EDIT : (Dec 1, 2005)

hi all expert here.. I tried the VLOOKUP and also the OFFSET / MATCH combo that I found on the Net ... .. I still can't do what I want...

ok .. for example.. I am making a loan repayment excel.

col A1..A1000 ... I will have all the date in ascending order
col B1..B1000 .. it will contain my remaining balane ... since I am repaying .. it will be in a descending order...

and the B column changes as my repayment varies..

So.. basically I want to have a function .. that will go to the B column ... .. find out when B column becomes 0 or becomes negative and then return me the corresponding "date" in col A ..

So.. basical I want my excel to tell me when I will be debt-free

and none of the LOOKUP functions will work because
(1) there might not be an exact "0"
(2) the "remaining balance" column B is in descending order while VLOOKUP req. column in ascending order..

So... any expert here able to give me a hand ? ... maybe this is beyond sheet function ..but then how about some VB code ?

Thanks a million

BH



====================================================
(Original Post)

Hi all, I have some excel question and hope all the expert here can help..

last time.. the SUMPRODUCT really helped me a lot and I am still trying to really understand how it works..

anyways.... .. here is my problem...

Col A is a list of date in ascending order, Col B is an numberic value.

Say in cell C1 , I will enter a random date.. and how can I make Excel to search through Col A and find the closest date to what I have entered in C1 and return the corresponding Col B value ?

Well. not really closest... as long as Col A is a date that is less then and yet the closest .


imagine in Col A is the date and Col B is the histoical account balance ...

now.. in C1, I will entered a date and I want Excel to automatically tell me ... as of that date... how much balance I used to have ...


hope I am clear... and TIA for all your help

BH