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

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel check if value exists in range

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.

Excel check if value exists in range

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.

COUNTIF Function

➤ Press ENTER and drag down the Fill Handle tool.

COUNTIF Function

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

Excel check if value exists in range


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.

Excel check if value exists in range

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.

IF and COUNTIF Function

➤ Press ENTER and drag down the Fill Handle tool.

IF and COUNTIF Function

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.

Excel check if value exists in range


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 (*).

Excel check if value exists in range

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.

partial match

➤ Press ENTER and drag down the Fill Handle tool.

partial match

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.

Excel check if value exists in range


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.

Excel check if value exists in range

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

ISNUMBER & MATCH Function

➤ Press ENTER and drag down the Fill Handle tool.

ISNUMBER & MATCH Function

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

ISNUMBER & MATCH Function

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.

Excel check if value exists in range

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

IF, ISNA & VLOOKUP Function

➤ Press ENTER and drag down the Fill Handle tool.

IF, ISNA & VLOOKUP Function

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.

Excel check if value exists in range


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.

Excel check if value exists in range

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

IF, ISNA & MATCH Function

➤ Press ENTER and drag down the Fill Handle tool.

IF, ISNA & MATCH Function

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.

IF, ISNA & MATCH Function


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.

Excel check if value exists in range

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.

Conditional Formatting

Then, the New Formatting Rule wizard will appear.
➤ Select Use a formula to determine which cells to format option, and click the Format option.

Conditional Formatting

After that, the Format Cells Dialog Box will open up.
➤ Select Fill Option
➤ Choose any Background Color, and then, click on OK.

Excel check if value exists in range

Then, the Preview Option will be shown as below.

Conditional Formatting

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

Conditional Formatting

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.

Excel check if value exists in range

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.

Excel check if value exists in range

Steps:
➤ Go to the Developer Tab >> Visual Basic Option.

VBA Code

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

VBA Code

After that, a Module will be created.

VBA Code

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

VBA Code

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

Excel check if value exists in range


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.

Practice


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

2 Comments
  1. 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 List column. I will use a formula that will match a product from the Order List column with products from the Product List 1 column, for matches, the name of the product will return. Otherwise, the formula will search for matches in the Product List 2 column and will return the product name if any matches are found. Otherwise, we will get “No Match”.

      1

      • 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 Handle tool.

      2

      Finally, we are having Green Apple and Kiwi as they appear in the Product List 2 column, and Banana as it appears in the Product List 1 column.

      3

      Best Regards,
      Tanjima Hossain
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo