Can i match two columns in excel

Table of Contents hide. Download Working Files. Further Readings. Source code. Save Saved Removed 0. Siam Hasan Khan. Related Articles. Added to wishlist Removed from wishlist 0. Reply Hai Jul 17, at PM. Working Files are not found. Could you resend alive links?

How to compare multiple columns in Excel in the same row for matches? Count the total duplicates also. We have given the procedure to compare two columns in excel for the same row above. But if you want to compare multiple columns in excel for the same row then see the example. Here we have compared data of column A, column B, and column C. After this, I have applied the above formula in column D and get the result. To understand this thing in a better way we will take an example.

Here we will take two tables and now want to do pull matching data. In the first table, you have a dataset and in the second table, take the list of fruits and then use pull matching data in another column. For pull matching, use the formula. To find the duplicate , you can use various methods like conditional formatting, Vlookup, If Statement, and many more.

Excel also has an in-built tool where you can just select the data, and remove the duplicates from a column or even multiple columns.

If your table has three or more columns and you want to find rows that have the same values in all cells, an IF formula with an AND statement will work a treat:. If you are looking for a way to compare columns for any two or more cells with the same values within the same row, use an IF formula with an OR statement:. In case there are many columns to compare, your OR statement may grow too big in size. If the count is 0, the formula returns "Unique", "Match" otherwise.

For example:. Suppose you have 2 lists of data in Excel, and you want to find all values numbers, dates or text strings which are in column A but not in column B. If no match is found, the formula returns "No match in B", an empty string otherwise:. If you want a single formula to identify both matches duplicates and differences unique values , put some text for matches in the empty double quotes "" in any of the above formulas.

Sometimes you may need not only match two columns in two different tables, but also pull matching entries from the second table. For example, the following formula compares the product names in columns D and A and if a match is found, a corresponding sales figure is pulled from column B.

If you don't feel very comfortable with this formula, then you may want to try the Merge Tables wizard - a fast and intuitive solution that can compare and match 2 tables by any column s. When you compare columns in Excel, you may want to "visualize" the items that are present in one column but missing in the other.

You can shade such cells in any color of your choosing by using the Excel Conditional Formatting feature and the following examples demonstrate the detailed steps. To compare two columns and Excel and highlight cells in column A that have identical entries in column B in the same row, do the following:.

If you are new to Excel conditional formatting, please see How to create a formula-based conditional formatting rule for step-by-step instructions. You create the conditional formatting rules with the following formulas:. And get the following result:. If you closely followed the previous example , you won't have difficulties adjusting the COUNTIF formulas so that they find the matches rather than differences.

All you have to do is to set the count greater than zero:. When comparing values in several columns row-by-row, the quickest way to highlight matches is creating a conditional formatting rule, and the fastest way to shade differences is embracing the Go To Special feature, as demonstrated in the following examples. Here the Conditional Formatting compares two columns without case insensitive. Sometimes, after comparing two columns, you may take other actions on the matches or difference, such as selection, deletion, copy and so on.

After free installing Kutools for Excel, please do as below:. Click Ok , a dialog pops out to remind you the number of values that have been found, click OK to close the dialog. And at the same time, the values have been selected, now you can delete or copy or do other operations. If you check the Fill backcolor and Fill font color checkboxes, the result is shown as this:.

If you want to compare with case sensitive, check the Case sensitive option. If you want to list the matching values in another column after comparing two columns cell by cell, here the below macro code can help you. VBA: List duplicates in beside column after comparing two columns.

Press F5 key to run the code, there are two dialogs popping out one by one for you to select two columns separately. Note : Please select the left column firstly, then select the right column in the second dialog, or the duplicates will replace original data in the second column. If you want to compare two columns cell by cell and then highlight the matches, you can try below code.

Copy below macro code and paste them in the new blank Module script. Press F5 key to run the code. In the first popping out dialog, select the column that you want to compare duplicate values according to.

In the second dialog, select the column you want to highlight duplicate values in. Now the duplicates in the second column have been highlighted with a background color after comparing with first column. Sometimes, you may want to compare more than two columns in the same row, such as the data set as below screenshot shown.

Here in this section, it lists different methods on comparing multiple columns. To find full matches across columns in the same row, the below IF formula can help you. Press Enter key to get the first comparing result, then drag auto fill handle over to cell E7.

If you need to compare more or equal to three columns, you can use below formula:. In the formula, 3 is the number of columns, you can change it to meet your need. Sometimes, you want to find out if any two columns in the same row are matched, you can use below IF formula.

In this formula, you need to compare any one pair of cells in the same row. Press Enter key, and drag fill handle over to cell E7. If there are many columns needed to compare, to compare any one pair of cells in the formula may be too long. If you want to highlight the rows that all cells are matching with each other, you can use the Conditional Formatting feature in Excel.

In the New Formatting Rule dialog, choose Use a formula to determine which cells to format from the Select a Rule Type section, then you can use one of below formulas in the Format values where this formula is true textbox.

Note : If the number of columns is more than three, supposing is 5, the formulas should be changed to:. Click Format button to go to the Format Cells dialog, then choose one fill color or other cell formatting to outstand the rows. If you want to highlight the row differences, which means it compares column cells one by one, and find the different cells according to the first column, you can use Excel built-in feature- Go To Special.

In the popping Go To Special dialog, check Row differences option. Now the row differences have been selected. Supposing there are two columns, column B is longer, and column C is shorter as below screenshot shown. Compared with column B, how to find out the missing data in column C? If you only want to identify which data is missing after comparing two columns, you can use one of below formulas:.

Press Enter key, then drag auto fill handle over cell D If you want to list the missing data below the shorter column after comparing two columns, the INDEX array formula can help you.

In the below cell of the shorter column, supposing cell C7, type below formula:. Then you can remove the error value, and all missing data has been listed below the shorter column. In According to section, choose the shorter column which misses some data.