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 |

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:

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

## Leave a Reply

You must be logged in to post a comment.