Questions? We have answers.

Data Formatting Guide: Looking up and Retrieving Data from Another File

Article author
Kate H
  • Updated

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:

mceclip1.png

 

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:

mceclip0.png

 

Bring the Chart of Account worksheet into your GL workbook, then add an Account ID column (column F below).

 

mceclip2.png

 

In cell F2,

=INDEX(ChartOfAccounts!$A:$A,MATCH(C2,ChartOfAccounts!$B:$B,0))

matches the account description in C2 in column B of ChartOfAccounts, 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?
Log in to MindBridge to chat with us, submit a request, or reach out to your assigned Customer Success Manager.


Related Articles

 

 

 

 

 

 

 

Share this:

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.