Vlookup and Trial Balances Function and Example
Use VLOOKUP to compare trial balances. Sometimes you have two (or more) trial balances for different accounting periods which you need to compare. If the list of trial balance accounts changed from one period to another, it may become difficult to match the accounts side by side. This Excel lecture explains how to use VLOOKUP to build a comparable trial balance for two or more periods.
Screen prints and sample Excel files are provided so that you can practice while reading the lecture. Major Excel functions employed in this task are SUM, IF, ISERROR and VLOOKUP.
After some practice, the procedure explained below takes about 5-7 minutes.
The company name and numbers in this lecture are not real and no association with a real company with a similar name, if such exists, should be made.
The steps are outlined below.
1) Obtain trial balances for required periods
The trial balances should preferably be in the same format in Excel. For example, a simple structure may be as follows:
- column A - account number
- column B - account description
- column C - debit balance
- column D - credit balance
In our example, we will build a comparable trial balance for two periods - 12/31/05 and 12/31/06 (you can download the files and use them to follow the steps). The chart of accounts is categorized by the account number. An account number starting with:
- "1" indicates a current asset account;
- "2" - non-current asset account;
- "3" - current liability account;
- "4" - non-current liability account;
- "5" - owners' equity account;
- "6" - revenue account;
- "7" - cost of sales account;
- "8" - other expenses account.
Below you can see a screen print of 12/31/05 file (tab Step 1 in 12/31/05 file):
2) Check to ensure debits and credits balance
Before working with any trial balance, we should ensure that debits equal credits. Let's check that our trial balance files.
First, we want to put all debit and credit balances into one column. Thus, in cell E2 (we labeled column E as "Debit / Credit") we enter a formula to add debits and credits: =C2 + D2. Then we drag the formula to the bottom of the list (tab Step 2 in 12/31/05 file):
Page 1 of 7
Location