I have 2 tabs of data - 1 containing "Raw" data (blue screenshot), and an "Output" table (Yellow screenshot). I need a formula that will read the "Doc#", Active Revision, and Name from the yellow tab, go to the blue tab, compare all 3 entries, and then identify and populate the "status" in the yellow tab.
Each "Doc" has subsections (steps going up in increments of 10). So a user can have Doc 1, Rev E, step 10, 20, 30, 40 entries. For Error Proofing - I'll need to define higherarchey since the raw data is manually entered. For example - if a "User" is "Decertified" for any of the 4 steps but is "inactive" or "active" for other steps - they are decertified for the entire document.
Higherarchy is: Terminated>Decertified>Inactive>Active
](https://i.stack.imgur.com/pYNxy.png)](https://i.stack.imgur.com/9ih3Z.png){kind=link}
I've tried Index/Match. It didn't give all the correct status - plus the data stopped processing after 19 rows and just gave "N/A" for rest of them (and became super laggy). I also tried Nested If/Vlookup but again it stopped processing after 6 cells and gave N/A for rest of the values, became super laggy, and didn't capture all the status options.