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

[Wright1908]

Hello everyone! 

I'm in a bit of a complication - if you could guide as to what might work for this scenario.

I have 3 sheets.

Sheet 1 - has data in column F and H  (F has Billing Codes, H has a value that should be either A,B,C dependent on sheet 2 (column L) if it's True = A, otherwise B or C are acceptable.

Sheet 2 - has 2 columns - one for the data matching in sheet 1 (F) and 1 for True/False values (H) to determine if it should be A or B,C. Again if it's True = A, otherwise B or C are acceptable.

Sheet 3 - Audit sheet which would need to have the value in column A/row x populated if the following do not match:

IF value in Sheet 1 column F is compared to Sheet 2 column L and is "T" then must populate A, if A is not populated, then input Cell value A from the same row of sheet 1 on sheet 3

Example

If Sheet 1 data in column F matches any one of the data on Sheet 2 column J,  and Sheet 2 column L for the same row is "T", but Sheet 1 column H != A then populate Sheet 1 value A6 in Sheet 3 cell C12. 

Sheet 1 data in column F looks something like this:

002

126

127

128

Sheet 2 data in column J is the same list + additional values (not populated) on Sheet 1 with a column next to it (column L) value applied is "T" for True, or "F" for False.

002   T

126   F

127   F

128   F

Sheet 3 data in cell C12 - wants the value of A from the same row where Sheet 1 is referencing

Audit:

Sheet 1 H must be "A" if Sheet 2 column J matches to column L = 'True': [Value from column Ax)

Thanks!

[#2]

Is it at all possible for you to post the spreadsheets in question on OneDrive or in GoogleDrive? It is NOT at all easy to follow your verbal description (which I suspect is why after 54 views you have yet to have a reply)

[#3]

https://docs.google.com/spreadsheets/d/1Hq90jGBDRrj8r3ImnBSbJ85SyUQodhFNnJp7Z7h1yvA/edit?usp=sharing

[#4]

@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.