Often times your databases that are external to QuickBooks Online will create reports that have data by account or item number instead of customer, vendor, employee or item name. So the question is how do you translate the account numbers to match your names in QuickBooks Online? You can do this very easily by using the VLOOKUP formula in excel.
Here are some step by step instructions on how to create your lookup table in excel to be used in conjunction with the VLOOKUP formula.
- Add a tab to your workbook. In our example we created a tab called Lookup. We created two columns. The first column A, Import File, contains all the account numbers that could be contained in your import file. The second column B, contains the Customer Name as it is stored in QuickBooks.
- Then when you download your data that you want to be imported into QuickBooks Online you would copy it to the first tab of this workbook which is called Import Data.
- On the Import Data tab in your workbook you would then insert a column to the left of the column that contains the Customer Number and populate it with the VLOOKUP formula. Please note that we have written this formula so that the QB Customer field is blank if the VLOOKUP does not find a match the field will be left blank. This is by design because in case you do not notice that this field is blank when you attempt the import Transaction Pro Importer will not let you import this record until you fill a value in.
- Below is a screen shot of the VLOOKUP formula.
You can download the actual excel file so that you can use the formulas here.
You can also try a free trial of Transaction Pro Importer on the Intuit App Center here.
If you have any other Excel fomulas or questions that you would like us to cover please post a comment below.
