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

¦³ÃöIndex »P Lookup¦p¦ó¯à§ä¥X¨Ì¤j¤p¼g­­©wªº­È©O

¦³ÃöIndex »P Lookup¦p¦ó¯à§ä¥X¨Ì¤j¤p¼g­­©wªº­È©O

¦U¦ì¤j¤j¡A
¤p§Ìª¾¹D¥i¥H³z¹LIndex»PLookup¤À§O§ä¥X²Å¦Xªº­Èªº²Ä¤@µ§¡A»P³Ì«á¤@µ§²Å¦X¸ê®Æ¡A¦ý¬O³o­Ó§äªk¡A¤j¤p¼g¤£¦P·|µø¬°²Å¦X­È¡A¯à¤£¯à¦³¿ëªk¥[¤W­­©w¤j¤p¼g­n§¹¥þ¬Û¦Pªº±ø¥ó©O¡H


§ä²Ä¤@µ§²Å¦Xªº¦¡¤l¬O¡GINDEX(A:A,MATCH(D2,B:B,0))
§ä³Ì«á¤@µ§²Å¦Xªº¦¡¤l¬O¡GLOOKUP(1,0/(B:B=D3),A:A)
Áٽаª¤âÀ°§Ú¸Ñ´b¡A·PÁ±z
Find.zip (6.39 KB)
clio

²Ä1­Ó§¹¥þ¬Û¦P
=MATCH(,0/EXACT($D2,$B$2:$B$14),0)

³Ì«á1­Ó§¹¥þ¬Û¦P
=MATCH(,0/EXACT($D2,$B$2:$B$14),1)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# ML089
ML089¤j¤j¡K
§Ú¸Õ¹L¡A¶]¤£¥Xµ²ªG¡A·|¥X²{#DIV/0!
clio

TOP

¦^´_ 3# clio
°}¦C¤½¦¡¡A¿é¤J¤½¦¡­n¥Î¤TÁä¿é¤J

ÂI¿ï½s¿è¦C¤½¦¡
¥ý«ö¦í SHIFT+CTRL ¤£©ñ ¦A«öENTER ¿é¤J
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 4# ML089
·PÁÂML089¤j¤j¡A
¥i¥H¤F¡A§Ú¯à¦A½Ð±Ð¤@¤U¶Ü¡H
MATCH(,0/EXACT($D2,$B$2:$B$14),0)¡÷¡iMATCH(,¡j¬Ù²¤¤@­Ó°Ñ¼Æ¡A¬O¤£¬Oµ¥¦P¸É¤W0¬°¡iMATCH(0,¡j
clio

TOP

¦^´_ 5# hcm19522
·PÁÂhcm19522¤j¤j¡A
³o­Óµª®×¤]¬O¥i¥Hªº¡A¤p§Ì¬ã¨s¤@¤U¦³¤@­Ó°ÝÃD·Q½Ð±Ð¤j¤j
=MATCH(COLUMN(A1)-1,0/EXACT($D2,$B$2:$B$14),COLUMN(A1)-1)¡÷³o­Ó¦¡¤l¡A¦b§ä²Ä¤@µ§¸ê®Æ®É¡A¤pªºµo²{COLUMN(A1)-1¡÷0¡A¦ý¬O§Ú­Yª½±µ§ï¦¨1¡Aµª®×´N·|¤£¥¿½T¡A¦ý¬O
¦b§ä³Ì«á¤@µ§¸ê®Æ®É·|Åܦ¨COLUMN(B1)-1¡÷1¡A­Y¬O§Úª½±µ§ï¦¨0¡Aµª®×¤´¬O¥¿½Tªº¡A©Ò¥H§Ú¦bMatch«áªº²Ä¤@­Ó°Ñ¼Æ¡A0©Î1ªº®t§O¡A¦ó®É¤GªÌ³£¥i¥Î¡A¦ó®É¥u¯à¥Î0©O¡A¤pªº¤£¤Ó¤F¸Ñ³o­Ó·N¸q¯à§_½Ð¤j¤j¥i¥HÀ°¤p§Ì¸Ñ´b¡A·PÁ±z¡C
clio

TOP

¦^´_ 6# clio


Match(,
µ¥¦P
Match(0,
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 8# ML089
·PÁÂML089¤j¤jªº¦^ÂÐ
clio

TOP

¦^´_ 7# clio
¤À¶} («e«á¤£¤@­P 0»P1 ,³¡¤À¦³»~)
MATCH(,0/EXACT($D2,$B$2:$B$14),)
MATCH(1,0/EXACT($D2,$B$2:$B$14),1)
¤@¦¡¤½¦¡  («e«á¥[ COLUMN(A1)-1)
MATCH(COLUMN(A1)-1,0/EXACT($D2,$B$2:$B$14),COLUMN(A1)-1)
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD