具体引用位置请自行修改,以下列出4种
OFFSET的- =OFFSET(H$1,SMALL(IF(G$2:G$3=$A$2,ROW($2:$3)),ROW(A1))-1,)
复制代码 INDEX 的- =INDEX(H$2:H$3,SMALL(IF(G$2:G$3=$A$2,ROW($2:$3)),ROW(A1))-1)
复制代码 INDIRECT的- =INDIRECT("H"&SMALL(IF(G$2:G$3=$A$2,ROW($2:$3)),ROW(A1)))
复制代码 VLOOKUP的- =VLOOKUP(A$2&ROW(A1),IF({1,0},G$2:G$3&COUNTIF(INDIRECT("G2:G"&ROW(G$2:G$3)),G$2:G$3),H$2:H$3),2,)
复制代码 |