Excel: Compare columns - how it works

If you want to compare two columns in Excel to find duplicate values, you can use a formula. We will show you in our practical tip.

Compare columns in Excel: difference

If you want to roughly compare numbers according to their size, you can easily have the difference between the cell values ​​output. As an example, let us assume that a number of employees are measured by their sales turnover.

• First create a new column. You can call this difference or progress, as in the picture. Now enter the first two cells that you want to compare as subtraction in the top field of the new column. The syntax field then contains, for example, "= C2 - B2".
• If the values ​​are the same, the result is 0. Otherwise, you get a positive or a negative number that represents the difference between the values ​​in both fields.
• Now copy the formula into the entire column by dragging the cell at the lower right corner with the mouse or use the "Auto Fill" function.

Extended column comparison in Excel using the IF function

It is often not enough to calculate the new column using the other two. Use the following method to adapt the output to your needs, regardless of whether there are numbers or terms in your worksheet.

• In the column next to the two you want to compare, first insert the logical formula "IF". This carries out a so-called truth test about a statement about certain cells or cell areas.
• For example, if you want to check whether the content in cell A1 corresponds to that in cell B1, the truth test is "A1 = B1". In contrast to the "AND" function, "IF" does not simply distinguish between "TRUE" and "FALSE".
• Instead, you determine yourself what happens in the cell, depending on whether the truth test is positive or negative. The fields "Wert_wenn_wahr" and "Wert_wenn_falsch" serve this purpose.
• These can be simple numbers or words, but also mathematical calculations. For example, if you only want to calculate the difference for negative truth tests, your formula could be: "= IF (C2> = B2;" Goal achieved "; C2-B2)"

Find column of entries

You can also program Excel to find the column in which a certain value is located. So you can easily determine where an entry is in your workbook. located.

• The COMPARE function shows the number of the column in which a particular table entry is located. Because you define the search area yourself, this number does not necessarily correspond to the count in the workbook.
• The COMPARE function has even greater added value if it is built into the INDEX formula. Now you not only get the rough position of an entry, but also a comparison value of your choice.
• In our example, the number of pieces sold by the seller, which we enter in field C7, should be specified in field C8. The formula is then "= INDEX (C2: C5; COMPARISON (C7; A2: A5))".