Get Last Matching Date

MrDB4Excel

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
334
Office Version
  1. 2013
Platform
  1. Windows
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
ABCDEFGHIJKLM
1162222222226/ Aug/ 2023$2,900.00
2LegendDateAmount In Account Per Date ShownDebit AmountAmount Added to Savings Per Date ShownAmount For DepositDate of Deposit$2,900.00LEGEND
31FSCBJan/01/2023$10,000.001FSCB
44CSOBJan/03/2023$9,000.00$1,000.00Jan/03/20232OFP
52OFPJan/06/2023$8,900.00$100.003AA2FS
64CSOBJan/09/2023$7,900.00$1,000.00Jan/10/20234CSOB
73AA2FSJan/11/2023$8,200.00$300.005$sW
87O4MJan/23/2023$8,100.00$100.006$sA2FS
94CSOB30/ Jan/ 2023$7,100.00$1,000.00Feb/13/20237O4M
102OFP30/ Jan/ 2023$7,000.00$100.00
113AA2FS13/ Feb/ 2023$7,800.00$800.00
122OFP15/ Feb/ 2023$7,600.00$200.00
132OFP26/ Feb/ 2023$7,500.00$100.00
144CSOB06/ Mar/ 2023$6,500.00$1,000.00Mar/08/2023
153AA2FS08/ Mar/ 2023$7,100.00$600.00
164CSOB10/ Apr/ 2023$6,100.00$1,000.00Apr/12/2023
173AA2FS13/ Apr/ 2023$6,900.00$800.00
182OFP27/ Apr/ 2023$6,800.00$100.00
194CSOB08/ May/ 2023$5,800.00$1,000.00May/11/2023
202OFP12/ May/ 2023$5,600.00$200.00
214CSOB07/ Jun/ 2023$4,600.00$1,000.00Jun/08/2023
223AA2FS08/ Jun/ 2023$5,200.00$600.00
234CSOB06/ Jul/ 2023$4,200.00$1,000.00Jul/12/2023
242OFP12/ Jul/ 2023$4,100.00$100.00
254CSOB28/ Jul/ 2023$3,100.00$1,000.00Aug/09/2023
262OFP28/ Jul/ 2023$3,000.00$100.00
272OFP19/ Aug/ 2023$2,900.00$100.00
284CSOB05/ Sep/ 2023$1,900.00$1,000.00Sep/08/2023
293AA2FS08/ Sep/ 2023$2,430.00$530.00
302OFP08/ Sep/ 2023$2,300.00$130.00
314CSOB09/ Oct/ 2023$1,300.00$1,000.00Oct/09/2023
323AA2FS09/ Oct/ 2023$2,020.00$720.00
332OFP09/ Oct/ 2023$1,900.00$120.00
344CSOB08/ Nov/ 2023$900.00$1,000.00Nov/08/2023
Accounting
Cell Formulas
RangeFormula
J1J1=TODAY()
K1K1=LOOKUP(2,1/(TEXT(dates,"mmyyyy")=TEXT(J1,"mmyyyy")),Current_Amount)
K2K2=K1
B3:B34B3=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,D6D4=D3-G4
D5,D33,D30,D26:D27,D24,D20,D18,D12:D13,D10,D8D5=D4-E5
D7,D32,D29,D22,D17,D15,D11D7=SUM(D6+F7)
Named Ranges
NameRefers ToCells
Current_Amount=Accounting!$D$3:$D$44K1, D4
dates=Accounting!$C$3:$C$44K1
XL=Accounting!$A$3:$A$500B3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K7Expression=$B7="O4M"textNO
K7Expression=$B7="$sA2FS"textNO
K7Expression=$B7="$sW"textNO
K7Expression=$B7="OFP"textNO
K7Expression=$B7="CSOB"textNO
K7Expression=$B7="AA2FS"textNO
K7Expression=$B7="FSCB"textNO
K2Expression=$B2="O4M"textNO
K2Expression=$B2="$sA2FS"textNO
K2Expression=$B2="$sW"textNO
K2Expression=$B2="OFP"textNO
K2Expression=$B2="CSOB"textNO
K2Expression=$B2="AA2FS"textNO
K2Expression=$B2="FSCB"textNO
K1Expression=$B1="O4M"textNO
K1Expression=$B1="$sA2FS"textNO
K1Expression=$B1="$sW"textNO
K1Expression=$B1="OFP"textNO
K1Expression=$B1="CSOB"textNO
K1Expression=$B1="AA2FS"textNO
K1Expression=$B1="FSCB"textNO
H34Expression=$B34="O4M"textNO
H34Expression=$B34="$sA2FS"textNO
H34Expression=$B34="$sW"textNO
H34Expression=$B34="OFP"textNO
H34Expression=$B34="CSOB"textNO
H34Expression=$B34="AA2FS"textNO
H34Expression=$B34="FSCB"textNO
H28Expression=$B28="O4M"textNO
H28Expression=$B28="$sA2FS"textNO
H28Expression=$B28="$sW"textNO
H28Expression=$B28="OFP"textNO
H28Expression=$B28="CSOB"textNO
H28Expression=$B28="AA2FS"textNO
H28Expression=$B28="FSCB"textNO
H6Expression=$B6="O4M"textNO
H6Expression=$B6="$sA2FS"textNO
H6Expression=$B6="$sW"textNO
H6Expression=$B6="OFP"textNO
H6Expression=$B6="CSOB"textNO
H6Expression=$B6="AA2FS"textNO
H6Expression=$B6="FSCB"textNO
H25Expression=$B25="O4M"textNO
H25Expression=$B25="$sA2FS"textNO
H25Expression=$B25="$sW"textNO
H25Expression=$B25="OFP"textNO
H25Expression=$B25="CSOB"textNO
H25Expression=$B25="AA2FS"textNO
H25Expression=$B25="FSCB"textNO
H23Expression=$B23="O4M"textNO
H23Expression=$B23="$sA2FS"textNO
H23Expression=$B23="$sW"textNO
H23Expression=$B23="OFP"textNO
H23Expression=$B23="CSOB"textNO
H23Expression=$B23="AA2FS"textNO
H23Expression=$B23="FSCB"textNO
H21Expression=$B21="O4M"textNO
H21Expression=$B21="$sA2FS"textNO
H21Expression=$B21="$sW"textNO
H21Expression=$B21="OFP"textNO
H21Expression=$B21="CSOB"textNO
H21Expression=$B21="AA2FS"textNO
H21Expression=$B21="FSCB"textNO
H19Expression=$B19="O4M"textNO
H19Expression=$B19="$sA2FS"textNO
H19Expression=$B19="$sW"textNO
H19Expression=$B19="OFP"textNO
H19Expression=$B19="CSOB"textNO
H19Expression=$B19="AA2FS"textNO
H19Expression=$B19="FSCB"textNO
H16Expression=$B16="O4M"textNO
H16Expression=$B16="$sA2FS"textNO
H16Expression=$B16="$sW"textNO
H16Expression=$B16="OFP"textNO
H16Expression=$B16="CSOB"textNO
H16Expression=$B16="AA2FS"textNO
H16Expression=$B16="FSCB"textNO
H14Expression=$B14="O4M"textNO
H14Expression=$B14="$sA2FS"textNO
H14Expression=$B14="$sW"textNO
H14Expression=$B14="OFP"textNO
H14Expression=$B14="CSOB"textNO
H14Expression=$B14="AA2FS"textNO
H14Expression=$B14="FSCB"textNO
H9Expression=$B9="O4M"textNO
H9Expression=$B9="$sA2FS"textNO
H9Expression=$B9="$sW"textNO
H9Expression=$B9="OFP"textNO
H9Expression=$B9="CSOB"textNO
H9Expression=$B9="AA2FS"textNO
H9Expression=$B9="FSCB"textNO
H4Expression=$B4="O4M"textNO
H4Expression=$B4="$sA2FS"textNO
H4Expression=$B4="$sW"textNO
H4Expression=$B4="OFP"textNO
H4Expression=$B4="CSOB"textNO
H4Expression=$B4="AA2FS"textNO
H4Expression=$B4="FSCB"textNO
H31,B3:G500,N22Expression=$B3="O4M"textNO
E8Expression=$B8="$sA2FS"textNO
E8Expression=$B8="$sW"textNO
E8Expression=$B8="OFP"textNO
E8Expression=$B8="CSOB"textNO
E8Expression=$B8="AA2FS"textNO
E8Expression=$B8="FSCB"textNO
D8Expression=$B8="$sA2FS"textNO
D8Expression=$B8="$sW"textNO
D8Expression=$B8="OFP"textNO
D8Expression=$B8="CSOB"textNO
D8Expression=$B8="AA2FS"textNO
D8Expression=$B8="FSCB"textNO
C8,F8:H8Expression=$B8="$sA2FS"textNO
C8,F8:H8Expression=$B8="$sW"textNO
C8Cell Valuecontains "FALSE"textNO
C8,F8:H8Expression=$B8="OFP"textNO
C8,F8:H8Expression=$B8="CSOB"textNO
C8,F8:H8Expression=$B8="AA2FS"textNO
C8,F8:H8Expression=$B8="FSCB"textNO
B3:G7,H31,B9:G500,N22Expression=$B3="$sA2FS"textNO
B3:G7,H31,B9:G500,N22Expression=$B3="$sW"textNO
B3:B7,B9:B500Cell Valuecontains "FALSE"textNO
B3:G7,H31,B9:G500,N22Expression=$B3="OFP"textNO
B3:G7,H31,B9:G500,N22Expression=$B3="CSOB"textNO
B3:G7,H31,B9:G500,N22Expression=$B3="AA2FS"textNO
B3:G7,H31,B9:G500,N22Expression=$B3="FSCB"textNO
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
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

Excel Formula: 
=LOOKUP(2,1/(($B:$B=$J$1)*($C:$C<=EoMonth(Today(),0)),$C:$C))
 
Upvote 0
For some odd reason, this is not working. This is the message that happens:
1693040364329.png
 
Upvote 0
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.
 
Upvote 0
MrDB4Excel said:
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.
Click to expand...
Excel Formula: 
=XLOOKUP(1,($C:$C=$J$2)*($B:$B=$J$1),$D:$D)
 
Upvote 0
MrDB4Excel said:
XLOOKUP(J2,dates,Current_Amount,0)
Click to expand...
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.

Excel Formula: 
=XLOOKUP(1,($C:$C=$J$2)*($B:$B=$J$1),$D:$D)
 
Upvote 0
You must log in or register to reply here.

Similar threads

MrDB4Excel
  • Question
If Find Blank Cell
Replies
0
Views
455
MrDB4Excel
MrDB4Excel
gtd526
  • Solved
vba selecting entire range from offset
Replies
5
Views
221
Tetra201
Tetra201
K
  • Question
Countifs between dates by name
Replies
5
Views
387
kxellis26
K
K
  • Question
sumproduct+countif+sumif
2
Replies
11
Views
451
KRice
K
E
  • Solved
Pivot Table Sorting Dates Out of Order
Replies
3
Views
879
awoohaw
awoohaw

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top