synonyms - need help with vlookup

[Roger on Excel]

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

 

[Gary Brown]

I would suggest that you create a 'Master Chemicals Names' worksheet that
contains 2 columns.
Column A contains all the variants of the chemical's name.
Column B contains ONE consistent name for all the variant chemical's names.

Your 'Chemical Database' worksheet should have the chemical name from Column
B above.

Now, you've set yourself up to be able to look up the 'generic' name in the
Master list to find the properties in the database.

 

[Roger on Excel]

Thanks Gary,

I like your solution, very logical and works very nicely too - thankyou.

I wonder if there is a solution whereby one could have all the variants for
each chemical in column A and have adjacent merged cells with the data
entered once in each adjacent merged cell.

What do you think?

Roger

 

[ryguy7272]

Access is great for this!! In the meantime, try this example: A, B, C
(repeating) in ColumnA then 1-20 in Column B. Put 'C' in cell E1 and this
function in cell F1
=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$20,$E$1),INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=$E$1,ROW($A$1:$A$20)-ROW($E$1)+1),ROWS(B$1:B1))),"")

Hit Ctrl+Shift+Enter...not just enter.

Regards,
Ryan---

 

[Roger on Excel]

Ryan,

I am sorry, I tried your code but I dont see how it relates to my synonym
problem for vlookup for multiply named chemicals.

Can you explain, as this might be a very nifty solution to my problem.

Thanks,

Roger

 

[ryguy7272]

I think I misread your post the first time; sorry about that. Maybe a named
range would do it for you.

Check this out:
http://www.mrexcel.com/articles/excel-vlookup-index-match.ph
http://www.timeatlas.com/mos/5_Minute_Tips/General/Assigning_Cell_and_Range_Names_in_Excel/

I tried the above example with 5 types of acid; all five types were in a
range named 'Acid'.
acetic acid
acic acid
ethanoic acid
estic acid
fectic acid

The results were just as I would expect from Vlookup. It takes a little
getting used to, but just fumble around with it and you will get the hang of
it pretty quick.

Regards,
Ryan---

 

[Gary Brown]

Column A contains all the variants of the chemical's name.
Column B contains ONE consistent name for all the variant chemical's names.
Column C contins EITHER the chemical's properties OR a vlookup such as...
=VLOOKUP(B2,B:C,2,FALSE)