Google Sheets: Generate column in query based on reference range matches?

Question

I'm no sheets expert, and I couldn't find the answer searching likely due to not using right search terms. Sorry if this is already answered and sorry if it's not possible.

We have a data source that has inaccurate location codes for employees, so we reference a separate employee and location list often. It would be nice to be able to generate a list with one formula that adds a column which populates based on the reference range.

Example sheet: https://docs.google.com/spreadsheets/d/1vEpF84dxMeUun-H0vDFzfX5sitjdeoHgiv3C7fAiZpY/edit?usp=sharing

In this example there is a main data set and a reference for location:

The current solution is to populate a new row separately from the query. The drawbacks are extra formulas for blank cells (especially a problem with huge data sets), and the inability to sort the list by the new location column. Is there a solution using arrays or something that can just add the new column based on name column matches, so I can sort them at the same time? See picture for current solution and my wish

Question (are you ready): Is this possible?

Answer 1

Your formula would be:

=QUERY({G2:I,ArrayFormula(VLOOKUP(H2:H,{C2:C,B2:B},2,0))}, 
     "select Col4, Col1, Col2, Col3 where Col3='passed' order by Col4 , Col2 ")

Functions used:
+ VLOOKUP
+ ARRAYFORMULA
+ QUERY