How to quickly check if a range contains some values in Excel?
If there is a huge range and each cell contains several data, how can you quickly check if it contains some values in a column as below screenshot shown? In this article, I introduce some tricks for 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:
Notes:
1. In the formula, $A$2:$A$6 is the range you want to use, B2 is the value you want to check if it appears in the range.
2. This formula also works for a range where each cell contains only one data.
Relative Articles:
Best Office Productivity Tools
Breaking News: Kutools for Outlook Launches Free Version!
Experience the all-new Kutools for Outlook with 100+ incredible features! Click to download now!
📧 Email Automation: Auto Reply (Available for POP and IMAP) / Schedule Send Emails / Auto CC/BCC by Rules When Sending Email / Auto Forward (Advanced Rules) / Auto Add Greeting / Automatically Split Multi-Recipient Emails into Individual Messages ...
📨 Email Management: Recall Emails / Block Scam Emails by Subjects and Others / Delete Duplicate Emails / Advanced Search / Consolidate Folders ...
📁 Attachments Pro: Batch Save / Batch Detach / Batch Compress / Auto Save / Auto Detach / Auto Compress ...
🌟 Interface Magic: 😊More Pretty and Cool Emojis / Remind you when important emails come / Minimize Outlook Instead of Closing ...
👍 One-click Wonders: Reply All with Attachments / Anti-Phishing Emails / 🕘Show Sender's Time Zone ...
👩🏼🤝👩🏻 Contacts & Calendar: Batch Add Contacts From Selected Emails / Split a Contact Group to Individual Groups / Remove Birthday Reminders ...
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!
🚀 One-Click Download — Get All Office Add-ins
Strongly Recommended: Kutools for Office (5-in-1)
One click to download five installers at once — Kutools for Excel, Outlook, Word, PowerPoint and Office Tab Pro. Click to download now!
- ✅ One-click convenience: Download all five setup packages in a single action.
- 🚀 Ready for any Office task: Install the add-ins you need, when you need them.
- 🧰 Included: Kutools for Excel / Kutools for Outlook / Kutools for Word / Office Tab Pro / Kutools for PowerPoint