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 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 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 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.
Similar Readings
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 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
=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 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
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”.
• 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.
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.
Best Regards,
Tanjima Hossain
ExcelDemy