How to quickly check if a range contains some values in Excel?
If there are a huge range and each cell contains several data, and how can you quickly check if it contains some values in a column as below screenshot shown? In this article, I introduce some tricks on solving this problem.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Select a blank cell next to the value you want to check if appears in the range, type this formula =COUNTIF($A$2:$A$6,"*"&B2&"*")>0, and drag the auto fill handle down to check other values. See screenshot:
1. In the formula, A2:A6 is the range you want to use, B2 is the value you want to check if appears in the range.
2. This formula also can use to a range that each cell contains one data.