<think>我们面对的问题是：使用VLOOKUP函数返回多列数据（包括连续列和非连续列）
已知：VLOOKUP函数本身只能返回查找范围中的某一列（通过col_index_num参数指定）
因此，我们需要通过其他方法来实现返回多列。
解决方案：
1. 连续列：可以通过构建动态列索引，使用数组公式或拖拽填充实现
2. 非连续列：需要分别指定列索引，或者使用其他函数组合（如CHOOSE函数）重构查找范围
具体方法如下：
一、连续多列返回（例如返回第3、4、5列）
方法1：拖拽公式法（适用于列连续且数量不多的情况）
- 在第一个单元格写VLOOKUP返回第3列
- 向右拖拽，手动修改第三个参数为4、5（或者使用COLUMN函数辅助自动生成列索引）
示例公式（返回第3列）：
=VLOOKUP($A2, $B:$E, 3, FALSE)
向右拖拽时，将公式中的3改为4、5，可以分别得到第4列和第5列。
优化：使用COLUMN函数自动生成列索引
=VLOOKUP($A2, $B:$E, COLUMN(C1), FALSE)
注意：这里COLUMN(C1)返回3，当向右拖拽到D1时变成4，E1时变成5。
方法2：数组公式法（一次返回多列，Excel 365可用）
=VLOOKUP(A2, B:E, {3,4,5}, FALSE)
这个公式会返回一个包含3列的水平数组。
二、非连续多列返回（例如返回第3列和第5列）
方法1：分别使用多个VLOOKUP
- 在第一个单元格返回第3列：=VLOOKUP($A2, $B:$E, 3, FALSE)
- 在第二个单元格返回第5列：=VLOOKUP($A2, $B:$E, 5, FALSE)
方法2：使用CHOOSE函数重构查找范围（适用于列索引不连续且不想多次计算查找的情况）
- 思路：用CHOOSE函数将需要返回的列重新组合成一个新的区域，使得这些列变成连续的第2列、第3列...然后就可以用连续列的方法返回。
- 示例：假设原数据区域是B:E（共4列），我们想返回第3列和第5列（注意：原区域只有4列，所以第5列不存在？这里可能是笔误，应该是返回第3列和第1列？或者重新解释：非连续列指的是在原数据区域中不连续的列位置，比如第2列和第4列）
修正：假设我们实际的数据区域有5列（B:F），现在要返回第2列和第5列（非连续）。
公式：
=VLOOKUP($A2, CHOOSE({1,2}, $B:$B, $E:$E), 2, FALSE)
这个公式只能返回一列（第2列，即重构区域的第2列，对应原区域的E列）。如果要同时返回两列，我们可以重构一个两列的区域，然后使用数组公式返回两列。
在Excel 365中，我们可以这样：
=VLOOKUP(A2, CHOOSE({1,2}, B:B, E:E), {2}, FALSE) 这样只能返回一列（因为{2}只指定了一列）
实际上，我们可以这样写数组公式（同时返回两列）：
=VLOOKUP(A2, CHOOSE({1,2}, B:B, E:E), {1,2}, FALSE)
但是这样返回的两列分别是重构区域的第一列（B列）和第二列（E列）。注意，重构区域的第一列是B列，第二列是E列，所以{1,2}分别返回这两列。
然而，这种方法有一个缺点：重构的区域只有两列，所以列索引只能是1或2。如果我们想返回的列在原始区域中不相邻，我们可以通过CHOOSE函数按顺序组合它们。
方法3：使用INDEX+MATCH组合（更灵活）
- 返回第3列：=INDEX($C:$C, MATCH($A2, $B:$B, 0))
- 返回第5列：=INDEX($E:$E, MATCH($A2, $B:$B, 0))
这种方法可以任意指定返回的列，而且不需要列连续。我们可以分别写两个公式，也可以使用数组公式（如果列在同一行返回）：
=INDEX($C:$C, MATCH($A2, $B:$B, 0)) 和 =INDEX($E:$E, MATCH($A2, $B:$B, 0))
或者，使用一个公式返回两个单元格（水平方向）：
=INDEX(C:C, MATCH(A2, B:B, 0)) 写在第一个单元格
=INDEX(E:E, MATCH(A2, B:B, 0)) 写在第二个单元格
在Excel 365中，我们可以使用一个公式返回两个值（数组溢出）：
=INDEX(C:C:E:E, MATCH(A2, B:B, 0), {1,2})
注意：这里C:C:E:E是一个多列区域（第1列是C:C，第2列是D:D，第3列是E:E？实际上这样写不行，应该用CHOOSE或直接选择多列）
正确写法（使用HSTACK，Excel 365新函数）：
=HSTACK(INDEX(C:C, MATCH(A2, B:B, 0)), INDEX(E:E, MATCH(A2, B:B, 0)))
或者使用CHOOSE：
=INDEX(CHOOSE({1,2}, C:C, E:E), MATCH(A2, B:B, 0), {1,2})
三、性能优化
如果数据量很大，建议将整个查找区域定义为表（使用结构化引用），或者使用INDEX+MATCH组合，因为VLOOKUP在查找范围很大时效率较低。
四、错误处理
在返回多列时，我们可能需要对每一列都进行错误处理。可以结合IFERROR函数：
=IFERROR(VLOOKUP($A2, $B:$E, COLUMN(C1), FALSE), "")
或者对于INDEX+MATCH：
=IFERROR(INDEX(C:C, MATCH($A2, $B:$B, 0)), "")
五、示例
假设有以下数据（表1：数据源，在Sheet1的A:E列）：
A B C D E
1 ID Name Age Dept Salary
2 1001 Alice 25 Sales 5000
3 1002 Bob 30 IT 6000
表2（在Sheet2）：
A B C D
1 ID Name Dept Salary
2 1001
目标：在Sheet2的B2、C2、D2分别返回Name、Dept、Salary（连续列，在数据源中是B、D、E列，即第2列、第4列、第5列）
方法1（VLOOKUP+COLUMN）：
B2: =VLOOKUP($A2, Sheet1!$A:$E, COLUMN(B1)+1, FALSE) // 因为数据源中Name是第2列，而COLUMN(B1)返回2，但我们的查找区域是从A列开始，所以Name在查找区域的第2列，所以直接用COLUMN(B1)即可。但注意，查找区域是A:E，所以第2列就是B列（Name）。
但是，我们想要返回的列在数据源中分别是第2列（Name）、第4列（Dept）、第5列（Salary）。所以：
Name列（第2列）：COLUMN(B1) 得到2
Dept列（第4列）：如果我们向右拖拽，到C列，公式中COLUMN(C1)得到3，但我们希望是4，所以需要调整。
调整：我们可以用COLUMN(A1)+1，这样在B2时，COLUMN(A1)=1，加1得2；拖到C2时，COLUMN(B1)=2，加1得3，但我们希望是4，所以不行。
因此，我们可以直接指定：
B2（Name）: =VLOOKUP($A2, Sheet1!$A:$E, 2, FALSE)
C2（Dept）: =VLOOKUP($A2, Sheet1!$A:$E, 4, FALSE)
D2（Salary）: =VLOOKUP($A2, Sheet1!$A:$E, 5, FALSE)
或者，如果我们知道这些列在数据源中的列索引，我们可以用数组公式（Excel 365）：
=VLOOKUP(A2, Sheet1!A:E, {2,4,5}, FALSE)
然后这个公式会返回三列，分别对应2,4,5列。
但是，如果我们使用数组公式，需要确保公式所在的单元格有足够的空间（向右两个单元格）来溢出。
方法2（INDEX+MATCH）：
B2: =INDEX(Sheet1!B:B, MATCH($A2, Sheet1!$A:$A, 0))
C2: =INDEX(Sheet1!D:D, MATCH($A2, Sheet1!$A:$A, 0))
D2: =INDEX(Sheet1!E:E, MATCH($A2, Sheet1!$A:$A, 0))
或者使用一个数组公式（Excel 365）：
=INDEX(Sheet1!B:B:D:D:E:E, MATCH(A2, Sheet1!A:A, 0), {1,2,3})
注意：这样写不行，因为B:B:D:D:E:E不是一个连续的区域。我们可以用HSTACK：
=HSTACK(INDEX(Sheet1!B:B, MATCH(A2,Sheet1!A:A,0)), INDEX(Sheet1!D:D, MATCH(A2,Sheet1!A:A,0)), INDEX(Sheet1!E:E, MATCH(A2,Sheet1!A:A,0)))
六、总结
根据需求选择：
- 连续列：VLOOKUP的数组形式或拖拽填充（配合COLUMN函数）
- 非连续列：多个VLOOKUP分别指定列索引，或使用INDEX+MATCH，或使用CHOOSE重构区域（配合数组公式）
另外，引用[3]中提到“查找返回多列数据”和“动态列查询”，我们可以参考其思路。
根据引用[3]的目录，我们还可以使用“动态列查询”技巧，即使用MATCH函数来动态确定列索引（适用于列标题可能变动的情况）。例如：
=VLOOKUP($A2, $B:$E, MATCH(B$1, $B$1:$E$1, 0), FALSE)
这里，B$1是当前列的标题（比如在B1单元格是"Name"），然后在数据源的表头$B$1:$E$1中匹配，返回列索引。
这样，当我们向右拖拽时，列标题变化，MATCH函数会返回对应的列索引。
这种方法适用于列顺序可能变动，或者我们不想手动指定列索引的情况。
因此，对于非连续列，我们也可以使用这种方法，只要在每一列的表头写上需要返回的字段名，然后用MATCH函数去查找这个字段名在数据源表头中的位置即可。
示例（在表2中）：
B1: Name, C1: Dept, D1: Salary
B2: =VLOOKUP($A2, Sheet1!$A:$E, MATCH(B$1, Sheet1!$A$1:$E$1, 0), FALSE)
然后向右拖拽。
注意：查找区域（Sheet1!$A:$E）的首列是A列（ID），而表头匹配区域（Sheet1!$A$1:$E$1）包含A列（ID）和后面的列。所以，MATCH(B$1, Sheet1!$A$1:$E$1,0)会返回字段名在数据源中从A列开始算的列位置。
例如，Name在数据源中位于B列，即查找区域的第2列（因为A是第1列，B是第2列），所以返回2。
但是，如果我们的数据源表头在Sheet1的A1:E1，而我们的查找区域是从A列开始，那么这样匹配是准确的。
七、错误值处理
我们可以结合IFERROR：
=IFERROR(VLOOKUP($A2, Sheet1!$A:$E, MATCH(B$1, Sheet1!$A$1:$E$1, 0), FALSE), "")
八、注意事项
1. 使用MATCH函数动态确定列索引时，要确保表头完全匹配（包括空格等）。
2. 如果数据源和当前表的表头不完全一致，会导致匹配错误。
3. VLOOKUP的查找区域的首列必须是查找值所在的列（即A列是ID列）。
综上所述，我们可以根据具体情况选择合适的方法。</think>### Excel VLOOKUP函数返回多列数据解决方案
在Excel中使用VLOOKUP函数返回多列数据（包括连续列和非连续列）时，需要结合其他函数实现。以下是详细解决方案：
---
#### **一、连续多列返回（推荐方法）**
**使用数组公式 + 动态列索引**
```excel
=VLOOKUP($A2, $B:$F, {2,3,4}, FALSE)
```
- **说明**：
- `$A2`：查找值
- `$B:$F`：查找区域（首列必须是匹配列）
- `{2,3,4}`：**数组形式指定连续列索引**（返回第2、3、4列）
- **优势**：单公式返回多列，自动水平溢出结果（Excel 365+）
- **动态索引优化**（适应列变动）：
```excel
=VLOOKUP($A2, $B:$F, COLUMN(B1)-COLUMN($B$1)+2, FALSE)
```
向右拖拽时自动递增列索引[^3]。
---
#### **二、非连续多列返回**
##### **方法1：CHOOSE函数重构区域**
```excel
=VLOOKUP($A2, CHOOSE({1,2}, $C:$C, $E:$E), 2, FALSE)
```
- **说明**：
- `CHOOSE({1,2}, $C:$C, $E:$E)`：将非连续的C列和E列组合成新区域
- 参数`2`：返回新区域的第2列（即原E列）
- **多列扩展**：
```excel
=VLOOKUP($A2, CHOOSE({1,2,3}, $C:$C, $D:$D, $F:$F), {1,2,3}, FALSE)
```
##### **方法2：INDEX+MATCH组合（更灵活）**
```excel
=INDEX($C:$C, MATCH($A2, $B:$B, 0)) // 返回C列
=INDEX($E:$E, MATCH($A2, $B:$B, 0)) // 返回E列
```
- **优势**：
- 可任意指定非相邻列
- 避免VLOOKUP的"首列限制"
- **数组形式（Excel 365+）**：
```excel
=HSTACK(
INDEX($C:$C, MATCH($A2, $B:$B, 0)),
INDEX($E:$E, MATCH($A2, $B:$B, 0))
)
```
---
#### **三、动态表头匹配（自适应列位置）**
```excel
=VLOOKUP($A2, $B:$F, MATCH(B$1, $B$1:$F$1, 0), FALSE)
```
- **说明**：
- `B$1`：当前列标题（如"部门"）
- `$B$1:$F$1`：数据源表头区域
- **向右拖拽时自动匹配不同列**，无需手动修改索引[^3]
---
#### **四、错误处理与优化**
1. **屏蔽错误值**：
```excel
=IFERROR(VLOOKUP(...), "替代值")
```
2. **避免重复计算**（INDEX+MATCH版）：
```excel
=LET(
rowPos, MATCH($A2, $B:$B, 0),
HSTACK(INDEX($C:$C, rowPos), INDEX($E:$E, rowPos))
)
```
3. **结构化引用**（表格自动扩展）：
```excel
=VLOOKUP([@ID], Table1, MATCH("Salary", Table1[#Headers], 0), FALSE)
```
---
#### **五、方案对比**
| **场景** | **推荐方法** | **优势** |
|------------------|--------------------------|-----------------------------------|
| 连续列 | 数组形式VLOOKUP | 单公式返回多列，简洁高效 |
| 非连续列 | INDEX+MATCH | 无首列限制，任意指定列 |
| 列位置可能变动 | VLOOKUP+MATCH表头 | 自适应列变化，减少维护成本 |
| 大数据量 | INDEX+MATCH+LET | 避免重复计算，提升性能 |
> ⚠️ **关键注意**：
> - VLOOKUP默认返回**首个匹配值**，如需返回所有匹配值需用FILTER函数
> - 查找值必须在查找区域的**首列**
> - 精确匹配时第4参数必须为`FALSE`或`0`
---
#### **实战案例**
**目标**：根据员工ID返回姓名、部门和薪资（非连续列）
```excel
// B2单元格（姓名）：
=INDEX(C:C, MATCH($A2, $B:$B, 0))
// C2单元格（部门）：
=INDEX(E:E, MATCH($A2, $B:$B, 0))
// D2单元格（薪资）：
=INDEX(F:F, MATCH($A2, $B:$B, 0))
```
*公式向右拖拽时仅需修改INDEX的列参数*[^3]
---
扫一扫
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
