ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

½Ð°ÝÃö©ó§Ö³t¿z¿ï¸ê®Æ¡C

½Ð°ÝÃö©ó§Ö³t¿z¿ï¸ê®Æ¡C

¦U¦ì¤j¤j¦n¡A°²³]§Ú¦³10000µ§email¡A¥t¤@¥÷¦W³æ¬O¨ä¤¤300µ§¡A§ÚÀ³­n¦p¦ó¦b³o10000µ§¸Ì­±¡A¬D¥X³o300µ§¨Ã§R°£¡H

¦U¦ì¤j¤j¦n¡A§Ú·Q¨ì¤èªk¤F¡G
§â300­Óemail¥ý½s¸¹(1~300)¡A¤§«á¥ÎVLOOKUP¨ç¼Æ°µ¹ïÀ³¡A­Y¬O§ä¨ì²Å¦Xªº´N¶Ç¦^300­Óemail¸Ì¡A§Ú©Òµ¹ªº½s¸¹(­YÅã¥Ü"#N/A"«hªí¥Ü§ä¤£¨ì½s¸¹¡A¤]´N¬O¤£¦b300­Óemail¸Ì­±)¡A¤§«á¦A¶}±Ò¿z¿ï¡A­ç°£¦³½s¸¹ªº³¡¤À§Y¥i¡C
¤£ª¾¨ä¥L¤j¤j¬O§_¦³§óÁo©úªº¤èªk¡AÁÂÁ¡C

TOP

¥»©«³Ì«á¥Ñ sunnyso ©ó 2013-4-30 10:47 ½s¿è

¦^´_ 2# tku0216
A1~ A1000 = email 1 ~ email 1000
C1 ~C300 = email 1 ~ 300

¤èªk¤@
(Ctrl+Shift+ Enter)¦V¤U½Æ»s. °}¦C¸ê®Æ¦h¥i¯à·|¤ñ¸ûºC
B1 = IFERROR(INDEX(A:A,SMALL(IF(COUNTIF(OFFSET($C$1,,,COUNTA(C:C),),OFFSET($A$1,,,COUNTA(A:A),))=0,ROW(OFFSET($A$1,,,COUNTA(A:A),)),""),ROW(1:1)),1),"")

¤èªk¤G
»²§UÄæD, µª®×ÄæE;¦V¤U½Æ»s
D1 = IF(COUNTIF(OFFSET($C$1,,,COUNTA(C:C),),A1)=0,ROW(A1),"")
E1 =IFERROR(INDEX(OFFSET($A$1,,,COUNTA(A:A),),SMALL(D:D,ROW(A1)),1),"")
ss

TOP

        ÀR«ä¦Û¦b : ¤H¥Í¤£¤@©w²y²y¬O¦n²y¡A¦ý¬O¦³¾ú½mªº±j¥´ªÌ¡AÀH®É³£¥i¥H´§´Î¡C
ªð¦^¦Cªí ¤W¤@¥DÃD