DGET fxn with nested TODAY fxn error

[NewGuy44]

Hello everyone.

I have a specific goal I am trying to achieve and I'm hoping you can help me.

My Goal: I am using a basic loan amortization schedule spreadsheet to tract a loan. I want to add a dynamic cell that displays the current balance of the loan based on the current month.

My incorrect solution: I have recently discovered the DGET function and am able to achieve my goal if I simply type "=A3" into cell E2 . However, once I add the TODAY fxn to make it dynamic, it gives an #VALUE error.

If there is a simpler way to achieve the correct result, please let me know.

Amort Schedule				Date	Balance
Date	Balance			TODAY Fxn	DGET Fxn
Jul-18	5000
Aug-18	4900
Sep-18	4800
Oct-18	4700
Nov-18	4600

<tbody>
</tbody>

Any help would be appreciated.

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F
1	Amort Schedule				Date	Balance
2	Date	Balance			7/14/2018	5000
3	Jul-18	5000
4	Aug-18	4900
5	Sep-18	4800
6	Oct-18	4700
7	Nov-18	4600
Sheet4

Cell Formulas
Range	Formula
E2	=TODAY()
F2	=VLOOKUP(E2,$A$3:$B$7,2,1)

(dates are 1st of month)

 

[NewGuy44]

This?

Excel 2010

	A	B	C	D	E	F
1	Amort Schedule				Date	Balance
2	Date	Balance			7/14/2018	5000
3	Jul-18	5000
4	Aug-18	4900
5	Sep-18	4800
6	Oct-18	4700
7	Nov-18	4600

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula E2 =TODAY() F2 =VLOOKUP(E2,$A$3:$B$7,2,1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

(dates are 1st of month)

Unfortunately, this gives me a #REF . Also, I just realized that the dates in A3:A7 are nested IF functions that give the date as the output using "DAY(fpdate)". Not sure if that changes things.

Thank you for the help so far!

 

[sheetspread]

How do the dates appear in the formula window when you paste as a value?

 

[Aladin Akyurek]

Amort Schedule				Date	Balance
Date	Balance			7/1/2018	5000
7/1/2018	5000
8/1/2018	4900
9/1/2018	4800
10/1/2018	4700
11/1/2018	4600

<tbody>
</tbody>

If you insist on using DGET, in E2 enter:

=EOMONTH(TODAY(),-1)+1

not just:

=TODAY()

In F2 enter:

=DGET($A$2:$B$7,"balance",$E$1:$E$2)

You can also invoke a fast VLOOKUP instead of DGET. This allows using just

=TODAY()

in E2.

F2 becomes:

=VLOOKUP(E2,$A$3:$B$7,2,1)