How to Check If a Value Is in List in Excel (10 Ways)

Consider a dataset about some products of a company. We will check whether specific values in the Product column exist.

check if a value is in list in Excel


Method 1 – Using Find & Select to Check If a Value Is in a List

We are searching for the product Banana.

check if a value is in list in Excel

  • Go to the Home tab, select Find & Select, and pick Find.

Find & Select

  • The Find and Replace dialog box will appear.
  • Write down the name of the product you are looking for in the Find what box (Banana)
  • Select the following: Within → Sheet; Search → By Rows; Look in → Values.
  • Press Find All.

Find & Select

  • You will get the cell positions of the product Banana in the list.

check if a value is in list in Excel


Method 2 – Using ISNUMBER and MATCH Functions to Check If a Value Is in a List

We have some items in the Item column which we want to check in the list of the products in the Product column. The check result will appear in the Result column.

check if a value is in list in Excel

  • Select the output cell F5.
  • Insert the following formula:
=ISNUMBER(MATCH(E5,$B$5:$B$10,0))

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. ISNUMBER will return TRUE if the value is a number (i.e. if MATCH finds something).

ISNUMBER+MATCH function

  • Hit Enter and drag down the Fill Handle tool.

ISNUMBER+MATCH function

  • Here are the results.

ISNUMBER+MATCH function


Method 3 – Using the COUNTIF Function

We’ll use the same conditions as in Method 2.

check if a value is in list in Excel

  • Select the output cell F5.
  • Insert the following formula
=COUNTIF($B$5:$B$10,E5)>0

COUNTIF will return how many times the check value appears in the array, so it will be greater than 0 if it finds a result. The output will then be TRUE.

COUNTIF function

  • Hit Enter and drag down the Fill Handle.

COUNTIF function

Results:

check if a value is in list in Excel


Method 4 – Using IF and COUNTIF Functions

We’ll use the same dataset as before.

check if a value is in list in Excel

  • Select the output cell F5.
  • Insert 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 of occurrences of this value, so it will be greater than 0. IF will then return Matched. Otherwise, it will return Not Matched if the value is not in the list.

IF+COUNTIF function

  • Hit Enter and drag down the Fill Handle tool.

IF+COUNTIF function

Results

check if a value is in list in Excel


Method 5 – Checking a Partial Match with Wildcards

In the following table, we have Apple and Berry in the Item column but they are not exact matches to the product names (we have modified Apple in the dataset to Green Apple).

check if a value is in list in Excel

  • Select the output cell F5.
  • Insert the following formula:
=IF(COUNTIF($B$5:$B$10,"*"&E5&"*")>0,"Matched","Not Matched")

E5 is the check value, where “*” is joined with this value by using the Ampersand operator. “*” is used to replace zero or more characters.

checking partial match

  • Hit Enter and drag down the formula with the Fill Handle.

checking partial match

Results

check if a value is in list in Excel


Method 6 – Using the OR Array Function to Check If a Value Is in a List

We’ll use the same dataset.

check if a value is in list in Excel

  • Select the output cell F5.
  • Insert the following formula:
=OR($B$5:$B$10=E5)

OR function

  • Hit Enter and drag down the Fill Handle to fill the other cells. If you are using any version other than Excel 365, press Ctrl + Shift + Enter instead of pressing Enter.

OR function

Results

check if a value is in list in Excel


Method 7 – Using the IF Function to Check If Values in Lists Match

We’ll crosscheck two lists for matches across rows.

check if a value is in list in Excel

  • Select the output cell F5.
  • Insert the following formula:
=IF(($B$5:$B$10=$E$5:$E$10),"Matched","Not Matched")

IF function

  • Press Enter or Ctrl + Shift + Enter (for Excel versions other than Excel 365).

Results

IF function


Method 8 – Using the ISERROR and VLOOKUP Functions

We’ll go back to the base dataset and check item-wise.

check if a value is in list in Excel

  • Select the output cell F5.
  • Insert the following formula:
=IF(ISERROR(VLOOKUP(E5,$B$5:$B$10,1,FALSE)),"Not Matched","Matched")

ISERROR+VLOOKUP function

  • Hit Enter and drag down the Fill Handle.

ISERROR+VLOOKUP function

Results

check if a value is in list in Excel


Method 9 – Using ISERROR, INDEX, and MATCH Functions

We’ll use the same dataset as before.

check if a value is in list in Excel

  • Select the output cell F5.
  • Insert the following formula:
=IF(ISERROR(INDEX($B$5:$B$10,MATCH(E5,$B$5:$B$10,0))),"Not Matched","Matched")

INDEX+MATCH function

  • Hit Enter and drag the Fill Handle down to fill other cells in the column.

INDEX+MATCH function

Results

INDEX+MATCH function


Method 10 – Checking Multiple Values in a List

We have a single cell that contains multiple values that may or may not be in the list. We’ll return the list of items that do appear in a list.

check if a value is in list in Excel

  • Select the output cell D13.
  • Insert 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))), "")

checking multiple values

  • Press Enter.

Results

checking multiple values


Practice Section

We have provided a Practice section like below for each method in each sheet.

practice


Download the Practice Workbook


Further Readings


<< Go Back to Find Value in Range | Excel Range | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

2 Comments
  1. METHOD 6: The use of OR in this context can be problematic in some edge cases because OR is not designed to handle array comparisons directly.

    • Hello Savoir-faire,

      Thanks for pointing this out! Using OR with arrays can sometimes lead to unreliable results, especially in non-array formulas or older Excel versions. It’s better to use more robust methods like MATCH or COUNTIF, which handle list comparisons more efficiently and avoid edge cases where OR might fail. We used OR in this context is its simplicity and readability. For users working with Excel 365 or newer versions, this method provides an easy way to compare multiple values without needing complex functions like MATCH or COUNTIF. It’s particularly helpful for small datasets and straightforward checks. Additionally, the OR function works well for quick visual comparisons in smaller spreadsheets.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo