电子表格 |索引匹配 |

Question

我需要您在以下方面的帮助:

假设我们有 3 个不同的组:(A、B、C)

我们在每个组中有几个值范围:

(Eg. A has 0 - 100, 101 - 200 while B has 0 - 200, 201 - 400 and C has 0 - 300, 301 - 600.)

最后，对于属于他们拥有的任何范围的每个组，他们将被分配一个最终编号。
(例如。

[A,95] = 0.5 / [A,101] = 1.0
[B,95] = 1.5 / [B,205] = 3.0
[C,95] = 4.5 / [C,308] = 6.0)

目前我的索引匹配公式如下:

"=INDEX(finalnumber!F2:F29,MATCH(C11&C25,Group!A2:A29&valuerange!D2:D29,0))"

我不断收到#N/A 回复。

我还创建了一个数组表，如下所示:

enter image description here

Group    Range    Final Number
A        0 - 100      0.50
A        101 - 200    1.00
B        0 - 200      1.50
B        201 - 400    3.00
C        0 - 300      4.50
C        301 - 600    6.00

请帮忙!提前谢谢了!

Answer 1

您可以使用以下内容(您需要根据您的布局进行调整)。我假设 A和 95例如，当您连接公式中的单元格以进行查找时，它们位于单独的单元格中。

=IF(AND(ISERROR(INDEX(OFFSET(INDEX(C:C,H2),,,I2-H2+1,1),MATCH(VLOOKUP(G2,LEFT(OFFSET(INDEX(B:B,H2),,,I2-H2+1,1),FIND("-",OFFSET(INDEX(B:B,H2),,,I2-H2+1,1))-1)*1,TRUE),LEFT(OFFSET(INDEX(B:B,H2),,,I2-H2+1,1),FIND("-",OFFSET(INDEX(B:B,H2),,,I2-H2+1,1))-1)*1,0))),INDEX(A:A,COUNTA(A:A)+1)=F2,G2>=1*LEFT(INDEX(B:B,COUNTA(A:A)+1),FIND("-",INDEX(B:B,COUNTA(A:A)+1))-1),G2<=1*RIGHT(INDEX(B:B,COUNTA(A:A)+1),LEN(INDEX(B:B,COUNTA(A:A)+1)) - FIND("-",INDEX(B:B,COUNTA(A:A)+1)))),INDEX(C:C,COUNTA(A:A)+1),INDEX(OFFSET(INDEX(C:C,H2),,,I2-H2+1,1),MATCH(VLOOKUP(G2,LEFT(OFFSET(INDEX(B:B,H2),,,I2-H2+1,1),FIND("-",OFFSET(INDEX(B:B,H2),,,I2-H2+1,1))-1)*1,TRUE),LEFT(OFFSET(INDEX(B:B,H2),,,I2-H2+1,1),FIND("-",OFFSET(INDEX(B:B,H2),,,I2-H2+1,1))-1)*1,0)))

使用 Ctrl+ Shift+ Enter 作为数组公式输入

辅助单元格中的公式(使整体公式更清晰):
H2是 =MATCH(F2,A:A,0) ' 找到字母的第一个匹配项，例如 A I2是 =MAX(IF(A:A=F2,ROW(A:A)-ROW(INDEX(A:A,1,1))+1)) ' 找到字母的最后一个匹配项，例如 A .这是使用 Ctrl+ Shift+ Enter 输入的，即数组公式。

数据布局



示例运行:



笔记:

您可能希望将整个内容包装在 IFERROR( formula, "") 中。隐藏任何未找到的错误消息。