Determining if Value In List
 

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?

Determining if Value In List
 

Explore the VLOOKUP function...I think it will help.

http://www.contextures.com/xlFunctions02.html

***********
Regards,
Ron

XL2002, WinXP-Pro


"E.Q." wrote:

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?

Determining if Value In List
 

Thanks Ron,
I've used VLOOKUP a number of times but I've always ignored the last,
Optional argument for "Range-Lookup".
I've tended to try to use Indes(Match()) type functions of late. I read
somewhere that this is preferable (though I don't recall the logic behind
that). It looks like the venerable VLOOKUP has the edge here.
Thanks for the tip.
E.Q.

"Ron Coderre" wrote:

Explore the VLOOKUP function...I think it will help.

http://www.contextures.com/xlFunctions02.html

***********
Regards,
Ron

XL2002, WinXP-Pro


"E.Q." wrote:

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?