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]

Even i changed the end date to EOM also its not showing last value.

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

You can see in my first post there is no more than one value each line.

I think i got the problem now, EOM is used to calculate on 12 month basis but if you see in Asset Depreciation sheet the total lines are 13 and each time on 1 year calculation it ignores the 13 number line.

Again i don't know exactly what to fix in this formula even though i discover the possibility of error.

 

[Cubist]

Cell I2476 has the same month and date as I2489.

 

[Cubist]

I don't have a full understanding of your table set up. This will return the latest entry with the matching month and date.

=XLOOKUP(1,($C103='Asset Depreciation'!$C$11:$C$3623)*(EOMONTH(+'Asset Depreciation'!$I$11:$I$3623,0)=EOMONTH(J$6,0)),Asset Depreciation'!$K$11:$K$3623,"",,-1)