Comparing data in Excel on different sheets. Match in Excel Columns

Today I’ll tell you how to search for a match in Excel columns. Let's look at all the subtleties using examples.

Problem 1: There are 6 texts in 6 cells. You need to find out which ones are unique and which ones are repeated.

We will use Conditional Formatting.

Select the cells that need to be compared;
In the tab home go to "Conditional formatting -> Cell selection rules -> Repeating values";

A new window pops up and the cells in the table that are repeated begin to be highlighted.

In this window you can choose two settings: highlight Repetitive or Unique cells, as well as what kind of backlight to use - border, text, text color, background or your own unique one.

Let's look at another example. You need to compare two columns in Excel for matches. There is a table that also has matches, but they are numerical.

Select the table and go to Duplicate values. All matches will be highlighted.

You can, for example, find matches in a single column. To do this, it is enough to select only it before using the option.

Well, as I said above, from the drop-down list you can select not only repeating cells, but also unique ones.

You can search for letters, words, symbols, texts, etc. in this way.

In this lesson I will tell you how to attach a header to each Excel page. This may be necessary if you are working with large tables. Usually, when they are printed, documents with a large number of pages are produced, and in order to make it convenient to work with them, it is necessary to save the table header on each sheet.

In this lesson I will tell you how to split text into columns in Excel. This lesson is suitable for you if you want to split text from one column into several. Now I will give an example. Let's say you have cell "A", which contains your first name, last name and patronymic. You need to make sure that in the first cell “A” there is only the last name, in cell “B” - the first name, and in cell “C” the middle name.

In this article I will tell you how remove duplicates in Excel. We will consider the simplest standard method, which has appeared since Excel 2007.

We have two order tables copied into one worksheet. It is necessary to compare data from two tables in Excel and check which positions are in the first table but not in the second. There is no point in manually comparing the value of each cell.

Compare two columns for matches in Excel

How to compare values in Excel of two columns? To solve this problem, we recommend using conditional formatting, which quickly highlights items that are in only one column. Worksheet with tables:

The first step is to name both tables. This makes it easier to understand which cell ranges are being compared:

Select the FORMULAS - Defined Names - Assign Name tool.
In the window that appears, in the “Name:” field, enter the value – Table_1.
Use the left mouse button to click on the “Range:” input field and select the range: A2:A15. And click OK.

For the second list, perform the same steps, only give it a name – Table_2. And specify the range C2:C15 - respectively.

Helpful advice! Range names can be assigned more quickly using the names field. It is located to the left of the formula bar. Simply select ranges of cells, and in the name field, enter the appropriate name for the range and press Enter.

Now let's use conditional formatting to compare two lists in Excel. We need to get the following result:

Items that are in Table_1 but not in Table_2 will be displayed in green. At the same time, positions that are in Table_2, but not in Table_1, will be highlighted in blue.

The principle of comparing data between two columns in Excel

When defining the conditions for formatting the column cells, we used the COUNTIF function. In this example, this function checks how many times the value of the second argument (for example, A2) appears in the list of the first argument (for example, Table_2). If number of times = 0 then the formula returns TRUE. In this case, the cell is assigned the custom format specified in the conditional formatting options.

The link in the second argument is relative, which means that all cells of the selected range will be checked one by one (for example, A2:A15). For example, to compare two price lists in Excel, even on different sheets. The second formula works similarly. The same principle can be applied to various similar tasks.

Question from a user

Hello!

I have one task, and for the third day now I’ve been racking my brains - I don’t know how to complete it. There are 2 tables (about 500-600 rows in each), you need to take the column with the name of the product from one table and compare it with the name of the product from the other, and if the products match, copy and paste the value from table 2 into table 1. Confusedly explained , but I think you’ll understand the task from the photo ( approx. : the photo was cut out by censorship, it’s still personal information).

Thank you in advance. Andrey, Moscow.

Good day everyone!

What you described refers to fairly popular tasks that are relatively easy and quick to solve using Excel. All you need to do is put your two tables into the program and use the VLOOKUP function. More about her work below...

An example of working with the VLOOKUP function

As an example, I took two small signs, they are shown in the screenshot below. In the first table (columns A, B- product and price) no data on the column B; in the second, both columns (product and price) are filled in. Now you need to check the first columns in both tables and automatically, if a match is found, copy the price to the first table. It seems like a simple task...

How to do it...

Place the mouse pointer in a cell B2- that is, in the first cell of the column where we have no value and write the formula:

=VLOOKUP(A2,$E$1:$F$7,2,FALSE)

A2- the value from the first column of the first table (what we will look for in the first column of the second table);

$E$1:$F$7- a completely selected second table (in which we want to find and copy something). Pay attention to the "$" sign - it is necessary so that when copying the formula, the cells of the selected second table do not change;

2 - the number of the column from which we are copying the value (note that our selected second table has only 2 columns. If it had 3 columns, then the value could be copied from the 2nd or 3rd column);

LIE- we are looking for an exact match (otherwise the first similar one will be substituted, which obviously does not suit us).

Actually, you can adjust the finished formula to your needs by slightly changing it. The result of the formula is presented in the picture below: the price was found in the second table and entered in auto mode. Everything is working!

To set the price for other product names, simply extend (copy) the formula to other cells. Example below.

After which, as you can see, the first columns of the tables will be compared: from the rows where the cell values coincide, the necessary data will be copied and substituted. In general, it is clear that tables can be much larger!

Note: I must say that the VLOOKUP function is quite demanding on computer resources. In some cases, with an excessively large document, it may take quite a long time to compare tables. In these cases, it is worth considering either other formulas or completely different solutions (each case is individual).

That's all, good luck!

Perhaps everyone who works with data in Excel is faced with the question of how to compare two columns in Excel for similarities and differences. There are several ways to do this. Let's take a closer look at each of them.

How to compare two columns in Excel row by row

When comparing two columns of data, it is often necessary to compare the data in each individual row for matches or differences. We can do such an analysis using the function. Let's see how this works using the examples below.

Example 1: How to compare two columns for matches and differences in the same row

In order to compare the data in each row of two columns in Excel, let's write a simple formula. The formula should be inserted into each row in the adjacent column, next to the table in which the main data is located. Having created a formula for the first row of the table, we can stretch/copy it to the remaining rows.

In order to check whether two columns of the same row contain the same data, we need the formula:

=IF(A2=B2; “Match”; “”)

The formula that determines the differences between the data of two columns in one row will look like this:

=IF(A2<>B2; "Do not match"; “”)

We can fit the match and difference test between two columns on one line in one formula:

=IF(A2=B2; “Match”; “Do not match”)

=IF(A2<>B2; "Do not match"; "Match up")

An example calculation result might look like this:

To compare data in two columns of the same row in a case-sensitive manner, use the formula:

=IF(MATCH(A2,B2), “Match”, “Unique”)

How to compare multiple columns for matches in one Excel row

Excel allows you to compare data in multiple columns of the same row using the following criteria:

Find rows with the same values in all columns of the table;
Find rows with the same values in any two columns of the table;

Example 1. How to find matches in one row in multiple columns of a table

Let's imagine that our table consists of several columns of data. Our task is to find rows in which the values match in all columns. Excel and functions will help us with this. The formula for determining matches will be as follows:

=IF(AND(A2=B2,A2=C2); “Coincide”; “ “)

If our table has a lot of columns, then it will be easier to use the function in combination with:

=IF(COUNTIF($A2:$C2,$A2)=3;”Match”;” “)

In the formula, “5” indicates the number of columns of the table for which we created the formula. If your table has more or less columns, then this value should be equal to the number of columns.

Example 2: How to find matches of the same row in any two columns of a table

Let's imagine that our task is to identify from a table with data in several columns those rows in which the data is the same or repeated in at least two columns. The and functions will help us with this. Let's write a formula for a table consisting of three columns of data:

=IF(OR(A2=B2,B2=C2,A2=C2);”Match”;” “)

In cases where there are too many columns in our table, our formula with the function will be very large, since in its parameters we need to specify the matching criteria between each column of the table. An easier way, in this case, is to use the .

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Unique string”; “Not unique string”)

=IF(COUNTIF($B:$B,$A5)=0; “There are no matches in column B”; “There are matches in column B”)

This formula checks the values in column B to see if they match the cells in column A.

If your table consists of a fixed number of rows, you can specify a clear range in the formula (for example, $B2:$B10). This will speed up the formula.

How to compare two columns in Excel for matches and highlight them in color

When we look for matches between two columns in Excel, we may need to visualize the found matches or differences in the data, for example using color highlighting. The easiest way to highlight matches and differences in color is to use Conditional Formatting in Excel. Let's look at how to do this using the examples below.

Finding and highlighting matches in color in multiple columns in Excel

In cases where we need to find matches in several columns, then for this we need:

Select the columns with data in which you want to calculate matches;
On the “Home” tab on the Toolbar, click on the menu item “Conditional Formatting” -> “Rules for highlighting cells” -> “Repeating values”;
In the pop-up dialog box, select “Repeating” in the left drop-down list, and in the right drop-down list, select what color repeating values will be highlighted in. Click “OK” button:
After this, matches will be highlighted in the selected column:

Find and highlight matching rows in Excel

Finding matching cells with data in two or more columns and searching for matches of entire rows with data are different concepts. Please note the two tables below:

The tables above contain the same data. Their difference is that in the example on the left we were looking for matching cells, and on the right we found entire repeating lines with data.

Let's look at how to find matching rows in a table:

To the right of the table with the data, we will create an auxiliary column in which, opposite each row with the data, we will put a formula that combines all the values of the table row into one cell:

=A2&B2&C2&D2

In the auxiliary column you will see the combined table data:

Now, to determine the matching rows in the table, do the following steps:

Select the area with data in the auxiliary column (in our example this is a range of cells E2:E15 );
On the “Home” tab on the Toolbar, click on the menu item “Conditional Formatting” -> “Rules for highlighting cells” -> “Repeating values”;
In the pop-up dialog box, select “Repeating” in the left drop-down list, and in the right drop-down list, select what color the repeating values will be highlighted in. Click “OK” button:
After this, duplicate lines will be highlighted in the selected column: