You can download a free trial Kutools add-in and use it to compare two columns and return the value in a third column.ġ. Use the fill handle to drag the formula to the rest of the cells you want to match. Using the same example, compare the projects in columns A and D and if a match is found, return the corresponding name in column B. You can pull the matching content from one column to another using the Index and match formulas only. In the above formulas, cell D2 is the criteria cell that returns a value based on column cells A2:A20 which is to be compared with and return the matching value from data range A2:B20.
Autofill the formula to the rest of the cells Copy the formula to the rest of the cells using the Autofill feature or drag the fill handle down to the cells you want to copy the formula.ġ. Press ENTER key to get the matching content on the E2.Ĥ. Option 7: Combining INDEX, MATCH, And IFERROR FunctionsĢ.Option 5: Combining IF And EXACT Functions.In this tutorial, we explained how you can use Excel's column comparison tool to look for matches ( duplicates) and differences ( unique values) in data. Then click on OK.Īfter clicking OK, we can see the unique values has been highlighted. In our case, we are selecting Unique to highlight the duplicate data and values with as Yellow Fill with Dark Yellow Text. In the place of values with, select a colour combination from the dropdown list From the popup window, Duplicate if you want to Highlight the duplicate data, otherwise select unique. (Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values). Then from the dropdown list, put cursor on Highlight Cell Rules, then go to Duplicate Values. Now from the Ribbon, Go to Home Tab, then from the Styles group, select Conditional Formatting option. In our case, we are selecting the data from cell A2 to B10. Select the data in the columns to whom you want to compare. Using the conditional formatting feature of Excel, it is possible to compare two columns and highlight the Unique and Duplicate cells between them. If there will be a match, it will return the result as True and False otherwise. Note that we can use another formula here, that is =A2=B2. To get the result in remaining cells, click the ‘+’ sign that appears at the lower right corner of the cell C2, which activates the autofill function and then drag down. IF(A2=B2,"Matched","Mismatched")Īfter adding the formula, when you press enter, it will compare the value of A2 cell with B2 cell and if they are equal, it will return "Matched", else "Mismatched". In our case we have created another column named result and we are selecting C2 cell. Select a blank cell from another column in which you want to get the result. Some of the employees are tasked with contributing to both of the projects. We have the names of employees who are working on Project1 and Project2, listed here in our Excel sheet. Let’s Understand through an example step by step. If you want to receive a result that is more descriptive, you may use a straightforward IF formula that will return the word "Match" when the names are the same and the word "Mismatch" when the names are not the same. Compare Two Columns for Row Match using IF formula In this tutorial you are going to learn some methods to compare two columns and find out matched data and mismatched data.
You can save time by using Excel's functions to streamline the process, as compared to analyzing the columns and entering "Match" or "Mismatch" into a separate column in the spreadsheet. Comparing the data in two columns in a large Excel spreadsheet can be a time-consuming process when working with such a document. When you compare and match data in Microsoft Excel, you can do so in a variety of ways. When comparing and matching data in Microsoft Excel, you may do so in a variety of ways however, the vast majority of these approaches focus on searching within a single column. The task of comparing columns in Excel is one that will eventually be required of each and every one of us.
0 kommentar(er)
