Any value in A also in B

I recently received a question: “I have two columns of numbers and want one cell at the top to tell me if any values in column A exist in column B.” In this blog post, we’ll walk through how to accomplish this using some essential Excel functions.

Video

Objective

The question asked how to write a formula that indicates if a value in List A is also found in List B. Such a formula is found in F7 in the image below:

We’ll walk through the functions needed to write such a formula, as well as how to use conditional formatting to identify the duplicate values, if any.

Tutorial

We’ll take this process step by step using a few exercises.

Exercise 1: Getting Familiar with the Functions

First, let’s familiarize ourselves with the key functions and operators we’ll be using.

COUNTIF Function

The COUNTIF function returns the number of cells within a range that meet a given criteria.

=COUNTIF(range, criteria)

For example, to count the number of cells in range A1:A10 that are equal to the value 2, we could use this:

=COUNTIF(A1:A10, 2)

That is the function that will do the heavy lifting for us, but let’s cover one more detail about comparison operators.

Comparison Operators

If we wanted our formula to return TRUE/FALSE instead of the actual count, we can simply add a comparison operator to our formula. For example, the greater than > or less than < operator.

While the formula above would return the actual count, the following formula would return TRUE if that count is greater than zero, and FALSE if not.

=COUNTIF(A1:A10, 2) > 0

With these basics covered, let’s head to the next exercise.

Exercise 2: Applying the Functions

Here’s the scenario: we have List A and List B. We want to determine if any values in List A also exist in List B.

We’ll build the formula logic in intermediate steps, one step at a time. Then at the end, we’ll combine all of the logic into a single formula.

Step 1: Get the count

First, we’ll use the COUNTIF function with a single criteria argument:

=COUNTIF($B$10:$B$16,C10)

Note the dollar signs in the range of the first argument. This absolute reference just ensures that as we fill the formula down, Excel won’t change the range reference.

We can fill the formula down:

When the COUNTIF returns 0, there are no matches in the other list. When it returns 1, it means there is one cell that matches (ie, appears in both lists).

Depending on your version of Excel, you may be able to include a range (instead of a single cell) as the second COUNTIF argument, like this:

=COUNTIF(B10:B16,C10:C18)

This alleviates the need for an absolute range reference as well as manually filling the formula down. Either way, the same results are returned:

Now let’s move to the final exercise.

Exercise 3: The Final Formula

As per the original question, we want a single formula that tells us if any values in List A exist in List B.

So, in any cell, we can summarize the results of the COUNTIF function by wrapping a SUM function around it, like this:

=SUM(COUNTIF(B10:B16,C10:C18))

And, if we’d like the result to indicate TRUE/FALSE instead of a count, we can use a comparison operator like this:

=SUM(COUNTIF(B10:B16,C10:C18))>0

This will tell us TRUE/FALSE if a value appears in both lists.

If we want to visually identify the duplicate values, we can use Excel’s Conditional Formatting feature. Here’s how:

1. Select the entire range that includes both lists, for example B10:C18.
2. Go to the Home tab.
3. Click on Conditional Formatting.
4. Select Highlight Cell Rules, then Duplicate Values.
5. Click OK.

This will highlight any duplicate values found within the selected ranges.

Mission accomplished!

Conclusion

And that’s how you can use Excel functions and conditional formatting to identify duplicate values in your data. I hope you found this tutorial helpful. If you have any questions or comments, please feel free to leave them below.

Sample File

FAQ

Q: Can I use this method with text values?

A: Yes, COUNTIF works with text values as well. Just ensure your criteria match the text exactly.

Q: What if I have more than two columns to compare?

A: You can extend this method by adding more COUNTIF functions or using MATCH for more complex scenarios.

Q: Will this work in older versions of Excel?

A: Yes, but dynamic arrays are only available in newer versions of Excel, such as Excel 365. For older versions, you’ll need to fill down the formulas manually.

Q: Is there a way to ignore case sensitivity in text comparison?

A: COUNTIF is not case-sensitive, so it treats “Apple” and “apple” as the same.

Q: Is there a way to extract the duplicate values into a separate list?

A: Yes, you can use the FILTER function to create a new list that contains only the duplicate values found in both lists. For example: =FILTER(B10:B16,ISNUMBER(MATCH(B10:B16,C10:C18,0)))