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

[µo°Ý] Vlookup¹J¨ì¦X¨ÖÀx¦s®æ......

[µo°Ý] Vlookup¹J¨ì¦X¨ÖÀx¦s®æ......

½Ð±Ð¦U¦ì¤j¤j¡G

§Q¥ÎHLOOKUP´M§ä¼Æ­È®É¡A¹J¨ì¦X¨ÖÀx¦s®æ¡A¸Ó¦p¦ó³B²z¡H
¤w¸gª¦¹L¤å¡A¦ýÁÙ¬OµLªk²z¸Ñ
©Ò¥H¸Û¤ß½Ð±Ð¦U¦ì¤j¤j¡AÀµ½Ð¤j®aÀ°¦£

A1=°Ó«~¦WºÙ
I4¬°¦X¨ÖÀx¦s®æ¡]°Ó«~¦WºÙ¡^
­n¬d¸ßI6©MJ6¨â®æªº¼Æ­È
I6§Ú¥i¥H§Q¥Î=HLOOKUP( A1,4:6,  3,0) ¬d¸ß¨ì§Ú­nªº­È
¦ýJ6´NµLªk¤F¡A
¥i¥H½Ð¤j®aÀ°¦£¬Ý¬Ý¶Ü¡H  ·PÁÂ

³øªí.zip (5.94 KB)

³øªí

B2¿é¤J¤½¦¡¡A¦V¤U½Æ»s¨ìB3 :

=OFFSET(A$1,5,MATCH(A$1,$4:$4,)+ROW()-3)

TOP

¦^´_ 2# JBY

·PÁ¤j¤jÀ°¦£¡A
¤w¸g¥i¥H¤F¡A¦ý·QÁA¸Ñ¨ä¤¤ªº·N¸q
¥i¥H¸ò±z½Ð±Ð
=OFFSET(A$1,5,MATCH(A$1,$4:$4,)+ROW()-3)
¸Ì­±¥Nªíªº·N¸q¶Ü¡H
¦]¬°·Q­nÁA¸Ñ¨ä¤¤ªº§t·N¡A¤£·Q¥H«á¦A¨Óµo°Ý³oºØ°ÝÃD
©Ò¥H¦A¦¸³Â·Ð¤j¤j¤F¡A·P®¦~

TOP

¦^´_ 1# hsien33

   ¸Õºâªí³Ì¦n¤£­n¥Î¦X¨Ö®æ·|¼W¥[¹Bºâ³Â·Ð¡C¤@©w­n¥Î³Ì¦n¥Î°²©Ê¦X¨Ö®æ
¡@½Ð°Ñ¦Òhttp://forum.twbts.com/viewthrea ... A8%D6%C0x%A6s%AE%E6
    B2=INDEX($6:$6,MATCH(A$1,$4:$4,)+ROW(A1)/2)
   ©¹¤UCOPY

TOP

¦^´_ 4# ANGELA

·PÁ¤j¤jªºÀ°¦£
¤w¸g¥i¥H¤F
¥t¥~¡A¸ò¤j¤j»¡©ú¤@¤U
¤£¬O§Ú·Q¥Î¦X¨ÖÀx¦s®æ
¦Ó¬OµLªk±±ºÞ¹ï¤è¨Óªºªí®æ
¥u¯à¨Ì¾Ú¥Lªºªí®æ¨Ó³B²z
©Ò¥H«ÜµL©`

¹ï¤F¡A¤j¤j±z¤è«K±Ð§Ú
B2=INDEX($6:$6,MATCH(A$1,$4:$4,)+ROW(A1)/2)
¸Ì­±MATCH() ¸òROW(A1)/2ªº·N¸q¶Ü¡H
§Ú­ì¥»¥H¬°$4:$4¬O½d³ò¡A¦ý§ï¦¨$C4:$Z4
´N¶]¥X¤£¤@¼Ëªº¼Æ­È
ÁÙ¦³ROW¤]·d¤£¤ÓÀ´§Ú­n«ç»ò¥[´X¦æ
©Ò¥H·QÁA¸Ñ¨ä¤¤ªº·N²[¡A¤§«á¤~¯à¥[¥H¹B¥Î
¤]¤~¤£·|±`¨Ó°Ý²Â°ÝÃD
¦pªG³y¦¨¤j¤jªº§xÂZ¡A½Ð¨£½Ì.......
ÁÂÁÂ

TOP

¦^´_ 5# hsien33


    $4:$4¬O½d³ò¨S¿ù,¥NªíA4:*4  (*¥Nªí³Ì«á¤@Äæ,¬Ý§A¥Î¨º­Óª©¥»ªºEXCEL)
§ï¦¨$C4:$Z4,match¨ç¼Æ·|±qC4§ä°_¦Ó¤£¬OA4§ä°_,±o¨ìªºµª®×·|®t2
index¨ç¼Æ·|¨ú¾ã¼Æ©¿µø¤p¼ÆB2ªº¤½¦¡ row(a1)/2=0.5§Yµ¥©ó+0  
B3ªº¤½¦¡ row(a2)/2=1§Yµ¥©ó+1§YB2»PB3±o¨ìªºµª®×·|®t¤@Äæ
µ½¥Î¤½¦¡¤¤ªº¤½¦¡½]®Ö¥i¤F¸Ñ¤½¦¡ªº¹B§@
¤Ï¥Õ½s¿è¦Cªº¤½¦¡¤¤¬Y¤@¬q¤½¦¡¦A«öF9¤]¬O¤@ºØ¤èªk

TOP

¦^´_ 6# ANGELA

¦A½Ð±Ð¤j¤j
¦pªG§Ú­n·j´Mªº¸ê®Æ¡y°Ó«~¦WºÙ¡z
¦b²Ä¥|¦C¤¤¦³¨â­Ó¤@¼Ëªº
¦Ó§Ú­n¬dªº­È¬O²Ä¤G­Ó¡y°Ó«~¦WºÙ¡z
¤Uªº­È
¸Ó¦p¦ó³B²z¡H

TOP

©êºp¤j¤j
¥i¯à¬O§ÚÁ¿ªº¤£°÷²M·¡
§Ú­«·s°µ¤F¤@­ÓÀÉ®×
§Æ±æ¯à¸ÑÄÀªº²M·¡¤@ÂI

°²³]§Ú¦³¨â­Ó¤@¼Ë¦WºÙªº¡y²Ö­p¡z
¦ý©³¤Uªº­È¤£¤@¼Ë
¤S¸Ó¦p¦ó¬d¸ß©O¡H
¡]D3¡BD4­n¬d¸ßM7¡BN7ªº¤½¦¡¸Ó¦p¦ó³]©O¡^

javascript:;

Àµ½Ð¦U¦ì¤j¤jÀ°À°§Ú¡A·PÁÂ

³øªí.jpg (41.61 KB)

³øªí

³øªí.jpg

TOP

¦^´_ 8# hsien33

D3=INDEX(7:7,LOOKUP(2,1/(A6:N6=c3),COLUMN(A1:N1)))

TOP

¦^´_ 3# hsien33

D3¿é¤J¤½¦¡¡A¦V¤U½Æ»s¨ìD4 :

=INDEX($7:$7,INDEX(MATCH(2,1/(A$6:N$6=C3)),))

TOP

        ÀR«ä¦Û¦b : ¡i®É¤é²öªÅ¹L¡j¤@­Ó¤H¦b¥@¶¡°µ¤F¦h¤Ö¨Æ¡A´Nµ¥©ó¹Ø©R¦³¦hªø¡C¦]¦¹¥²¶·»P®É¶¡Ävª§¡A¤Á²ö¨Ï®É¤éªÅ¹L¡C
ªð¦^¦Cªí ¤W¤@¥DÃD