Using VLOOKUP function to detect new lines of data

Let’s say you have a Balance Sheet that you update each month with data from your accounting system. The thing that always trips you up is when a new GL account has been added and it isn’t already in your Balance Sheet. If you’ve got a lot of accounts, this can be a daunting task to do manually.

Using the VLOOKUP function, you can do it very quickly. What you need to do is lookup each account in the new data and see if that account exists in your Balance Sheet. Using the above example, we’ll put formulas next to the new data. Starting in cell D10, the formula to use is =VLOOKUP(A10,$A$3:$A$6,1,FALSE) This tells Excel to look up the GL account (cell A10) in the list of GL accounts in our Balance Sheet (i.e., range A3:A6). The dollar signs make the reference absolute, which means they won’t change if we copy the formula. The 1 means to return the value in the first column in our range, which is only one column wide anyways. The FALSE parameter means that the match must be exact. If you set this to TRUE or omitted it, it will return the closest match, which is not what we want. Now just copy the formula down for each of the new data rows.

TIP: The quick way to add the $ signs is to press the F4 key right after you enter that parameter. (Note that multiple pressing of F4 will cycle you through all the possible $ sign uses, so you can, say, make just the column or just the row absolute.)

You will now see the #N/A error for the GL that does not exist in the Balance Sheet. It’s a pretty simple formula and you can do this analysis in just a few seconds really.

Now, if you want to get elegant, you can use an IF or IFERROR function to return something like “NOT HERE”, but since it’s a throw-away one-time deal, why waste the time.

Leave a Reply

Your email address will not be published. Required fields are marked *