Query or formula that takes data from different ranges

Question

I am looking for a single query (or formula) that can take data from different ranges and pull it together.

Example:

range (name, class) and range (class, year) giving output (name, class, year).

What is the best method?

It can be done by querying the first range, then adding vlookup to every row (to get data from second range).

Or a neater solution is to get data from second range with a single formula, using vlookup nested in arrayformula.

Example:    =arrayformula(vlookup(J2:J, E3:G23, 2, false))

BUT I am looking for a single query or formula so the the final output can be ordered.

Example Sheet: https://docs.google.com/spreadsheets/d/1njKqm9YwSP7iKnjzGkHxmYniwykrZRtxgGB2T-sf1pE/edit?usp=sharing

Answer 1

use:

=INDEX({QUERY(A3:C,"select B,A where C is null"), IFNA(
VLOOKUP(QUERY(A3:C,"select A   where C is null"), E3:G23, {2, 3}, ))})