使用ARRAYFORMULA和INDIRECT迭代行并返回块 [英] Using ARRAYFORMULA and INDIRECT to iterate rows and return as a block

问题描述

Sheet1有列A到D，其中A包含一个唯一的记录键。

在Sheet2上，我想重新排列数据，违背我自己的排序顺序。 Colum A包含我自己的自定义订购记录键。

我在Sheet2上完成的工作是;我正在计算B栏中的记录位置：
$ b $ pre $ = ARRAYFORMULA（MATCH（A1：A100，Sheet！A：A， 0））


我一直试图使用ARRAYFORMULA INDIRECT来一次性获取数据。
我可以正确使用单行

$ p $ = ARRAYFORMULA（INDIRECT（（（Sheet1！B& B1& amp; ;：D& B1）））


我想要的就像下面的东西，但它

  = ARRAYFORMULA（INDIRECT（（Sheet1！B& B1：B100&： B 
 
 
 
 
 
 
 
 
 
 
 
 
 $ b $ div class =h2_lin>解决方案

不幸的是，INDIRECT不支持迭代数组。

幸运的是，VLOOKUP的确如此，你不需要帮助栏。因此：
$ b

= ArrayFormula（VLOOKUP（A1：A100，Sheet1！A：D，{2,3,4} * SIGN（ROW A1：A100）），0））

以及表格的新版本，第三个参数可以简化：

= ArrayFormula（VLOOKUP（A1：A100，Sheet1！A：D，{2,3,4}，0））

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.

解决方案

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

这篇关于使用ARRAYFORMULA和INDIRECT迭代行并返回块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！