I thought about when it might be essential to use a high ISO and trundled off to the British Museum. In this situation you can't use flash or a tripod. I set my shutter speed to 1/30 which is about as low as I can go without any sign of camera shake. Then set the apeture to f4, the widest possible with the lens I was using. To get an acceptable exposure I needed to go to ISO 1000. This image is below.
To highlight the difference the four images below are all taken with a shutter speed of 1/30 and aperture f4. Moving from left to right the ISO used is: 200, 400, 800, 1000. I have cropped a section of each image and magnified 100%. Noise appeared in the shadow part of the image using ISO 1000, but the lower ISO is unacceptably under exposed. I printed a copy of the image at 1000 on A4 paper. I think it is perfectly acceptable. There are times when this is your only option. Dance photographers are very often using very high ISO's.
Trying to capture birds in flight is difficult. The lens I was using (24-105) is not the best for this type of shot as I have found that it does not perform at its best at the fullest zoom (used in these shots). However this exercise is about ISO which are demonstrated below. Below are sections of an image magnified 200%.
The first image shutter 800 - aperture F8 - ISO 1600.
The second image is Shutter 40 - Aperture F8 - ISO 200.
In the top image I was able to capture the birds in flight by retaining a high shutter speed and sharp aperture (although focus is not great). The cost is the black of the birds, a lot of noise with reddish banding.
The second image the birds are lovely and black but the very slow shutter speed has made the image blurry. So both images have faults and both have advantages. Choices as to what you want most out of the image need to be made at the time of taking the picture.
I looked for a situation when the only real option was to use a high ISO. I was photographing birds and squirrels in the shade under trees in the middle of the day. I wanted to keep my aperture at a level that would keep the image sharp. I also needed a fast shutter speed to freeze the action. This only leaves an adjustment in ISO. I needed to go to ISO 1600 to achieve this. I have magnified the section of the image with the squirrel by 200%. Although some noise is introduced to the image I find this totally acceptable as I managed to get a sharp action shot freezing the squirrel mid-leap.