ªð¦^¦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
2019-07-05_111914.png
2019-7-5 11:19


§ä²Ä¤@µ§²Å¦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

¦^´_ 10# hcm19522
·PÁÂhcm19522ªº¦^ÂСAÁöµM§ÚÁÙ¬O¤£¤Ó¤F¸Ñ¡K¦ý¬O¤ÏÂиչL«á¡A¥ÎMatch¨Ó§ä³Ì«á¤@µ§¥X²{ªº²Å¦X¼Æ¦r¡A¦n¹³¤£¬O¨C¤@­Ó±¡ªp³£¯à²Å¦X¡A¥HMATCH(1,0/EXACT($D2,$B$2:$B$14),1)¡A¦]¬°§äªºRange¸Ì­±¡A³£¥u¦³0¸ò¿ù»~°T®§¡A©Ò¥H¥i¥H¥¿½Tªº§ä¨ì³Ì«á¤@­Ó0ªº¦ì¸m¡A¦ý¬O­Y¬O³o­Ó¸`³ò¡A¬O¤@¶ô¼Æ¦r¡A¥B¤£¬O¨Ì±Æ§Ç¤j¤p¡A·|¦³­«½Æ¥X²{ªº¡A¦n¹³´N¤£¤@©w¯à§ä¨ì¤F¡A¬O³o¼Ëªº¶Ü¡H
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)
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

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

TOP

¦^´_ 6# clio


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

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

¦^´_ 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

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

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

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

TOP

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

TOP

        ÀR«ä¦Û¦b : ­n¤ñ½Ö§ó¨ü½Ö¡D¤£­n¤ñ½Ö§ó©È½Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD