List Rules: Allowed Values with COUNTIF & MATCH
All lessons
- Number & Date Rules as Check FormulasFree
- List Rules: Allowed Values with COUNTIF & MATCHFree
- Dependent Lists: Category → Item with INDEX/MATCH Pro
- Text Length & Pattern Checks: LEN, LEFT, SEARCH Pro
- Duplicate Prevention: COUNTIF & COUNTIFS Rules Pro
- Cross-Field Rules: End ≥ Start and Shares = 100% Pro
- Input Form with Error Messages & an Error Summary Pro
- Template Protection Checklist & Completeness Dashboard Pro
You are not signed in — the lab works, but your progress is not saved. Start free
Read first
In Excel: a drop-down list
- Select
B2:B9, Data > Data Validation, Allow = List, Source ==$A$13:$A$16and keep In-cell dropdown ticked. - Short fixed lists may be typed directly:
North,South,East,West(use the list separator of your Excel). - Put the list in an Excel Table or a named range (Formulas > Define Name, e.g. Regions), so new entries appear in the drop-down automatically: Source =
=Regions. - Need case to matter (product codes)? Use Allow: Custom with
=SUMPRODUCT(--EXACT(B2,$A$13:$A$16))>0.
Loading the spreadsheet…