Let's get something straight. The alchemists, as a whole, were not stupid. They were not crazy, they were not ignorant, and they were not charlatans (for the most part). Alchemists were at the forefront of science and technology, and they essentially invented modern chemistry and discovered important laws of physics such as Equivalent Exchange during their unorthodox experiments. This was, however, nothing more than a side effect of their true research, which was something altogether more ambitious...
Producing gold from lead was considered possible (and economically desirable) by the ancient alchemistsnote but for them this was only a worthless parlor trick. The panacea could cure any disease and extend life. But what alchemists worldwide were truly looking for was Applied Phlebotinum that would grant life eternal and perhaps nigh-omnipotence. In Dar-al-Islam, it was known as the elixir. Indian alchemists sought it as soma ras. In Christendom, it was called the philosopher's stone. This was the Great Work.note Some of the alchemists were even rumored to have succeeded — there are occultists who will inform you that Le Comte de Saint Germain, born in the 18th century, still walks the streets among us. The same is said for the alchemist Nicolas Flamel and his wife, who lived in Paris in the 15th century.
It should also be noted that the Philosopher's Stone is not a MacGuffin where Possession Equals Mastery. If one is skilled enough to create one at any time, then they can do the other things as well. A Master Makes Their Own Tools. It's like how originally a Black Belt in martial arts was the same White Belt they started with that Yellowed and eventually blackened by the dirt of experience; or more accurately, how a master programmer could make a program that others could use; but others would just be script kiddies unless they understand as well.
Alchemists had a vast cosmological worldview that shares more in common with the ancient pagan pantheon than it does with either monotheism or modern science. They believed, as did Aristotle, that the world was composed of a handful of elements that generated the world by moving according to their own natures, animated by the great Source from which all life sprung. Western alchemists generally divided the elements into fire, water, wind, earth, and quintessence (star-matter). Eastern alchemists had a different worldview, and saw fire, water, earth, wood, and metal. Due to the suppression of alternative religious philosophy in Europe, many alchemical books are intentionally difficult to read and understand.
Alchemy in its day also inspired a great deal of artistic media in textbooks, ikons, painted plates, and paintings; alchemical symbols gained an even wider audience, few of whom understood what they were seeing. Many alchemical texts take the form of myths and allegories, and the authors could sneak more occult truths behind the apparently harmless fiction. When alchemy as a science was supplanted by modern chemistry, alchemy as a philosophical, spiritual, and psychological journey continued to inspire artists and writers, most notably the surrealists.
The more comprehensible stuff:
- The Emerald Tablet
- Chymical Wedding of Christian Rosenkreutz
- The Book of Lambspring
In the modern period, the language of alchemy texts has become less based in chemistry metaphor. While these might not be "alchemical" texts in the strictest definition, they convey a great deal of the same worldview.
- The Kybalion
- Psychology and Alchemy, Alchemical Studies, Mysterium Coniunctionis, et al. by Carl Jung
In real life, the publishing of "The Sceptical Chymist" by Robert Boyle in 1661 is considered the point in which Alchemy and Chemistry split off as a Mysticism and a Science respectively. However, the twentieth century brought with it the advent of nuclear engineering, which is widely regarded as a restoration of alchemy as a science, forsaking the associations with mysticism. Irene and Frederick Joliot-Curie were awarded the Nobel Prize for discovering the first artificial transmutation of one element into another. Ironically, it's much easier to turn gold into lead than vice versa, not to say there aren't other nuclear transmutations that are profitable.
See Alchemy Is Magic for its fantastical treatment in fiction.