How to Match Data in Excel (Table of Contents)
Introduction to Match Data in Excel
Microsoft Excel offers various options to match and compare data, but usually, we compare only one column in most of the scenarios. Comparing the data for one or more or multiple columns, various options are available based on the data structure. It’s the most frequent task used in the comparative data analysis, which is carried out either on the tabular row or column data.
Definition of Match Data in Excel
It’s a process to find out or spot a difference between datasets of two or more columns or rows in a table. It can be done by various procedure, where it depends on the type of datasets structure.
In Excel, we have a procedure or a tool where we can track the differences between a row’s cells and highlight them, irrespective of the number of row or column datasets & worksheet size.
Examples on How to Match Data in Excel
Let’s check out the various available option to compare data sets between two rows or columns in excel & to spot out a difference between them.
Example #1
How to Compare or Match Data in the Same Row
In the below-mentioned example, I have two columns, i.e. List 1 & 2, where it contains the list of student names; now, I have to compare & match a dataset in these two columns row by row.
To check whether the name in list 1 is similar to list 2 or not. There are various options available to carry out.
4.5 (3,195 ratings)
View Course
Method 1 – I can apply the below-mentioned formula in a separate column to check out the row data one by one, i.e. =A3=B3, it is applied to all the other cell range.
If there is a data match, it returns a value “True”; otherwise, it will return a “False” value.
Method 2 – To Compare data by using IF logical formula or test
If logical formula gives a better descriptive output, it is used to compare case sensitive data.
i.e. =IF (A3=B3,”MATCH”, “MISMATCH”)
It will help out whether the cells within a row contain the same content or not. If the logical test is case sensitive. i.e. Suppose, if the name is “John” in one row & “John” in the other row, it will consider it as different & result in Mismatch or a false value.
Here is the result mention below.
Method 3 – To Compare data with the help of Conditional Formatting
Here, suppose I want to highlight the matching data between two rows with some color, say Green, otherwise no color for data mismatch, then to perform this Conditional Formatting is used with a set a certain criterion
- To perform conditional formatting, I need to select the entire tabular data set.
- Go to the Home tab, under the style option, select a Conditional Formatting option.
- Various options under the conditional formatting appear in that select New Rule.
- Once you click on the new rule, the ‘New Formatting Rule dialog box appears; under the option to select a rule type at the top, you need to select a “Use a formula to determine which cells to format”. In the formula field, to compare & match a dataset between two rows, we need to enter the formula $A3 = $B3.
- Here, I want to highlight the matching data between two rows with green color, so in the format set option, I need to select a Green color and click on Ok.
- Again click on OK.
Now, it will highlight all the matching data with green color and no color for the data which are a mismatch between rows.
The above three methods are used to compare and match the data; they can also be used for numeric data, date or time values. A similar process is performed to compare multiple columns data match which is in the same row.
Example #2
How to Compare or Match Data between Columns & Highlighting the Difference in the Data
In some cases, the dataset matches may be in different rows (It is not present in the same row); in these scenarios, we have to compare two columns & match the data.
In the below-mentioned example, the student list 1 in column B is slightly bigger than the student list 2 in column C. and Also, few names are there in both the student lists, but it is not present in the same row (such as John, Mark, Edward).
Data match or comparison between two columns can be done through a duplicate option under conditional formatting.
Let’s check out the steps involved to carry out this procedure.
- To perform conditional formatting, I need to select the entire tabular data set.
- In the Home tab, under the style option, select a Conditional Formatting option.
- Various options under the conditional formatting appear; under the Highlight Cells Rules, you have to select the Duplicate Values.
- Once the duplicate values are selected, a popup message appears, where you need to select the color of your choice to highlight the duplicate values; here, I have selected Light Red Fill with Dark Red Text.
Once it is selected, you can observe common student present in both the columns which are highlighted with red color (with dark red text) and unique values are the ones which are not colored.
Example #3
Highlighting the Row Difference with the help of “Go to Special” Feature
Compared to other methods, with the help of this option, we can perform the task faster. It can also be used for multiple columns.
- To perform “Go to Special”, you need to select the entire tabular data set and click on CTRL + G; once it is selected, the Go-To Special dialog box appears; in that, you need to select “Row Difference” and then click on OK.
- It will highlight the cells with different datasets; now, you can color it to green to track a difference in the dataset between rows.
Things to Remember about the Data Match in Excel
Apart from the above methods, there are various third-party add-on tools to perform the textual data match in excel.
Fuzzy Lookup Add-In tool for Excel
It will help out to track the difference in the data table within a selected row. It will also help out find various types of errors, including abbreviations, synonyms, spelling mistakes, and missing or added data. It is most commonly used to match and compare a customer name & address data.
Recommended Articles
This is a guide on How to Match Data in Excel. Here we discuss How to Match Data in Excel using different methods along with Different Examples and a downloadable excel template. You may also look at the following articles to learn more –
