求助大神，匹配查找区间值

hukeqiang007 · 发表于 2023-6-9 12:00

根据对应的产品名称和订单数量去查找对应阶梯订单量区间所匹配的单价

sayhi95 · 发表于 2023-6-9 12:17

复制代码

yfan17 · 发表于 2023-6-9 12:34

=INDEX(C:C,AGGREGATE(14,6,ROW($A$4:$A$21)/($A$4:$A$21=$E4)/($B$4:$B$21<=$F4),1))

复制代码

hxj119 · 发表于 2023-6-9 12:55

本帖最后由 hxj119 于 2023-6-9 13:05 编辑

=LOOKUP(F4,OFFSET($B$4:$C$4,MATCH(E4,A:A,)-4,,6))

jivy1212 · 发表于 2023-6-9 12:57

复制代码

ericxzhou · 发表于 2023-6-9 13:01

=LOOKUP(F4,IF($A$4:$A$21=E4,$B$4:$B$21),$C$4:$C$21)

solextrade · 发表于 2023-6-9 13:12

=LOOKUP(F4,B$4:C$21/(A$4:A$21=E4))

我的城市你来过 · 发表于 2023-6-9 13:54

=TRIM(MID(SUBSTITUTE(TRIM(SUBSTITUTE(TEXTJOIN(",",,IF(($A$4:$A$21=$E4)*($F4<=$B$4:$B$21),$C$4:$C$21,"")),","," "))," ",REPT(" ",66),1),1,66))

博爱风雨同舟 · 发表于 2023-6-9 14:01

=XLOOKUP(F4,$B$4:$B$21/($A$4:$A$21=E4),$C$4:$C$21,"",-1,-1)

hukeqiang007 · 发表于 2023-6-9 14:07

sayhi95 发表于 2023-6-9 12:17

亲测有用，感谢

		自动登录	找回密码
密码			免费注册

[求助] 求助大神，匹配查找区间值