我正在使用“IF”和“填充”函数来寻找结果，但它太复杂了。
是否可以先重新制定下面的公式并写入一个单元格？
提前致谢！
J33=IF(I33=A33,1,IF(I33=A32,2,IF(I33=A31,3,IF(I33=A30,4,IF(I33=A29,5,IF(I33=A28,6,IF(I33=A27,7,IF(I33=A26,8,"*")))))))) =IF(ISBLANK(OFFSET(J33,-J33-1,0)),"Y","")
K33=IF(K33="Y",IF(ISBLANK(B30),"R1",IF(ISBLANK(B29),"R2",IF(ISBLANK(B28),"R3",IF(ISBLANK(B27),"R4",IF(ISBLANK(B26),"R5",IF(ISBLANK(B25),"R6","R")))))),"")
M33=IF(L33="error",IF(ISBLANK(B24),"R7",IF(ISBLANK(B23),"R8",IF(ISBLANK(B22),"R9",IF(ISBLANK(B21),"R10","error")))),"")
Q33=IF(L33="Error","",IF(L33="R2",CONCATENATE(B30," ",B31),IF(L33="R3",CONCATENATE(B29," ",B30," ",B31),IF(L33="R4",CONCATENATE(B28," ",B29," ",B30," ",B31),IF(L33="R5",CONCATENATE(B27," ",B28," ",B29," ",B30," ",B31),"Error")))))&IF(M33="Error","",IF(L33="R6",CONCATENATE(B26," ",B27," ",B28," ",B29," ",B30," ",B31),IF(L33="R7",CONCATENATE(B25," ",B26," ",B27," ",B28," ",B29," ",B30," ",B31),IF(L33="R8",CONCATENATE(B24," ",B25," ",B26," ",B27," ",B28," ",B29," ",B30," ",B31),IF(L33="R9",CONCATENATE(B23," ",B24," ",B25," ",B26," ",B27," ",B28," ",B29," ",B30," ",B31),IF(L33="R10",CONCATENATE(B22," ",B23," ",B24," ",B25," ",B26," ",B27," ",B28," ",B29," ",B30," ",B31),IF(L33="R11",CONCATENATE(B21," ",B22," ",B23," ",B24," ",B25," ",B26," ",B27," ",B28," ",B29," ",B30," ",B31),"Error")))))))
答案1
抱歉，无法添加评论，但请将jlear's MATCH()函数更改为XMATCH()，并使用其中的最后一个选项（设置为-1）从后到前搜索范围。这将恢复您的测试序列。
XMATCH()新功能上线后不会立即引起轰动，这是一大优势。在这里非常有用。
答案2
您可以使用 IFS 来简化其中一些功能。例如
=IFS(I33=A33,1,I33=A32,2,I33=A31,3,I33=A30,4,I33=A29,5,I33=A28,6,I33=A27,7,I33=A26,8,true,"*")
但在第一个公式中，您似乎正在数组中搜索匹配项。考虑使用 xmatch 函数。
=IFNA(9-XMATCH(I33,A26:a33,0,-1),"*")
如果找不到精确匹配，XMATCH 函数将返回 N/A，否则将返回匹配数组中的索引。第三个参数 0 表示必须精确匹配，第四个参数 -1 表示按相反顺序搜索。
IFNA 检查 9-MATCH 值是否为 N/A。如果是，则返回“*”。如果不是，则返回 9-XMATCH 值。
---- 根据 Jeorje 的建议，编辑为使用 XMATCH 而不是 MATCH ----