Date Range Look Up

[jking1]

Hi All,
I'm putting together a loan amortization schedule and am struggling with finding a suitable date formula.

I'd like to be able to have a reference cell with =TODAY() in, then a secondary cell refencing a column of data. Depending if TODAY date is within a range I'd like it to return the closing balance.

For example:

B3 has =TODAY()

A20 to A33 has a range of dates at month end.

in C16 I'd like the formula to be able to look at todays date, look down column A and if the date is within the range for it to return the closing balance which is in the corresponding cell in column D.

I know Excel helpfully provide a loan amort schedule in a pre-populated template - however id like to understand how this works so i can replicate this in the future if need be.

 

[etaf]

=index(D20:D33,match(today(),A20:A33,0))
But this assumes the date of today() exists in the range A20-A33

But it sounds like its just the month end dates

so if the range only has the last day of a month
=index(D20:D33,match(eomonth(today(),0),A20:A33,0))

otherwise
A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

 

[jking1]

Many thanks - the Index & Match today have worked a treat. Found a work around which now gives me the closing balance of the account as of the last day of the month.

Thanks!

 

[etaf]

you are welcome