Get Month from a Column Based on Specific Text in an Adjacent Column

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
348
Office Version
  1. 2013
Platform
  1. Windows
Having tried several options it seems I am missing something.
I need a formula that will check column “C” for the text ‘Balances With * Statement’ and then get the month from that row in column “B”.
Obviously, each time the cell contents showing ‘Balances With * Statement’, the Month is displayed per the formula that resides in each case of ‘Balances With * Statement’ and this seems to be the sticky part in that I need each case of ‘Balances With * Statement’ to return the month shown in column “B” per the row of each instance of ‘Balances With * Statement’ and put that month as the month number in column “G” and as the month name in column “I” which in turn provides data from column “G” into the formula in the relevant row number in column “H” giving the total of each month’s expenses. This formula result in column “H” is then applied elsewhere in another workbook.
Obviously, these are all bogus amounts and some are also bogus expense types but given here to create this bogus sheet which will then be a template for the real sheet that this data is loosely based upon.

PercentCheck.xlsx
ABCDEFGHI
1NUMBER OR CODEDate dd/mm/yyyyyTRANSACTION DESCRIPTIONPAYMENT AMOUNTDEPOSIT AMOUNT or returnsBALANCE
2Currency Conversion Rates Excel
3Beginning Balance from end of 2020>>$10,000.00
4$10,000.001$1,600.00January
5rdJan/06/2021Cash Deposit$1,000.00$11,000.002$11,000.00February
6ebpJan/08/2021January 2021 Health Insurance$100.00$10,900.003$1,900.00March
7ebpJan/08/2021January 2021 Health Insurance$100.00$10,800.004$600.00April
8ebpJan/12/202112-Mobile-Dec$100.00$10,700.00
9ebpJan/14/2021Cellar Storage for January 2021$100.00$10,600.00
10ebpJan/14/2021Electricity for January 2021$100.00$10,500.00
11ebpJan/14/2021Utilities for January 2021$100.00$10,400.00
12ebpJan/14/20212021 Internet-Billing$1,000.00$9,400.00
13bwsFeb/02/2021Balances With January Statement$9,400.00
14olp-dctFeb/01/2021Amazon.com$100.00$9,300.00
15olp-dctFeb/02/2021MONEYGRAM$100.00$9,200.00
16rdFeb/08/2021Cash Deposit$1,000.00$10,200.00
17ebpFeb/08/2021February 2021 Health Insurance$100.00$10,100.00
18ebpFeb/08/2021February 2021 Health Insurance$100.00$10,000.00
19olp-dctFeb/08/2021Amazon.com$100.00$9,900.00
20ebpFeb/10/202101-Mobile-Jan$100.00$9,800.00
21ebpFeb/13/2021Cellar Storage for February 2021$100.00$9,700.00
22ebpFeb/13/2021Electricity for February 2021$100.00$9,600.00
23ebpFeb/13/2021Utilities for February 2021$100.00$9,500.00
24etrFeb/18/2021MONEYGRAM Refund$100.00$9,600.00
25etrFeb/24/2021Amazon.com$100.00$9,500.00
26bwsMar/02/2021Balances With February Statement$9,500.00
27cwFeb/26/2021Cash Withdrawal$10,000.00$500.00
28cwMar/01/2021Payment card fee for Cash Withdrawal$100.00$600.00
29cwMar/07/2021Amazon.com$100.00$700.00
30ebpMar/08/2021March 2021 Health Insurance$100.00$800.00
31ebpMar/08/2021March 2021 Health Insurance$100.00$900.00
32rdMar/08/2021Cash Deposit$10,000.00$9,100.00
33cwMar/08/2021United Tickets$1,000.00$8,100.00
34ebpMar/12/202102-Mobile-Feb$100.00$8,000.00
35ebpMar/12/2021Cellar Storage for March 2021$100.00$7,900.00
36ebpMar/12/2021Electricity for March 2021$100.00$7,800.00
37ebpMar/12/2021Utilities for March 2021$100.00$7,700.00
38cwMar/16/2021Amazon.com$100.00$7,600.00
39etrMar/22/2021Refund from Health Insurance$1,000.00$8,600.00
40etrMar/29/2021Refund from United$1,000.00$9,600.00
41bwsApr/02/2021Balances With March Statement$9,600.00
42ebpApr/08/2021April 2021 Health Insurance$100.00$9,500.00
43ebpApr/08/2021April 2021 Health Insurance$100.00$9,400.00
44rdApr/08/2021Cash Deposit$1,000.00$10,400.00
45ebpApr/08/202103-Mobile-Mar$100.00$10,300.00
46ebpApr/14/2021Cellar Storage for April 2021$100.00$10,200.00
47ebpApr/14/2021Electricity for April 2021$100.00$10,100.00
48ebpApr/14/2021Utilities for April 2021$100.00$10,000.00
49bwsMay/02/2021Balances With April Statement$10,000.00
50ebpMay/08/2021May 2021 Health Insurance$100.00$9,900.00
51ebpMay/08/2021May 2021 Health Insurance$100.00$9,800.00
52rdMay/06/2021Cash Deposit$1,000.00$10,800.00
53ebpMay/10/202103-Mobile-Apr$100.00$10,700.00
54ebpMay/14/2021Cellar Storage for May 2021$100.00$10,600.00
55ebpMay/14/2021Electricity for May 2021$100.00$10,500.00
56ebpMay/14/2021Utilities for May 2021$100.00$10,400.00
2021
Cell Formulas
RangeFormula
G4G4=MONTH(B5)
H4:H7H4=SUMPRODUCT((MONTH($B$5:$B$58)=G4)*($D$5:$D$58))
I4I4=TEXT($B5,"mmmm")
G5G5=MONTH(B13)
I5I5=TEXT($B13,"mmmm")
G6G6=MONTH(B26)
I6I6=TEXT($B26,"mmmm")
G7G7=MONTH(B41)
I7I7=TEXT($B41,"mmmm")
C50:C51,C42:C43,C30:C31,C17:C18,C6:C7C6=TEXT(B6,"MMMM")&" 2021"&" Health Insurance"
C9,C54,C46,C35,C21C9="Cellar Storage for "&TEXT(B9,"MMMM")&" 2021"
C10,C55,C47,C36,C22C10="Electricity for "&TEXT(B10,"MMMM")&" 2021"
C11,C56,C48,C37,C23C11="Utilities for "&TEXT(B11,"MMMM")&" 2021"
C13,C49,C41,C26C13="Balances With "&TEXT(B12,"MMMM")&" Statement"
F52,F49,F44,F39:F41,F32,F24,F16,F4:F5F4=SUM(F3+E4)
F53:F56,F50:F51,F45:F48,F42:F43,F33:F38,F25:F31,F17:F23,F6:F15F6=SUM(F5-D6)
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
I discovered an answer as follows shown here in the Xl2bb post:
Thoughts anyone before I mark this as solved?

PercentCheck.xlsx
GHIJK
3Balances With December
41$1,600.00JanuaryBalances With JanuaryJan/02/2021
52$11,000.00FebruaryBalances With FebruaryFeb/02/2021
63$1,900.00MarchBalances With MarchMar/02/2021
74$600.00AprilBalances With AprilApr/02/2021
85$600.00MayBalances With MayMay/02/2021
2021
Cell Formulas
RangeFormula
G4:G8G4=MONTH(K4)
H4:H8H4=SUMPRODUCT((MONTH($B$5:$B$58)=G4)*($D$5:$D$58))
I4:I8I4=TEXT($K4,"mmmm")
K4:K8K4=XLOOKUP("*"&$J3&"*",$C$3:$C$77,$B$3:$B$77,2)


Slight changes to the original Xl2bb post as follows:
PercentCheck.xlsx
ABCDEF
1NUMBER OR CODEDate dd/mm/yyyyyTRANSACTION DESCRIPTIONPAYMENT AMOUNTDEPOSIT AMOUNT or returnsBALANCE
2Currency Conversion Rates Excel
3Dec/31/2020Beginning Balance from end of 2020>>$10,000.00
4bwsJan/02/2021Balances With December Statement$10,000.00
5rdJan/06/2021Cash Deposit$1,000.00$11,000.00
6ebpJan/08/2021January 2021 Health Insurance$100.00$10,900.00
7ebpJan/08/2021January 2021 Health Insurance$100.00$10,800.00
2021
Cell Formulas
RangeFormula
C4C4="Balances With "&TEXT(B3,"MMMM")&" Statement"
C6:C7C6=TEXT(B6,"MMMM")&" 2021"&" Health Insurance"
F4:F5F4=SUM(F3+E4)
F6:F7F6=SUM(F5-D6)
 
Upvote 0
Hi MrDB4Excel,

How are you using XLOOKUP when your profile says you are using Excel 2013?
 
Upvote 0
Easy. ExcelDna.XFunctions64 found here: exceldna.xfunctions64 at DuckDuckGo
I don't remember exactly what site I visited to get this to work but the above links should help.
Also this: Free XLOOKUP XFuntion Excel Addin For All Versions of Excel
 
Upvote 0

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

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