Nearly seven (seven!) years ago, I wrote a post that helped people compare two or more lists in Excel with ISNA and MATCH. It’s the second most popular post on Big Hungry Geek, right behind my post about merging worksheets in Excel, which has been the number one fan favorite for several years running.

Since that time, I took a brief hiatus from writing and, unfortunately, from working on Big Hungry Geek. That brief hiatus grew and grew and grew until it spanned multiple years and became what most reasonable people would call total abandonment. I guess you might say I stopped writing altogether. I went dark. I will investigate the reasons behind this extended leave of absence in a future post, but for now, suffice to say it’s been a long time. So let’s get to it.

The other day, early in the morning on the Fourth of July (of all days), for reasons unknown, I decided to browse through some of my older posts. As I read to the end of my post about comparing lists with ISNA and MATCH, I kept going and began reading through some of the comments left by my readers several years ago. Most of the comments were questions, but a few industrious readers took the time to share other tips or suggestions or alternative, more natural ways to accomplish the same thing.

On August 19, 2014, at 11:35 pm, a reader posted the following comment under the handle 09755:

Hi Ryan,

Another way of comparing two lists, is doing two vlookups:

Vlookup #1: Looks up individual line items from List A in List B. Error Values mean “item in List A but not in List B”

Vlookup #2: Looks up individual line items from List B in List A. Error Values mean “items in List B but not in List A”

I use this very often. Just quickly do the vlookups and then filter cells with error values. Though this method does get cumbersome when you are comparing more than two lists.

One of my favorite parts about writing for Big Hungry Geek is knowing that I might have helped someone, somewhere, figure something out, answer a question, or learn something new, whether that’s for work or fun (or both). Another favorite part is when total strangers emerge briefly from wherever they are in the world and contribute their perspective or experience or know-how to this online community by leaving helpful comments for other strangers. That is so cool, and I appreciate it so much. It warms my heart.

Okay, so anyway – for those of you who are just here to learn how to compare two lists in Excel using VLOOKUP, here we go.

How it’s done

1. Arrange the lists in columns, leaving at least two blank columns in between. I chose column A for List A and column D for List B.
2. Label the columns next to each list “Missing?” In this example, columns B and E are each labeled “Missing?”
3. In cell B5 enter the formula: =VLOOKUP(A5,D:D,1,FALSE) and press Enter.
   
4. Select cell B5 and navigate to Home → Conditional Formatting → Highlight Cell Rules → Text that Contains…
5. In the Text That Contains dialogue box, click the drop-down next to Specific Text, select Errors, and click OK.
6. Select cell B5 down to the end of List A and press Ctrl + D (command + D for MacOS users) to copy the formula and conditional formatting down the entire List A.
7. In cell E5, enter the formula: =VLOOKUP(D5,A:A,1,FALSE) and press Enter.
8. Repeat steps four through six for cell E5, making sure to copy the formula down the entire List B.
   

The result

If any value from List A is missing from List B, a red #N/A will appear next to that value in List A’s “Missing?” column. If any value from List A is found in List B, that value will reappear in List A’s “Missing?” column.

#N/A = value is missing from the other list. Otherwise, the value is not missing from the other list.

You could take it one step farther by filtering either list to display only error values, which would then only show the values missing from the other list. Be careful only to filter one list at a time.

How and why it works

VLOOKUP is pretty damn useful. It’s one of my favorite go-to Excel functions, mostly because it can be used in so many ways to make life easier. If you aren’t that familiar with VLOOKUP, or maybe could use a quick refresher on what it does or see a few different ways in which it can be used, I’ve got you covered.

The formula

=VLOOKUP(lookup_value,table_array,col_index_num,match_type)

VLOOKUP has three required arguments, or inputs, and one optional argument:

• lookup_value what do you want Excel to lookup or find?
• table_array where should Excel look? VLOOKUP is typically used with a table having multiple columns, however in our example, A:A and D:D selects those entire columns, which are essentially one column tables.
• col_index_num which column in the lookup table contains the search result? How do you know what number to enter here? Simply count over from the first column in the lookup table, starting with 1. In our example, A:A and D:D selects those entire columns, so we just enter 1 to tell Excel to look in the first and only column.
• match_type what type of a match Excel should find? Do you want an exact match to the lookup value, or is an approximate match okay? If you want an exact match, enter FALSE or 0 here. If you’re okay with an approximate match, enter TRUE or 1. This argument is optional but will default to TRUE or 1 if you leave it out. I usually want an exact match, and in our example we definitely want an exact match, so enter FALSE here.

Practice yourself

The best way to learn is to practice yourself, so click the link below to download the Excel workbook used to show the methods described in this post.

Compare two or more lists with VLOOKUP