
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.

=VLOOKUP(A5To 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:
=VLOOKUP(A5,’Current List’!$A$1:$I$64,9,FALSE)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.


How do your Excel skills stack up?
Test NowNext up:
- Fabulous, fitting feedback
- Apply permissions on web parts in SharePoint
- Scheduling Rostered Days Off (RDO) in Project 2013
- ‘CONNECT ANY DATABASE’ in SQL Server 2014
- Using the ‘Flash Fill’ feature to apply the desired formatting in Excel 2013
- Here’s how situational leaders spend more time watching sunsets than watching their people
- Save time with print presets in Adobe InDesign CS6
- Normalising your database: First Normal Form (1NF) – Part 1
- Change the Spell Check Language on all slides in PowerPoint using VBA (2007 onwards)
- Fruitcakes and lines in the sand
Previously
- The Windows Server 2012 R2 Desktop Experience
- Removing the background from a picture in Microsoft Office
- Deliver successful organisational transformation
- Using SharePoint to create a ‘Team Based Master Calendar’
- Control your digital world from your mobile device
- Charts that aren’t charts
- Using Delegates in .NET
- More haste, less speed
- Active Directory Administrative Center (ADAC) Updated
- How to create a template in Microsoft Outlook 2010 and 2013