vlookup - Searching for x with y criteria with a z conditional?

[oddzac]

I hope the title makes some sense of what I'm asking here, but my workplace recently transitioned to a new way of recording training transcripts.
The old system had a dedicated "Revoked" column that allowed simple filtering to determine if an associate was certified or not..
The new system is more of a pile of *all* training records and I'm struggling to find a decent way to filter out associates with a revoke on specific trainings.


Pictured below, I have 3 columns.
Emp Login is the main target of my vlookup (being the individual).
- 3 entries shown are the same individual.

LHQ Equiv is the reference code for a specific training.
- 3 entries shown are the same training module

Status is the recorded "result" of that training.
- We have 2 completed entries and 1 revoked

Mainly, I would like the one "Revoked" entry to override the "Completed" entries when I'm checking to see who's trained in a way that removes this indiviual from the final list.

My brainstorm formula looks like:

{=vlookup([login], if([LHQ]=335,B:E,""),4,0)}

Obviously, this doesn't factor in the "Status" column in a meaningful way and the array formula itself is incredibly memory intensive for the 3k+ line data dump that I'm working with. I feel like I'm overthinking this and the end result that I need is a simple pivot table that can be filtered by module number and an accurate status..


Any help or suggestions would be greatly appreciated

Thanks!
-z

 

[oddzac]

The solution I ran with for this was to remove duplicates during the transformation of the queried file.
Transformation steps:

Reorder columns so that relevant info was situated closely and in descending order by date:

= Table.Sort(#"Reordered Columns",{{"Emp Login", Order.Ascending}, {"LHQ Equiv", Order.Ascending}, {"Entry Date", Order.Descending}})

Remove duplicates (keeps first):

= Table.Distinct(Table.Buffer(#"Sorted Rows"), {"Emp Login", "LHQ Equiv"})

Notably, I had to change the default syntax of the step to run the remove dupes *after* the steps I had used on the sheet prior to this one

Default Syntax:

= Table.Distinct(#"Removed Duplicates", {"Emp Login", "LHQ Equiv"})

I still had to update to Office 2021 to utilize xlookup, but this step was vital in getting the final product.