Compare Two Columns In Excel Using VLOOKUP

Okay, let’s establish the goal. We want to see if one ‘bunch’of data exists within another bunch of data. Now unless you are prepared to spend some time using VBA to do complex scripting, you won’t get a precise result. But that’s okay.

Often we just want to confirm if data exists for investigative reasons. It’s not some complex report or analysis we are building, just a verification and that’s the premise of this quick guide. It’s not a lengthy guide on the subject but by George it will get you some answers!

So, with that said, let’s go forth and use the trusty VLOOKUP to get this done.

So, I’ve got List of Artists 1 and List of Artists 2 (spot your favourites!)

So, the task is to see if artists in List 1 are in List 2 AND vice versa. So first, let’s prepare the space on the sheet. I’m going create a column to the right of each list (right click on col B and select ‘Insert’) and label it ‘Artist exist in other list?’

VLOOKUP To Compare The Two Columns

Now we are going to use the trusty VLOOKUP formula (The ultimate VLOOKUP guide is here if you want to learn more about this wunder formula!).

All we want to do is to enter the following formula into Cell B2

=VLOOKUP(A2,C:C,1,0)

Get Our Guide To The 27 Best Excel Formulas When You Subscribe To Our Awesome Emails 

*We are GDPR compliant and take your privacy very seriously. No spam. Just great content

This formula will check if the contents of Cell A2 (Thom Yorke) exists in List of Artists 2. Once we press ‘Enter’, we can see the result is an #N/A, which is Excel’s error for letting us know it can’t find what we are looking for.

Now, let’s drag this formula all the way down to B15

We can see that ‘Jeff Buckley’ and ‘Tim Buckley’ are the only names in List 1 that appear in List 2.

Doing the same for the other side with the formula =VLOOKUP(C2,A:A,1,0) dragged all the way down, we get:

Again, ‘Tim Buckley’ & ‘Jeff Buckley’

Did We Miss Any Values When Comparing Lists With VLOOKUP?

So far great…BUT!

If I do a visual inspection, I can see that ‘Christopher Wallace’ is in both lists, but his name hasn’t been verified in either with the VLOOKUP, what gives?!

I can reveal that taking a closer look, it appears that the name ‘Christopher Wallace’ in List 2, has an extra space after it!

So, how do we account for this without having to concern ourselves with a visual inspection every time?

Well, this is a classic error that requires a bit of adjusting to the formulas.

We need to use the TRIM formula, which removes leading and trailing spaces from the contents of any cell.

So, for the second formula, we would re-write it like this;

=VLOOKUP(TRIM(C2),A:A,1,0)

So, if we drag the formula all the way down we get ‘Christopher Wallace’ (Highlighted in Yellow)

But what about the other side? Where there is no extra space? Well, we need to adjust the entre range that the VLOOKUP looks in (Column C) with the TRIM Formula.

He way we do that is to use something called an ‘Array Formula’. I won’t get in to the details of an array formula here as it’s quite complex, but it can help us to adjust the entire range within the formula.

So, we write this:

=VLOOKUP(A2,TRIM(C:C),1,0)

BUT, instead of pressing ‘Enter’, we press Ctrl + Shift + Enter at the same time

We end up with a result in B2 that looks like this:

{=VLOOKUP(A2,TRIM(C:C),1,0)}

Excel puts curly braces around the formula, which indicates we are using an array formula.

Now if we drag this down, we get…

This is the result we are after and really, we want to combine the use of TRIMs, so we essentially use the same formula in both columns:

=VLOOKUP(TRIM(A2),TRIM(C:C),1,0) [ctrl + shift + enter]

To see if list 1 entries are in list 2, and

=VLOOKUP(TRIM(C2),TRIM(A:A),1,0) [ctrl + shift + enter]

To see if list 2 entries are in list 1

So there you have a quick and dirty way of comparing two lists with VLOOKUP.

Stop Wasting Time With The WRONG Excel Formulas!!

Our Free Excel Formulas Book will teach you the 27 BEST Formulas that you need for your work. Subscribe to our Excel-lent emails and we’ll send you the book right away!

*We are GDPR compliant and take your privacy very seriously. No spam. Just great content