Excel-vlookup(查找值,区域范围,列序号,0)如何固定住列序列号,这样即使区域范围变动也不受影响...

博客指出VLOOKUP的列序列号不会随区域范围自动调节,易导致V错值,介绍了实现列序号随表格自动变化的三种方法。一是用函数得出目标列在区域范围中的列数;二是用“[@表格名]表格样式”代替COLUMN函数参数;三是用“名称管理器”代替COLUMN函数参数。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

突然,发现VLOOKUP的列序列号并不会随着区域范围的改变而自动调节改变,只是傻瓜的一个数,导致V错值。所有,就想实现随表格自动变化的列序号。

方法一:在列序号那里,用函数得出永远想要的那个列在区域范围中的列数:区域范围内的目标列COLUMN值-区域范围首列COLUMN值+1

=VLOOKUP(I2,$B$2:$G$15,COLUMN($G$2)-COLUMN($B$2)+1,0)

方法二:原理跟方法一相同,只是用“[@表格名]表格样式”代替COLUMN函数中的参数

添加[@表格名]功能方法:选中数据区域-》插入-》表格-》-》确定-》-》-》这样就可以在这个区域,用[@列表名]代替函数参数。

 

 

方法三:原理跟方法一相同,只是用“名称管理器”代替COLUMN函数中的参数

有个快速方法新建一个名称管理单元区域:

 

 之后就,可以直接用小甜心为函数参数,不需要加引号不需要中括号,直接用作参数即可;

转载于:https://www.cnblogs.com/Formulate0303/p/11045448.html

<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)) ``` ![非连续返回示例](https://exceljet.net/sites/default/files/styles/function_screen/public/images/formulas/index%20and%20match%20multiple%20columns.png?itok=JcJ1kXlG) *公式向右拖拽时仅需修改INDEX的*[^3] ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值