Question

WHAT ARE THE STEPS ON COMPARING TWO COLUMNS NOT BY EACH LINES BUT SEARCH WHOLE COLUMNS FROM EXCEL TO COMPARE EACH AND GIVE RESULTS? I AM NOT LOOKING FOR A1=B1 I AM LOOKING FOR COLUMNS TO CHECK IF THE STRINGS ARE MATCHING OR NOT. NOT BY THE LINES BUT AS A WHOLE TWO COLUMNS TO CHECK IF IT MATCHES OR NOT

Answer 1

#Compare two columns for matches or differences in the same row

To compare two columns in Excel row-by-row, write a usual IF formula that compares the first two cells. Enter the formula in some other column in the same row, and then copy it down to other cells by dragging the fill handle (a small square in the bottom-right corner of the selected cell). As you do this, the cursor changes to the plus sign.

Formula for matches

To find cells within the same row having the same content, A2 and B2 in this example, the formula is as follows:

=IF(A2=B2,"Match","")

Formula for differences

To find cells in the same row with different content, simply replace "=" with the non-equality sign:

=IF(A2<>B2,"No match","")

Matches and differences

And of course, nothing prevents you from finding both matches and differences with a single formula:

=IF(A2=B2,"Match","No match")

Or

=IF(A2<>B2,"No match","Match")

#Compare multiple columns for matches in the same row

In your Excel worksheets, multiple columns can be compared based on the following criteria:

Find rows with the same values in all columns (Example 1)Find rows with the same values in any 2 columns (Example 2)Example 1. Find matches in all cells within the same row

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 your table has a lot of columns, a more elegant solution would be using the COUNTIF function:

=IF(COUNTIF($A2:$E2, $A2)=5, "Full match", "")

Where 5 is the number of columns you are comparing.

Example To Find matches in any two cells in the same row

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:

=IF(OR(A2=B2, B2=C2, A2=C2), "Match", "")
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 1st column, the second COUNTIF counts how many of the remaining columns are equal to the 2nd column, and so on. If the count is 0, the formula returns "Unique", "Match" otherwise. For example:

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0,"Unique","Match")