Tips for Excel, Word, PowerPoint and Other Applications

Using VLOOKUP to Match Lists

Why It Matters To You

There are times when you need to match lists against lists, e.g., Fleet vehicle drivers against Business Segment, roster of names vs HR database, etc). I suppose you could manually sort your list and then manually tag all of your matching criteria (e.g., PS codes), but this could result in hours of work if your data set is large. We could speed things up a bit using a VLOOKUP formula. This offers 2 advantages: 1) it's faster (which means I go home earlier) and 2) it's dynamic (which means that I can train my boss to do some of the work).

Remember, in our last Tip, we talked about VLOOKUPs and how flagging the formula to only return exact matches would result in an #NA error value if no match was found. This is valuable information we can use to our advantage.

Dynamic? What does making a list dynamic do for you? Well, let's say I wanted to identify, within a list, the employees who belonged to a series of Accounting codes. If I were flagging them manually, this means that everytime someone decided to add or delete a PS code, I have to go back to the master list and remove or add flags. Depending on the size of the list, it may be very difficult to 1) make sure I got 100% of the matches and 2) it's hard to make a linked list on which others can comment. If I use a dynamic formula (e.g., the VLOOKUP), then adding or deleting list values takes place in a relatively simple, easy-to-manage table, one that I could even e-mail to my boss for input.

We're also starting to combine topics we've discussed in previous tips … so you can see how everything builds on previous topics.

Data Sources and Exercise Files

  1. matching.xls - Contains my sample fleet inventory report and a table of Account Codes to match against.

How To ...

Let's visit pretend land for a minute and say that I work for a service company that has a large fleet of vehicles. Late Friday afternoon, the COO "asks" for a list of vehicles belonging to the Home Delivery Division (he gives me a list of Account Codes) and he'd like it by Monday morning. Hmmmm, I think. Only 1,000 vehicles, shouldn't be a problem. So the first thing I do is pull up the latest Fleet Inventory Report and review my source data set to better understand how the data is laid out.

  1. First, I recognize that the Account Code resides in the middle of the vehicle ID#, so in column C, I used a MID formula to extract the Account Code. At this point, I could also use a pivot table to count vehicles by Account Code, but I'd still have to do some matching against the list of Departments the COO gave me.
    2. =MID(B5,4,6)
  2. I then created a simple 1-column table of the Account Codes I want to filter for in 'LOOKUP Table'
  3. I use a simple VLOOKUP, in Column D, to lookup the Account Code associated with each vehicle ID on the table of Account Codes I'm filtering for in 'LOOKUP Table'. Notice, that I'm searching on the entire B column, instead of a discrete range of cells (e.g., B4:B15) since I may want to add more PS codes at a later date.
    4. =VLOOKUP($C5,'LOOKUP Table'!$B:$B,1,FALSE)
  4. Remember, from the Introduction to VLOOKUP article, if you search for exact matches, an error value of #NA is returned when no match is found.
  5. As you can see, this gets me close, but I don't really like the error values showing like that, so in Column E, I modified the formula a little with an IF and ISERROR function. Basically, the ISERROR tests whether the result of the VLOOKUP is an error (no match) or not (match). The IF converts this information into a text message of "match" or "no match". We covered the basics of this in the VLOOKUP and IF/ISERROR combos article.
    6. =IF(ISERROR(VLOOKUP($C5,'LOOKUP Table'!$B:$B,1,FALSE)),"No Match", "Match")
  6. This formula now results in a nice column of values reporting "match" or "no match" which I can then use with COUNTIF and/or conditional formatting or a pivot table to derive some summary statistics of the number of matching codes.

That's it

Now wasn't that simple?

Notes

Last updated8/15/08
Application VersionExcel 2003
AuthorMichael Kan
Pre-requisites Comparing Two Lists in Excel
Cell Parsing: LEFT, MID, RIGHT
Introduction to VLOOKUP
Introduction to Logical Statements and Logical Math
Excel Error Messages
Related TipsTBD