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

 

[Fluff]

If column A is always sorted by date old to new, try

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

 

[BobtBuilder]

If column A is always sorted by date old to new, try

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

continuously return 0

 

[Fluff]

Are your dates sorted & are there any blank cells in the range?

 

[BobtBuilder]

Are your dates sorted & are there any blank cells in the range?

the dates are sorted in the table and there are no blanks

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[BobtBuilder]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

is there any other way to load the data, as our it does not allow us to install addons

 

[Fluff]

You can simply copy paste you data into the reply window.

 

[BobtBuilder]

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​

 

[Fluff]

Thanks for that, but there is no data for col J, it just goes up-to col I