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.

Name	Sales
access	45
word	66
Excel	34
ppt	23
Word	435
Word	443

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