Spreadsheet: 833581d1687097509-offset-countif-index-match-no-value-for-searchable-drop-down-list-searchable-dd
Sheet: Sheet1

 |  |  |  | Search box
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | List of names in column F | search
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Type a name in the search cell A5 | [NAME]
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | return 0 & 1 if the search term is found | [NAME]
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Increment the occurences | IF
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Create a Vlookup Function using Rows | Rows
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Count the result of the Vlookup Function | Vlookup
 |  |  |  |  |  |  |  |  |  |  | Count |  |  |  |  |  |  |  |  |  |  |  |  |  | Define a name using the Offset Function | IfError
 | Main List |  |  |  |  |  |  |  |  |  | Valid | List for DD |  |  |  |  |  |  |  |  |  |  |  |  | Use the defined name in data validation | Countif
 | 2 ACETYL THIAZOLE |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Uncheck the error box | Offset
 | 5 METHYL 2 PHENYL 2 HEXENAL = COCAL |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Define Name
 | 5 METHYL 2 PHENYL 2 HEXENAL 1% |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Data Validation
 | 5 METHYL 2 PHENYL 2 HEXENAL 10% |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Deal with Error
 | 5 METHYL FURFURAL
 | ACCORD CERISE
 | ACETAL R
 | ACETAL R 10%
 | ACETAL R 10% IPM
 | ACETANISOLE
 | ACETANISOLE 1 %
 | ACETANISOLE 10 %
 | ACETANISOLE 10 % IPM
 | ACETATE 2 METHYL BUTYL
 | ACETATE 2 METHYL BUTYL 10 %
 | ACETATE ALPHA TERPENYLE (MPs)
 | ACETATE ANISYLE
 | ACETATE ANISYLE 10 %
 | ACETATE BENZYLE
 | ACETATE BENZYLE 10 % IPM
 | ACETATE BENZYLE 10%
 | ACETATE BETA TERPENYLE (MPs)
 | ACETATE BORNYLE (MPs)
 | ACETATE BULNESYL (MPs)
 | ACETATE BULNESYL + EUGENOL (MPs)
 | ACETATE CARVYL CIS (MPs)
 | ACETATE CARYOPHYLLENE
 | ACETATE CARYOPHYLLENE 10%
 | ACETATE CEDRYLE
 | ACETATE CEDRYLE 10 %
 | ACETATE CEDRYLE 10% IPM
 | ACETATE CINNAMYLE
 | ACETATE CINNAMYLE 1%
 | ACETATE CINNAMYLE 10%
 | ACETATE CIS 3 HEXENYLE
 | ACETATE CIS 3 HEXENYLE 1 % IPM
 | ACETATE CIS 3 HEXENYLE 1%
 | ACETATE CIS 3 HEXENYLE 10%
 | ACETATE CIS 3 HEXENYLE 10% IPM
 | ACETATE CITRONELLYLE
 | ACETATE CITRONELLYLE 1 %
 | ACETATE CITRONELLYLE 10%
 | ACETATE CITRONELLYLE 10% IPM
 | ACETATE CYCLO HEXYL ETHYLE
 | ACETATE CYCLO HEXYL ETHYLE 1 %
 | ACETATE DIHYDRO MYRCENYLE
 | ACETATE DIHYDROCARVYL TRANS (MPs)
 | ACETATE DMBC
 | ACETATE DMBC 1%
 | ACETATE DMBC 10 % IPM
 | ACETATE DMBC 10%
 | ACETATE ETHYL LINALYLE
 | ACETATE ETHYLE
 | ACETATE ETHYLE 10%
 | ACETATE EUGENYL (MPs)
 | ACETATE FARNESYL (MPs)
 | ACETATE FENCHYLE
 | ACETATE FENCHYLE 10 %
 | ACETATE GAMMA TERPENYLE (MPs)
 | ACETATE GAYOL
 | ACETATE GAYOL 10%
 | ACETATE GERANYLE 98
 | ACETATE GERANYLE 98 10%
 | ACETATE GERANYLE 98 10% IPM
 | [NAME]
 | ACETATE HEXYLE
 | ACETATE HEXYLE 1 % IPM
 | ACETATE HEXYLE 10 %
 | ACETATE HEXYLE 10% IPM
 | ACETATE ISOAMYLE
 | ACETATE ISOAMYLE 1%
 | ACETATE ISOAMYLE 10 %
 | ACETATE ISOAMYLE 10% IPM
 | ACETATE ISOBORNYLE
 | ACETATE ISOBORNYLE 1 %
 | ACETATE ISOBORNYLE 1 % IPM
 | ACETATE ISOBORNYLE 10 %
 | ACETATE ISOBUTYLE
 | ACETATE ISONONYLE
 | ACETATE ISONONYLE 10 %
 | ACETATE LAVANDULYLE (MPs)
 | ACETATE LINALYLE
 | ACETATE LINALYLE 10 %
 | ACETATE LINALYLE 10 % IPM
 | ACETATE MENTHANYLE
 | ACETATE MENTHYLE
 | ACETATE MENTHYLE 10 %
 | ACETATE NERYLE
 | ACETATE NERYLE 1 % IPM
 | ACETATE NERYLE 1%
 | ACETATE NERYLE 10 % IPM
 | ACETATE NERYLE 10%
 | ACETATE NOPYLE
 | ACETATE OCTYLE
 | ACETATE OCTYLE 1%
 | ACETATE OCTYLE 10 %
 | ACETATE OCTYLE 10 % IPM
 | ACETATE P.A.
 | ACETATE PA 10%
 | ACETATE PARACRESYLE
 | ACETATE PARACRESYLE 1%
 | ACETATE PARACRESYLE 10%
 | ACETATE PARACRESYLE 10% IPM
 | ACETATE PHENYL ETHYLE
 | ACETATE PHENYL ETHYLE 1%