Question about categorizing transactions from bank statement in EXCEL

I have a problem of categorizing every transaction in the bank statement for bookkeeping purposes. As shown below, I have to input the Description for every item, and every item has to refer to d...

excel

Formulas and Functions

OliverScheurich
May 23, 2024
udonlover
=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.
bank statement.xlsx13 KB

udonlover
Copper Contributor
May 19, 2024
OliverScheurich
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.
- OliverScheurich
  Gold Contributor
  May 19, 2024
  udonlover
  =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.
  Question about categorizing transactions from bank statement in EXCEL.xlsx10 KB
  - udonlover
    Copper Contributor
    May 20, 2024
    OliverScheurich
    
    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!

Forum Discussion

Question about categorizing transactions from bank statement in EXCEL

Resources