I'm working to develop an application for a non-Excel litterate biologist. I
think I can get the source file from the Chemist as an Excel spreadsheet.
(That's how they receive it from the outside lab, but they distribute it as a
PDA file.) The chemist problem is that the outside lab doesn't label their
test variables using the CAS number to identify the chemical. So one sample
may say "Mercury" while the next one may say "Hg". (Though they probably use
PDA due to the non-litterate recipients.)
I'd like to set up an application for the chemist to match the test chemical
against the CAS number. I'm hoping the source lab isn't too creative in the
names they use. My thought is to develop a database of chemical synonyms
with the proper CAS number and have Excel pick the proper number from the
list.
I think I can use the =Match() function to help. My concern is with
chemical names that are not found on the list of synonyms. I'd like to bug
the chemist just a bit to supply those CAS numbers (and add them to the list
for future reference).
So How do I get Excel to tell me that the given item is not on the list?