Three Simple Techniques to Determine Whether a Value Exists in an Excel ListTutorial with Screenshots

Written By:
Adiste Mae

People who work with Excel usually deal with a multitude of data.

When this is the case, it can be tiresome to check if specific data exist in the Excel list.

This is especially true for large amounts of data maintained in WPS Excel list versions 2016, 2019, Mac, or Online. 

We will be exploring a variety of shortcut methods to detect the presence of a value in a list within Excel through this article.

By the end of this topic, there is no question that you will now be proficient when it comes to checking values that exist in large data in an Excel list.

There are three techniques to verify if a value is present in an Excel list which we will explore in the sections below. 

The Use of the COUNTIF Function

To determine if a value is present in a list, follow the steps and illustrated images below.

Here are the steps in doing this: 

  1. Launch the Spreadsheet or WPS Excel file you need to check.
  2. Select the cell where you want the output to appear upon checking for the value in the list. Type the formula: “=COUNTIF” and press the Tab key.
  3. Initiate the COUNTIF Function which requires two parameters to be entered – the Range (the list to be checked for the value) and Criteria (the value enclosed in the inverted commas).

After clicking on Enter, the COUNTIF function will display the appearance frequency of the value searched for. 

Using Embedded CountIF in IF Function

Another approach to determining the presence of a value in a list is by embedding COUNTIF within the IF function.

Follow the steps below for this method: 

  1. Launch the Spreadsheet or WPS Excel file you need to check.
  2. Select the cell where you want the output to appear upon checking for the value in the list. Type the formula: =IF(COUNTIF” and press the Tab key.
  3. When the function is activated, it will require four parameters to be entered – the Range, Criteria Value IF True (use “Yes”), and Value IF False (use “No”).
  4. After pressing Enter, this method will display “yes” if the value exists or “no” if it does not.

The Use of the MATCH Function Embedded in the ISNUMBER Function

This is the final technique used for checking the existence of a value in an Excel list. The steps are outlined below:

  1. Launch the Spreadsheet or WPS Excel file you need to check.
  2. Select the cell where you want the output to appear upon checking for the value in the list. 
  3. Enter the formula “=ISNUMBER(MATCH” and press the Tab key. This will activate the ISNUMBER function with an embedded MATCH function. Three parameters are required to be entered in this function: Lookup Value (the value that needs to be verified), Lookup Array (where the value of the data needs to be checked), and Match Type (to generate the exact match, this must be set to 0).
  4. When you click on Enter, the result will show “true” if the value exists in the list as opposed to displaying the number of occurrences as shown in the first method demonstrated above. If the value does not exist, the result will return “false”.

Conclusion

This article has provided the three shortcut methods in WPS Excel to check whether a specific value exists in a list.

By using the COUNTIF function, embedding COUNTIF in the IF function, or Embedding the MATCH function in the ISNUMBER function, users can easily determine whether a value is present in a list.

These methods are useful for data analysis and decision-making processes.