How To Find Excel Range contains a value, not in another range in Excel?
Tags: excelformula, excelformulas
Published: 2021-10-25 12:41:28

---
title: How To Find Excel Range contains a value, not in another range in Excel?
published: true
date: 2021-10-22 11:33:08 UTC
tags: ExcelFormula,Excelformulas
canonical_url: https://geekexcel.com/how-to-find-excel-range-contains-a-value-not-in-another-range-in-excel/
---

For Example, we have a list of values, and we want to find an Excel range what would we do? In this article, I introduce a formula that can quickly help you **find an**  **Excel range containing a Value, not in another range** in Excel.

To find an Excel range, you can use a formula based on the [**SUMPRODUCT**  ](https://geekexcel.com/how-to-use-excel-sumproduct-function-in-office-365/) **function**. Let’s see them below!! Get an official version of ** MS Excel**  from the following link: [https://www.microsoft.com/en-in/microsoft-365/excel](https://www.microsoft.com/en-in/microsoft-365/excel)

## Excel Range

- **Excel range** is a **collection of cells** (two or more cells).
- The **selected cells** are called as **“current selection”** in the spreadsheet.

## Generic Formula

- You need to use the below-given formula to find an Excel range containing a Value or not in another range.

```
=[SUMPRODUCT](https://geekexcel.com/how-to-use-excel-sumproduct-function-in-office-365/)(--([ISNA](https://geekexcel.com/how-to-use-isna-function-in-microsoft-excel-365/)([MATCH](https://geekexcel.com/how-to-use-match-function-in-microsoft-excel-365/)(rngA,rngB,0))))>0

```

## Syntax Explanation

- **SUMPRODUCT**  – In Excel, the [**SUMPRODUCT Function**](https://geekexcel.com/how-to-use-excel-sumproduct-function-in-office-365/) will help to multiply the corresponding array or range and return the result as the sum of the products.
- **MATCH ** – The **[MATCH function](https://geekexcel.com/how-to-use-match-function-in-microsoft-excel-365/)** helps to locate the position of a lookup value in a row, column, or table.
- **ISNA** – The **[ISNA function](https://geekexcel.com/how-to-use-isna-function-in-microsoft-excel-365/)** returns TRUE when a cell contains the #N/A error and FALSE for any other value, or any other error type.
- **Comma symbol (,)** – It is a separator that helps to separate a list of values.
- **Parenthesis ()** – The main purpose of this symbol is to group the elements.
- **rng**  – It represents the input range.

## Practical Example

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

- For instance, we have to **create a sample data** with **range contains list values**.

![](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/10/Sample-data-42.png)<figcaption>Sample data</figcaption>

- Here, you need to  **find ** out the **Excel range containing a Value, not in another range**.
- Then, you have to apply the below-given formula to the **[formula bar](https://geekexcel.com/how-to-use-formula-bar-for-various-purpose-in-excel-365/)** section and hit  **Enter**   **key**.

![](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/10/Use-the-formula-2.png)<figcaption>Use the formula</figcaption>

- Now, it will  **display the result**  as per the below image.

![](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/10/Output-41.png)<figcaption>Output</figcaption>

**Check this too:**

- [Excel Formulas To Find The Last Cell Address In A Range](https://geekexcel.com/excel-formulas-find-last-cell-address/)
- [Formulas To Find The Address Of First Cell In A Range](https://geekexcel.com/excel-formulas-to-find-the-address-of-first-cell-in-a-range/)
- [Excel Formulas To Find The Minimum Value From A Range](https://geekexcel.com/excel-formulas-find-minimum-value/)

## Sum-Up

In the above article, you can easily understand the steps to  **find an Excel range containing a Value, not in another range** in Excel. Feel free to share your  **queries/feedback**  with us in the below comment box. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)** Keep Learning!!

**Read Also:**

- **[Excel Formulas to Check If the Cell Value is NOT This or That!!](https://geekexcel.com/excel-formulas-to-check-if-the-cell-value-is-not-this-or-that/)**
- **[Check If Cell Contains All Values from a Range in Excel Office 365!!](https://geekexcel.com/check-if-cell-contains-all-values-from-a-range-in-excel-office-365/)**
- **[Excel Formulas to Check If the Cells Contain Certain Text!!](https://geekexcel.com/excel-formulas-check-if-cells-contain-certain-text/)**