If you are looking for some of the easiest ways to check if a value exists in range in Excel, then you will find this article useful. For a large dataset, it is quite nettlesome to find the desired value in a range.

So, to make this task easier you can follow this article for exploring different ways to check a value in a range.

**Table of Contents**hide

## Download Workbook

## 8 Ways to Check If Value Exists in Range in Excel

Here, we have the **Product List **and the **Order List **of the products of a company, and we want to check if the products of the **Order List **are available in the **Product List**. To check the values in the range of the **Product List **column, and then get status about the availability of the products we are going to discuss the following 8 ways here.

We have used *Microsoft Excel 365* version here, you can use any other versions according to your convenience.

__Method-1__: Using COUNTIF Function to Check If Value Exists in Range in Excel

We will check the products of the **Order List **column in the range of the **Product List **column by using the **COUNTIF function** and then we will get the results as **TRUE **or **FALSE **in the **Status **column.

** Steps**:

➤ Type the following formula in cell

**F4**

`=COUNTIF($B$4:$B$10,E4)>0`

Here, **$B$4:$B$10 **is the range of the **Product List**, **E4 **is the value to check in this range. When the value matches it will return **1 **and then because of being greater than **0 **it will return **TRUE**, otherwise **FALSE**.

➤ Press **ENTER **and drag down the **Fill Handle **tool.

As a result, you will get **TRUE **for the products that are available in the **Product List **and **FALSE **for the unavailable products.

**Read More:** **How to Check If a Value is in List in Excel (10 Ways)**

__Method-2__: Using IF and COUNTIF Functions to Check If Value Exists in Range

Here, we will use the **IF function** and the **COUNTIF function **to check the values of the **Order List **column in the range of the **Product List **column.

** Steps**:

➤ Type the following formula in cell

**F4**

`=IF(COUNTIF($B$4:$B$10,E4)>0,"Exist","Does not Exist")`

Here, **$B$4:$B$10 **is the range of the **Product List**, **E4 **is the value to check in this range. When the value matches it will return **1 **and then because of being greater than **0 **it will return **TRUE**, otherwise **FALSE**.

For the result **TRUE**, we will get **Exist **and for **FALSE **we will get **Does not Exist**.

➤ Press **ENTER **and drag down the **Fill Handle **tool.

Finally, we are getting **Exist **for the products ** Banana **and

**which are available in the**

*Lemon***Product List**range, and for the unavailable products we are getting

**Does not Exist**.

__Method-3__: Checking Partial Match of Values in Range

Here, we will check the partial match of the products also (for this method we have interchanged the first product of the **Product List **and **Order List**) by putting a wildcard operator ** Asterisk** (*).

** Steps**:

➤ Type the following formula in cell

**F4**

`=COUNTIF($B$4:$B$10,"*"&E4&"*")>0`

Here, **$B$4:$B$10 **is the range of the **Product List**, **E4 **is the value which we will check in this range.

After adding the ** Asterisk **symbol before and after the value of cell

**E4**, it will check the values for the partial matches, like a substring in a string.

➤ Press **ENTER **and drag down the **Fill Handle **tool.

As a result, we can see that in addition to the products **Banana **and **Lemon**, **Apple **and **Berry **are also giving **TRUE **for their partial matches with **Green Apple**, **Strawberry**, and **Blackberry **in the **Product List**.

__Method-4__: Using ISNUMBER and MATCH Functions to Check If Value Exists in Range

In this section, we will be using the **ISNUMBER function** and the **MATCH function** to check the values of the **Order List **column to the range of the **Product List **column.

** Steps**:

➤ Type the following formula in cell

**F4**

`=ISNUMBER(MATCH(E4,$B$4:$B$10,0))`

Here, **$B$4:$B$10 **is the range of the **Product List**, **E4 **is the value which we will check in this range.

**MATCH(E4,$B$4:$B$10,0) →**returns the row index number of the value*Green Apple*in cell**E4**in the range**$B$4:$B$10**, otherwise**#N/A**error for not matching the values**Output →**#N/A

**ISNUMBER(MATCH(E4,$B$4:$B$10,0))**becomes**ISNUMBER(#N/A) →**returns**TRUE**for any number values otherwise**FALSE****Output →**FALSE

➤ Press **ENTER **and drag down the **Fill Handle **tool.

Afterward, you will get **TRUE **for the products that are available in the **Product List **and **FALSE **for the unavailable products.

__Method-5__: Check If Value Exists in Range Using IF, ISNA, and VLOOKUP Functions

You can use the **IF function**, **ISNA function**, **VLOOKUP function** to check the values in the range of the **Product List **column to check their availability for completing order procedures.

** Steps**:

➤ Type the following formula in cell

**F4**

`=IF(ISNA(VLOOKUP(E4,$B$4:$B$10,1,FALSE)),"Does Not Exist","Exists")`

Here, **$B$4:$B$10 **is the range of the **Product List**, **E4 **is the value which we will check in this range.

**VLOOKUP(E4,$B$4:$B$10,1, FALSE) →**finds the exact match of the product*Green Apple*in the range**$B$4:$B$10**and extracts this value from this column and for not finding the value in the range returns**#N/A**.**Output →**#N/A

**ISNA(VLOOKUP(E4,$B$4:$B$10,1,FALSE))**becomes**ISNA(#N/A) →**returns**TRUE**if there is a**#N/A**error otherwise**FALSE****Output →**TRUE

**IF(ISNA(VLOOKUP(E4,$B$4:$B$10,1,FALSE)),”Does Not Exist”,”Exists”)**becomes**IF(TRUE, “Does Not Exist”, “Exists”) →**returns**Does Not Exist**for**TRUE**and**Exists**for**FALSE****Output →**Does Not Exist

➤ Press **ENTER **and drag down the **Fill Handle **tool.

Eventually, we are getting **Exists **for the products ** Banana **and

**which are available in the**

*Lemon***Product List**range, and for the unavailable products we are getting

**Does not Exist**.

__Method-6__: Using IF, ISNA, and MATCH Functions to Check If Value Exists in Range

In this section, we will use the combination of the **IF function**, **ISNA function**, **MATCH function **to determine the availability status of the products in the range **Product List**.

** Steps**:

➤ Type the following formula in cell

**F4**

`=IF(ISNA(MATCH(E4,$B$4:$B$10,0)),"Does Not Exist","Exists")`

Here, **$B$4:$B$10 **is the range of the **Product List**, **E4 **is the value which we will check in this range.

**MATCH(E4,$B$4:$B$10,0)**finds the exact match of the product*Green Apple*in the range**$B$4:$B$10**and gives the row index number of this product in the range**$B$4:$B$10**and for not finding the value in the range returns**#N/A**.**Output →**#N/A

**ISNA(MATCH(E4,$B$4:$B$10,0))**becomes**ISNA(#N/A) →**returns**TRUE**if there is a**#N/A**error otherwise**FALSE****Output →**TRUE

**IF(ISNA(MATCH(E4,$B$4:$B$10,0)),”Does Not Exist”,”Exists”)**becomes**IF(TRUE, “Does Not Exist”, “Exists”) →**returns**Does Not Exist**for**TRUE**and**Exists**for**FALSE****Output →**Does Not Exist

➤ Press **ENTER **and drag down the **Fill Handle **tool.

After that, we are getting **Exists **for the products ** Banana **and

**which are available in the**

*Lemon***Product List**range, and for the unavailable products we are getting

**Does not Exist**.

__Method-7__: Conditional Formatting to Check If Value Exists in Range

Here, we will use **Conditional Formatting** to highlight the products in the **Order List **column if they are available in the **Product List **column.

** Steps**:

➤ Select the cell range on which you want to apply the

**Conditional Formatting**(Here, we have selected the column

**Order List**)

➤ Go to the

**Home**Tab >>

**Styles**Group >>

**Conditional Formatting**Dropdown >>

**New Rule**Option.

Then, the **New Formatting Rule **wizard will appear.

➤ Select **Use a formula to determine which cells to format** option, and click the **Format **option.

After that, the **Format Cells **Dialog Box will open up.

➤ Select **Fill **Option

➤ Choose any **Background Color**, and then, click on **OK**.

Then, the **Preview **Option will be shown as below.

➤ Write the following formula in the **Format values where this formula is true: **box

`=MATCH(E4,$B$4:$B$10,0)`

If the value of cell **E4 **remains in the range **$B$4:$B$10**, then, it will highlight the corresponding cell.

➤ Press **OK**.

Ultimately, you will be able to highlight the cells containing ** Banana **and

**in the**

*Lemon***Order List**column because these products are available in the range of the

**Product List**column.

**Read More:** **How to Check If a Cell is Empty in Excel (7 Methods)**

__Method-8__: Using VBA Code to Check If Value Exists in Range in Excel

Here, we are going to use a **VBA** code to check the values of the **Order List **column in the range of the **Product List **column.

** Steps**:

➤ Go to the

**Developer**Tab >>

**Visual Basic**Option.

Then, the **Visual Basic Editor **will open up.

➤ Go to the **Insert **Tab >> **Module **Option.

After that, a **Module** will be created.

➤ Write the following code

```
Sub checkvalue()
Dim X As Variant
Dim Rng As Range
For i = 4 To 8
X = Cells(i, 5)
With Sheets("VBA").Range("B4:B10")
Set Rng = .Find(What:=X, After:=.Cells(.Cells.Count), _
LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not Rng Is Nothing Then
Cells(i, 6).Value = "Exists"
Else
Cells(i, 6).Value = "Does not exist"
End If
End With
Next i
End Sub
```

Here, we have declared **X** as **Variant**, **Rng** as **Range**, and here, **VBA **is the sheet name.

The **FOR** loop will execute the operations for each row of the column **Order List **from *Row 4 *to *Row8*, **Range(“B4:B10”) **is the range of the **Product List **column. **X **is assigned to the values of each cell of the **Order List **column and after finding the matching by using the **FIND function** we will get ** Exists **in the adjacent cell of the corresponding cell of this column. For not finding the value it will return

**.**

*Does not exist*➤ Press **F5**.

After that, we are getting **Exists **for the products ** Banana **and

**which are available in the**

*Lemon***Product List**range, and for the unavailable products we are getting

**Does not exist**.

**Read More:** **VBA to Check If Cell is Empty in Excel (5 Methods)**

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below in a sheet named **Practice**. Please do it by yourself.

## Conclusion

In this article, we tried to cover the ways to check if a value exists in a range Excel easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.