excel查找指定值并返回对应的名称？ EXCEL查找数据并返回对应列的值
excel\u5185\u67e5\u627e\u5305\u542b\u7279\u5b9a\u5b57\u7b26\u7684\u5217\u5e76\u8fd4\u56de\u76f8\u5e94\u7684\u6570\u503cexcel\u5185\u67e5\u627e\u5305\u542b\u7279\u5b9a\u5b57\u7b26\u7684\u5217\u5e76\u8fd4\u56de\u76f8\u5e94\u7684\u6570\u503c\u7684\u5177\u4f53\u64cd\u4f5c\u65b9\u6cd5\u5982\u4e0b\uff1a
1.\u7b80\u5355\u7684\u4e3e\u4e2a\u4f8b\u5b50\uff0c\u4f8b\u5b50\u7b80\u5355\u4f46\u5f88\u5f62\u8c61\uff0c\u80fd\u591f\u4e86\u89e3\u51fd\u6570\u7684\u7528\u6cd5\u5c31\u53ef\u4ee5\u4e86\u3002
2.\u90a3\u4e48\u6211\u4eec\u5728C\u5217\u4e2d\u8f93\u5165\u51fd\u6570vlookup\u5339\u914d\u51fd\u6570\uff0c\u627e\u51fa\u5b58\u5728\u7684\u9879\u76ee\u5e76\u663e\u793a\u51fa\u6765\uff1b\u7b2c\u4e00\u5217\u662fA1\u7136\u540e\u7528\u9017\u53f7\u9694\u5f00\uff0c\u5c31\u53ef\u4ee5\u70b9\u51fb\u63d0\u793a\u7684\u7b2c\u4e8c\u4e2a\u53c2\u6570\uff1b\u7b2c\u4e8c\u4e2a\u53c2\u6570\u6211\u4eec\u76f4\u63a5\u70b9\u51fb\u7b2c\u4e8c\u5217\u5373B\u5217\u3002
3.\u7136\u540e\u9017\u53f7\u9694\u5f00\uff0c\u9009\u62e9\u4e0b\u4e2a\u53c2\u6570\uff0c\u7b2c\u4e09\u4e2a\u53c2\u6570\u662f1\uff1b\u7b2c\u56db\u4e2a\u53c2\u6570\u662f\u7cbe\u786e\u4e0e\u6a21\u7cca\u5339\u914d\u7684\u6982\u5ff5\uff0c\u6211\u4eec\u8fd9\u91cc\u9009\u62e90\u6216false\uff0c\u7cbe\u786e\u5339\u914d\u3002
4.\u6211\u4eec\u53ef\u4ee5\u9009\u62e9\u5f80\u4e0b\u62c9\uff0c\u4e5f\u53ef\u4ee5\u9009\u4e2d\u7b2c\u4e00\u4e2a\u5355\u5143\u683c\u53f3\u4e0b\u89d2\u51fa\u73b0\uff0b\u5b57\u540e\u53cc\u51fb\uff0c\u6570\u76ee\u5e94\u4e8e\u7b2c\u4e00\u5217\u76f8\u540c\u3002
5.\u6211\u4eec\u53ef\u4ee5\u770b\u5230C\u5217\u4e2d\u90fd\u5b58\u5728\u6570\u636e\uff0c\u8bf4\u660eA\u5217\u5728B\u5217\u4e2d\u90fd\u5b58\u5728\uff0c\u5982\u679c\u4e0d\u5b58\u5728\u90a3\u4e48\u73b0\u5b9e#value\uff0c\u5c31\u662f\u65e0\u6cd5\u663e\u793a\u7684\u610f\u601d\uff1b
6.\u5728\u6a21\u7cca\u5339\u914d\u7684\u65f6\u5019\u8981\u7528\u5230*\u53f7\uff0cB\u5217\u662f12345678\uff0cA\u5217\u662f345\uff0c\u90a3\u4e48\u6211\u4eec\u53ef\u4ee5\u5199\u6210\u201c*\u201d&A&\u201c*\u201d\uff0c\u8fd9\u4e2a\u901a\u914d\u7b26\u6765\u8868\u793a\u4efb\u610f\u5b57\u7b26\uff0c\u53ea\u8981\u5305\u542b\u5373\u53ef\u3002
\u5047\u8bbe\u539f\u59cb\u6570\u636e\u5728A1:B10\u4e2d,\u516c\u5f0f\u53ef\u4e3a
=IF(SUMPRODUCT(N(ISNUMBER(FIND("*",A1:A10))*(B1:B101))),"\u4e0d\u901a\u8fc7","\u901a\u8fc7")
=LOOKUP(1,0/(B$1:B$12=B14),A$1:A$13)
输入公式下拉
在C14输入
=LOOKUP(1,0/(B3:B13=B14),A3:A13)
B14=MIN(B3:B13)
C14=INDEX(A3:A13,MATCH(B14,B3:B13,0))
C14=OFFSET(A2,MATCH(B14,B3:B13,0),,)
绛旓細Excel涓鍙互鍒╃敤Vlookup鍑芥暟鍦ㄤ竴鍒椾腑鏌ユ壘鎸囧畾鍊,鐒跺悗杈撳嚭浠栧悗闈㈢殑閭ｄ竴鍒椼傝蒋浠剁増鏈細Office2007 涓句緥璇存槑濡備笅锛1.鏌ユ壘A鍒椾腑涓嶦鍒楃浉鍚岀殑鍊硷紝杩斿洖瀵瑰簲鐨凢鍒楃殑鍊硷細2.杈撳叆鍏紡濡備笅锛氬叕寮忚В閲婏細鏌ユ壘鍊涓篈鍒楁暟鍊硷紝鍥犳绗竴涓弬鏁颁负A1锛涚浜屼釜鍙傛暟鏌ユ壘鍖哄煙涓篍1:F7锛屽洜涓鸿涓嬫媺濉厖锛屾墍浠ヨ繖閲屽繀椤昏鐢ㄧ粷瀵瑰紩鐢...
绛旓細濡備綍鐢‥xcel閲岀殑VLOOKUP鍋氭暟鎹尮閰 涓銆佹槑纭瓟妗 VLOOKUP鍑芥暟鏄Excel涓鐢ㄤ簬鏌ユ壘鍜屽尮閰嶆暟鎹殑鍔熻兘锛屽彲浠ュ揩閫熸壘鍒鎸囧畾鍊煎苟杩斿洖鐩稿叧鏁版嵁銆備簩銆佽缁嗚В閲 1. 鍑芥暟缁撴瀯锛歏LOOKUP鍑芥暟鐨勫畬鏁寸粨鏋勬槸`=VLOOKUP`銆傚叾涓紝鈥鏌ユ壘鍊鈥濇槸浣犳兂瑕佹壘鍒扮殑鏁版嵁锛屸滄煡鎵捐寖鍥粹濆寘鍚彲鑳界殑鍖归厤鏁版嵁鍖哄煙锛屸杩斿洖鍊鍒楁暟鈥濇槸浣犳兂浠庡尮閰...
绛旓細Excel琛ㄦ牸鐨刅LOOKUP鍏紡浣跨敤鏂规硶 涓銆佹槑纭瓟妗 VLOOKUP鍏紡鏄Excel涓鐢ㄤ簬鏌ユ壘骞惰繑鍥涓鎸囧畾鍊鍖归厤鐨勬暟鎹殑闈炲父瀹炵敤鐨勫嚱鏁般傚叾鍩烘湰璇硶涓猴細VLOOKUP銆傛帉鎻LOOKUP锛岃兘鏋佸ぇ鎻愰珮宸ヤ綔鏁堢巼銆備簩銆佽缁嗚В閲 1. 鏌ユ壘鍊涓庢煡鎵捐寖鍥达細鏌ユ壘鍊兼槸鎮ㄦ兂鍦ㄦ暟鎹〃涓煡鎵剧殑鐗瑰畾鏁版嵁銆傛煡鎵捐寖鍥村垯鏄寘鍚彲鑳藉尮閰嶆暟鎹殑鍖哄煙銆傝繖涓や釜鍙傛暟...
绛旓細鎮ㄥ彲浠ヤ娇鐢 Excel 涓鐨 VLOOKUP 鍑芥暟鏉ュ疄鐜拌繖涓洰鐨勩俈LOOKUP 鍑芥暟鐢ㄤ簬鍦ㄨ〃鏍间腑鍨傜洿鏌ユ壘鏌愪釜鍊锛骞惰繑鍥璇ュ兼墍鍦ㄨ鎸囧畾鍒楃殑鍊笺傚亣璁炬偍瑕佸湪鍙︿竴涓〃鐨瀵瑰簲鏃ユ湡鍒椾腑濉叆鏌ユ壘鍒扮殑鍊硷紝骞跺湪鎵句笉鍒扮殑鎯呭喌涓嬪～鍏モ滄湭鎵惧埌鈥濓紝鍙互鎸夌収浠ヤ笅姝ラ鎿嶄綔锛1. 鍦ㄩ渶瑕佸～鍐欐煡鎵惧埌鐨勫肩殑琛ㄤ腑鍒涘缓涓涓柊鐨勫垪锛岀敤浜庡瓨鏀 ...
绛旓細VLOOKUP鍑芥暟鐨勪綔鐢ㄦ槸锛屽湪琛ㄦ牸鏁扮粍鐨勯鍒鏌ユ壘鎸囧畾鐨勫硷紝骞剁敱姝杩斿洖琛ㄦ牸鏁扮粍褰撳墠琛屼腑鍏朵粬鍒楃殑鍊笺俈LOOKUP 涓殑 V 鍙傛暟琛ㄧず鍨傜洿鏂瑰悜銆傚綋姣旇緝鍊间綅浜庨渶瑕佹煡鎵剧殑鏁版嵁宸﹁竟鐨勪竴鍒楁椂锛屽彲浠ヤ娇鐢 VLOOKUP 鑰屼笉鏄 HLOOKUP銆傝娉 VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)绗竴鍙傛暟锛歀ookup_value ...
绛旓細瑕佹煡鎵剧洰鏍骞惰繑鍥炴煡鎵鍖哄煙璇ョ洰鏍囩殑鍊艰浣跨敤鍑芥暟VLOOKUP銆傛柟娉曞涓嬶細浠2007鐗EXCEL涓轰緥锛屾墦寮涓嬪浘鎵绀Excel宸ヤ綔琛紝瑕佸湪鏌ユ壘鑼冨洿C鍒椼丏鍒椾腑鏌ユ壘A鍒椾腑鐨勭紪鍙峰苟杩斿洖鏌ユ壘鑼冨洿鍐呰鐩爣瀵瑰簲鐨勫湪D鍒椾腑鐨鏁板銆1銆佸湪F2鍗曞厓鏍间腑杈撳叆鍑芥暟锛=VLOOKUP锛堬級锛屽涓嬪浘鎵绀恒傝杩愮敤VLOOKUP鍑芥暟锛岄鍏堟垜浠鐭ラ亾瀹冪殑璇硶鍜...
绛旓細鍙互浣跨敤Excel鐨刅LOOKUP鍑芥暟鏉ュ疄鐜拌繖涓渶姹傘俈LOOKUP鍑芥暟鍙互鏌ユ壘鎸囧畾鐨勫煎湪鏌愪竴鍒椾腑鐨勪綅缃紝骞舵牴鎹綅缃杩斿洖璇ヨ鐨勫叾浠栨暟鎹傚叿浣撴潵璇达紝浣犲彲浠ュ湪O鍒椾腑杈撳叆浠ヤ笅鍏紡锛=VLOOKUP(M1,A:J,2,FALSE)杩欎釜鍏紡鐨勬剰鎬濇槸锛歏LOOKUP鍑芥暟鐨勭涓涓弬鏁版槸鏌ユ壘鐨勫硷紝鍦ㄨ繖閲屼负M1鍗曞厓鏍间腑鐨勫笺俈LOOKUP鍑芥暟鐨勭浜屼釜鍙傛暟鏄...
绛旓細浣犲ソ锛孷LOOKUP鍑芥暟鏄釜鏌ユ壘寮曠敤鍑芥暟锛屽湪鎸囧畾鍖哄煙鐨勯鍒鏌ユ壘鎸囧畾鐨勫硷紝骞惰繑鍥璇ュ兼墍鍦ㄧ殑琛岀殑鏌愪竴鍒楃殑鍊笺俈LOOKUP鍑芥暟閫氫織鐨勮〃绀轰负锛=VLOOKUP锛堟煡鎵惧璞★紝鏌ユ壘鍖哄煙锛杩斿洖鍊鐨勫垪鏁锛夊厛鐪嬩竴涓畝鍗曠殑鍚屼竴琛ㄦ牸鐨勬煡鎵惧紩鐢ㄤ緥瀛愶細鍋囪A1:A3鏄鐢熷鍚嶏細寮犱笁銆佹潕鍥涖佺帇浜 B1:B3鏄鏂囨垚缁╋細98銆90銆88 C3鍗曞厓鏍奸噷...
绛旓細1銆Excel涓繑鍥炴悳绱㈠鐨鎸囧畾浣嶇疆闇瑕佺敤鍒板嚱鏁癕ATCH銆2銆丮atch鍑芥暟鐨勪娇鐢ㄦ牸寮忎负锛歁ATCH(lookup_value,lookup_array,match_type)銆3銆佸嚱鏁颁腑锛孡ookup_value浠ｈ〃闇瑕佸湪鏁版嵁琛ㄤ腑鏌ユ壘鐨鏁板锛 Lookup_array琛ㄧず鍙兘鍖呭惈鎵瑕佹煡鎵剧殑鏁板肩殑杩炵画鍗曞厓鏍煎尯鍩燂紱 Match_type琛ㄧず鏌ユ壘鏂瑰紡鐨勫硷紙-1銆0鎴1锛夈4銆佸湪鍑芥暟鐨...
绛旓細姝ゆ椂鍦ㄨ繖涓垪鐨勬暟鎹笂闈㈠氨鐪嬪埌鏈変釜涓嬫媺鎸夐挳锛岀偣鍑诲氨鍙互瀵硅繖鍒楁暟鎹繘琛岀瓫閫変簡銆Excel涓鍙互鍒╃敤Vlookup鍑芥暟鍦ㄤ竴鍒椾腑鏌ユ壘鎸囧畾鍊锛岀劧鍚庤緭鍑轰粬鍚庨潰鐨勯偅涓鍒椼excel鍐呮煡鎵惧寘鍚壒瀹氬瓧绗︾殑鍒骞惰繑鍥炵浉搴鐨勬暟鍊肩殑鍏蜂綋鎿嶄綔鏂规硶濡備笅锛氱畝鍗曠殑涓句釜渚嬪瓙锛屼緥瀛愮畝鍗曚絾寰堝舰璞★紝鑳藉浜嗚В鍑芥暟鐨勭敤娉曞氨鍙互浜嗐
