RECOMMENDED: Click here to fix Windows errors and optimize system performance
In this tutorial I will show you how to compare two columns in Excel so that the values that appear in both columns are highlighted and you can do something with these duplicate values.
Using INDEX and MATCH instead of VLOOKUP
The use of VLOOKUP is subject to certain restrictions: the VLOOKUP function can only search for a value from left to right. This means that the column with the required value must always be to the left of the column with the return value. If your spreadsheet is not structured in this way, do not use VLOOKUP. Instead, use the combination of the INDEX and MATCH functions.
This example shows a small list where the value we want to search for, Chicago, is not in the left column. Therefore, we cannot use VLOOKUP. Instead, we will use the MATCH function to find Chicago in field B1:B11. Then INDEX uses this value as a search argument and finds the population of Chicago in the fourth column (column D). The formula used is displayed in cell A14.
Comparing two Columns and Highlighting Matches
If you want to compare two columns and highlight the corresponding data, you can use the replication functionality in conditional formatting.
Note that this is different from what we saw when we compared each row. In this case, we will not perform a row-by-row comparison.
Example: Comparing two columns and highlighting the corresponding data
Often you will get records that have matches but are not in the same row.
Note that the list in column A is larger than the list in column B. Some names also exist in both lists, but not in the same row (such as IBM, Adobe, Walmart).
If you want to highlight all the corresponding company names, you can do so using conditional formatting.
Here are the steps to do this:
- Select the complete record.
- Click on the Home tab.
- In the Styles group, click the Conditional Formatting option and click Conditional Formatting.
- Move the mouse pointer to the “Highlight Cell Rules” option.
- Click on Duplicate Values. Select the duplicate values in Conditional Formatting
- Make sure that Duplicate is selected in the Duplicate Values dialog box. i.e., duplicate in conditional format
- Specify the formatting. Specify formatting in conditional format
- Click OK.
The VLOOKUP Function
The V in VLOOKUP means vertical. This means that it can search for a specific value in a column. It can also return a value in the same row as the one it found.
If you have a subscription to Office 365 in the Monthly Channel, you can use the new XLOOKUP. If you only have a six-month subscription, you will be able to get it in July 2020.
Let’s use the same inventory data and try to find the price of something.
Where we’ve already looked for a line, enter the formula :
J1 refers to the cell with the corresponding value. E2:G9 is the range of values we are working with. But VLOOKUP only looks for a match in the first column of this range. The 3 refers to the third column, which is skipped from the beginning of the range.
So, if we enter a reference number (SKU) in J1, VLOOKUP finds the match and takes the value of the 3 columns in the cell above it. FALSE tells Excel what type of match we are looking for. FALSE means that it must be an exact match, while TRUE would say that it must be a close match.
RECOMMENDED: Click here to troubleshoot Windows errors and optimize system performance
CCNA, Web Developer, PC Troubleshooter
I am a computer enthusiast and a practicing IT Professional. I have years of experience behind me in computer programming, hardware troubleshooting and repair. I specialise in Web Development and Database Design. I also have a CCNA certification for Network Design and Troubleshooting.