How to Match Data in Excel (Table of Contents)
Introduction to Match Data in Excel
Microsoft Excel offers a various number of 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, there are various different options available based on the data structure. It’s a 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’s 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.
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 contains the same content or not in. 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, ‘New Formatting Rule’ dialog box appears, under the option 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, it 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 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 is 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
When compared to other methods, with the help of this option, we can perform the task in a faster way. it can be also 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, 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-in 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 to find various type of errors which includes 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 in Different ways with Different Examples and Methods along with the downloadable excel template. You may also look at the following articles to learn more –
