Match arrays to return one or more columns in Google Sheets

Question

What is the best easily adaptable formula for returning one or more values based on a matching ID # in google sheets?

the goal formula would be the most efficient way to specify 2 sheets with matching rows and desired return columns

formulaX(Sheet1!array, Sheet2!array, col1, [col8, …], [col2, …])

Normally, you could use arrayformula+vlookup together as in the tables below:

For example, if I one sheet named 'database' and a second named 'lookup' where lookup has matching ID numbers in col a, I could use 1 arrayformula vlookup for every value I want to lookup

arrayformula(IFERROR(vlookup(A:A, database!A:F,2,false)))

but this results in more complexity and isnt very efficient if I want to match multiple values.

Database

ID	First	Last	Birthday	Siblings	Age
AB	Jack	Messi	01/01/19	Maria, Mary	5
CD	Jack	Smith	01/02/19		5
EF	Sam	Messi	02/01/19	Tara, James, Billy	5
GH	Samatha	Reynoldo	03/02/19	Andrea	5
IJ	John	Jordan	04/01/19		5
KL	Jamie	Bryant	05/02/18	Jack, Michael, Elizabeth	5
MN	Janie	Oneil	01/01/17		7

LOOKUP

ID	First	Age
AB	Jack	5
CD	Jack	5
EF	Sam	5
IJ	John	5

the above uses this example uses in b2, c2=arrayformula(IFERROR(vlookup(A:A, database!A:G,2,false))), arrayformula(IFERROR(vlookup(A:A, database!A:G,5,false)))

Possible functions

1. Query()
2. Index() + Match()
3. Byrow()
4. lamda()

because index and match are in-built they might be faster or more efficient than using a lamda() inside of google sheets

Answer 1

Assuming the data is in A2:F and the search keys are in I2:I, you can use:

=ARRAYFORMULA(IFNA(VLOOKUP(I2:I,A2:F,{2,6},0)))

You could also define a custom function ZLOOKUP with 3 arguments, key, range, labels:

=ARRAYFORMULA(IFNA(VLOOKUP(key,range,XMATCH(labels,INDEX(range,1)),0)))

And use it as follows:

=ZLOOKUP(I2:I,A:F,J1:K1)