19

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Riyaz Mansoor
  • 685
  • 1
  • 8
  • 22

1 Answers1

18

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

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • 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 Dec 17 '13 at 18:58
  • 3
    How come such useful information is not available in the documentation? – Riyaz Mansoor Dec 17 '13 at 19:31
  • 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". https://productforums.google.com/forum/#!searchin/docs/multivalue$20parallel$20lookup/docs/36A0epDlIdE/qnywZst0DioJ – AdamL Dec 17 '13 at 21:26