google sheets - Using ARRAYFORMULA and INDIRECT to iterate rows and return as a block

Question

Sheet1 has columns A to D where A contains a unique record key.

On Sheet2 I want to rearrange the data, against my own sort order. Colum A contains my own custom ordered record keys.

What I have done upto now on Sheet2 is; I'm calculating the record positions in Column B as

=ARRAYFORMULA(MATCH(A1:A100,Sheet!A:A,0))

And I have been trying to use ARRAYFORMULA INDIRECT to get the data in one go. I can get a single row correctly using

=ARRAYFORMULA(INDIRECT(("Sheet1!B"&B1&":D"&B1)))

What I want is something like below, but it still returns the first row ONLY.

=ARRAYFORMULA(INDIRECT(("Sheet1!B"&B1:B100&":D"&B1:B100)))

Help.

Answer 1

Unfortunately INDIRECT doesn't support iteration over an array.

Fortunately, VLOOKUP does, and also means you don't require the helper column. So:

=ArrayFormula(VLOOKUP(A1:A100,Sheet1!A:D,{2,3,4}*SIGN(ROW(A1:A100)),0))

and in the new version of Sheets, the third argument can be simplified:

=ArrayFormula(VLOOKUP(A1:A100,Sheet1!A:D,{2,3,4},0))