Forum Discussion
Question about categorizing transactions from bank statement in EXCEL
=IFNA(
IFNA(
IFNA(
IFNA(
IFNA(
IFNA(
IFNA(INDEX(Filling!$A$2:$A$11,MATCH(1,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$K2))*(Filling!$B$2:$B$11=$F2),0)),
INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$H2)),0))
),
INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$I2)),0))
),
INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$J2)),0))
),
INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$K2)),0))
),
INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$L2)),0))
),
INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$M2)),0))
),INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$D2)),0)))In the formula in the bank statement file i've adapted the ranges from e.g. Filling!$A$2:$A$200 to Filling!$A$2:$A$11 because there is only criteria up to row 11 in the Filling sheet.
In addition the INDEX and MATCH highlighted in red is now evaluated in the first step of the whole nested formula. Before it was evaluated in the last step (but only if all other INDEX and MATCH formulas returned an error).
I understand that the formula i suggested earlier works well and i'm glad it does but can't explain how it works at the moment.
Thank you very much for your replies, you are extremely accommodating.
I noted that it is not preferred to do the calculation as a whole column, thank you for your advice.
Yes, the spreadsheet shows almost what I wanted.
In addition, for keyword joe, a loan with different amounts (500 and 124) should be considered as different loans.
The keyword joe may appear in different columns.
The following one should also be considered as a different loan payment
--------------------------------------------
Also, I have some trouble when trying this formula in my office.
I have attached an Excel file (Bank statement - returns 0.xlsx) in which I have changed the figure for reference.
I have entered something like:
=IFNA(
IFNA(
IFNA(
IFNA(
IFNA(
IFNA(
IFNA(
INDEX(Filling!$A$2:$A$200,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$200,$D2)),0)),
INDEX(Filling!$A$2:$A$200,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$200,$H2)),0))
),
INDEX(Filling!$A$2:$A$200,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$200,$I2)),0))
),
INDEX(Filling!$A$2:$A$200,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$200,$J2)),0))
),
INDEX(Filling!$A$2:$A$200,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$200,$K2)),0))
),
INDEX(Filling!$A$2:$A$200,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$200,$L2)),0))
),
INDEX(Filling!$A$2:$A$200,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$200,$M2)),0))
),
INDEX(Filling!$A$2:$A$200,MATCH(1,ISNUMBER(SEARCH(Filling!$C$2:$C$200,$K2))*(Filling!$B$2:$B$200=$F2),0))
)
(Filling is the separate worksheet name)
As the result, the values appeared as 0
I guess because the value in column D goes false, which may affect the rest of the formula.
I also tested the loan payment and found out that the loan payment cannot be differed by different debit amounts.
I would be pleased if you could advise how to modify the formula in this situation.
I truly appreciate your help.
=IFNA(
IFNA(
IFNA(
IFNA(
IFNA(
IFNA(
IFNA(
IFNA(
IFNA(
INDEX(Tabelle2!$A$2:$A$21,MATCH(1,ISNUMBER(SEARCH(Tabelle2!$C$2:$C$21,$H5))*(Tabelle2!$B$2:$B$21=$C5),0)),
INDEX(Tabelle2!$A$2:$A$21,MATCH(1,ISNUMBER(SEARCH(Tabelle2!$C$2:$C$21,$I5))*(Tabelle2!$B$2:$B$21=$D5),0))),
INDEX(Tabelle2!$A$2:$A$21,MATCH(1,(Tabelle2!$B$2:$B$21=D5)*ISNUMBER(SEARCH(Tabelle2!$C$2:$C$21,$F5)),0))),
INDEX(Tabelle2!$A$2:$A$21,MATCH(1,(Tabelle2!$B$2:$B$21=D5)*ISNUMBER(SEARCH(Tabelle2!$C$2:$C$21,$G5)),0))),
INDEX(Tabelle2!$A$2:$A$21,MATCH(1,(Tabelle2!$B$2:$B$21=D5)*ISNUMBER(SEARCH(Tabelle2!$C$2:$C$21,$H5)),0))),
INDEX(Tabelle2!$A$2:$A$21,MATCH(1,(Tabelle2!$B$2:$B$21=D5)*ISNUMBER(SEARCH(Tabelle2!$C$2:$C$21,$I5)),0))),
INDEX(Tabelle2!$A$2:$A$21,MATCH(1,(Tabelle2!$B$2:$B$21=D5)*ISNUMBER(SEARCH(Tabelle2!$C$2:$C$21,$J5)),0))),
INDEX(Tabelle2!$A$2:$A$21,MATCH(1,(Tabelle2!$B$2:$B$21=D5)*ISNUMBER(SEARCH(Tabelle2!$C$2:$C$21,$K5)),0))),
INDEX(Tabelle2!$A$2:$A$21,MATCH(TRUE,ISNUMBER(SEARCH(Tabelle2!$C$2:$C$21,$G5)),0))),
INDEX(Tabelle2!$A$2:$A$21,MATCH(TRUE,ISNUMBER(SEARCH(Tabelle2!$C$2:$C$21,$B5)),0)))
You are welcome. Thank you for your examples. I've updated the formula in column E of sheet Tabelle1 in the attached file. The formula now distinguishes the different payment amounts from Joe. And "other loan - 345" is returned in cell E11.
I'll take a look at the Excel file (Bank statement - returns 0.xlsx) tomorrow and communicate the result.
Thank you very much for your kind support!
I have tried the formula in the office's spreadsheet, and it works perfectly.
I will keep in mind that the range for searching the separate worksheet should be limited to the rows inputted something, thank you for reminding me.
You move INDEX(Filling!$A$2:$A$11,MATCH(1,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$K2))*(Filling!$B$2:$B$11=$F2),0)), to the beginning, this makes sure that "debit" and "REF4" column will be matched in the first place, so it won't be obstructed be other functions. Am I correct?
I will look into the details later to have a better understanding to this formula.
Once again, thank you very much for your super-patient and sincere support. I have learned a lot from it, and it facilitates my office work. Thanks a lot.
=IFNA(
IFNA(
IFNA(
IFNA(
IFNA(
IFNA(
IFNA(INDEX(Filling!$A$2:$A$11,MATCH(1,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$K2))*(Filling!$B$2:$B$11=$F2),0)),
INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$H2)),0))
),
INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$I2)),0))
),
INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$J2)),0))
),
INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$K2)),0))
),
INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$L2)),0))
),
INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$M2)),0))
),INDEX(Filling!$A$2:$A$11,MATCH(TRUE,ISNUMBER(SEARCH(Filling!$C$2:$C$11,$D2)),0)))In the formula in the bank statement file i've adapted the ranges from e.g. Filling!$A$2:$A$200 to Filling!$A$2:$A$11 because there is only criteria up to row 11 in the Filling sheet.
In addition the INDEX and MATCH highlighted in red is now evaluated in the first step of the whole nested formula. Before it was evaluated in the last step (but only if all other INDEX and MATCH formulas returned an error).
I understand that the formula i suggested earlier works well and i'm glad it does but can't explain how it works at the moment.
- Thank you for the modified formula!
I will study the new formula in detail later but do not understand it well.
It seems that the look-up array of match functions becomes (Tabelle2!$B$2:$B$21=D6)*ISNUMBER(SEARCH(Tabelle2!$C$2:$C$21,$F6))
which column D is matched with all columns.
I have tested if I enter something N/A in the B column, and some value should be matched in F-K column, the function works perfectly, but I am not sure of the mechanism of it.
I would be pleased if you could explain how it works.
I apologize for bothering you too much.
I am looking forward to the Bank statement - returns 0.xlsx solution.
Again, thank you very much for your hard work and patience.