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.
Please check if the attached sample file works for you.
Thank you very much! This is what exactly I want!
Could you please explain more about the INFA, Index, and Match functions?
Also, I would be pleased if I could put the table on a separate worksheet.
Thank you very much for your excellent work.
=IFNA(IFNA(INDEX(Tabelle2!$C$2:$C$100,MATCH(TRUE,ISNUMBER(SEARCH(Tabelle2!$B$2:$B$100,G5)),0)),INDEX(Tabelle2!$C$2:$C$100,MATCH(TRUE,ISNUMBER(SEARCH(Tabelle2!$B$2:$B$100,B5)),0))),INDEX(Tabelle2!$C$2:$C$100,MATCH(1,ISNUMBER(SEARCH(Tabelle2!$B$2:$B$100,I5))*(Tabelle2!$A$2:$A$100=D5),0)))
You are welcome. If the INDEX and MATCH function highlighted in red returns #NA then the IFNA highlighted in blue evaluates the INDEX and MATCH highlighted in green.
If the INDEX and MATCH function highlighted in green returns #NA then the IFNA highlighted in purple evaluates the INDEX and MATCH function highlighted in grey.
If the INDEX and MATCH function highlighted in grey returns #NA then the whole nested formula returns the #NA error.
Thank you for your response!
I have learned a lot from it!
For Tabelle2, 1 column is for REF1-6 and bank transfer info, and 1 column is for debit amount, right?I have tried to customize the Excel file. These are the changes I have made:
1. Make all Ref 1-6 can be referred
2. Add a new rule: a description will be entered when the credit amount and Ref 3 fulfill certain criteria simultaneously.
https://1drv.ms/x/s!AnmfMbKzoJKpjlv3tsLdtXSh1cuu?e=heCFKJ
However, I cannot figure out how to solve the following problem:
CREDIT and DEBIT refer to the same cell to get the result (e.g. REF2), the output will become 0.We cannot refer to all columns including bank and REF1-6 for simultaneous criteria.
Thank you for your work!
You are welcome. In Tabelle2 one column can be used for either debit or credit. This condition would be for debit
Tabelle2!$B$2:$B$18=$D5
and this would be for credit
Tabelle2!$B$2:$B$18=$C5
In sheet Tabelle2 it refers to column B with either the debit or credit amount and in sheet Tabelle1 it refers to either the entry in the debit or credit column.
In order to reference the bank column and Ref 1-6 we have to add an additional column in sheet Tabelle2 (in my opinion) as shown in the attached file.
The formula in column L in sheet Tabelle1 refers to the bank column and all Ref columns.
Can you add an example in the sample file which shows under which condition you want the output to become 0? I'm not sure i understand what you exactly want to do here:
"CREDIT and DEBIT refer to the same cell to get the result (e.g. REF2), the output will become 0."