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. However, not many of us use VLOOKUP to the full extent. Yes, we say full extent because there is a lot more beyond traditional VLOOKUP. We can do many other things with the VLOOKUP Excel function. So in this article, we will show you ways of comparing two columns of data in Excel using the VLOOKUP functionVLOOKUP FunctionThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Compare Two Columns in Excel Using Vlookup (wallstreetmojo.com)

Compare Two Columns in Excel Using Vlookup (Find Matches)

VLOOKUP is the lookup functionLookup FunctionThe LOOKUP excel function searches a value in a range (single row or single column) and returns a corresponding match from the same position of another range (single row or single column). The corresponding match is a piece of information associated with the value being searched. read more is used to fetch the data often, but not many of us use it as the data of a comparing column.

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

  • First, when the two column’s data is lined up like the below, we can use the VLOOKUP function to see whether column 1 includes column 2. We need to match whether “List A” contains all the “List B” values or not. We can do this by using the VLOOKUP function. So, we must open the VLOOKUP function first. Our LOOKUP value will be the C2 cell value because we are comparing whether “List A” contains all the “List B” values or not, so choose the C2 cell reference. The table array will be “List A” cell values, so select the range of cells from A2 to A9 and make it an absolute cell reference. Next is the “Col Index Num,” i.e., from the selected table array from which column we need the result. Since we have chosen only one column, our “Col Index Num” will be 1. We are looking for the range LOOKUP for an exact match. So, we must choose “FALSE” as the argument, or we can insert 0 as the argument value. We are done with the formula, so we must close the bracket and press the “Enter” key to get the result.

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 words as “Mind Tree Software Co.” (cell A6). So in such cases, the VLOOKUP Excel cannot work.

Partial Lookup Using Wildcard Characters

As seen above, VLOOKUP requires the LOOKUP value to be the same in both “List A” and “List B.” So even if there is any extra space or character, it cannot match the result. But the same VLOOKUP formula can match two columns of 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.

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

Now, we must complete the formula by following the already shown steps.

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

  • You must be wondering how this is possible?It 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 in 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,” a wildcard that matches this short form company name word with the full 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 returned the full result.Note: This wildcard method is not recommended because it can go wrong. So, unless we are sure about the data, please do not use it and rely on it.

Things to Remember

  • VLOOKUP can match if only the LOOKUP is the same as the table array.Table Array.When we use a reference cell or value to search in a group of columns containing data to be matched and retrieve the output in the VLOOKUP table array or vertical lookup, the range we use is called VLOOKUP table array.read moreWildcard character asterisk can match any number of characters if the same string of words is available with a table array.VLOOKUP does not necessarily require all the cell values to be neatly sorted and organized alphabetically.

This article is a guide to Compare Two Columns in Excel using Vlookup. Here, we discuss comparing two columns and finding 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 CriteriaCompare Two Excel Columns for MatchVLOOKUP for Text in ExcelVLOOKUP with Match Function