Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Left Lookup in Excel
  • Lookup entire row in Excel
  • Offset in Excel
  • Excel Advanced Lookup using Index and Match Functions
  • CHOOSE function: Description, Usage, Syntax, Examples and Explanation

Data Analysis Examples

  • How to Create One and Two Variable Data Tables in Excel
  • Subtotal function in Excel
  • How to Create Gantt Chart in Excel
  • How to Create Area Chart in Excel
  • Add Outline to Data in Excel

Data Validation Examples

  • Excel Data validation no punctuation
  • Excel Data validation exists in list
  • Excel Data validation allow weekday only
  • Excel Data validation must begin with
  • Excel Data validation specific characters only

Excel Data validation exists in list

by

Using the example below, this tutorial shows how to create Data validation exists in list in Excel.

Formula

=COUNTIF(list,A1)>0

Explanation

Note: Excel has a built-in data validation rules for dropdown lists. This page explains how to create a your own validation rule for lists when you don’t want the dropdown behavior.

Worked Example:   If cell begins with x, y, or z in Excel

To allow only values from a list in a cell, you can use data validation with a custom formula based on the COUNTIF function.

In the example shown, the data validation applied to C5:C9 is:

=COUNTIF(colors,C5)>0

How this formula works

Data validation rules are triggered when a user adds or changes a cell value.

Worked Example:   Basic text sort formula in Excel

In this case, the COUNTIF function is part of an expression that returns TRUE when a value exists in a specified range or list, and FALSE if not.

The COUNTIF function simply counts occurrences of the value in the list. Any count greater than zero will pass validation.

Worked Example:   Count unique text values in a range in Excel

Note:  Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5.

Post navigation

Previous Post:

Excel Data validation allow weekday only

Next Post:

Excel Data validation must begin with

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • How to extract nth word from text string in excel
  • Convert text to numbers using VALUE function in Excel
  • How to count specific characters in a cell in Excel
  • How to use double quotes inside a formula in Excel
  • How to extract domain name from URL in Excel
  • Create date range from two dates in Excel
  • How to calculate next scheduled event in Excel
  • How to calculate next anniversary date or birthday in Excel
  • How to get Holiday Date from Year in Excel
  • Get last working day in month in Excel
  • PRICEMAT function: Description, Usage, Syntax, Examples and Explanation
  • COUPNUM function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate compound interest in Excel
  • IRR function: Description, Usage, Syntax, Examples and Explanation
  • YIELDDISC function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles