What Is VLOOKUP To Compare Two Columns In Excel & Find Matches?
The option to compare two columns in Excel using VLOOKUP enables one to find the matches between two columns of data. And a match indicates a data point from one column appears in the other.
Users can compare two columns in Excel using VLOOKUP to find duplicates and similarities between massive lists required to make decisions in Data Analytics.
For example, the table below shows the eligible candidates for Level I and II Tests at a school in columns A and C.
The requirement is to determine the Level I Test eligible candidates who are also eligible for the Level II Test and display the output in column E.
Then, we can compare two columns in Excel using VLOOKUP formula in the target cells and achieve the required output.
In the above, compare two columns in Excel using VLOOKUP formula example, the Excel VLOOKUP function accepts the column A cell value as the lookup value. It checks the name specified in the column A cell, in the given lookup range C2:C8 containing the Level II Test eligible candidates. And as we require the value corresponding to the lookup value in the same row as the match in the lookup range C2:C8 from the same range, the third argument is 1. Next, as the match must be exact, the last argument is 0.
Thus, if we execute the function in a target cell and it finds a match for the specific Level I Test eligible candidate from column A in the Level II Test eligible candidates list in column C. Then, the function output in the target cell will be the Level II Test-eligible candidate name. Otherwise, the function output will be the #N/A error.
Table of contents
Key Takeaways
- The compare two columns in Excel using VLOOKUP helps us compare two columns of data for matches. And the function output can be the value corresponding to the lookup value matched in the lookup range or the #N/A error value. While a match indicates a value from one column is present in the other column, the error value #N/A indicates the concerned value in one column is not present in the other.
- Users can compare two Excel columns using the VLOOKUP() to find exact or approximate matches between two large lists in financial modeling.
- The VLOOKUP() accepts three mandatory arguments and one optional argument. While the lookup_value argument will be the lookup value from one column, the table_array argument will be the data range in the other column. And the col_index_num argument will be 1. On the other hand, the last argument, range_lookup, will be TRUE (or 1) or FALSE (or 0) based on whether we require the VLOOKUP() to perform an approximate or exact match.
- We can use Wildcard characters, ‘*’ and ‘?’, to compare two Excel columns using the VLOOKUP() based on a partial match.
How To Compare Two Columns In Excel Using VLOOKUP (Find Matches)?
The steps to compare two columns in Excel using VLOOKUP to find matches are as follows:
- First, ensure the data in the two columns to compare are accurate and valid, without unwanted leading and trailing spaces.
- Please choose the first cell of the target column, where we need to display the columns comparison.
- Type =VLOOKUP( in the cell. [ Alternatively, type =V or =VL and double-click the VLOOKUP function from the Excel suggestions.]
- The function accepts four argument values, lookup_value, table_array, col_index_num, and range_lookup. So, enter the function argument values and close the bracket. In this scenario, the lookup_value will be a value from one column, for which we must find a match in the table_array, which will be the other column range. And as the table_array contains only one column, the col_index_num will be 1. Finally, we typically aim to find an exact match for the specified lookup value. Hence, the last argument, range_lookup, will be False (0).
- Press Enter to execute the VLOOKUP().
- Copy the formula or iterate steps 2 to 5 in the remaining target cells to view the output for the two columns comparison for matches.
Furthermore, we can compare two columns in Excel using VLOOKUP and highlight differences.
The differences in the two columns comparison indicate those values, which are present in only one of the two columns. And in such scenarios, the VLOOKUP() will return the #N/A error value in the corresponding target cells.
So, we can use the VLOOKUP() within the Excel IFERROR function. The formula will enable us to display a customized comment instead of the error value, which we can use as a medium to highlight the differences between the two given columns.
Alternatively, we can use the VLOOKUP() in the Excel Conditional Formatting feature rule to compare two columns in Excel using VLOOKUP and highlight differences. It can be a way to highlight the error values in the target cells, which indicate the unique lookup values present in only one of the two columns. Otherwise, we can highlight the unique values in the source column containing the lookup values.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Basic Example
The below image shows two lists of the top 10 US states by land area and population in columns A and C.
And the requirement is to determine which states from column A are present in column C and display the output in column E.
Then, we can compare two columns in Excel using VLOOKUP method in the target cells to obtain the desired output.
- Choose cell E2 and enter the VLOOKUP() as shown below.
=VLOOKUP(
Next, enter the cell reference A2 from the first column as the lookup_value argument value and a comma.
=VLOOKUP(A2,
And then, enter the cell range $C$2:$C$11 from the second column as the table_array, where we must search the lookup value for a match, followed by a comma.
Please note that we use the absolute reference in Excel for the table_array range, as we will copy the VLOOKUP() formula in the remaining target cells in column E.
And the table_array range should be constant in the VLOOKUP() in all the target cells.
=VLOOKUP(A2, $C$2:$C$11,
Next, as the table_array has only one column, the col_index_num argument value will be 1. And once we enter a comma, Excel will show the options to choose the last argument, range_lookup, value for an approximate or exact match.
=VLOOKUP(A2, $C$2:$C$11,1,
Double-click on FALSE to select the last argument value for an exact match.
=VLOOKUP(A2, $C$2:$C$11,1,FALSE
Finally, close the bracket.
=VLOOKUP(A2, $C$2:$C$11,1,FALSE)
- Press Enter to execute the VLOOKUP() in the target cell.
- Using the Excel fill handle, update the formula in the remaining target cells E3:E11.
Let us check the cell E11 formula to know how it executes.
The VLOOKUP() looks up the cell A11 value, Ohio, in the lookup range C2:C11. And the value matches the cell C8 data, and we need the value corresponding to the lookup value in the same row as the match in the cell range C2:C11. So, the function returns the value Ohio as the output.
Thus, the outcome shows that three states from column A, Texas, California, and Ohio, are in column C. And the remaining values in column A are unique, which the error value #N/A in the corresponding rows of column E represents.
Furthermore, let us see how to highlight the differences between columns A and C. And here, let us highlight the values present in column A and not in column C and the error values in column E, which also denote the differences. - Select the cell range A2:A11 and E2:E11 and choose the Home tab → Conditional Formatting drop-down.
And choose the New Rule option from the Conditional Formatting drop-down list.
The New Formatting Rule window will open.
And choose the last rule type to enter the conditional formatting formula in the field inside the Edit the Rule Description section.
- Enter the conditional formatting formula, the VLOOKUP() within the ISERROR Excel function, in the field inside the Edit the Rule Description section.
=ISERROR(VLOOKUP($A2,$C$2:$C$11,1,0))
And click Format to access the Excel Format Cells window. - Click the Fill tab to open it and choose the required background color in the Format Cells window.
And click OK in the Format Cells window.
And then click OK in the New Formatting Rule window.
Thus, the final output will appear as shown below.
First, the VLOOKUP() in the conditional formatting rule searches for a match for the column A cell lookup value in column C. But, if it does not find a match for the lookup value, it returns an error value.
Next, the ISERROR() checks if the VLOOKUP() output is an error value. And if the condition holds in a row, the corresponding chosen cells in the row get highlighted in the specified format.
The highlighted cells in column A represent the differences between columns A and C when we compare them. Likewise, the highlighted cells in column E have the same interpretation.
Partial Lookup Using Wildcard Characters
Sometimes the lookup value may be a part of a value in the lookup range. Otherwise, the values in the lookup range may contain additional characters, even spaces, which may not be in the lookup value.
In such a case, we can perform a partial lookup using Excel Wildcard characters, ‘*’ and ‘?’. While ‘*’ represents multiple characters, ‘?’ represents one character.
We can add a ‘*’ once or ‘?‘, with the count equaling the number of characters we wish to represent using the character before and or after the lookup value in the VLOOKUP(). And we must use the Ampersand symbol to add the Wildcard characters to the lookup value in the VLOOKUP().
Let us see an example for compare two columns in Excel using VLOOKUP in different sheet with a Wildcard characters-based partial lookup.
The first two images show two columns in two worksheets.
And the requirement is to compare the two given columns to check which company names and stocks match in the two lists and display the output in column C of the second sheet.
Then, we can compare two columns in Excel using VLOOKUP in different sheet technique. And we shall perform a partial lookup of the tech company names in the stocks listed in the first sheet, using the Wildcard character, ‘*’, to get the least possible errors.
- Step 1: Choose cell C2 in the second sheet and enter the VLOOKUP() as shown below.
=VLOOKUP(“*”&A2&”*”,
Next, as the lookup range is in the first sheet, we will click the first sheet tab.
=VLOOKUP(“*”&A2&”*”,’Best Tech Stocks’!
Next, enter the absolute reference to the lookup range A2:A8, as the lookup range is constant in all the target cells, and we will copy the formula in them.
=VLOOKUP(“*”&A2&”*”,’Best Tech Stocks’!$A$2:$A$8,
Finally, we shall supply the last two argument values in the VLOOKUP() and close the bracket.
=VLOOKUP(“*”&A2&”*”,’Best Tech Stocks’!$A$2:$A$8,1,0)
- Step 2: Press Enter to view the VLOOKUP() output in cell C2.
- Step 3: Enter the formula in the remaining target cells using the fill handle.
The VLOOKUP() output in cell C7 is an error value. And the reason is that the best tech stocks list in the first sheet does not contain even a VLOOKUP partial match for the corresponding company name.
Otherwise, the partial lookup using the Wildcard character ‘*’ ensures the VLOOKUP() returns error-free output in the remaining cells.
We will check the cell C9 formula to understand how the Wildcard characters work.
First, the VLOOKUP() accepts the lookup_value as “*”&A9&”*”. The Wildcard ‘*’, before and after the excel cell reference A9, indicates the function to check the lookup range for a value containing the cell A9 value. And the value in the lookup range may or may not have additional characters before or after it.
And cell A7 in the lookup range A2:A8 in the first sheet contains the lookup value, the company name TENCENT followed by additional characters. So, Excel returns the cell A7 value from the first sheet as the output in the target cell C9.
Furthermore, had we not used the Wildcard character, the function output in all the cells would be the #N/A error. The reason is that all the company names are part of the stock names, with the stock names having additional characters. And the ‘*’ helped represent the additional characters in the stocks list, which resulted in the function partially matching the lookup value in the lookup range in each target cell.
Important Things To Note
- When we compare two columns in Excel using VLOOKUP, the table_array rangewill only have one column. And hence, the col_index_num argument value will be 1.
- When comparing two Excel columns using the VLOOKUP() with approximate match (range_lookup argument value is TRUE), sort the column values in the table_array. Otherwise, the function may return an incorrect or error value.
- Use the Wildcard character ‘?’ and ‘*’ in the lookup_value argument to represent single and multiple characters in a string, respectively.
- The two columns comparison using the VLOOKUP function in Excel is case and format insensitive.
Frequently Asked Questions (FAQs)
The compare two columns in Excel using VLOOKUP is not picking up values, perhaps because of the following reasons:
• The cells referenced contain hidden spaces or non-printable characters.
• The cells referenced contain invalid data.
• The lookup value is not in the first column of the lookup range.
• The lookup value is smaller than the least value in the lookup range.
• When setting the last argument, range_lookup, as TRUE, you did not sort the lookup range for an approximate match.
We can compare more than two columns using VLOOKUP in Excel using the following steps:
1) Create a helper column containing unique identifiers, developed using the lookup values from each row of the required columns. And the helper column should be the first column in the table_array.
2) Please select the first cell in the target column, where we must compare more than two columns. And enter the VLOOKUP().
The lookup_value should be created using the lookup values from the source columns, with the format as in the helper column. And the table_array range should be from the helper column up to the column, from where we must fetch the value corresponding to the lookup value in the same row as the match.
Finally, the col_index_num will be the count of the column from the helper column, from where we must fetch the value corresponding to the lookup value. And the last argument, range_lookup, will be FALSE for an exact match.
3) Press Enter to execute the VLOOKUP().
So, using the lookup values from multiple columns and the lookup range containing the helper column and one or more columns, the function compares more than two columns.
You can compare 3 columns in Excel using VLOOKUP by applying the method, which is explained below with an example.
The first table lists fruits, their grades and grade-based costs per carton.
And the requirement is to update the cost per carton data for the fruits and their grades mentioned in columns E and F in the second table.
So, the logic is to compare columns E, F, and C using the VLOOKUP() in the target cells to achieve the required outcome.
• Step 1: Right-click on the column C header to choose Insert from the contextual menu.
The above action will insert a column before the original column C.
Next, choose cell C1 and enter the column heading Helper.
• Step 2: Select cell C2 and enter the below formula.
=A2&”_”&B2
Next, using the fill handle, update the formula in cells C3:C7 to create unique identifiers for the lookup values to use in the VLOOKUP() in the target cells.
• Step 3: Select the target cell H2, enter the VLOOKUP(), and press Enter.
=VLOOKUP(F2&”_”&G2,$C$2:$D$7,2,0)
• Step 4: Enter the formula in the remaining target cells using the fill handle to complete the comparison of three columns using the VLOOKUP().
The expression used for the first argument in the VLOOKUP() creates the lookup value in the same format as the entries in the Helper column. And as the Helper column is the first column in the table_array, the function searches for a match for the lookup value in the Helper column. And then, it returns the value corresponding to the lookup value in the same row as the match from column D.
Thus, columns E and F form a part of the lookup value, and column D (previously column C) is part of the lookup range. And hence, in this way, the VLOOKUP() helps compare three columns.
Download Template
This article must be helpful to understand the Compare Two Columns In Excel Using VLOOKUP, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Compare Two Columns In Excel Using VLOOKUP. We learn to compare them based on exact & partial matches with examples. You can learn more from the following articles –
Leave a Reply