我有兩個列表，一個包含 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 列排序，然後按 Bottom 列排序。輸入公式後，可以將其複製並貼上到表 2 的末尾。
=VLOOKUP(B2, OFFSET(Sheet1!B:D, MATCH(A2, Sheet1!A:A, 0) - 1, 0, COUNTIF(Sheet1!A:A, A2)), 3)
請注意，頂部列實際上並不是必需的。
分解這個公式，它使用VLOOKUP函數在底部列中搜尋小於或等於總計值的最接近值，然後傳回同一行的百分比列中的值。它還必須將搜尋限制為與正確 ID 相符的行，並且它使用OFFSET功能。這OFFSET函數使用找到的第一個匹配 ID 的行MATCH函數和找到的匹配 IDS 的總數COUNTIF功能。