How to Check If Value Exists in Range in Excel (8 Ways)

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

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

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

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

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

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

Similar Readings

• How to Use Excel IF Function with Range of Values
• How to Use Excel IF Between Multiple Ranges

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

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

➤ 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 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

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

➤ 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

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 Lemon in the 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

• How to Use Multiple IF Statements with Text in Excel
• How to Write Greater Than or Equal To in Excel IF Function
• How to Use MAX IF Function in Excel
• If a Value Lies Between Two Numbers Then Return Result in Excel
• How to Check If a Value Is Between Two Numbers in Excel
• How to Make Yes 1 and No 0 in Excel
• [Fixed!] IF Function Is Not Working in Excel