Use select columns from several ranges to create another dataset

Question

I am working with several ranges within the same document and would like to combine select columns from the 2 into 1 new dataset

I have 1 sheet where the dataset is 2 columns, Accounts and Names. Then I have another sheet where I have several ranges of 2 columns each, Accounts and Score.

What I would like to do is to create a table which contains 3 columns: Accounts, Names, and Scores. Ideally I would also learn how to do this when Accounts/Names information is a different Google Sheet document and I would like to know how to change the sorting (by Account, by Name, by Score, etc)

Example/helper sheet. Sheet1 is where I have my Accounts/Names pairs and in Sheet2, columns K/L, N/O, and Q/R is where I have my account/score pairs, and Sheet3 is where I have my desired datasets which I created manually https://docs.google.com/spreadsheets/d/16fadyCYthm9sWOx3eViueaIBJkyVJh3zKV4DzGbYjC8/edit?usp=sharing

TIA

Answer 1

Use vlookup(), like this:

={ Sheet2!H1:H }

=arrayformula( iferror( vlookup(A1:A, Sheet1!A1:B, columns(Sheet1!A1:B), false) ) )

={ Sheet2!I1:I }

=query(A1:C, "where A is not null order by A", 1)

See your sample spreadsheet.