formula to get date or closest date

[BobtBuilder]

Formula==INDEX(INDIRECT("'" & I2 & "'!J2:J6503"), MAX(IF(INDIRECT("'" & I2 & "'!A2:A6503")=TODAY(), ROW(INDIRECT("'" & I2 & "'!A2:A6503")))), 1)
So I have multiple Worksheets (ie test1, test2 etc) I am on a dashboard worksheet and would like to get information from these worksheet tables. Basically want row based on todays date, and if it does not exist go to the closest date prior and get row j. This formula does not work if there is no exact match, it spills.
THank you

 

[BobtBuilder]

sorry

2003-05-04​	EFT	Mani	6193655 1683133595427a		R	324.23​	0.00​	-324.23​
2014-06-02​		Monthly Fee		Fees & Charges:Bank Fee	R	20.00​	0.00​	-344.23​
2014-06-06​		Chq Fee		Fees & Charges	R	161.49​	0.00​	-505.72​
2014-06-06​		Deposit			R	0.00​	30,775.97​	30,270.25​
2014-06-09​	1472	jj	465974​		R	314.59​	0.00​	29,955.66​
2014-06-09​	1471	Morn	a4854476,51635,52345		R	253.52​	0.00​	29,702.14​
2014-06-09​	1470	Fulto	15492​		R	78.41​	0.00​	29,623.73​
2014-06-16​	1475	Airt	287670​		R	23.29​	0.00​	29,600.44​
2014-06-16​	1474	Fulto	15495​		R	196.96​	0.00​	29,403.48​
2014-06-16​	1473	Mid	95567117-14-24		R	221.75​	0.00​	29,181.73​
2014-06-17​		Od Interest			R	0.02​	0.00​	29,181.71​
2014-06-17​		Deposit	transfer		R	0.00​	96,000.00​	125,181.71​
2014-06-18​		Deposit			R	0.00​	5,000.00​	130,181.71​
2014-06-19​		Deposit			R	0.00​	5,358.14​	135,539.85​
2014-06-20​		Deposit			R	0.00​	40,315.00​	175,854.85​
2014-06-23​	EFT34147	visa			R	1,946.72​	0.00​	173,908.13​
2014-06-23​	eft6034	AMERICAN EXPRESS		Purchases	R	3,015.24​	0.00​	170,892.89​
2014-06-23​		Deposit			R	0.00​	9,415.40​	180,308.29​
2014-06-26​	EFT	bv			R	2,250.00​	0.00​	178,058.29​
2014-06-26​	EFT	bc			R	5,250.00​	0.00​	172,808.29​
2014-06-27​	EFT	Deposit	quebec		R	0.00​	4,000.00​	176,808.29​
2014-06-30​	EFT	FORD CREDIT	37 OF 60		R	753.37​	0.00​	176,054.92​
2014-06-30​	1476	fult	1549850406​		R	599.95​	0.00​	175,454.97​
2014-07-02​	EFT	anch			R	876.75​	0.00​	174,578.22​
2014-07-02​	EFT	morn			R	287.37​	0.00​	174,290.85​
2014-07-02​		Fees			R	46.70​	0.00​	174,244.15​
2014-07-02​	1477	midl	9.56099E+35​		R	584.30​	0.00​	173,659.85​
2014-07-03​		Deposit			R	0.00​	4,500.00​	178,159.85​
2014-07-04​	eft2579	supr	6939​		R	2,563.95​	0.00​	175,595.90​
2014-07-04​	EFT	bc			R	4,285.00​	0.00​	171,310.90​
2014-07-04​		Deposit			R	0.00​	31,138.43​	202,449.33​
2014-07-04​		Deposit			R	0.00​	4,558.61​	207,007.94​
2014-07-09​	1478	fulto	155080712​		R	1,237.40​	0.00​	205,770.54​
2014-07-09​	EFT	morn			R	505.23​	0.00​	205,265.31​
2014-07-11​	eft7777	bc			R	706.90​	0.00​	204,558.41​
2014-07-11​	1480	cpc	15566,81,39,09,cn635		R	73,914.40​	0.00​	130,644.01​
2014-07-11​	1479	anco	368381385​		R	31,621.31​	0.00​	99,022.70​
2014-07-11​		Deposit		Other Inc	R	0.00​	12,671.88​	111,694.58​
2014-07-15​	1481	fulto	1551516​		R	576.28​	0.00​	111,118.30​
2014-07-21​	1484	Dicom			R	47.52​	0.00​	111,070.78​
2014-07-21​	1483	fulto	15518​		R	167.78​	0.00​	110,903.00​
2014-07-21​	1482	jj	469801​		R	322.64​	0.00​	110,580.36​
2014-07-21​	eft1493	midl	98104194​		R	61.46​	0.00​	110,518.90​

 

[BobtBuilder]

also column g is hidden

 

[Fluff]

How about

=TAKE(FILTER(INDIRECT("'" & I2 & "'!J2:J6503"),(INDIRECT("'" & I2 & "'!A2:A6503")<=TODAY())*(INDIRECT("'" & I2 & "'!A2:A6503")<>"")),-1)

 

[BobtBuilder]

How about

=TAKE(FILTER(INDIRECT("'" & I2 & "'!J2:J6503"),(INDIRECT("'" & I2 & "'!A2:A6503")<=TODAY())*(INDIRECT("'" & I2 & "'!A2:A6503")<>"")),-1)

thanks that seems to work

 

[Fluff]

You're welcome & thanks for the feedback.