WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Compare Two Columns in Excel Using Vlookup

Compare Two Columns in Excel Using Vlookup

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

VLOOKUP to Compare Two Columns in Excel & Find Matches

When it comes to comparing one thing with another in excel, lookup functions are the kings, and VLOOKUP is the household formula for all excel users. Not many of us use VLOOKUP to the full extent; yes, I say full extent because there is a lot more beyond traditional VLOOKUP, and we can do many other things with VLOOKUP. So in this article, we will show you ways of comparing two columns of data in excel using the VLOOKUP function in excel.

Compare Two Columns in Excel Using Vlookup

Compare Two Columns in Excel Using Vlookup (Find Matches)

VLOOKUP is the lookup function used to fetch the data often times but not many of us use it as the data of a comparing column.

You can download this VLOOKUP to Compare Two Columns in Excel Template here – VLOOKUP to Compare Two Columns in Excel Template
  • When the two columns data is lined up like the below, we will use VLOOKUP to see whether column 1 includes column 2 or not.

Compare Two Columns in Excel Example 1

  • We need to match whether “List A” contains all the “List B” values or not; this can be done by using the VLOOKUP function. Open the VLOOKUP function first.

Compare Two Columns in Excel Example 1-1

  • Out lookup value will be the C2 cell value because we are comparing “List A” contains all the “List B” values or not, so choose C2 cell reference.

Compare Two Columns in Excel Example 1-2

  • The table array will be “List A” cell values, so select the range of cells from A2 to A9 and make it as absolute cell reference.

Compare Two Columns in Excel Example 1-3

  • Next up is “Col Index Num,” i.e., from the selected table array from which column we need the result. Since we have selected only one column, our “Col Index Num” will be 1.

Compare Two Columns in Excel Example 1-4

  • Range Lookup is we are looking for an exact match, so choose FALSE as the argument, or you can enter 0 as the argument value.

Example 1-5

  • Ok, we are done with the formula; close the bracket and hit the enter key to get the result.

Example 1-6

So, wherever we have got “#N/A” that means those values do not exist in the “List A” column.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

But look at the row number 7 value in “List B” is “Mind Tree,” but in “List A,” the same company name is written in full word as “Mind Tree Software Co.” (cell A6). So in such cases, VLOOKUP cannot do anything.

Partial Lookup Using Wildcard Characters

As we have seen above, VLOOKUP requires lookup value to be exactly the same in both “List A” and “List B.” Even there is any extra space or character; it cannot match the result. But the same VLOOKUP formula can match two columns data if we provide wildcard characters for the lookup value.

So, that wildcard character is an asterisk (*), while providing lookup value before and after the lookup value, we need to concatenate this wildcard character.

Using Wildcard Characters 1

As you can see above, I have concatenated lookup value with special wildcard character asterisk (*) before and after the lookup value using the ampersand (&) symbol.

Using Wildcard Characters 1-1

Now complete the formula by following already shown steps.

Now, look at the results; in the previous example, we have got errors in rows 2 & 7, but this time we have got a result.

  • You must be wondering how this is possible?
  • This is mainly because of the wildcard character asterisk (*). This wildcard matches any number of characters for the provided value. For example, look at the value of is C3 cell. It says “CCD,” and in cell A5, we have the full company name as “Coffeeday Global Ltd (CCD).” Because in the table array, we have the word “CCD,” wildcard matched this short form company name word with the entire company name in “List B.”
  • Similarly, in cell C7, we have the company name “Mind Tree,” but in “List A” (A6 cell), we have the full company name as “Mind Tree Software Co,” so there are extra characters in “List A.” Since we have provided wildcard character, it has matched the remaining portion of a word and return the full result.
  • Note: This wildcard method is not the recommended method because it can go wrong at any time. So unless you are sure about the data you have, do not use this and rely on it.

Things to Remember

  • VLOOKUP can match if only the lookup is exactly the same as in the table array.
  • Wildcard character asterisk can match any number of characters if the same string of words available with table array.
  • VLOOKUP does not necessarily require all the cell values to be neatly sorted and organized alphabetically.

Recommended Articles

This has been a guide to Compare Two Columns in Excel using Vlookup. Here we discuss how to compare two columns and find matches in excel using VLOOKUP and wildcard characters along with examples and a downloadable excel template. You may also look at these useful functions in excel –

  • Index+Match to Match Multiple Criteria
  • Compare Two Excel Columns for Match
  • VLOOKUP for Text in Excel
  • VLOOKUP with Match Function
8 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VLOOKUP to Compare Two Columns in Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More