Index Match with Dates

[mba_110]

Hi Everyone,

I have following formula that calculate the amount based on dates and criteria, i used index and Match and it is working fine.

Fixed Assets - Register.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
103	LT-FA0079	25 Apr 2023	24 Apr 2024	Camera Bag	30.49	28.52	30.49										AED 89.51
Year - 2024

Cell Formulas
Range	Formula
D103	D103	=VLOOKUP(C103,'Asset List'!$C$12:$R$174,2,0)
E103	E103	=VLOOKUP(C103,'Asset Depreciation'!$C$33:$L$4524,7,0)
F103	F103	=VLOOKUP(C103,'Asset List'!C89:R251,4,0)
G103	G103	=IF(AND(D103>$G$6,C103<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($G$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))
H103	H103	=IF(AND(D103>$H$6,C103<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($H$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))
I103	I103	=IF(AND(D103>$I$6,C103<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($I$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))
J103	J103	=IF(AND(D103>$J$6,C103<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($J$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))
K103	K103	=IF(AND(D103>$K$6,C103<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($K$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))
L103	L103	=IF(AND(D103>$L$6,C103<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($L$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))
M103	M103	=IF(AND(D103>$M$6,C103<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($M$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))
N103	N103	=IF(AND(D103>$N$6,C103<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($N$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))
O103	O103	=IF(AND(D103>$O$6,C103<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($O$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))
P103	P103	=IF(AND(D103>$P$6,C103<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($P$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))
Q103	Q103	=IF(AND(D103>$Q$6,C103<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($Q$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))
R103	R103	=IF(AND(D103>$R$6,$C$65<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($R$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))
S103	S103	=SUM(G103:R103)

Fixed Assets - Register.xlsx
	B	C	D	E	F	G	H	I	J	K	L
2476	79	LT-FA0079	25 Apr 2023	360.00	Camera Bag	1		24 Apr 2024	366		AED 360.00
2477		LT-FA0079			Camera Bag	0.98	25 Apr 2023	30 Apr 2023	6	5.90	AED 354.10
2478		LT-FA0079			Camera Bag	0.90	1 May 2023	31 May 2023	31	30.49	AED 323.61
2479		LT-FA0079			Camera Bag	0.82	1 Jun 2023	30 Jun 2023	30	29.51	AED 294.10
2480		LT-FA0079			Camera Bag	0.73	1 Jul 2023	31 Jul 2023	31	30.49	AED 263.61
2481		LT-FA0079			Camera Bag	0.65	1 Aug 2023	31 Aug 2023	31	30.49	AED 233.11
2482		LT-FA0079			Camera Bag	0.57	1 Sep 2023	30 Sep 2023	30	29.51	AED 203.61
2483		LT-FA0079			Camera Bag	0.48	1 Oct 2023	31 Oct 2023	31	30.49	AED 173.11
2484		LT-FA0079			Camera Bag	0.40	1 Nov 2023	30 Nov 2023	30	29.51	AED 143.61
2485		LT-FA0079			Camera Bag	0.31	1 Dec 2023	31 Dec 2023	31	30.49	AED 113.11
2486		LT-FA0079			Camera Bag	0.23	1 Jan 2024	31 Jan 2024	31	30.49	AED 82.62
2487		LT-FA0079			Camera Bag	0.15	1 Feb 2024	29 Feb 2024	29	28.52	AED 54.10
2488		LT-FA0079			Camera Bag	0.07	1 Mar 2024	31 Mar 2024	31	30.49	AED 23.61
2489		LT-FA0079			Camera Bag	0.00	1 Apr 2024	24 Apr 2024	24	23.61	AED -
2490
2491				AED 360.00	TOTAL AMOUNT :				366	360.00	AED -
Asset Depreciation

Cell Formulas
Range	Formula
D2476	D2476	=VLOOKUP(C2476,'Asset List'!$C$12:$R$176,2,0)
E2476	E2476	=VLOOKUP(C2476,'Asset List'!$C$12:$N$175,3,0)
F2476	F2476	=VLOOKUP(C2476,'Asset List'!$C$12:$N$175,4,0)
G2476	G2476	=VLOOKUP(C2476,'Asset List'!$C$12:$N$175,10,0)
I2476	I2476	=ROUND(D2476+G2476*365,0)
J2476	J2476	=ROUND(I2476-D2476+1,0)
L2476	L2476	=E2476
F2477:F2489,C2477:C2489	F2477	=F2476
G2477	G2477	=(-$G$2476/$J$2476)*J2477+$G$2476
H2477	H2477	=D2476
I2477:I2489	I2477	=IF(H2477+30<=$I$2476,EOMONTH(H2477,0),IF(H2477="","",$I$2476))
J2477:J2489	J2477	=ROUND(I2477-H2477+1,0)
K2477:K2489	K2477	=$E$2476/$J$2476*J2477
L2477:L2489	L2477	=L2476-K2477
G2478:G2489	G2478	=(-$G$2476/$J$2476)*J2478+G2477
H2478:H2489	H2478	=IF($I$2476>EOMONTH(I2477,0),I2477+1,"")
E2491,K2491	E2491	=SUBTOTAL(9,E2476:E2490)
J2491	J2491	=SUM(J2477:J2489)
L2491	L2491	=L2476-K2491

C103 is 30/04/2024

Now i want J103 should allocate the reminder amount of 23.61 as scheduled in Asset depreciation sheet since its falling during month April 2024 and C103 is mentioned 30/04/2024.

Any help will be appreciated.

 

[mba_110]

Can anyone help me out on this formula ?

=IF(AND(D103>$H$6,C103<>'Asset Depreciation'!$C$11:$C$3623),"",IFERROR(INDEX('Asset Depreciation'!$K$11:$K$3623,MATCH(1,($H$6='Asset Depreciation'!$I$11:$I$3623)*(C103='Asset Depreciation'!$C$11:$C$3623),0)),""))

 

[Cubist]

C103 is 30/04/2024

I see C103 has the value "LT-FA0079"

What's in J6 and H6?

 

[mba_110]

I see C103 has the value "LT-FA0079"

What's in J6 and H6?

J6,H6,I6, all this mentioned month end dates which is 30/04/2024, 31/05/2024, 30/06/2024, but the calculation end date which is mentioned on E103 = 24 Apr 2024

for example : Asset depreciation sheet schedule mentioned last line for month is April 2024 which is ending on 24 April 2024 as mentioned on E103.

i want if E103 date is falling during same month and year of J6 which is 30/04/2024, the reminder amount showed in above Asset Depreciation sheet should allocate in this column

 

[mba_110]

I see C103 has the value "LT-FA0079"

What's in J6 and H6?

Sorry it was my mistake you are right.

 

[Cubist]

Are those cells real dates or are they text? Check with

=ISNUMBER(cell)

 

[mba_110]

Those mention dates are date format cell

Are those cells real dates or are they text? Check with

=ISNUMBER(cell)

 

[Cubist]

Try this in G103 and paste to the right.

=FILTER(Asset Depreciation'!$K$11:$K$3623,($C103='Asset Depreciation'!$C$11:$C$3623)*(EOMONTH(+'Asset Depreciation'!$I$11:$I$3623,0)=EOMONTH(G$6,0)),"")

 

[mba_110]

Try this in G103 and paste to the right.

=FILTER(Asset Depreciation'!$K$11:$K$3623,($C103='Asset Depreciation'!$C$11:$C$3623)*(EOMONTH(+'Asset Depreciation'!$I$11:$I$3623,0)=EOMONTH(G$6,0)),"")

The Formula should reference to J6 which is 30/04/2024, however after i change it to this it gave me spill error.

 

[Cubist]

The Formula should reference to J6 which is 30/04/2024, however after i change it to this it gave me spill error.

That means you have more than 1 value in 'Asset Depreciation'!$I$11:$I$3623 with the same month and year.