How To Correlate Two Sets Of Data In Excel

How to Lucifer Information in Excel (Table of Contents)

Introduction to Match Data in Excel
Examples on How to Match Information in Excel

Introduction to Match Data in Excel

Microsoft Excel offers diverse options to match and compare data, only ordinarily, nosotros compare only ane column in most of the scenarios. Comparing the data for one or more or multiple columns, various options are bachelor based on the information structure. It'south the nigh frequent job used in the comparative information analysis, which is carried out either on the tabular row or column information.

Definition of Lucifer Data in Excel

It'south a process to notice out or spot a divergence between datasets of ii or more columns or rows in a table. It tin be done by various procedure, where information technology depends on the type of datasets structure.

In Excel, we have a procedure or a tool where we tin can track the differences betwixt a row's cells and highlight them, irrespective of the number of row or column datasets & worksheet size.

Examples on How to Lucifer Information in Excel

Let'due south check out the diverse available selection to compare data sets between 2 rows or columns in excel & to spot out a difference between them.

You can download this How to Match Data Excel Template here – How to Match Information Excel Template

Example #1

How to Compare or Match Data in the Same Row

In the below-mentioned case, I have two columns, i.east. Listing 1 & 2, where information technology contains the listing of student names; now, I have to compare & match a dataset in these 2 columns row by row.

To check whether the name in list one is similar to list ii or non. There are various options bachelor to acquit out.

Method 1 – I can utilise the below-mentioned formula in a split column to bank check out the row data one past one, i.e. =A3=B3, it is practical to all the other cell range.

If there is a data match, it returns a value "Truthful"; otherwise, it will return a "Fake" value.

Method 2 – To Compare data by using IF logical formula or exam

If logical formula gives a better descriptive output, it is used to compare case sensitive data.

i.eastward. =IF (A3=B3,"MATCH", "MISMATCH")

It will help out whether the cells within a row comprise the same content or non. If the logical exam is instance sensitive. i.e. Suppose, if the proper noun is "John" in ane row & "John" in the other row, information technology will consider it as different & event in Mismatch or a simulated value.

Here is the issue mention below.

Method 3 – To Compare data with the aid of Provisional Formatting

Hither, suppose I want to highlight the matching data between two rows with some colour, say Green, otherwise no color for data mismatch, and then to perform this Conditional Formatting is used with a gear up a sure criterion

To perform conditional formatting, I demand to select the entire tabular data set.

Go to the Domicile tab, under the style selection, select a Provisional Formatting option.

Various options nether the conditional formatting announced in that select New Rule.

Once y'all click on the new rule, the 'New Formatting Dominion dialog box appears; nether the option to select a rule type at the top, you need to select a "Use a formula to make up one's mind which cells to format".In the formula field, to compare & match a dataset betwixt ii rows, we need to enter the formula $A3 = $B3.

Here, I want to highlight the matching information between two rows with green color, and so in the format set option, I demand to select a Green color and click on Ok.

Again click onOK.

Now, it will highlight all the matching data with green color and no color for the data which are a mismatch betwixt rows.

The above 3 methods are used to compare and match the information; they can also exist used for numeric data, engagement or time values. A similar procedure is performed to compare multiple columns data match which is in the same row.

Instance #2

How to Compare or Friction match Data between Columns & Highlighting the Difference in the Data

In some cases, the dataset matches may be in dissimilar rows (Information technology is non present in the aforementioned row); in these scenarios, nosotros have to compare two columns & match the data.

In the below-mentioned example, the student listing one in cavalcade B is slightly bigger than the educatee listing ii in column C. and Too, few names are in that location in both the student lists, but it is not present in the same row (such as John, Mark, Edward).

Information match or comparison between two columns tin exist washed through a duplicate option nether provisional formatting.

Permit's check out the steps involved to acquit out this procedure.

To perform conditional formatting, I need to select the entire tabular data set.

In the Home tab, nether the mode selection, select a Conditional Formatting option.

Diverse options under the conditional formatting appear; nether the Highlight Cells Rules, you lot take to select the Duplicate Values.

In one case the duplicate values are selected, a popup message appears, where y'all need to select the color of your selection to highlight the duplicate values; hither, I have selected Light Cerise Make full with Night Red Text.

Once it is selected, you tin observe common student present in both the columns which are highlighted with red color (with dark scarlet text) and unique values are the ones which are not colored.

Annotation: In Conditional Formatting duplicate values option is not case-sensitive; where it considers the upper or lower case as similar records, it will non highlight them every bit duplicate.

Example #3

Highlighting the Row Deviation with the assistance of "Go to Special" Feature

Compared to other methods, with the help of this option, we can perform the task faster. It tin also be used for multiple columns.

To perform "Go to Special", you need to select the unabridged tabular data gear up and click on CTRL + G; in one case information technology is selected, theGo-To Special dialog box appears; in that, yous need to select "Row Difference" and and then click on OK.

It will highlight the cells with different datasets; now, you can color it to green to rails a deviation in the dataset between rows.

Things to Recall about the Data Match in Excel

Apart from the higher up methods, there are various third-party add-on tools to perform the textual data match in excel.

Fuzzy Lookup Add-In tool for Excel

Information technology will assistance out to runway the difference in the information table within a selected row. Information technology 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.