根据siteID区分数据,表1中每个siteID对应多行数据,要求在表2中的A2填入所需siteID,自动提取表1中对应的数据。
试了几种方法都不行,请各位大神帮忙看下
方法1失败:
=INDEX(Sheet1!H:H,SMALL(IF(Sheet1!$A$2:$A$6991=A2,ROW($2:$6991),4^8),ROW(A1)))&""
方法2失败:
=INDEX(Sheet1!H:H,SMALL(IF(Sheet1!$A$2:$A$6991=A2,ROW($2:$6991),4^8),COUNTIF(Sheet1!$A$2:$A2,A2)))
æ ¹æ®å ³é®åï¼è·¨è¡¨å¼ç¨ï¼å¯ä»¥ç¨vlookupå½æ°æ¥å®ç°ã
表ä¸ç¤ºä¾æ°æ®ï¼
å¦ä¸å¾ï¼å¨åå·¥èµè¡¨æ¶ï¼éè¦æåºç¡èµæ表ï¼å示å§åãèå¡ãåºèªçåºç¡ä¿¡æ¯ã
åå¦å¨è¡¨äºä¸ï¼ç¨å§åä½ä¸ºå ³é®åï¼å¯ä»¥å¦ä¸å¾æ示ï¼
B2å ¬å¼ä¸ºï¼=VLOOKUP($A$2,Sheet1!$A$2:$D$8,COLUMN(B:B),0)
C2å ¬å¼ä¸ºï¼=VLOOKUP($A$2,Sheet1!$A$2:$D$8,COLUMN(C:C),0)
D2å ¬å¼ä¸ºï¼=VLOOKUP($A$2,Sheet1!$A$2:$D$8,COLUMN(D:D),0)
该å½æ°ä»¥å§åä½ä¸ºå ³é®åï¼å¨è¡¨ä¸$A$2:$D$8åºåä¸æ¥æ¾ï¼ç¶ååå«è¿å该åºåç第2ã3ã4åï¼å¹¶ç²¾ç¡®å¹é ãå ¶ä¸COLUMNå½æ°ç¨æ¥çæ该å½æ°ç第ä¸åæ°ï¼å¼ç¨æ¹å¼ä¸ºç¸å¯¹å¼ç¨ï¼åå³æå¨å ¬å¼æ¶ï¼å¯ä»¥èªå¨æ¹ååæ°æ°å¼ã