Using Array, Match, and Index to look up and match on values across sheets

Wright1908 

First, thank you for posting a link to the file. I have to say, however, that now that I've looked at it, I have more questions, more of a diagnostic nature. (In addition to having worked for a number of years in IT system design, I used to teach diagnostic thinking processes to lots of folks, including PhD scientists.) 

I want to avoid just putting a band-aid on a wound before ascertaining the cause of the wound itself. The "wound" I'm talking about is the one pictured below:

The value, you say, "should be A." So, I ask, why isn't it? There is no formula in that column for any of the other values in that column. It would be quite easy to put a bandaid on--write a formula that converts it to "A" based on what's in Sheet 2--but that doesn't sit well with me. So I need to ask, where do all those values come from in the first place? Why is that one "wrong" when all the others (I'm presuming) are correct? Would it in fact make more sense to go back to the original source of the "B" rather than just erasing it and replacing it?

Then there's this on the "Audit" sheet.

Now, I realize you've only asked for the formulas to deliver the results in those isolated cells, but you have to give me some credit for wanting to understand the bigger picture. If I can stay with the medical analogy, you do recognize you've come to the doctor for help, but your request is somewhat akin to somebody coming to the doctor with various pains and asking, "Write me a prescription for ____________; I saw it* advertised and it* is just what I need." Any doctor worth her (or his) salt is going to ask a lot more questions, possibly do some tests to establish what's really going on. Otherwise he (or she) would be guilty of malpractice. I do my best to avoid malpractice in spreadsheet design.

So if you could help with filling in some of the blanks here, describing the bigger picture. How do the values in Sheets 1 and 2 get generated in the first place? What's the rest of the Audit sheet look like? I'm not asking for you to disclose proprietary information or violate confidences , but I am looking for a more complete description of data flow, inputs and outputs, etc.

____________________

*"it" as in you've already, in the very title of your post, prescribed "Using Array, Match, and Index" as the solution you're seeking.