How to find matching values in two columns in Excel

Suppose you're working with a table filled by a colleague who enters data in an unstructured manner, specifically related to sales volume for certain products. One of your tasks is to compare this data. You need to check if a column in the table contains a specific value or not. Of course, you can use the "Find" tool (keyboard shortcut CTRL+F), but for regular searches, this method becomes inconvenient. Moreover, this tool does not allow you to perform calculations with the search results. It's essential for every user to learn how to automatically solve tasks in Excel.



Using the EXACT Function to Compare Columns

Table Comparison with Dropdown

To automate this process, you can use a formula involving the =OR() and =EXACT() functions.

Two Tables

Here's how you can easily check for product presence in the table:

  1. In cell D2, enter the product name, for example, "Monitor."
  2. In cell E2, enter the following:
    3. =OR(EXACT(D2,$A$2:$A$12))
  3. After entering the formula, make sure to press CTRL+SHIFT+Enter to confirm. This needs to be entered as an array formula. If done correctly, you'll see curly braces in the bar.
EXACT Function

The formula will return a TRUE or FALSE logical value depending on whether the table contains the specified value or not.

For example, you can use a formula that returns logical values for conditional formatting and in combination with a dropdown list. This results in a convenient interface for comparing two data tables (the original and the data referenced by the dropdown list):

Table Comparison with Dropdown

Explanation of how the formula to compare two columns in different tables works:

The =EXACT() function compares (with case sensitivity) whether two values are identical or not. If they are, it returns a TRUE logical value. Since code is entered as an array formula, the =EXACT() function compares the value in cell B1 with each value in the range A5:A10. Thanks to the =OR() function, the formula returns the results of the =EXACT() function separately. Without using the OR function, the formula would return only the result of the first comparison.

Here's how you can apply multiple such formulas in practice to compare two columns in different tables simultaneously:

Comparing Values in Two Tables for Matches

download file Download

Simply enter the array of formulas into one cell (E2), and then copy it to all the other cells in the range E2:E8. Note that we're now using absolute references to the range $A$2:$A$12 in the second argument of the EXACT function.