How to compare two lists in excel for matches

All the time there is a necessity in Excel to look at two records or two informational indexes to track down absent or matching things. As this is Excel, there is in every case more than one method for getting things done, including looking at information. From equations and Conditional Formatting to Power Query. Let’s take a look at a number of ways of contrasting two records in Excel and we will likewise take a gander at looking at whole columns of an informational collection. The following are a few distinct strategies used to look at two arrangements of a segment in Excel for matches and contrasts.

• Strategy 1: Compare Two Lists Using Equal Sign Operator.
• Strategy 2: Match Data by Using Row Difference Technique.
• Strategy 3: Match Row Difference by Using IF Condition.
• Strategy 4: Match Data Even If There is a Row Difference.
• Strategy 5: Highlight All the Matching Data utilizing Conditional Formatting.

Presently, let us talk about every one of the techniques exhaustively with a model: –

Strategy 1: Compare Two Lists Using Equal Sign Operator

We should follow the underneath moves toward looking at the two records.

• Immediately after the two columns, we must insert a new column in the next column.

• We should place the equation in cell C2 as =A2=B2.

• This equation tests whether cell A2 esteem is equivalent to cell B2. On the off chance that both the cell values are coordinated, we will come by the outcome as “TRUE” or “FALSE.”

• We will drag the recipe to cell C9 to decide on different qualities.

Any place we share similar qualities practically speaking lines, we obtain the outcome as “TRUE” or “FALSE”.

Strategy 2: Match Data by Using Row Difference Technique

You probably won’t have utilized the “Column Difference” strategy in your working environment. Be that as it may, today, we will tell you the best way to utilize this procedure to match information column by line.

• To feature non-matching cells column by line, we should choose the whole information first.
• Presently, we should press the successful easy route key “F5” to open the “Go to Special” instrument. Presently in the “Go-To” window, press the “Special” tab.

• In the following window, we should go to the “Go To Special” and pick the “Row differences” choice. Then, at that point, click on “OK.”

• We will obtain the accompanying outcome.

As we can find in the above window, it has chosen the cells any place there is a line distinction. Hence, we should fill in a variety to feature the line contrast values.

Strategy 3: Match Row Difference by Using IF Condition

How might we leave out the IF condition when we have any desire to match information column by line. In the primary model, we have by the same token “TRUE” or “FALSE.” But consider the possibility that we really want an alternate outcome rather than the default consequences of by the same token “TRUE or FALSE.” Assume we want an outcome as “Coordinating” in the event that there is no column distinction and the outcome ought to be “Not Matching” on the off chance that there is a line contrast.

• To begin with, we should open the IF condition in cell C2. Apply the logical test as A2=B2.
• Note: Logical Test=>In excel, toward the early phases of learning, it is a seriously troublesome errand to grasp the idea of consistent tests. Be that as it may, when you ace this, it will be significant expertise to your CV. As a general rule, in succession, we utilize a legitimate test to match various models also, show up at the ideal arrangement. To succeed, we have as numerous consistent equations. Go to the Formula tab and snap on the Logical Function gathering to see every one of the sensible recipes.

• We really want to apply the recipe to come by the outcome. We should drag the recipe to cell C9 to decide on different qualities.

Strategy 4: Match Data Even If There is a Row Difference

The matching information on the column distinctions system may not work continually; the value may be in various cells also. So we truly need to include different advances in these circumstances. As of now, look at the underneath data. So let us utilize our capability VLOOKUP(Note: The VLOOKUP succeed capability looks for a specific worth and returns a relating match in view of an exceptional identifier. An exceptional identifier is extraordinarily connected with every one of the records of the information base.)

Along these lines, if the data matches, we get the Name; regardless, we get the bumble regarded as #N/A. Showing mistake values doesn’t look great. So rather than showing the mistake, let us supplant them with “Not Available.” For this, utilization of the IFERROR( Note: The IFERROR capacity in Excel truly investigates a recipe for mix-ups and returns a foreordained worth rather than the goof. This returned worth can be a text, an empty string, a reasonable worth, a number, etc. The bumbles are dealt with by the capacity to consolidate “#N/A,” “#DIV/0!,” “#NAME, and so forth) capability in excel.

Strategy 5: Highlight All the Matching Data utilizing Conditional Formatting

On the off chance that you hate excel recipes, relax. We can in any case match information without the equation. For instance, utilizing straightforward restrictive formatting (Note: Conditional designing in excel helps design scope of cells in view of the satisfaction of at least one circumstance. The design to be applied can either be chosen from the different choices given in Excel or made without any preparation by the client. Contingent designing chips away at the standard “if so, then that ought to be finished.”) in excel, we can feature every one of the matching information of two records.

• We ought to first pick the data. Presently, we should go to “Home” and “Contingent Formatting” and pick “Highlight Cell Rules” > “Duplicate Values.”

• Subsequently, we can see the “Duplicate Cell Values” arranging window.
• We can pick the different design tones starting from the drop list in excel. Select the primary arranging tone and press the “OK” button.
• This will feature every one of the matching information from the two records.

• Simply for the situation, rather than featuring every one of the matching information, to feature not matching information, then, at that point, we can go to the “Duplicate Values” window and pick the choice “Unique.”
• Subsequently, it will feature every one of the non-matching qualities, as displayed beneath.

Microsoft Excel offers two different methods to help you compare two lists and highlight the missing items in each one. You can choose to highlight the missing items in both lists or in a single one. We’ll show you how.

RELATED: How to Find the Percentage of Difference Between Values in Excel

The Quick Way: Highlight Unique Cells to Compare Lists

A quick way to compare two lists in your spreadsheet is to use Excel’s unique highlight feature. This feature highlights the items in a list that are not found in the other list. This way you know exactly what items are missing from your lists.

To use the method, first, select the lists you want to compare in your spreadsheet.

While your lists are highlighted, in Excel’s ribbon at the top, click the “Home” tab.

On the “Home” tab, in the “Styles” section, click Conditional Formatting > Highlight Cells Rules > Duplicate Values.

In the “Duplicate Values” box, click “Duplicate” and choose “Unique.” Click the second drop-down menu and choose how you would like to highlight the missing items. To specify your own formatting, choose “Custom Format” from the menu.

Then apply your changes to your lists by clicking “OK.”

Excel will highlight the missing items in your lists. For example, in your first list, you will have those items highlighted that are missing from the second list, and so on.

And that’s it.

The Formula Way: Use Conditional Formatting to Compare Lists

The above method highlights items in both your lists. If you’d only like to highlight missing items in a specific list, then use a formula with conditional formatting as explained below.

RELATED: How to Use Conditional Formatting to Find Duplicate Data in Excel

First, in your spreadsheet, select all rows of your first list. Then, in the top-left corner, click the text box, type FirstList, and press Enter. This assigns a unique name to your range of cells, which lets you refer to all these cells using a single name.

Assign a unique name to your second list by first selecting all rows of your list. Then, in the top-left corner, click the text box, type SecondList, and press Enter.

Select all rows of your first list by clicking the text box in the top-left corner and choosing “FirstList.”

In Excel’s ribbon at the top, click the “Home” tab and choose Conditional Formatting > New Rule.

On the “New Formatting Rule” window, you’ll specify how your missing items will be highlighted. On this window, from the “Select a Rule Type” section, choose “Use a Formula to Determine Which Cells to Format.”

In the “Format Values Where This Formula is True” box, type the following:

Select the “Format” button and specify how you’d like to format the missing items in your list. Then, save your changes by clicking “OK.”

Back on the spreadsheet, Excel has highlighted the items in your first list that are missing from the second list. Your job is done.

And that’s how you quickly perform a comparison of two different lists in your Excel spreadsheets. Very useful!

You can also alphabetize your data to manually find the differences in two lists in your spreadsheets. Check out our guide to learn how to do that.

RELATED: How to Alphabetize Data in Microsoft Excel