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.

### Method 1 – Using the COUNTIF Function to Check If a Value Exists in a Range

We will get the results as **TRUE **or **FALSE **in the Status column.

**Steps**:

- Use the following formula in cell
**F4**

`=COUNTIF($B$4:$B$10,E4)>0`

- Press
**Enter**and drag down the**Fill Handle**tool.

- Here are the results.

### Method 2 – Using IF and COUNTIF Functions to Check If a Value Exists in a Range

We’ll use the same dataset. We’ll return custom texts as results.

**Steps**:

Use the following formula in cell **F4**

`=IF(COUNTIF($B$4:$B$10,E4)>0,"Exist","Does not Exist")`

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.

Here are the results.

### Method 3 – Checking a Partial Match of Values in a Range

We will check the partial match of the products. We’ve modified the dataset a bit.

**Steps**:

- Use the following formula in cell
**F4**

`=COUNTIF($B$4:$B$10,"*"&E4&"*")>0`

Adding the **Asterisk **symbol before and after the value of cell **E4 **will account for zero or more other characters around it.

- Press
**Enter**and drag down the**Fill Handle**tool.

- Here are the results.

### Method 4 – Using ISNUMBER and MATCH Functions to Check If a Value Exists in a Range

We’ll use the same dataset.

**Steps**:

- Use the following formula in cell
**F4**

`=ISNUMBER(MATCH(E4,$B$4:$B$10,0))`

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

- Here are the results.

**Read More: **How to Use Excel IF Function with Range of Values

### Method 5 – Check If a Value Exists in a Range Using IF, ISNA, and VLOOKUP Functions

We’ll use the same dataset.

**Steps**:

- Use the following formula in cell
**F4**

`=IF(ISNA(VLOOKUP(E4,$B$4:$B$10,1,FALSE)),"Does Not Exist","Exists")`

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

- Here are the results.

### Method 6 – Using IF, ISNA, and MATCH Functions to Check If a Value Exists in a Range

We’ll use the same dataset.

**Steps**:

- Use the following formula in cell
**F4**

`=IF(ISNA(MATCH(E4,$B$4:$B$10,0)),"Does Not Exist","Exists")`

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

- Here’s the result.

### Method 7 – Conditional Formatting to Check If Value Exists in Range

We will 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**(We have selected the column*Order List*). - Go to the
**Home**tab, choose**Conditional Formatting,**and select**New Rule**.

- The
**New Formatting Rule**wizard will appear. - Select
**Use a formula to determine which cells to format**, and click the**Format**option.

- The
**Format Cells**dialog will open. - Select the
**Fill**tab. - Choose any
**Background Color**and click on**OK**.

- The
**Preview**will be shown as below.

- Use the following formula in the
**Format values where this formula is true**box:

`=MATCH(E4,$B$4:$B$10,0)`

- Press
**OK**.

- This highlights all the cells that fill the formula’s criteria.

**Read More: **How to Use IF Function with Multiple Conditions in Excel

### Method 8 – Using VBA Code to Check If a Value Exists in a Range in Excel

We’ll use the same starting dataset.

**Steps**:

- Go to the
**Developer**tab and select**Visual Basic**.

- The
**Visual Basic Editor**will open. - Go to the
**Insert**tab and select**Module**.

- A
**Module**will be created.

- Insert the following code in the module:

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

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

## Practice Section

We have provided a Practice section like below in a sheet named Practice.

**Download the Practice Workbook**

## Related Articles

**<< Go Back to Excel IF Function | Excel Functions | Learn 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 Listcolumn. I will use a formula that will match a product from theOrder Listcolumn with products from theProduct List 1column, for matches, the name of the product will return. Otherwise, the formula will search for matches in theProduct List 2column 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 Handletool.Finally, we are having

Green AppleandKiwias they appear in theProduct List 2column, andBananaas it appears in theProduct List 1column.Best Regards,

Tanjima Hossain

ExcelDemy