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.
1 Answer
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))
