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

 

[MrDB4Excel]

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)

Why does this =XLOOKUP(1,($C:$C=$J$2)*($B:$B=$J$1),$D:$D) give a #N/A result?

BogusDateMatch.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			16	22	22	22	22			OFP	$2,950.00		26/ Aug/ 2023
2		Legend	Date	Amount In Account Per Date Shown	Debit Amount	Amount Added to Savings Per Date Shown	Amount For Deposit	Date of Deposit		19/ Aug/ 2023	$2,950.00		LEGEND
3	1	FSCB	Jan/01/2023	$10,000.00						$2,950.00		1	FSCB
4	4	CSOB	Jan/03/2023	$9,000.00			$1,000.00	Jan/03/2023		#N/A		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,050.00	$50.00
27	2	OFP	19/ Aug/ 2023	$2,950.00	$100.00
28	4	CSOB	05/ Sep/ 2023	$1,950.00			$1,000.00	Sep/08/2023
29	3	AA2FS	08/ Sep/ 2023	$2,480.00		$530.00
30	2	OFP	08/ Sep/ 2023	$2,350.00	$130.00
31	4	CSOB	09/ Oct/ 2023	$1,350.00			$1,000.00	Oct/09/2023
32	3	AA2FS	09/ Oct/ 2023	$2,070.00		$720.00
33	2	OFP	09/ Oct/ 2023	$1,950.00	$120.00
34	4	CSOB	08/ Nov/ 2023	$950.00			$1,000.00	Nov/08/2023
Accounting

Cell Formulas
Range	Formula
M1	M1	=TODAY()
K1	K1	=LOOKUP(2,1/(TEXT(dates,"mmyyyy")=TEXT(M1,"mmyyyy")),Current_Amount)
K2	K2	=K1
J2	J2	=LOOKUP(2,1/(($B:$B=$J$1)*($C:$C<=EOMONTH(TODAY(),0))),$C:$C)
J3	J3	=XLOOKUP(J2,dates,Current_Amount,0)
J4	J4	=XLOOKUP(1,($C:$C=$J$2)*($B:$B=$J$1),$D:$D)
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
Crowns	=Accounting!$B$3:$G$500	J2
Current_Amount	=Accounting!$D$3:$D$44	K1, J3:J4, D4
dates	=Accounting!$C$3:$C$44	K1, J2:J3
HideFalse	=Accounting!$B$3:$B$500	J2
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]

Why does this =XLOOKUP(1,($C:$C=$J$2)*($B:$B=$J$1),$D:$D) give a #N/A result?

Unable to copy my XL2BB here...

But it's giving me the right result

Still I will try to copy XL2BB here

 

[SanjayGMusafir]

Uploading XL2BB failed for me again...

Just try to input formula again and check...

It's working perfect with me... See Screenshot

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

 

[MrDB4Excel]

Uploading XL2BB failed for me again...

Just try to input formula again and check...

It's working perfect with me... See Screenshot

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

Still not working. The thing about the possibility of "The problem would be when you have same date more than once for various Legend. Then it might return wrong result." is that I never have the same date for any instance of OFP as they are always different, so my other XLookup will suffice.
I just can't figure out why your formula is giving me this #N/A. Maybe when I am old and gray I will figure it out.