There are lots of ways for intelligent, dedicated, faithful Mormons to picture the flood as global, regional or local (or collective) and still attach a redemptive meaning to it. Some things simply can't be determined from the record itself. For example, where exactly did it happen? How tall were the “mountains” in the story (really mountains as we envision them or just large hills?); how isolated and enclosed was the valley they might have surrounded - which would affect water flow and “drainage” rates; what was the general condition of the water table in that area - which would affect “absorption” rates; etc. ad infinitum. (In Ohio, we had areas where there was standing water for months and months and months, since the water table generally is so high. I never would have believed it growing up in Utah, and it really was amazing to see.) Not one of these questions can be answered from the text, yet each is critical to understanding if it could have been a local event.
As to the redemptive value, if we allow that the flood might have been a local experience that wiped out all life in the world as Noah knew it, and if we extend our concept of vicarious work to the event, it is very easy to maintain a redemptive element for the flood - no matter how extensive it actually was.
I am far more comfortable with someone saying, “In light of all of the scientific evidence we now have before us, we ought to be willing to consider that the flood might not have been global,” than when someone says, “Scientific evidence be damned, our theology insists that it just had to be global.” These are ancient stories, and, again, our own Articles of Faith say that we don’t know if they have been recorded, translated and handed down to us in purity. That tells me we need to keep an open mind about other possibilities than the orthodox interpretations. To me, there is absolutely no difference in the significance of the story and its symbolism no matter its scope.
Finally, given the existence of catastrophic flood stories in nearly every ancient culture, might this be a case of Hebrew historians realizing that God had destroyed “all the earth” collectively, over time, by multiple, essentially identical floods - taking such stories and “likening them unto themselves” - assigning spiritual and redemptive meaning to “the collective acts of God” - and creating a central flood narrative to reflect this meaning and transmit it to a people who hadn’t yet experienced it themselves?
I tend to place that option beneath a local flood narrative, but our traditions and scriptures certainly would allow for it - and I personally think there is a lot of power in that view. I don’t want to debate that option, because I’m not willing to put it at the top of the list of possibilities, but I don’t think it would destroy the validity of the spiritual message we can take from it. In some ways, I think it even can strengthen that message - that God is no respecter of persons and will do unto all what He does unto some.
It’s at least worth considering that there are multiple ways to view ancient scriptural accounts without any of them being invalid or less powerful than others - which is the point of this post about MANY of the accounts that are recorded in our scriptures. In many cases, I believe it's more important to take them seriously as an opportunity to consider possibilities and learn multiple lessons than to take them literally and limit that learning to only one possible meaning.