This post will guide you how to use VLOOKUP function to check if a value exists in a given range of cells in Excel. How to check if a specified value exists in a range and then return the value in the adjacent cell.
For example, you want to look up the text value “Excel” in the range B1:C7, and you found it in the Cell B4, then return the adjacent Sales value (C4) in the column C.
Table of Contents
1. Check If a Value Exists in a Range Using Formula
Let’s write down the following Excel Formula based on the VLOOKUP function:
Type this formula into the formula box in cell E1, then press Enter.
Let’s see how this formula works:
The VLOOKUP function can be used to check if a given values exists in a range of cell, then return the value in a specified column that is specified by the third argument in the function. So number 2 is the column number that we want to pick. The “Excel” is the value for that we want to lookup. B1:B7 is a range from which we want to lookup the value. The TRUE value indicates that we want to lookup an approximate match from range B1:C7.
2. Check If a Value Exists in a Range Using VBA
Let’s see the second method, we’ll delve into a more advanced yet highly powerful technique using a user-defined function with VBA.
Step1: Press Alt + F11 to open the VBA editor in Excel.
Step2: In the VBA editor, right-click on any item in the project explorer on the left.
Step3: Choose “Insert” and then “Module” to add a new module.
Step4: Copy the provided VBA code for the user-defined function.
Step5: Paste the code into the code window of the newly created module.
Function ValueExists(searchValue As Variant, searchRange As Range) As String If Not IsError(Application.Match(searchValue, searchRange, 0)) Then ValueExists = "Exists" Else ValueExists = "Doesn't Exist" End If End Function
Step6: Close the VBA editor by clicking the “X” button or pressing Alt + Q.
Step7: Go back to your Excel workbook. In any cell, type the following formula to use the newly created function:
Replace A1 with the value you want to check, and B1:B10 with the range you want to check against.
Step8: Press Enter, and the result will indicate whether the value exists or not.
Now you’ve successfully added and executed the VBA code to create a user-defined function for checking if a value exists in a range.
3. Video: Check If a Value Exists in a Range
This Excel video tutorial where we’ll explore two essential methods to determine if a value exists within a range. Let’s delve into the first method using Excel’s built-in functions, followed by a more advanced method employing a user-defined function with VBA.
4. Related Formulas
- Lookup Entire Row using INDEX/MATCH
If you want to lookup entire row and then return all values of the matched row, you can use a combination of the INDEX function and the MATCH function to create a new excel array formula.
- Extract the Entire Column of a Matched Value
If you want to lookup value in a range and then retrieve the entire row, you can use a combination of the INDEX function and the MATCH function to create a new excel formula..…
- Lookup the Next Largest Value
If you want to get the next largest value in another column, you can use a combination of the INDEX function and the MATCH function to create an excel formula..
- Excel VLOOKUP function
The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….