tl;dr, Vinnie, Carolyn, possibly Amina and Marie. Not a complete sink in Dr. Orpheus who can load up the doom and sell it. Extremely situational otherwise.
An important thing to keep in mind when playing this is that the only damage/horror that matters is the last one. Damage and horror is just a clock you're working against; turning back that clock is good, but not if you're wasting time on a different clock. Spending time healing when you don't need to is counterproductive.
This is $3 to play, which is a lot, and it occupies a very valuable slot. Both of those resources could be contributing to you actually making progress in a scenario.
One action to heal one damage/horror is already a pretty bad exchange outside of the direst circumstances. Three dollars (each worth slightly less than an action), plus one action to equip it, plus the opportunity cost of giving up a hand slot is abysmal. You never want to use the second action on it's own unless you're forced to, and if you are forced to, it's probably because you didn't play a better card in your hand slot.
Now, healing two for one action is better. But to do that, you're adding a doom. Assuming there are no scenario effects that care about doom (which isn't a guarantee), doom only matters when it will push the agenda. The problem is that healing exists to fix a problem, and you have no guarantee that that problem will align with the window this gives you to solve it.
Most of the time, when someone has taken three damage, it doesn't matter. That's a perfectly safe amount of damage to leave sitting on your investigator. You can easily finish a scenario coasting on 3+ remaining health, and you want to, because that means you didn't waste time healing when you could have been investigating. But because you have to clear the last damage to remove the doom, you need to use the chalice right now, before you know if it will matter. It also means you're following around the injured investigator regardless of what you each need to do to make progress, because it requires two actions across two separate turns. And if you're one doom away from advancing the agenda (or two doom if the ubiquitous Ancient Evils and its ilk are in play), you can't use it now when you need to without potentially sabotaging everything else.
The one time the first ability is totally safe to use is the turn you know the agenda will advance (which again thanks Ancient Evils isn't guaranteed), but unlike, say Blood Pact, the chalice exhausts itself, so you only get a single use within that often once-per-game window, and that's assuming you don't have more important things to do (which you always do).
And sure, abilities like Moonlight Ritual exist, but if you're using that to clear doom from the chalice, you're now spending $3, two cards, and a minimum of three actions to solve a problem that could have been prevented by any cheap soak in the game, which usually has other utility beyond healing.
In summary, it's a trap, and only useful for certain investigators that can piggyback some other, better effect on top of the healing.