Reconciliation: FIND & MATCH function

While Performing bank reconciliation based on CSV / excel bank statements downloaded from banks, there will likely be a need for one to tag and group the transactions accordingly to facilitate the comparison with booked entries.

E.g All those transactions with "PAYR" appearing in the description field should be TAGGED as payroll; all those with "8233500" in description are e-commerce sales transactions credited to bank account after deduction of respective Credit Card Fees (Merchant Fees) thus should be tagged accordingly...etc

To perform this tagging, which is rather similar to VLOOKUP , will require a table with two columns to be set up. One column for the keywords to search for in the description field; the other column for the TAGGING to be applied upon locating the searched keyword.

The formula to be utilized in this case would be a combination of OFFSET and FIND (or SEARCH if non case -sensitive) as per below. Do note that its an array formula which requires CTRL+SHIFT+ENTER

An example worksheet is as per attached for reference to better understand how it roughly works. Hope it helps.

Download File