Go Back

Data validation exists in list

Data Validation is a feature in Microsoft Excel which restricts the values or type of data that users enter into a cell.  It automatically checks whether the value entered is allowed based on the specified criteria. This step by step tutorial will assist all levels of Excel users in allowing only values that exist in a list by using Data Validation.  

Figure 1. Final result: Data validation exists in list

Working formula: =COUNTIF($B$3:$B$5,E3)>0

Syntax of COUNTIF Function

COUNTIF returns the count or number of values in a specified range based on a given condition

=COUNTIF(range,criteria)

  • range – the data range that will be evaluated using the criteria
  • criteria – the criteria or condition that determines which cells will be counted

Setting up Our Data

Here we have a table named “Item List” in column B containing a list of allowable items.  Then we have another table consisting of two columns: Order No. (column D) and Item (column E).  We want to restrict the values entered in column E and allow only the values listed in column B. We can do this by using Data Validation.  

Figure 2. Sample data for data validation exists in list

Allow values that exist in list using Data Validation

We want to restrict the values to input in column E to the values that exist in our list in column B.   We can do this with Data Validation by following these steps:

Step 1.  Select the cells whose values we want to restrict.  In this case, select cells E3:E5

Step 2.  Click the Data tab, then the Data Validation menu and select Data Validation

Figure 3.  Selecting Data Validation

The Data Validation dialog box will pop up.  

Figure 4.  Data Validation preview

Step 3.  Click the Allow: drop-down button and select Custom as Validation criteria

Step 4.   Enter the following in the Formula bar:  =COUNTIF($B$3:$B$5,E3)>0

Our COUNTIF function counts the number of times that E3 exists in the list B3:B5.  Any count greater than zero “0” will return a TRUE value, otherwise the formula returns FALSE.  When the value entered in column E is not found in the list in column B, Data Validation fails and the value is restricted.  

Figure 5.  Creating a Data Validation rule

Step 5.   Click OK

We have now restricted the values in E3:E5 to allow only the values that exists in the list in B3:B5 through Data Validation.  Let us try and enter “Silver” into cell E3.  

Figure 6.  “Silver” allowed by Data Validation

The item “Silver” is allowed because it is found in our list of allowed values.  Now let’s input “Diamond” into cell E4.

Figure 7.  “Diamond” restricted by Data Validation

The item “Diamond” is not in the list, hence Data Validation has restricted the input of “Diamond” into cell E4.  For restricted values, Excel shows a default warning message that says:

“The value you entered is not valid.  A user has restricted values that can be entered into this cell.”  

We are then presented with three options: Retry, Cancel or seek Help.  

Data Validation is very accurate in allowing only the values as specified in the formula or validation criteria.  See below table that is filled up with items that exist in the list as allowed by Data Validation.

Figure 8.  Output: Data Validation allow only values that exist in list

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

How do i get data validation for multiple list
Solved by G. S. in 13 mins
I can't get the IFS function to work in the data validation drop down list
Solved by B. B. in 26 mins
I need help with data validation for a drop down date list
Solved by O. S. in 11 mins

Leave a Comment

avatar