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 difficult 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.

## How to Check If Value Exists in Range in Excel: 8 Easy Ways

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.

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

Here, we will use the** IF **and **COUNTIF** functions 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 *Lemon *which are available in the *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 that 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 **and **MATCH** functions 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.

**Read More: **How to Use Excel IF Function with Range of Values

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

You can use the **IF**, **ISNA**, **VLOOKUP** functions 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 *Lemon *which are available in the *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**, **ISNA**, and** MATCH** functions 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 *Lemon *which are available in the *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 Use IF Function with Multiple Conditions in Excel

__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 *Lemon *which are available in the *Product List* range, and for the unavailable products we are getting *Does not exist.*

## 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.

**Download Workbook**

## 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.

## Related Articles

**<< Go Back to Excel IF Function | Excel Functions | Learn Excel**

I have two columns in one table – either column could contain a match with a range on another table… how can I write a formula that checks for a match first in one of the columns, and if there is a match it returns that value, and if there is no match, it checks the other column for a match, and returns that value. If no matches in either column, then “no match”. Help?

Hello JORDAN,

Hope you are doing well! As per your requirement, I am considering the following scenario where in a table I have two lists of products with which I will compare the products in the

Order Listcolumn. I will use a formula that will match a product from theOrder Listcolumn with products from theProduct List 1column, for matches, the name of the product will return. Otherwise, the formula will search for matches in theProduct List 2column and will return the product name if any matches are found. Otherwise, we will get “No Match”.• Enter the following formula in cell

F4.`=IF (COUNTIF ($B$4: $B$10, E4)>0, E4, IF (COUNTIF ($C$4: $C$10, E4)>0, E4, "No Match"))`

• Drag down the

Fill Handletool.Finally, we are having

Green AppleandKiwias they appear in theProduct List 2column, andBananaas it appears in theProduct List 1column.Best Regards,

Tanjima Hossain

ExcelDemy