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.

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

Comments

[Riyaz Mansoor]

Worked like a charm, thanks Adam. Come to think of it, this will also work as a "poor man's" table join as well right?

[Riyaz Mansoor]

How come such useful information is not available in the documentation?

[AdamL]

Sure, it could be used as a table join, often with a hit on performance though (the formula is being recalculated with every edit you make), so you might consider changing to values only once the formula has done its work. As for the documentation, it has improved with the new version of Sheets, but there are still lots of things like this that we have to "discover". productforums.google.com/forum/#!searchin/docs/…