Nov 24, 2014
You may remember that in my last blog post
, I talked about how to do a VLOOKUP
in Microsoft Excel
. Today, we are going to use VLOOKUPS to compare and combine two lists of information that aren't the same length. This will allow us to find those list items that are in one list but not in the other.
I’d recommend reviewing that blog before going through this one.
For example, you might have a full list of products, but have a shorter list of current products and you want to grab a column out of the full list (say the price) and put it into the current list.
Another example from my real life, was a company that had a full list of all their clients, who also had a shorter list of the client’s email addresses for the clients that had given an email address (some clients hadn’t). The company wanted to combine the two lists to find out which clients they didn’t have an email address for.
My third example involves an accounting department who have a list of all the invoices sent out and also have a smaller list of all the payments received. They want to combine these two lists, to make sure that those people who paid their invoice actually paid the right amount, and they want to find out who didn't pay their invoice (the invoice is in the full list but not in the smaller list of those who paid).
Here are my two lists:
This is my full list of information. It is a full list of people in the company. This list is actually 94 rows long.
Here is my smaller list of the people who actually got paid this pay run. This list is smaller, it is only 63 rows long. Notice if you look at the list that person number 4 didn't earn any pay, so it doesn't appear in the smaller list. Also, notice that my two lists are on different worksheets of the same workbook.
To use this technique, the two lists need to have something in common. The two lists have their own column and both have the NUM information in them. In your work this would be something like 'Item number', 'Invoice number' and so on.
By clicking in D5 of the full list, here is where I’m going to do my VLOOKUP.
I type in: =VLOOKUP(
Just like we did in the last blog and we get the blue helper text:
is the common denominator from both tables. In this case it is the first person’s NUM which is in cell A5, which is what we put into the formula.
To separate the bits we type a comma and we are ready to do the second piece. The second piece is the table_array
and this is the other list in the other worksheet, we click over to the other sheet and click and drag to select the entire list, in this case: 'Current List'!A1:I64.
Before we finish with this piece, we need to do a little something. As we are going to copy this VLOOKUP formula down once we have completed it, we need to stop the area we have just chosen from sliding down and changing. So we put $ in front of all the letters and numbers of the cell references. Like so: 'Current List'!$A$1:$I$64
We type a comma and then put in the third piece of the VLOOKUP which is the col_index_num
. Looking at the smaller list (not the full list) we count across from the left to find the piece of information we want back. I’m going to get the GROSS PAY column back which is column number 9 (the ninth column from the left), so I type a '9'.
We type a comma to separate the pieces and the last piece is range_lookup
, which is really asking, ‘do you want the nearest one? Or do you want exact values?’
If we want exact values, we put the word FALSE
. We don’t want the nearest one because if person number 4 isn’t there we don’t want person number 3’s information. We want an error letting us know that person number 4 isn't there.
So the formula looks like this:
We press Enter and the VLOOKUP should give person 1’s gross pay. We have taken information from the smaller list and combined it into the full list.
We copy the formula down and we get this:
Now you can see the real beauty of this. We can easily see the relevant information for those people who are in the smaller list and we see an error (#N/A) for those people who aren’t in the smaller list. The VLOOKUPs are comparing the two lists and making it obvious which people are in the full list but aren’t in the smaller list.
This really is a classic use of VLOOKUPs and many people in many industries use VLOOKUP for this purpose. I hope you can make use of it too!