☰

根据两个条件进行查找，一个是数字，一个是范围

tag-icon • tag-icon microsoft-excel
根据两个条件进行查找，一个是数字，一个是范围

我有两个列表，一个包含基于不同范围的 ID 和百分比，另一个包含 ID 和总数。我需要根据第二个电子表格中的 ID 和总数查找第一个电子表格中的百分比。

ID  Bottom Top  Percentage
1   1      50       0.3
1   51     75       0.4
1   76              0.5
2   1               0.75
3   1      25       1
3   26     100      2
3   101    1000     3
3   1001   2000     4
3   2001            5

Second List
ID  Total Amount
1   100
2   146
3   1256

答案1

假设您的第一个表格在 Sheet1 上，第二个表格在 Sheet2 上，并且您想要填充第二个表格的“金额”列，则以下公式将针对 Sheet2 的第一个“金额”单元格（单元格 C2）完成此操作。这要求第一个表格按 ID 列排序，然后按“底部”列排序。输入此公式后，可以将其复制粘贴到表 2 的末尾。

=VLOOKUP(B2, OFFSET(Sheet1!B:D, MATCH(A2, Sheet1!A:A, 0) - 1, 0, COUNTIF(Sheet1!A:A, A2)), 3)

请注意，顶部列实际上对于此功能而言并不是必需的。

分解这个公式，它使用VLOOKUP函数在 Bottom 列中搜索小于或等于 Total 值的最近值，然后返回同一行的 Percentage 列中的值。它还必须将搜索限制在与正确 ID 匹配的行中，它使用OFFSET函数。OFFSET函数使用找到的第一个匹配 ID 的行MATCH函数和匹配的IDS总数COUNTIF功能。

相关内容