VLookups are valuable when you have data in two places, but need to link the two.
An example in our general ledger might be when you have to report by some alternate account numbers. In the government accounting world, you are sometimes asked to report to a funding agency using their prescribed numbers.
In our sample GL data:
To the right of our example (Column D), we want to put our new account number. In a separate tab named “New Accounts”, we will have all of our account numbers with their related new account numbers. See the layout:
Back in the tab with our GL data, in cell D2, type “=VLOOKUP(A2,’New Accounts’!A:D,3,FALSE)“. Let’s look at the pieces of the formula arguments:
- Lookup_value – This is the common part of both lists of data. In our case, it’s our account number.
- Table_array – This is where our new data is located. For us, it’s the tab “New Accounts”, and we have columns A through D (or A:D).
- Col_index_num – We have 3 here because we are choosing the third column of data from New Accounts. This is where our new account number is located.
- Range_lookup – I always put “FALSE” here. This means it must find an exact match in the new data.
I have copied the formula all the way down, here is the final product:
The next step might be grabbing the New Account Description (the forth column in our lookup data). In cell E2, that formula would just be “=VLOOKUP(A2,’New Accounts’!A:D,4,FALSE)“. The only difference in our formula above is the “4“, instead of “3“.
Once you have connected your existing GL data with the new account numbers, you could do some pivot tables to create income statements, balance sheets, etc.
One very important thing to know about this formula is the “Lookup_value” must have the common value (our account number) as the first field in the data we are connecting. This can sometimes require us to move our columns around.