Check If Cell Contains All Values from a Range in Excel Office 365!!
Tags: checkifacellcontains, excelpreface, excelbasics, exceltutorials
Published: 2021-01-25 10:35:35

---
title: Check If Cell Contains All Values from a Range in Excel Office 365!!
published: true
date: 2021-01-23 17:21:47 UTC
tags: checkifacellcontains,ExcelPreface,ExcelBasics,ExcelTutorials
canonical_url: https://geekexcel.com/check-if-cell-contains-all-values-from-a-range-in-excel-office-365/
---

In this article, we will explain how to **check if a cell contains all values from a range in Excel Office 365**. We are going to use a combination of **SEARCH, SUMPRODUCT, ISNUMBER,** and **COUNTA** functions. If the cell contains all values from a range, then it returns **TRUE** , else returns **FALSE**. Let’s see the formula syntax, explanation, and best example.
## Syntax and its Explanation:

- Use the below-given formula to check if a cell contains all values from a range in your worksheet.

**=[SUMPRODUCT](https://geekexcel.com/how-to-use-excel-sumproduct-function-in-office-365/)(– [ISNUMBER](https://geekexcel.com/how-to-use-isnumber-function-in-microsoft-excel-365/)([SEARCH](https://geekexcel.com/use-search-function-in-microsoft-excel-2013-simple-methods/)($D$1:$D$3,A1)))=[COUNTA](https://geekexcel.com/how-to-use-excel-counta-function-in-office-365-with-examples/)($D$1:$D$3)**  

- **[SUMPRODUCT function](https://geekexcel.com/how-to-use-excel-sumproduct-function-in-office-365/) –** In this function, the values within the arrays are multiplied and the products are added and returned as output.
- **[ISNUMBER function](https://geekexcel.com/how-to-use-isnumber-function-in-microsoft-excel-365/) –** It returns TRUE when a cell contains a number, and FALSE if there will not be a number.
- **[SEARCH function](https://geekexcel.com/use-search-function-in-microsoft-excel-2013-simple-methods/) –** This function locates the character between two text strings and returns the number of the starting position of the first text string from the first character of the second text string.
- **[COUNTA function](https://geekexcel.com/how-to-use-excel-counta-function-in-office-365-with-examples/) –** It counts the number of cells that are non-empty and returns the output in numbers.

## Practical Example:

You need to follow the below steps to achieve this task.

- For instance, we have shown you an example data in the range **A1:A4** , and the values that you want to check are given in the range **D1:D3**.

![Example data](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/EXCEL_N2GFFSFWer.png)<figcaption id="caption-attachment-21339">Example data</figcaption>

- We are going to **apply** the **formula** in **Cell B1.**

![Apply the formula](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/EXCEL_Py8INRsYwg.png)<figcaption id="caption-attachment-21340">Apply the formula</figcaption>

- After applying the formula, just **press** the **Enter** key to get the result as shown in the below screenshot.

![Click Enter button](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/EXCEL_3Ew3cM86m0.png)<figcaption id="caption-attachment-21341">Click Enter button</figcaption>

- To fill the remaining cells, you need to **drag cell B1 down.**
- Now, you will get the result for all remaining cells.

![Result](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/01/EXCEL_xbpV7DkD6G.png)<figcaption id="caption-attachment-21342">Result</figcaption>

## Sum-Up:

In the above article, you can easily understand the steps to **check if a cell contains all values from a range in Excel Office 365**. Feel free to share your **queries/feedback** with us in the below comment box. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)** Keep Learning!!

### Keep Reading:

- [Lookup the Next Largest Value in Excel Office 365!!](https://geekexcel.com/lookup-the-next-largest-value-in-excel-office-365/)
- [Transpose Values Based on Multiple Lookup Criteria in Excel!](https://geekexcel.com/transpose-values-based-on-multiple-lookup-criteria-in-excel-office-365/)