MrDB4Excel
Active Member
- Joined
- Jan 29, 2004
- Messages
- 334
- Office Version
- 2013
- Platform
- 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.
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 |
