If you are looking for some of the easiest ways to check if a value is in a list in Excel, then you are in the right place. To check out your desired value in a large range of data easily you can follow the methods of this article.

**Table of Contents**hide

## Download Workbook

## 10 Ways to Check If a Value is in List in Excel

Here, in the following table, I have some information about some products of a company. I will use this table to demonstrate the ways of checking a value in a list easily. Like in this case the list will be the **Product column **of this table.

For this purpose, I have used *Microsoft Excel 365* version, you can use any other versions according to your convenience.

__Method-1__: Using Find & Select Option to Check If a Value is in List

You can use the **Find & Select **Option to check a value in the *Product *list below. Here, we are searching for the product *Banana*.

** Step-01**:

➤Go to

**Home**Tab>>

**Editing**Groups>>

**Find & Select**Dropdown>>

**Find**Option.

Now, the **Find and Replace **Dialog Box will appear.

➤Write down the name of the product which you are looking in the **Find what **Box (For this case it is *Banana*)

➤Select the following**Within→Sheet**

**Search→By Rows**

**Look in→Values**

➤Press

**Find All**Option

** Result**:

After that, you will get the cell position of the product

*Banana*in the list.

**Read More:** **How to Check If Value Exists in Range in Excel (8 Ways)**

__Method-2__: Using ISNUMBER and MATCH Function to Check If a Value is in List

Here, we have some items in the **Item column **which we want to check in the list of the products in the **product column**. After checking the result will appear in the **Result column**. In this method, we are doing this job by using **the ISNUMBER function **and **the MATCH function**.

** Step-01**:

➤Select the output cell

**F5**.

➤Type the following formula

`=ISNUMBER(MATCH(E5,$B$5:$B$10,0))`

Here, the **MATCH function **will return the position of the value in the **E5 **cell in the range **$B$5:$B$10 **if it is found otherwise it will return **#N/A**.

Then **ISNUMBER **will return **TRUE **if there is a number otherwise **FALSE**.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool.

** Result**:

In this way, you will get

**TRUE**for

**Apple**because it is in the

*Product*list and

**FALSE**for

**Tomato**which is not in the list.

__Method-3__: Using COUNTIF Function

You can use **the COUNTIF function** for checking the items in the *Product *list.

** Step-01**:

➤Select the output cell

**F5**.

➤Type the following formula

`=COUNTIF($B$5:$B$10,E5)>0`

**$B$5:$B$10 **is the range where you are checking your desired value and **E5 **is the value which you are looking for.

When **COUNTIF **finds the value in the list it will return a number depending on the occurrence of this value and so it will be greater than **0 **and so the output will be **TRUE **otherwise it will be **FALSE** if the value is not in the list.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool.

** Result**:

Afterward, you will get

**TRUE**for

**Apple**because it is in the

*Product*list and

**FALSE**for

**Tomato**which is not in the list.

__Method-4__: Using IF and COUNTIF Function

Here, we are using **the IF function **and **the COUNTIF function **for checking the items of the ** Item** column in the

**column.**

*Product*** Step-01**:

➤Select the output cell

**F5**.

➤Type the following formula

`=IF(COUNTIF($B$5:$B$10,E5)>0,"Matched","Not Matched")`

**$B$5:$B$10 **is the range where you are checking your desired value and **E5 **is the value which you are looking for.

When **COUNTIF **finds the value in the list it will return a number depending on the occurrence of this value and so it will be greater than **0 **and then **IF **will return **Matched **otherwise it will return **Not Matched** if the value is not in the list.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool.

** Result**:

Afterward, you will get

**Matched**for

**Apple**because it is in the

*Product*list and

**Not Matched**for

**Tomato**which is not in the list.

__Method-5__: Checking Partial Match with Wildcard Operators

In the following table, we have *Apple *and *Berry *in the **Item column **but they are not fully matched (we have modified *Apple* in the dataset to *Green Apple* for explaining this case) rather than partially matched in the *Product *list. So, to check the values which are partially matched in the list here we are using **the** **IF function **and **the COUNTIF function**.

** Step-01**:

➤Select the output cell

**F5**.

➤Type the following formula

`=IF(COUNTIF($B$5:$B$10,"*"&E5&"*")>0,"Matched","Not Matched")`

**$B$5:$B$10 **is the range where you are checking your desired value and **E5 **is the value which you are looking for and **“*” **is joined with this value by using the **Ampersand **operator. **“*” **is used for partially matching the values.

When **COUNTIF **finds the value in the list it will return a number depending on the occurrence of this value and so it will be greater than **0 **and then **IF **will return **Matched **otherwise it will be **Not Matched** if the value is not in the list.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool.

** Result**:

After that, you will get

**Matched**for

**Apple**because it is in the

*Product*list as

**Green Apple**and

**Matched**for

**Berry**which is in the list as

**Strawberry**and

**Blackberry**.

__Method-6__: Using OR Function to Check If a Value is in List

You can use **the OR function **to check the values of the **Item column **in the **Product column**.

** Step-01**:

➤Select the output cell

**F5**.

➤Write the following formula

`=OR($B$5:$B$10=E5)`

**$B$5:$B$10 **is the range where you are checking your desired value and **E5 **is the value which you are looking for.

When the value matches in the list **OR **will return **TRUE **otherwise **FALSE**.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool.

📓**Note**If you are using any version other than

*Microsoft Excel 365*, then you have to press

**CTRL+SHIFT+ENTER**instead of pressing

**ENTER**.

** Result**:

Then, you will get

**TRUE**for

**Apple**because it is in the

*Product*list and

**FALSE**for

**Tomato**which is not in the list.

__Method-7__: Using IF Function to Check If a Value is in List

You can use **the IF function **to check the values of the **Item column **in the **Product column**.

** Step-01**:

➤Select the output cell

**F5**.

➤Write the following formula

`=IF(($B$5:$B$10=$E$5:$E$10),"Matched","Not Matched")`

**$B$5:$B$10 **is the range where you are checking your desired value and **$E$5:$E$10 **is the range of value which you are looking for.

When the value matches in the list **IF **will return **Matched **otherwise **Not Matched**.

➤Press **ENTER**

** Result**:

After that, you will get

**Matched**for

**Apple**,

**Banana**,

**Blackberry**because they are in the

*Product*list and

**Not Matched**for

**Orange**,

**Jackfruit**, and

**Strawberry**which are not in the list.

📓**Note**If you are using any version other than

*Microsoft Excel 365*, then you have to press

**CTRL+SHIFT+ENTER**instead of pressing

**ENTER**.

**Read More:** **How to Check If a Cell is Empty in Excel (7 Methods)**

__Method-8__: Using ISERROR and VLOOKUP Function

Here, we are using **the IF function**, **the ISERROR function**, and **the VLOOKUP function **for checking the items of the ** Item** column in the

**column.**

*Product*** Step-01**:

➤Select the output cell

**F5**.

➤Type the following formula

`=IF(ISERROR(VLOOKUP(E5,$B$5:$B$10,1,FALSE)),"Not Matched","Matched")`

**VLOOKUP **will look for the value of the cell **E5 **in the **$B$5:$B$10 **range, where **1 **is the **column index number **and **FALSE **is for an **exact match**.

If the value cannot be determined or does not match then **The ISERROR function** will return **TRUE **otherwise **FALSE**.**The IF function **will convert **TRUE **to **Not Matched **and **FALSE ** to **Matched**.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool.

** Result**:

Afterward, you will get

**Matched**for

**Apple**because it is in the

*Product*list and

**Not Matched**for

**Tomato**which is not in the list.

__Method-9__: Using ISERROR INDEX and MATCH Function

You can use **the IF function**, **the ISERROR function**, **the INDEX function**, and** the MATCH function **for checking the items of the ** Item** column in the

**column.**

*Product*** Step-01**:

➤Select the output cell

**F5**.

➤Type the following formula

`=IF(ISERROR(INDEX($B$5:$B$10,MATCH(E5,$B$5:$B$10,0))),"Not Matched","Matched")`

**MATCH **will look for the value of the cell **E5 **in the **$B$5:$B$10 **range, where **0 **is for an **exact match**.

If the value cannot be determined or does not match then **The ISERROR function** will return **TRUE **otherwise **FALSE**.**The IF function **will convert **TRUE **to **Not Matched **and **FALSE ** to **Matched**.

➤Press **ENTER**➤Drag down the

**Fill Handle**Tool.

** Result**:

Then, you will get

**Matched**for

**Apple**because it is in the

*Product*list and

**Not Matched**for

**Tomato**which is not in the list.

__Method-10__: Checking Multiple Values in a List

Here, we have an **Item List **that has different items separated by a comma and we want to match the items of this list in the **Product column**. For this purpose we will use **the IFERROR function**, **the INDEX function**, **the SMALL function**, **the IF function**, **the COUNTIF function**, **the MATCH function**.

** Step-01**:

➤Select the output cell

**F5**.

➤Type the following formula

`=IFERROR(INDEX($B$5:$B$10, SMALL(IF(COUNTIF(B13, "*"&$B$5:$B$10&"*"), MATCH(ROW($B$5:$B$10), ROW(B5:B10)), ""), COLUMNS($B$13:B13))), "")`

**$B$5:$B$10 **is the range where you are checking your desired value and **B13 **is the value which you are looking for.

Here, **IF **will return the *Product’s *name which will be matched in the list otherwise it will return **Blank**.

➤Press **ENTER**

** Result**:

Afterward, you will get

**Apple**because it is on the

*Product*list.

**Read More:** **VBA to Check If Cell is Empty in Excel (5 Methods)**

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below for each method in each sheet on the right side. Please do it by yourself.

## Conclusion

In this article, I tried to cover the easiest ways to check if a value is in a list in Excel effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.