google-sheetsgs-vlookup

Using ARRAYFORMULA and INDIRECT to iterate rows and return as a block

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.

Solution

  • 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))