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" wrote:

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---

--
RyGuy


"Roger on Excel" wrote:

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

"Gary Brown" wrote:

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.

--
Hope this helps.
Thanks in advance for your feedback.
Gary Brown


"Roger on Excel" wrote:

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