求助 判定等级

兴州兰

求助各位老师，在标黄的部位用公式筛选尺寸及值在要求表1规定的条件下判定A、B、C、D等级，谢谢！

hugeinfo

=IF(C8<=90,LOOKUP(D8,HSTACK(0,SUBSTITUTE($K$8:$N$8,"≦","")+1%%),HSTACK($K$7:$N$7,"无")),LOOKUP(D8,HSTACK(0,SUBSTITUTE($K$9:$N$9,"≦","")+1%%),HSTACK($K$7:$N$7,"无")))

对??

你是那块小饼干

数据不全 公式供参考=INDEX($K$7:$N$7,,XMATCH(D8,SUBSTITUTE(CHOOSEROWS($K$8:$N$9,IF(C8<=90,1,2)),"≦","")*1,1,1))

兴州兰

结果显示不出来

仗剑天涯0o0

1. =LET(a,--TEXTAFTER(IF(C8>90,$K$9:$N$9,$K$8:$N$8),"≦"),XLOOKUP(D8,a,$K$7:$N$7,"查找值不在此范围",-1))

复制代码

zhumumu1816

1. =IFERROR(IF(C8<=90,XLOOKUP(D8,INDEX(--SUBSTITUTE(SUBSTITUTE($K$8:$N$9,"≦",""),">",""),1),$K$7:$N$7,,1),XLOOKUP(D8,INDEX(--SUBSTITUTE(SUBSTITUTE($K$8:$N$9,"≦",""),">",""),2),$K$7:$N$7,,1)),"")

复制代码

秃头发师

=XLOOKUP(D8,--SUBSTITUTE(IF(C8<=90,$K$8:$N$8,$K$9:$N$9),"≦",""),$K$7:$N$7,"",1)

兴州兰

公式填上，总出现#name? ，显示不出结果，如A、B、C、D等级。

仗剑天涯0o0

兴州兰 发表于 2025-3-13 16:25
公式填上，总出现#name? ，显示不出结果，如A、B、C、D等级。

你wps版本太低了，更新下wps

袁勇

低版本：

1. =LOOKUP(1,0/FREQUENCY(1,COUNTIF(D8,SUBSTITUTE(OFFSET(K$7:N$7,2^(C8>90),),"≦","<="))),K$7:N$7)&""

复制代码