Slow to Fast 2

This is the second post in the Slow to Fast series, where we are taking incremental steps to improving our reconciliations.

In the first post we improved a purely manual process with conditional formatting. That approach works when the lists are relatively small, on the same sheet, and when we are only concerned with a single column.

In this post, we’ll examine the next item: lookup functions. These will help when the lists are larger, on separate sheets, or when we need to consider more than a single column.

Objective

Let’s quickly confirm our objective.

We have two different lists on two different worksheets. Continuing with our illustration, we are trying to compare our list of checks with the list of checks that cleared the bank.

Our check register may look like this:

And the banking activity we downloaded and stored in a table (Table1) may look like this:

Our goal is to figure out which checks on the check register also appear on the bank download. In addition to confirming that the check number appears, we’d also like to compare the amount. That way, we can determine if the amount the bank deducted from our account agrees to the amount in our check register.

Since the lists are long and appear on multiple sheets, and since we want to compare the amounts, we will use lookup functions.

Video

Details

If we zoom out for a moment, the basic purpose of a lookup function is to (a) find a specific value (eg, check number) and (b) return a related value (eg, amount) from a secondary table. Excel contains many lookup functions, and depending on your version of Excel, you may have some or all of them.

In this blog post, I’ll use a function that has been around for decades so that regardless of your Excel version you can work along. This function is called VLOOKUP.

Note: there are many other options besides VLOOKUP, including INDEX/MATCH, XLOOKUP, FILTER, SUMIFS, and more. If you’d like to watch a short comparison video I put together, check it out here.

Here is another look at our check register:

We want to find the first check number (B7, first argument) in the bank download (Table1, second argument) and return the related amount (2nd column, third argument); and we want it to look for an exact matching value (0, 4th argument). We write the following formula into cell D7:

=VLOOKUP(B7,Table1,2,0)

We hit Enter and bam:

It returns the amount from the bank download … yay! We can fill that formula down, and bam:

We notice that when the check number exists in the bank download (Table1), the formula returns the amount. However, when the check number does not exist, it return the #N/A error.

We could leave the #N/A errors showing if we wanted, but we could clean things up a bit by wrapping the IFERROR function around the VLOOKUP function. The IFERROR function tells Excel to return the result of VLOOKUP when good; but when VLOOKUP returns an error, we can substitute anything else. In our case, we’ll substitute an empty string by using a pair of quotes, like this:

=IFERROR(VLOOKUP(B7,Table1,2,0),"")

We fill the updated formula down and bam … much cleaner:

Now, computing the difference in E7 is a simple subtraction, like this:

=C7-D7

We fill the formula down, and bam:

We could use the IFERROR function again to replace any errors with an empty string like this:

=IFERROR(C7-D7,"")

We fill the updated formula down, and bam:

Conclusion

So, in the first post, we improved the manual reconcilation process by using conditional formatting. In this post, we improved it with a lookup function. But, what if we do this same type of activity on a recurring basis, like every day, week, month, quarter, or year. Well, rather than re-writing lookup formulas over and over, we can use Power Query … and we will explore that in our next post.

Sample file