多列使用vlookup函数

Question

我在B:B列中有以下公式：

=VLOOKUP(A1;'mySheet'!$A:$B;2;FALSE)

它会在B:B中输出mySheet!B:B中与A:A = mySheet!A:A相匹配的值。 这个功能很好。 现在，我还想获取第三列。 如果我将以下公式添加到整个C:C列中，则可以正常工作：

=VLOOKUP(A1;'mySheet'!$A:$C;3;FALSE)

然而，我正在处理超过100k行和约40列的数据。我不想做 100k * 40 * VLOOKUP 这么多运算，我希望只需要进行100k次VLOOKUP并得出所有列的结果。是否有一种方法（也许使用数组公式），只需要对每行执行一次VLOOKUP就能获取到我需要的所有列的结果？

---

数据示例

ID|Name
-------
 1|AB
 2|CB
 3|DF
 4|EF

ID|Column 1|Column 2
--------------------
 1|somedata|whatever1
 4|somedate|whatever2
 3|somedaty|whatever3

我希望得到：

ID|Name|Column 1|Column 2
-------------------------
 1|AB  |somedata|whatever1
 2|CB  |        |
 3|DF  |somedaty|whatever2
 4|EF  |somedate|whatever3

- dan

---

你的意思是什么？VLOOKUP可以根据与另一个数据范围的某些匹配来为我的数据行添加列。据我所知，HLOOKUP可以为我的列添加行。 - dan

尝试过这样的方法吗？http://thinketg.com/say-goodbye-to-vlookup-and-hello-to-index-match/。我不确定它能提高多少速度，但它可以避免使用更多的 VLOOKUP()。 - user3827404

Answer 1

INDEX函数比VLOOKUP函数更快，我建议使用INDEX函数。它可以减轻系统在进行多次vlookup时的负担。

首先，在一个帮助列中使用MATCH函数找到包含所需内容的行：

=MATCH(A1,'mySheet'!$A:$A,0)

然后使用该数字创建一个索引，您可以将其拖动到所有列中并填充：

=INDEX('mySheet'!B:B,$B1)

您的输出类似于：

ID|Name|Match |Column 1 |Column 2
-------------------------
 1|AB  |Match1|IndexCol1|IndexCol2
 2|CD  |Match2|IndexCol1|IndexCol2
 3|EF  |Match3|IndexCol1|IndexCol2

此外！我建议将这些范围设置为实际覆盖数据，而不是引用整列，以获得额外的速度提升，例如：

=INDEX('mySheet'!B1:B100000,$B1)

- Aaron Contreras

---

我还建议使用INDEX和MATCH公式结合使用，这在我的经验中更快，并且不关心数据范围是否已排序，并且不像VLOOKUP一样单向工作。 - SelfTaughtCodingIdiot

从性能的角度来看，这看起来非常有前途。尽管仍然会产生大约40*100000个单元格，几乎使用相同的公式，但计算速度仍然比仅使用100000个vlookup要快得多。我会试一试这个方法。 - dan

实际上，如果我添加一个MATCH列，然后立即计算INDEX，我只需要一次MATCH()公式。它有效，谢谢！如果我们只针对一列进行操作，不能说MATCH+INDEX比VLOOKUP更快，但由于这是针对多列的，因此每个超过第一列的列几乎快100%。 - dan

太棒了，很高兴这个能帮到你！我还在另一个思路的线路上发布了另一个答案，也许对你有所帮助 :) - Aaron Contreras

当您说最好设置范围而不是引用整个列时，我该如何引用它以便使用整个列？由于数据不断添加，行数始终在变化。 - dan

与你之前一样，例如=INDEX('mySheet'!B:B,$B1)。 - Aaron Contreras

Answer 2

我考虑了一下你的问题，如果你能控制你正在查找的数据，我有另一个建议可以尝试。

在“mysheet”中，原始数据所在的表格中，添加一个新列，将每一列连接成一个单元格，并使用一些不在数据中出现过的唯一分隔符。

=B1&"+"&C1&"+"&D1&"+"&E1 etc...

然后，您可以为每一行执行一个VLOOKUP或INDEX/MATCH，而不是40个。

一旦您将其放入新工作表中，您可以将结果拆分回来。

---

无公式拆分

将查找公式的结果复制/粘贴为下一列中的值。

选择该列，在您的功能区中的数据选项卡上，选择文本分列。

将其保留在分隔符上，点击下一步。取消选中制表符，选中其他，并输入您的分隔符（在我的示例中为+）。

点击完成。

---

使用公式拆分

使用=FIND()定位每个分隔符，并使用=MID()提取每组分隔符之间的文本，使用前一个分隔符作为Start_num。

明显是两种方法中更为复杂的一种。

- Aaron Contreras

---

实际上，数据是通过VBA导入的，然后使用VBA动态生成公式，我认为使用公式比大量的VBA循环更快。因此，我可以对数据做几乎任何想做的事情，只需要将一个工作表中的数据与另一个工作表匹配，但由于数据超过了10万行，所以所有的问题都归结于性能。 - dan

Answer 3

如果我理解正确，我会建议你使用=VLOOKUP(A1;'mySheet'!$A:LastColumn;COLUMN(B1);FALSE)来开始。这样，当你向右拖动Vlookup时，列引用将随之移动。

- 114

Answer 4

1. 查找值。使用 $A1（在 A 上放置 $ 而不是 1）
2. 源数据- 在所有地方放置 $ 符号
3. 列索引号。只需使用要从中提取数据的列名（例如，如果查找值在列 A 中，并且您想要从列 B 获取值，则使用 COLUMN(B1)）。
4. 输入 false 或 0

- user6920348

Answer 5

没有公式，没有输出。因此，无法仅在1列上应用公式并在其他列上获得结果。另一种可行的方法是，在一个单元格中放置公式，聪明地使用$符号，并在不必要地输入40次vlookup的情况下快速拖动所有单元格。

VLOOKUP有4个代码要输入

1-查找值。使用$A1（在A上放置$而不是1）

2-源数据-在everywhere上放置$符号

3-列索引号。在整个数据上方，在第1行中添加一个空行。 在A1中放置值1，在B1中放置值2，在C1中放置值3等。 现在在公式中，与其手动输入“2”或“3”，不如引用这些单元格。 在数字上放置$而不是列（B$1）。

4-键入false或0 然后将其在所有地方拖动。

- Rahul Shah

---

是的，我没有问题这样做，但那是40*100000个单元格，几乎使用相同的公式。每个单元格大约需要1秒钟才能计算。祝您等待计算永远完成时玩得开心... - dan