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

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.

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

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.

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

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 function and the MATCH function 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


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.

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 function, ISNA function, MATCH function 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 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.

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

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.

Practice


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo