分类：四个排名区间，不同的款式有参考价，根据当天的排名找出对应的参考价

304208902

分类：四个排名区间，不同的款式有参考价，请问如何根据当天的排名找出对应的参考价？
如果是2个排名区间可以用vlookup，但是变到4个区间就不知道如何处理了。请教下大家，谢谢~

niko88819

i2

1. =IFERROR(INDEX($B$2:$C$6,MATCH(G2,$A$2:$A$6,),MATCH(H2+1%%,{0,31,101})),INDEX($B$8:$C$14,MATCH(G2,$A$8:$A$14,),MATCH(H2+1%%,{0,51,101})))

复制代码

咔咔乱坠

想到那句话了，IF到世界尽头

1. =SUM(IF(A$2:A$6=G2,IF(H2<=30,B$2:B$6,C$2:C$6),),IF(H2>100,0,IF(A$8:A$14=G2,IF(H2<=50,B$8:B$14,C$8:C$14),)))

复制代码

砍了一点

1. =IF(H2>100,"",SUM(IF(G2=A$2:A$6,IF(H2<=30,B$2:B$6,C$2:C$6)),IF(G2=A$8:A$14,IF(H2<=30,B$8:B$14,C$8:C$14))))

复制代码

再砍一点

1. =IF(H2>100,"",INDEX(IF(H2<=IF(OR(CHAR(ROW($65:$69))=G2),30,50),B$2:B$14,C$2:C$14),MATCH(G2,A$2:A$14,)))

复制代码

tchh666

1. =IFNA(VLOOKUP(G3,A:C,MATCH(,IF(OR(A$2:A$6=G3),VDB(,,{30,100},{1,31},H3),VDB(,,{50,100},{1,51},H3)),)+1,),)

复制代码

橒♂蝣

=IF(H2>100,"无参考价",IF(ISNUMBER(XMATCH(G2,$A$2:$A$6)),FILTER(FILTER($B$2:$C$6,$A$2:$A$6=G2),ISNUMBER(FIND(MEDIAN(H2,30,31),$B$1:$C$1))),FILTER(FILTER($B$8:$C$14,$A$8:$A$14=G2),ISNUMBER(FIND(MEDIAN(H2,50,51),$B$7:$C$7)))))

袁勇

仅针对附件：

1. =IF(H2>100,"无参考价",VLOOKUP(G2,A:D,2+(H2>30+(G2>"E")*20)*1,0))

复制代码