取比这个单价小于的第一个单价

dwcolin

右边根据产品名称、规格、单价取左边比这单价小档的对应值

清梦扰

=VLOOKUP(J5,SORT(FILTER($E$5:$F$8,H5&I5=$C$5:$C$8&$D$5:$D$8)),2)

part98

K5=TAKE(SORT(IF(($C:$C=$H5)*($D:$D=$I5)*($E:$E<$J5),$F:$F,0)),-1)
L5=TAKE(SORT(IF(($C:$C=$H5)*($D:$D=$I5)*($E:$E<$J5),$E:$E,0)),-1)

袁勇

=AGGREGATE(14,6,E$5:E$8/((C$5:C$8=H5)*(D$5:D$8=I5)*(E$5:E$8<J5)),1)

秃头发师

=XLOOKUP(J9,$E$5:$E$8,$F$5:$F$8,,-1)
=XLOOKUP(J9,$E$5:$E$8,$E$5:$E$8,,-1)

我的城市你来过

=SORT(CHOOSEROWS($E$5:$F$8,XMATCH($J5,$E$5:$E$8,-1)),,,TRUE)

mykitami

=LET(a,FILTER(CHOOSECOLS($C$5:$F$8,4,3),($C$5:$C$8=H5)*($D$5:$D$8=I5)),XLOOKUP(J5,TAKE(a,,-1),a,,-1))

1786024734

仅针对本例中单价按降序排列的情况：
K5=LOOKUP(-J5,-$E$4:$E$8,$F$5:$F$9)下拉
L5=LOOKUP(-J5,-$E$4:$E$8,$E$5:$E$9)下拉