![]() If your table has a lot of columns, a more elegant solution would be using the COUNTIF function: 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: Find matches in all cells within the same row Find rows with the same values in any 2 columns ( Example 2)Įxample 1.Find rows with the same values in all columns ( Example 1).In your Excel worksheets, multiple columns can be compared based on the following criteria: =IF(EXACT(A2, B2), "Match", "Unique") Compare multiple columns for matches in the same row To find case-sensitive differences in the same row, enter the corresponding text ("Unique" in this example) in the 3 rd argument of the IF function, e.g.: If you want to find case-sensitive matches between 2 columns in each row, then use the EXACT function: Compare two lists for case-sensitive matches in the same rowĪs you have probably noticed, the formulas from the previous example ignore case when comparing text values, as in row 10 in the screenshot above. 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: Find matches in any two cells in the same row Where 5 is the number of columns you are comparing. In case there are many columns to compare, your OR statement may grow too big in size. In this case, a better solution would be adding up several COUNTIF functions. The first COUNTIF counts how many columns have the same value as in the 1 st column, the second COUNTIF counts how many of the remaining columns are equal to the 2 nd column, and so on. How to compare two columns in Excel for matches and differences If the count is 0, the formula returns "Unique", "Match" otherwise. ![]() #Compare two columns in excel and find missing how to ![]() If no match is found, the formula returns "No match in B", an empty string otherwise: 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.įor this, you can embed the COUNTIF($B:$B, $A2)=0 function in IF's logical test and check if it returns zero (no match is found) or any other number (at least 1 match is found).įor instance, the following IF/COUNTIF formula searches across the entire column B for the value in cell A2. If your table has a fixed number of rows, you can specify a certain range (e.g. The same result can be achieved by using an IF formula with the embedded ISERROR and MATCH functions: $B2:$B10) rather than the entire column ($B:$B) for the formula to work faster on large data sets. ![]()
0 Comments
Leave a Reply. |