EXCELR如何将品类所对应的价格带自动引用到左边黄色区域附件:链接:https://pan.baidu.com/s/1R00NAI4J4bIE6DjAVySchw 提取码:j74w
在C2单元格输入公式=IF($A2&$B2="","",SUBSTITUTE(SUBSTITUTE(IFERROR(INDEX($G$2:$G$10000,SMALL(IF(($F$2:$F$10000=$A2)*($G$2:$G$10000=$B2),ROW($2:$2)-1,6^6),ROW(A$1))),"")&IFERROR(INDEX($H$2:$H$10000,SMALL(IF(($F$2:$F$10000=$A2)*($H$2:$H$10000=$B2),ROW($2:$2)-1,6^6),ROW(A$1))),"")&IFERROR(INDEX($I$2:$I$10000,SMALL(IF(($F$2:$F$10000=$A2)*($I$2:$I$10000=$B2),ROW($2:$2)-1,6^6),ROW(A$1))),"")&IFERROR(INDEX($J$2:$J$10000,SMALL(IF(($F$2:$F$10000=$A2)*($J$2:$J$10000=$B2),ROW($2:$2)-1,6^6),ROW(A$1))),"")&IFERROR(INDEX($K$2:$K$10000,SMALL(IF(($F$2:$F$10000=$A2)*($K$2:$K$10000=$B2),ROW($2:$2)-1,6^6),ROW(A$1))),"")&IFERROR(INDEX($L$2:$L$10000,SMALL(IF(($F$2:$F$10000=$A2)*($L$2:$L$10000=$B2),ROW($2:$2)-1,6^6),ROW(A$1))),""),"始","价格带"),"终","价格带"))
数组公式,按CTRL+SHIFT+ENTER结束公式,使公式前后出现大括号{}后,下拉公式。 如图:
这个公式完全不符合条件啊
大神,这个也可以实现。谢谢谢谢!