The Lookup feature automatically matches the transactions on your statement to your QuickBooks Vendors, Accounts and even Classes (which QuickBooks itself cannot do!). The rules are flexible — they can be quite general to very specific.
The Lookup feature is most useful when you have multiple transactions with the same vendors. The initial setup takes about 15 minutes and the time to refine and maintain the rules depends on how many vendors you have. Let’s see if it would be worthwhile in your situation:
Consider using the Lookup feature if:
Using the Lookup feature might not be worthwhile if:
Matching rules are listed in an Excel worksheet which you maintain, like this:
The program reads down column A and stops at the first match.
A file with sample rules is provided with the program. Here is the sample file which you may download.
To use the Lookup feature, enable it in your conversion settings:
checkmark Use Lookup feature then click in the filename field to select the lookup table. The default is vendor_list.xls:
select Lookup table for each field you want to auto-fill:
Tip: It’s helpful to have QuickBooks open to your Chart of Accounts when updating your rules.
If you already have Vendors in your QuickBooks company, the fast way to get started is to export your Vendors from QuickBooks and copy them into Statement Converter:
In QuickBooks, export your vendor list:
select FILE > UTILITIES > LISTS TO IIF FILES
checkmark Vendor List
Back to Excel, select FILE > OPEN and browse to the file you just saved, QB Vendors.iif.
(NOTE: do not double-click the filename from your hard disk folder to open it; QuickBooks will attempt to open it and display an error).
Select the vendors you wish to add to Statement Converter (e.g. all of them). Select only the data in Column B, no other columns:
Now your QB Vendors are in your lookup table. If you do a test conversion now, many of them will match already!
The last steps are to specify the Distribution Accounts in column C and/or Classes in column D, according to your needs.
Most users enter accounts in column C, but it’s not required to specify an account.
Usually, the account in column C is an Expense account; if it’s any other type, you must enter the account type in column G.
When a transaction is not matched to an entry in the Lookup table, the program will:
To be notified when a transaction does not match a lookup rule, checkmark this option:
Non-matched transactions will be highlighted in yellow and a pop-up message will inform you:
To help you easily update your Lookup rules, non-matched transactions are also saved in a list:
The list is saved in the same folder as your statement.
Working from the list, you can easily adjust your rules. View the list with any text editor, such as NotePad or Word.
You may delete the list file at any time — it’s only for your reference and does not affect the program.
When a transaction does not match a lookup rule, the Vendor/Account/Class name will be assigned according to your conversion settings.
Use one of these options with the Lookup feature:
There is an option for two-part matching rules.
When creating lookup rules, avoid duplicate entries for the same vendor. It is harder to maintain and gets confusing:
BAD — duplicate entries for the same payee/vendor:
Instead, consolidate. Use the text that is shared in common by the transactions:
GOOD — the search text in column A is common to each transaction:
The accounts you enter in column C are considered Expense accounts unless you specify a different type in column G.
Example: To post purchases to a "cost of goods" account or deposits to an "income" account, you must specify "COGS" or "Income" in column G.
If you don’t specify the correct type, when you import QuickBooks will complain, "Can't change the type of an account". If you receive that message, this help article explains how to locate and correct it.
All QuickBooks account types are supported: