![根据两个条件进行查找,一个是数字,一个是范围](https://linux22.com/image/1444026/%E6%A0%B9%E6%8D%AE%E4%B8%A4%E4%B8%AA%E6%9D%A1%E4%BB%B6%E8%BF%9B%E8%A1%8C%E6%9F%A5%E6%89%BE%EF%BC%8C%E4%B8%80%E4%B8%AA%E6%98%AF%E6%95%B0%E5%AD%97%EF%BC%8C%E4%B8%80%E4%B8%AA%E6%98%AF%E8%8C%83%E5%9B%B4.png)
我有两个列表,一个包含基于不同范围的 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
功能。