Summary
Sometimes a GL provided will not have all the needed data, and it will be necessary to look up and pull in the needed data from another file, for example, if there are only account descriptions and no account numbers.
Resolution
Take the following excerpt as an example:
The first thing to do in this situation is to ask the client for another export with account numbers. Account ID is a required field in MindBridge.
But sometimes it is not possible to get account numbers, because that is how the client keeps their books. If you can get a chart of accounts (or trial balance) with account numbers and descriptions with a similar format to the following example, you can use INDEX()/MATCH() formulas to pull the account numbers into the General Ledger file.
Bring the Chart of Account worksheet into your GL workbook, then add an Account ID column (column F below).
In cell F2,
=INDEX(ChartOfAccounts!$A:$A,MATCH(C2,ChartOfAccounts!$B:$B,0))
matches the account description in C2 in column B of the ChartOfAccounts worksheet, then pulls the corresponding value in column A of that row.
Note: This does something similar to VLOOKUP(), except that it is more flexible in not requiring the column where you are looking up the known value (account description in this case) to be to the left of the column where you are pulling the corresponding value (account number in this case), and the formula automatically updates itself even if columns in the chart of accounts are moved around or additional ones are inserted.
If you are unable to get any mapping of account description to account number, you will need to map the account description to Account ID. In this case, make sure that the account descriptions are consistent across all files (i.e., they appear in exactly the same way in the GL, TB, and chart of accounts files).
Anything else on your mind? Chat with us or submit a request for further assistance.