[µo°Ý] VLOOKUP°Ñ·Ó¦hªí°ÝÃD

yisam626

=IF(A1,IF(A1=11,VLOOKUP(C1,'ªí1'!$A:$B,2,FALSE),IF(A1=28,VLOOKUP(C251,'ªí2'!$A:$D,4,FALSE),IF(A1=14,VLOOKUP(C1,'ªí3'!$A:$C,3,FALSE),B1))),"")
=IF(A1=11,VLOOKUP(C1,'ªí1'!$A:$B,2),IF(A1=28,VLOOKUP(C251,'ªí2'!$A:$D,4),IF(A1=14,VLOOKUP(C1,'ªí3'!$A:$C,3),B1)))
1.½Ð°Ý¤W¨â­Ó¤½¦¡¦³¦ó®t²§?
2.½Ð°Ý¦³¦ó¿ìªk¥i¥H²¤Æ¦¹¤½¦¡¹F¨ì¬Û¦P¥Øªº?

¦]¬°¦A¨Ó­n°Ñ·Óªºªí¶V¨Ó¶V¦h¤F  ¬G¨Ó¦¹½Ð¯q
ÁٽФ£§[½ç±Ð  ·PÁÂ

zxcxz

­º¤½¦¡·íA1=0®É±o¥X""¡A¦¸¤½¦¡·íA1=0®É±o¥XB1­È
¨ä¦¸¬Ovlookup true¸òfalse ªº¤À§O (¦¸¤½¦¡¨S¿ïtrue/false¡A¨t²Î±N¹w³]¬°true)

yisam626

­º¤½¦¡·íA1=0®É±o¥X""¡A¦¸¤½¦¡·íA1=0®É±o¥XB1­È
¨ä¦¸¬Ovlookup true¸òfalse ªº¤À§O (¦¸¤½¦¡¨S¿ïtrue/false ...
zxcxz µoªí©ó 2014-2-9 01:14

    ®¦®¦·PÁ¦^µª

½Ð°Ý¦³¤H¥i¥H¸Ñµª²Ä¤G­Ó°ÝÃD¶Ü

Hsieh

¦^´_ 3# yisam626

°Ñ·Óªí®æªº³W«hµLªk½T©w´N¤£¯à²¤Æ
¥H¥Ø«e1#¤½¦¡¬Ý¨Ó
3­Ó±ø¥óªº°Ñ·Ó³£¤£¦P
©Ò¥H´N¥²¶·±NVLOOKUPªº3­Ó¤Þ¼Æ¥Î¤½¦¡¥h±a¥X¨Ó
´£¨Ñ¤@ºØ¤è¦¡°Ñ¦Ò¬Ý¬Ý
=VLOOKUP(CHOOSE(MATCH(A1,{11,28,14},0),C1,C251,C1),INDIRECT("'ªí"&MATCH(A1,{11,28,14},0)&"'!A:D"),INDEX({2,4,3},MATCH(A1,{11,28,14},0)),0)

yisam626

¦^´_  yisam626

°Ñ·Óªí®æªº³W«hµLªk½T©w´N¤£¯à²¤Æ
¥H¥Ø«e1#¤½¦¡¬Ý¨Ó
3­Ó±ø¥óªº°Ñ·Ó³£¤£¦P
©Ò¥H´N¥² ...
Hsieh µoªí©ó 2014-2-10 14:44

    ®¦®¦¥ý·PÁ±zªº¦^ÂÐ
1. C251¥´¿ù¡A³£¬OC1¡C
2. ªí1¡Bªí2¡Bªí3¡A¥u¬O¥N¸¹¡A¹ê»Ú¥i¯à¬O¡Gmath..english..chinese¤§Ãþªº¡C

yisam626

¦^´_ 5# yisam626


    (¤£ª¾¹D«ç»ò¦^¯d¨¥)
¤£¦n·N«ä  Àɮ׬O¤½¥qªº¸ê®Æ
¥i¯à¤£¤Ó¤è«K¤W¶Ç
(¤§«e¨Sª`·N¨ì¦³¯d¨¥  ©Ò¥H¨S¦^ÂШì)

§±æ¢

¦^´_ 4# Hsieh


§Úªº¥X³f³æ¤¤¡A¦³¦n´X­Ó°Ó«~¦WºÙ¤À´²¦b¦U­Ó«~¦W¶µ¥Ø¤¤¡A¦ý¼Æ¶q¤£¦P¡A¨Ï¥ÎVLOOKUP¨ç¼Æ¡A§ì±o¨ì­È¡A¦ý¼Æ¶q¤£¥¿½T
©Ò¥H·Q¨Ï¥Î¦h±ø¥ó¬d§äªí®æ¡A°ÝÃD¬O¡K§Ú¸Õ¤F¦n´XºØ¤è¦¡¡A¶Ç¦^ªº­È¤£¬OªÅ¥Õ´N¬O¿ù»~¡A­n¨Ï¥Î¤°»ò¨ç¼Æ¤ñ¸û¦n©O?
¥ý»¡ÁnÁÂÁ³á!!

ªþ¤WÀÉ®×
ª«®ÆºÞ²z.zip (459.05 KB)

¤U¸ü¦¸¼Æ: 5

2014-4-2 10:48

Hsieh

¦^´_ 7# §±æ¢
F9°}¦C¤½¦¡
=VLOOKUP(¥X³f³æ!$E9,OFFSET(¦X¬ù!$B$1,MAX(IFERROR(MATCH($C9:$D9,¦X¬ù!$B:$B,0),0)),,10000,4),4,0)
¦Ü©ó²Î­p¦U¤ë¡B¦U¼t°Ó¥ý¦Û¦æª¦¤å«á°w¹ï¦³°ÝÃD¦A´£°Ý

§±æ¢

¦^´_ 8# Hsieh


    ¦n¡AÁÂÁ¡C
   ¥ý¬ã¨s¤@¤U¤½¦¡ªº²Õ¦X¡A«Ü¦hÁÙ¬O¤£¤ÓÀ´©O¡C