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.

1. Check If a Value Exists in a Range Using Formula

Let’s write down the following Excel Formula based on the VLOOKUP function:

=VLOOKUP("Excel", B1:C7,2,TRUE)

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:

=ValueExists(A1, B1:B10)

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

Related Functions

• 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])….