Vlookup based on date amorization calendar

[charcoop14]

Hello friends, Im trying to figure out a best way to get data based on todays date. Here is the situation:

First Tab I have todays date: =today()...TAB 1 CELL A1

Tab 2: I have 3 COLUMNS
Column A: DATE -which has the first of month date(e,g. 03/01/2018, 04/01/2018, 05/01,2018....etc)
Column B: Dollar Amount which is lower each month depending on loan amount to pay off (e.g. for march 1 its $400,000, april 1 its $399,400, etc)

Solution I need

I need a cell on tab 1 which lookups todays date and returns the dollar amount based on todays date within todays month.

So for example....today is 03/10/18 so the cell should pick up march 1 2018 dollar amount which is $400,000

on april 2 when I open the excel document, it will pick up the amount of $399,400 automatically.


Thanks!

 

[JoeMo]

Here's an example. Adjust sheet names and lookup range to fit your situation.
Sheet1

	A	B	C
	Today	Amount

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]3/10/2018[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3/10/2018[/TD]
[TD="align: right"]700[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula A1 =TODAY() B3 =A1 C3 =IFERROR(VLOOKUP(DATE(YEAR(B3),MONTH(B3),1),Sheet2!$A$2:$B$8,2,0),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sheet2

	A	B
#cacaca ; text-align:center; " >1	Date	Amount
#cacaca ; text-align:center; " >2
#cacaca ; text-align:center; " >3
#cacaca ; text-align:center; " >4
#cacaca ; text-align:center; " >5
#cacaca ; text-align:center; " >6
#cacaca ; text-align:center; " >7
#cacaca ; text-align:center; " >8

<tbody>
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]900
[/TD]

[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]800[/TD]

[TD="align: right"]3/1/2018[/TD]
[TD="align: right"]700[/TD]

[TD="align: right"]4/1/2018[/TD]
[TD="align: right"]600[/TD]

[TD="align: right"]5/1/2018[/TD]
[TD="align: right"]500[/TD]

[TD="align: right"]6/1/2018[/TD]
[TD="align: right"]400[/TD]

[TD="align: right"]7/1/2018[/TD]
[TD="align: right"]300[/TD]

</tbody>

EDIT: IGNORE THE LEFT COLUMN

Excel tables to the web >> Excel Jeanie HTML 4