Get Last Matching Date

[MrDB4Excel]

I have searched through many sites looking for what seems to me to be a very simple answer but nothing seems to work.
This is a bogus workbook that somewhat matches a confidential workbook, thus the fictitious amounts.
In column B you see text from a legend that indicates a specific activity.
In column C you see dates when the indicated activity occurred.
In column D you see $ amounts.
In cell J1 you see today’s date.
In cell K1 you see a formula that supposedly gets what I am after, but I am not sure.
In cell J2 you see it is blank.
What I want is for this cell to reflect the date shown in column C which is the last date shown for the current month (in this case August) that matches the legend OFP. Specifically, cell J2 would show 19/ Aug/ 2023.
Then from this result, I want to get the corresponding dollar amount in the same row as 19/ Aug/ 2023 that appears in column D, in this case, $2,900.
Any help is very much appreciated as I have spent the better part of the last 4 hours looking for a solution.
p.s. I use 2013 Excel but I have the Xlookup Addin and it works quite well on my 2013 Excel, thus I use Xlookup extensively without any issues.

BogusDateMatch.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			16	22	22	22	22			26/ Aug/ 2023	$2,900.00
2		Legend	Date	Amount In Account Per Date Shown	Debit Amount	Amount Added to Savings Per Date Shown	Amount For Deposit	Date of Deposit			$2,900.00		LEGEND
3	1	FSCB	Jan/01/2023	$10,000.00								1	FSCB
4	4	CSOB	Jan/03/2023	$9,000.00			$1,000.00	Jan/03/2023				2	OFP
5	2	OFP	Jan/06/2023	$8,900.00	$100.00							3	AA2FS
6	4	CSOB	Jan/09/2023	$7,900.00			$1,000.00	Jan/10/2023				4	CSOB
7	3	AA2FS	Jan/11/2023	$8,200.00		$300.00						5	$sW
8	7	O4M	Jan/23/2023	$8,100.00	$100.00							6	$sA2FS
9	4	CSOB	30/ Jan/ 2023	$7,100.00			$1,000.00	Feb/13/2023				7	O4M
10	2	OFP	30/ Jan/ 2023	$7,000.00	$100.00
11	3	AA2FS	13/ Feb/ 2023	$7,800.00		$800.00
12	2	OFP	15/ Feb/ 2023	$7,600.00	$200.00
13	2	OFP	26/ Feb/ 2023	$7,500.00	$100.00
14	4	CSOB	06/ Mar/ 2023	$6,500.00			$1,000.00	Mar/08/2023
15	3	AA2FS	08/ Mar/ 2023	$7,100.00		$600.00
16	4	CSOB	10/ Apr/ 2023	$6,100.00			$1,000.00	Apr/12/2023
17	3	AA2FS	13/ Apr/ 2023	$6,900.00		$800.00
18	2	OFP	27/ Apr/ 2023	$6,800.00	$100.00
19	4	CSOB	08/ May/ 2023	$5,800.00			$1,000.00	May/11/2023
20	2	OFP	12/ May/ 2023	$5,600.00	$200.00
21	4	CSOB	07/ Jun/ 2023	$4,600.00			$1,000.00	Jun/08/2023
22	3	AA2FS	08/ Jun/ 2023	$5,200.00		$600.00
23	4	CSOB	06/ Jul/ 2023	$4,200.00			$1,000.00	Jul/12/2023
24	2	OFP	12/ Jul/ 2023	$4,100.00	$100.00
25	4	CSOB	28/ Jul/ 2023	$3,100.00			$1,000.00	Aug/09/2023
26	2	OFP	28/ Jul/ 2023	$3,000.00	$100.00
27	2	OFP	19/ Aug/ 2023	$2,900.00	$100.00
28	4	CSOB	05/ Sep/ 2023	$1,900.00			$1,000.00	Sep/08/2023
29	3	AA2FS	08/ Sep/ 2023	$2,430.00		$530.00
30	2	OFP	08/ Sep/ 2023	$2,300.00	$130.00
31	4	CSOB	09/ Oct/ 2023	$1,300.00			$1,000.00	Oct/09/2023
32	3	AA2FS	09/ Oct/ 2023	$2,020.00		$720.00
33	2	OFP	09/ Oct/ 2023	$1,900.00	$120.00
34	4	CSOB	08/ Nov/ 2023	$900.00			$1,000.00	Nov/08/2023
Accounting

Cell Formulas
Range	Formula
J1	J1	=TODAY()
K1	K1	=LOOKUP(2,1/(TEXT(dates,"mmyyyy")=TEXT(J1,"mmyyyy")),Current_Amount)
K2	K2	=K1
B3:B34	B3	=IF(A3=$L$3,$M$3,IF(A3=$L$4,$M$4,IF(A3=$L$5,$M$5,IF(A3=$L$6,$M$6,IF(A3=$L$7,$M$7,IF(A3=$L$8,$M$8,IF(A3=$L$9,$M$9)))))))
D4,D34,D31,D28,D25,D23,D21,D19,D16,D14,D9,D6	D4	=D3-G4
D5,D33,D30,D26:D27,D24,D20,D18,D12:D13,D10,D8	D5	=D4-E5
D7,D32,D29,D22,D17,D15,D11	D7	=SUM(D6+F7)

Named Ranges
Name	Refers To	Cells
Current_Amount	=Accounting!$D$3:$D$44	K1, D4
dates	=Accounting!$C$3:$C$44	K1
XL	=Accounting!$A$3:$A$500	B3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K7	Expression	=$B7="O4M"	text	NO
K7	Expression	=$B7="$sA2FS"	text	NO
K7	Expression	=$B7="$sW"	text	NO
K7	Expression	=$B7="OFP"	text	NO
K7	Expression	=$B7="CSOB"	text	NO
K7	Expression	=$B7="AA2FS"	text	NO
K7	Expression	=$B7="FSCB"	text	NO
K2	Expression	=$B2="O4M"	text	NO
K2	Expression	=$B2="$sA2FS"	text	NO
K2	Expression	=$B2="$sW"	text	NO
K2	Expression	=$B2="OFP"	text	NO
K2	Expression	=$B2="CSOB"	text	NO
K2	Expression	=$B2="AA2FS"	text	NO
K2	Expression	=$B2="FSCB"	text	NO
K1	Expression	=$B1="O4M"	text	NO
K1	Expression	=$B1="$sA2FS"	text	NO
K1	Expression	=$B1="$sW"	text	NO
K1	Expression	=$B1="OFP"	text	NO
K1	Expression	=$B1="CSOB"	text	NO
K1	Expression	=$B1="AA2FS"	text	NO
K1	Expression	=$B1="FSCB"	text	NO
H34	Expression	=$B34="O4M"	text	NO
H34	Expression	=$B34="$sA2FS"	text	NO
H34	Expression	=$B34="$sW"	text	NO
H34	Expression	=$B34="OFP"	text	NO
H34	Expression	=$B34="CSOB"	text	NO
H34	Expression	=$B34="AA2FS"	text	NO
H34	Expression	=$B34="FSCB"	text	NO
H28	Expression	=$B28="O4M"	text	NO
H28	Expression	=$B28="$sA2FS"	text	NO
H28	Expression	=$B28="$sW"	text	NO
H28	Expression	=$B28="OFP"	text	NO
H28	Expression	=$B28="CSOB"	text	NO
H28	Expression	=$B28="AA2FS"	text	NO
H28	Expression	=$B28="FSCB"	text	NO
H6	Expression	=$B6="O4M"	text	NO
H6	Expression	=$B6="$sA2FS"	text	NO
H6	Expression	=$B6="$sW"	text	NO
H6	Expression	=$B6="OFP"	text	NO
H6	Expression	=$B6="CSOB"	text	NO
H6	Expression	=$B6="AA2FS"	text	NO
H6	Expression	=$B6="FSCB"	text	NO
H25	Expression	=$B25="O4M"	text	NO
H25	Expression	=$B25="$sA2FS"	text	NO
H25	Expression	=$B25="$sW"	text	NO
H25	Expression	=$B25="OFP"	text	NO
H25	Expression	=$B25="CSOB"	text	NO
H25	Expression	=$B25="AA2FS"	text	NO
H25	Expression	=$B25="FSCB"	text	NO
H23	Expression	=$B23="O4M"	text	NO
H23	Expression	=$B23="$sA2FS"	text	NO
H23	Expression	=$B23="$sW"	text	NO
H23	Expression	=$B23="OFP"	text	NO
H23	Expression	=$B23="CSOB"	text	NO
H23	Expression	=$B23="AA2FS"	text	NO
H23	Expression	=$B23="FSCB"	text	NO
H21	Expression	=$B21="O4M"	text	NO
H21	Expression	=$B21="$sA2FS"	text	NO
H21	Expression	=$B21="$sW"	text	NO
H21	Expression	=$B21="OFP"	text	NO
H21	Expression	=$B21="CSOB"	text	NO
H21	Expression	=$B21="AA2FS"	text	NO
H21	Expression	=$B21="FSCB"	text	NO
H19	Expression	=$B19="O4M"	text	NO
H19	Expression	=$B19="$sA2FS"	text	NO
H19	Expression	=$B19="$sW"	text	NO
H19	Expression	=$B19="OFP"	text	NO
H19	Expression	=$B19="CSOB"	text	NO
H19	Expression	=$B19="AA2FS"	text	NO
H19	Expression	=$B19="FSCB"	text	NO
H16	Expression	=$B16="O4M"	text	NO
H16	Expression	=$B16="$sA2FS"	text	NO
H16	Expression	=$B16="$sW"	text	NO
H16	Expression	=$B16="OFP"	text	NO
H16	Expression	=$B16="CSOB"	text	NO
H16	Expression	=$B16="AA2FS"	text	NO
H16	Expression	=$B16="FSCB"	text	NO
H14	Expression	=$B14="O4M"	text	NO
H14	Expression	=$B14="$sA2FS"	text	NO
H14	Expression	=$B14="$sW"	text	NO
H14	Expression	=$B14="OFP"	text	NO
H14	Expression	=$B14="CSOB"	text	NO
H14	Expression	=$B14="AA2FS"	text	NO
H14	Expression	=$B14="FSCB"	text	NO
H9	Expression	=$B9="O4M"	text	NO
H9	Expression	=$B9="$sA2FS"	text	NO
H9	Expression	=$B9="$sW"	text	NO
H9	Expression	=$B9="OFP"	text	NO
H9	Expression	=$B9="CSOB"	text	NO
H9	Expression	=$B9="AA2FS"	text	NO
H9	Expression	=$B9="FSCB"	text	NO
H4	Expression	=$B4="O4M"	text	NO
H4	Expression	=$B4="$sA2FS"	text	NO
H4	Expression	=$B4="$sW"	text	NO
H4	Expression	=$B4="OFP"	text	NO
H4	Expression	=$B4="CSOB"	text	NO
H4	Expression	=$B4="AA2FS"	text	NO
H4	Expression	=$B4="FSCB"	text	NO
H31,B3:G500,N22	Expression	=$B3="O4M"	text	NO
E8	Expression	=$B8="$sA2FS"	text	NO
E8	Expression	=$B8="$sW"	text	NO
E8	Expression	=$B8="OFP"	text	NO
E8	Expression	=$B8="CSOB"	text	NO
E8	Expression	=$B8="AA2FS"	text	NO
E8	Expression	=$B8="FSCB"	text	NO
D8	Expression	=$B8="$sA2FS"	text	NO
D8	Expression	=$B8="$sW"	text	NO
D8	Expression	=$B8="OFP"	text	NO
D8	Expression	=$B8="CSOB"	text	NO
D8	Expression	=$B8="AA2FS"	text	NO
D8	Expression	=$B8="FSCB"	text	NO
C8,F8:H8	Expression	=$B8="$sA2FS"	text	NO
C8,F8:H8	Expression	=$B8="$sW"	text	NO
C8	Cell Value	contains "FALSE"	text	NO
C8,F8:H8	Expression	=$B8="OFP"	text	NO
C8,F8:H8	Expression	=$B8="CSOB"	text	NO
C8,F8:H8	Expression	=$B8="AA2FS"	text	NO
C8,F8:H8	Expression	=$B8="FSCB"	text	NO
B3:G7,H31,B9:G500,N22	Expression	=$B3="$sA2FS"	text	NO
B3:G7,H31,B9:G500,N22	Expression	=$B3="$sW"	text	NO
B3:B7,B9:B500	Cell Value	contains "FALSE"	text	NO
B3:G7,H31,B9:G500,N22	Expression	=$B3="OFP"	text	NO
B3:G7,H31,B9:G500,N22	Expression	=$B3="CSOB"	text	NO
B3:G7,H31,B9:G500,N22	Expression	=$B3="AA2FS"	text	NO
B3:G7,H31,B9:G500,N22	Expression	=$B3="FSCB"	text	NO

 

[SanjayGMusafir]

Try

Assuming Cell J1 contains value "OFP" or any you are searching for...

You can further improve by putting "dates" instead of $C:$C and by naming range $B:$B and using that name

=LOOKUP(2,1/(($B:$B=$J$1)*($C:$C<=EoMonth(Today(),0)),$C:$C))

 

[MrDB4Excel]

For some odd reason, this is not working. This is the message that happens:

 

[SanjayGMusafir]

For some odd reason, this is not working

Yes I think I typed it wrong. Try this -

=LOOKUP(2,1/(($B:$B=$J$1)*($C:$C<=EoMonth(Today(),0))),$C:$C)

 

[MrDB4Excel]

That did the trick. Now the next step is to use that date as the Xlookup to get the corresponding column D value.
I will try this Xlookup and get back to you to let you know how that goes.
Thank you.

 

[MrDB4Excel]

Perfect, the Xlookup works as I hoped it would.
Kudos to Sanjay!!!

 

[SanjayGMusafir]

That did the trick. Now the next step is to use that date as the Xlookup to get the corresponding column D value.
I will try this Xlookup and get back to you to let you know how that goes.
Thank you.

=XLOOKUP(1,($C:$C=$J$2)*($B:$B=$J$1),$D:$D)

 

[SanjayGMusafir]

Glad to help you

 

[MrDB4Excel]

=XLOOKUP(1,($C:$C=$J$2)*($B:$B=$J$1),$D:$D)

This is the XLookup I ended up using: =XLOOKUP(J2,dates,Current_Amount,0)

 

[SanjayGMusafir]

XLOOKUP(J2,dates,Current_Amount,0)

The problem would be when you have same date more than once for various Legend. Then it might return wrong result.

But with XLOOKUP I gave, that will return the exact intended result every time... Because it will match date & Legend both to return Value.

=XLOOKUP(1,($C:$C=$J$2)*($B:$B=$J$1),$D:$D)