We need to use the TRIM formula, which removes leading and trailing spaces from the contents of any cell. 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.’ 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, why is it so? I can reveal that after 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 can see that ‘Jeff Buckley’ and ‘Tim Buckley’ are the only names in List 1 that appear in List 2. Now, let’s drag this formula all the way down to B15. Once we press ‘Enter’, we can see the result is #N/A, which is Excel’s error for letting us know it can’t find what we are looking for.
All we need to do is to enter the following formula into cell B2 = VLOOKUP(A2,C:C,1,0) This formula will check if the contents of Cell A2 (Thom Yorke) exist in the List of Artists 2. I’m going to create a column to the right of each list (right-click on column B and select ‘Insert’) and label it ‘does Artist exist in other lists?’ VLOOKUP to Compare the Two Columns Now, we are going to use the trusty VLOOKUP formula. At first, let’s prepare the space on the sheet. I’ve got a List of Artists 1 and a List of Artists 2 (spot your favorites!) Well, the task is to see if artists in List 1 are in List 2 AND vice versa. It’s not a lengthy guide on the subject, anyhow George will answer your queries! So, with that said, let’s go forth and use the trusty VLOOKUP to get this done.
It’s not some complex report or analysis we are building, it is just verification and that’s the premise of this quick guide. Simply, we just want to confirm if data exists for investigative reasons. Now, unless you are prepared to spend some time using VBA to do complex scripting, you won’t get a precise result. We want to see if one ‘bunch’ of data exists within another bunch of data. Özet: Compare Two Columns In Excel Using VLOOKUP Okay, let’s establish the goal.