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.

Check if a range contains some values with formula

Check if a range contains some values with formula

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:

Note:

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.

Select Cells/Rows/Columns with one or two criteria in Excel

The select Specific Cells of Kutools for Excel can quicky select all cells or rows or columns in a range based on one criterion or two criterion. Click for 30 days free trial!

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.

Relative Articles:

• How to check if a sheet is empty in Excel?
• How to check if a sheet exists in a workbook?