☰

索引/匹配 - 在包含多个相似值的表中

tag-icon • tag-icon microsoft-excel
索引/匹配 - 在包含多个相似值的表中

[![在此处输入图片描述][2]][2]

目的：

使用已知的“长度”值来找到正确的列标题，然后在列中搜索值，然后返回该数据表右侧的索引。

背景：

  1. 表体中的某些值在不同的列中重复
  2. 表体中的值代表区间的下限
  3. C4 至 F4 中的列标题表示区间的下限
  4. B5 至 B8 中的行标题分别代表一个唯一索引

桌子：

A   B   C   D   E   F   G   H   I   J   K               
3           Length                      Header                  
4       **4.1   5   5.3 6**     Length: 5.4 5.3 =HLOOKUP($J$4;C4:F4;1)          
5   **7**   18  20  21  23      Strength    28              
9   **20**  22  23  25      Index   11  =INDEX(B5:B8;MATCH(J5;E5:E8;4))                 
7   **11**  24  25  27  28                                  
8   **16**  27  28  30  31

已实现：

  1. J4 和 J5 中的信息始终是给定的（即已知的）
  2. HLOOPKUP（J4）用于识别正确的列标题——正确的结果出现在K4，即E列
  3. 然后，J6 中的 (INDEX/MATCH) 公式正确搜索 E 列标题下的值（E5:E8 - 我必须输入） -
    并正确识别出“28”（J5）落在“27，”间隔（E7）内 - 然后正确地将相应的行标题标识为“11”（B7）

需要协助：

  1. 目前我必须首先运行 HLOOKUP 公式来确定列标题
  2. 接下来，我必须根据 K4 的结果，手动将列值（不包括列标题）输入到 J6 中的公式中，以运行 INDEX/MATCH 函数
  3. 因此，我需要帮助以便根据 HLOOKUP 结果自动将列值（不包括列标题）插入到 INDEX/MATCH 公式中。

  4. 如果可能的话，我更愿意使用函数而不是 VBA

    [错误截图][1]截图显示了一些计算如何给出#N/A 结果。

问候。Rudi

答案1

使用OFFSET从最左上角的单元格移动范围以获取正确的范围。

=INDEX($B$5:$B$8, MATCH($J$5, OFFSET($C$5, 0, MATCH($J$4, $C$4:$F$4, 1)-1, ROWS($B$5:$B$8), 1), 1))

解释

这就像你的INDEX MATCH公式一样，但是我E5:E8在你的例子中将其更改为相对范围，定义为OFFSET( ... )

OFFSET($C$5, //Reference point
       0,    //Rows shifted below, in this case it doesn't shift the row
       MATCH($J$4, $C$4:$F$4, 1)-1, //Column shifted to the right, in this case it shifts from `1-4` minus 1.
       ROWS($B$5:$B$8),           //Height, in this case you want as many rows as the table has, taken from the left "header"
       1)    //Width, in this case you only want one column

$C$5是用作参考点的值的最左上角的单元格。

MATCH($J$4, $C$4:$F$4, 1)-1是获取列标题的索引。这与您的HLOOKUP公式类似，但它返回的是列号（例如，第 3 列返回 2）

ROWS($B$5:$B$8)是从左侧“标题”有多少行中获取表格有多少行

相关内容