HMS Hood was sunk by shells flying in a fairly high trajectory, but this was not hihg enough to go through the deck plates, those shells went through the thinner part of the armour belt.
Had HMS Hood been further away she would had been hit in the main armour belt and would almost certain have survived.
She had been designed as a battlecruiser, bug guns, less armour, higher speed but this was not really robust enough to take on a true battleship.
Others in her class had exploded during the battle of Jutland in WW1, this is thought to be because the system of protecting the main magazines was inadequate, especially in flash prevention.
HMS Hood was due in fact to be fitted with enhanced armour, just as her sister ships were such as HMS Warspite but Hood was on a far Eastern tour just prior to WWII and there was not enough time to carry out the work.
IIRC it took3 years to refit Warspite, and even on a wartime routine it would have taken over a year to carry out the work, which was simply too long for a capital ship to be unavailable.
HMS Warspite took a lot of hits and damage including German glider bombs but she survived them all, which just goes to show how it could have been for HMS Hood had she been refitted.
HMS Sheffield was not lost to fire in the wider view, she was lost because of her captain, who did not take the proper precautions when operating in a likely hostile zone.
When those two Exocets hit, they broke the firemain, but that firemain should have been isolated into two or more sections each supplied by a fire pump, as it was, there was no isonaltion and only one firepump operating, the five to ten minutes it took to start other pumps and to isolate the damaged section were enough for the fire to become overwhelming.
As for the OP,
The poster who mentions taking out the carrier radar systems is missing the point, you’d have to hit all the AWACS support aircraft, and you’d have to take out the satellite systems too.
Then you’d have to take out all the systems on the carrier groups escorts vessels.
No sub is likely to get more than one spread of missiles or torpedos fired at a carrier before it was destroyed, no aircr\ft could get within a couple of hundred miles before it was shot down.
Whatever it took to destroy a carrier it would likely have to be done with one shot, and that means nuclear.
Subs such as the Konsomolets were reckoned to be the greatest threat as they as designed to withstand a number of direct hits, buying time for more attempts on target.
Even so, it would take more than just one or two torpedo hits to take out a US supercarrier, and it would almost certainly be a suicide mission.