Synonyms - use with vlookup

[Roger on Excel]

Hi,

I posted this question in programming, but upon reflection, the general
question group would be a better place i think.

I use vlookup to access data from a database of chemicals. I enter the
chemical on one page and it accesses a range of chemical properties and
safety data for that chemical

Unfortunately many chemicals have various different names for the same
thing. For example acetic acid and ethanoic acid are the same chemical.

How can I manage a list so that the chemical data for each chemical is only
needed to be entered once? At the moment I have to have the exact same data
recorded twice for the example of acetic acid and ethanoic acid.

Can anyone help as some chemicals can have 4,5 or even six different names
and I dont want to have to enter the same information multiple times.

Help would be most welcome

Roger

 

[Sheeloo]

Well, one way is to decide which one is the primary name.

I mean for example you have A, B, C, and D which mean the same thing.

You may decide that A is the primary one...
Then have a list in Col A having all the four names and A in the
coresponding cell.

Now whenever you enter a name, do a VLOOKUP against this list, and run the
VLOOKUP for the details using the the name in Col B (chemical A in this
case)...