Hello! I have been trying to find a way to make bank reconciliations in QuickBooks quicker by using excel. When I download the bank statements into excel, the transaction descriptions vary, therefore a simple "match" formula won't work. After MANY different combinations of formulas, I found a formula to return the answers that I need, BUT each set of descriptions needs a DIFFERENT formula! Does anyone know a SINGLE formula that would work?
I made a sample spreadsheet to show seven different types of descriptions the bank may give. I put the formula that I used to return the correct answer under the answer. The table printed onto the second page.
Thank you!
May I suggest that you upload your example spreadsheet, in stead of a PDF. Much easier to work with data in an Excel file.
@riny I tried, but I kept getting an error message telling me that the "file type" wouldn't upload. I saved the file as a "macro-enabled" file so hopefully you can open it.
THANKS!
I would create a vendor table as demonstrated in the attached file. Start with the vendors and account numbers you want to 'attach' to the individual transactions. Add a column called 'Alternative' with the same names as in the Vendor column.
Now find the Vendor with your INDEX/MATCH formula. That works very well, though look up from the 'Alternative' column. Use XLOOKUP to return the account number.
When you don't find a match for the vendor name, add a row to the vendor table as shown in the attached file. After a while you will have built a table with alternative vendor names that may occur in the transaction descriptions.
