Goal: Make life easier for Chemistry equations
Disclaimer: I do not use Excel often enough to use complex functions.
I am attempting to create a function that will reference a Sheet that has all the data for the Periodic Table of Elements. To calculate the Formula Mass, I need to input the element, the quantity of elements, the atomic mass, then multiply the atomic mass by the quantity. Instead of looking up the atomic mass for each element individually, I would like to put H, HE, etc and have the atomic mass automatically found in the existing table data.
I found the below function and was unable to work it to my needs. (ref)
=IFERROR(INDEX('Periodic Table'!C2:C119,SMALL(IF(('Periodic Table'!C2:C119=Sheet1!A26),ROW(1:1)),ROW(1:1))),"Error")
I was able to get it to return the same element symbol, but could not get the column on the same row with the Atomic Mass.
=IFERROR(IF(MATCH(A26,'Periodic Table'!C2:C119), "true","false"), "Error")
Any help on finishing this would be greatly appreciated!
3
To make using the lookup easier in many sheets, we could create a table with data and a named function. For example,
(data from https://pubchem.ncbi.nlm.nih.gov/ptable/atomic-mass/)
- create table and sort it by symbol for faster lookups
- create named function, for example
masswith symbol (sym) as parameter
=LAMBDA(sym, XLOOKUP(sym, tbAtomicMass[Symbol], tbAtomicMass[Atomic Mass '[u']], , , 2))
Microsoft 365 is required for LAMBDA.
Answer provided in comments by Scott Craner and Mark S.