{"id":2523,"date":"2024-12-02T08:01:24","date_gmt":"2024-12-02T08:01:24","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong\/"},"modified":"2024-12-02T08:01:24","modified_gmt":"2024-12-02T08:01:24","slug":"cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong\/","title":{"rendered":"C\u00c1CH KH\u1eaeC PH\u1ee4C V\u00c0 S\u1eecA L\u1ed6I H\u00c0M VLOOKUP KH\u00d4NG HO\u1ea0T \u0110\u1ed8NG"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em><span style=\"font-weight: 400\">B\u00e0i vi\u1ebft d\u01b0\u1edbi \u0111\u00e2y, H\u1ecdc Excel Online s\u1ebd h\u01b0\u1edbng d\u1eabn c\u00e1c b\u1ea1n x\u1eed l\u00fd s\u1ef1 c\u1ed1 h\u00e0m Vlookup kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng trong Excel 2016, 2013, 2010, 2007 v\u00e0 2003. Ngo\u00e0i ra, <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong> <\/a>s\u1ebd h\u01b0\u1edbng d\u1eabn c\u00e1ch kh\u1eafc ph\u1ee5c <a href=\"http:\/\/blog.ezworkapp.com\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html\"><strong>c\u00e1c l\u1ed7i th\u01b0\u1eddng g\u1eb7p h\u00e0m Vlookup<\/strong><\/a> nh\u01b0 #N\/A, #NAME V\u00c0 #VALUE v\u00e0 c\u00e1c h\u1ea1n ch\u1ebfc\u1ee7a Vlookup.<\/span><\/em><\/p>\n<p><span style=\"font-weight: 400\">Trong c\u00e1c b\u00e0i vi\u1ebft tr\u01b0\u1edbc ch\u00fang ta \u0111\u00e3 t\u00ecm hi\u1ec3u nh\u1eefng kh\u00eda c\u1ea1nh kh\u00e1c nhau c\u1ee7a h\u00e0m Vlookup trong Excel. Tuy nhi\u00ean h\u00e0m Vlookup v\u1eabn c\u00f2n r\u1ea5t nhi\u1ec1u s\u1ef1 ph\u1ee9c t\u1ea1p v\u00e0 h\u1ea1n ch\u1ebf n\u00ean c\u00f3 r\u1ea5t nhi\u1ec1u v\u1ea5n \u0111\u1ec1 v\u1ec1 l\u1ed7i kh\u00e1c nhau.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong b\u00e0i vi\u1ebft n\u00e0y s\u1ebd gi\u1ea3i th\u00edch \u0111\u01a1n gi\u1ea3n v\u1ec1 c\u00e1c th\u00f4ng b\u00e1o l\u1ed7i #N\/A, #NAME V\u00c0 #VALUE c\u1ee7a VLOOKUP c\u0169ng nh\u01b0 c\u00e1c gi\u1ea3i ph\u00e1p \u0111\u1ec3 x\u1eed l\u00fd.\u00a0<\/span><\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_61 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Xem nhanh<\/p>\n<p><label for=\"ez-toc-cssicon-toggle-item-674d69536c08e\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><\/span><\/span><\/label><\/p>\n<nav>\n<ul class=\"ez-toc-list ez-toc-list-level-1 \">\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#Sua_loi_VLOOKUP_NA_trong_Excel\" title=\"S\u1eeda l\u1ed7i VLOOKUP #N\/A trong Excel\">S\u1eeda l\u1ed7i VLOOKUP #N\/A trong Excel<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#1_Loi_danh_may_hoac_sap_xep_sai_gia_tri_tra_cuu\" title=\"1. L\u1ed7i \u0111\u00e1nh m\u00e1y ho\u1eb7c s\u1eafp x\u1ebfp sai gi\u00e1 tr\u1ecb tra c\u1ee9u\">1. L\u1ed7i \u0111\u00e1nh m\u00e1y ho\u1eb7c s\u1eafp x\u1ebfp sai gi\u00e1 tr\u1ecb tra c\u1ee9u<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#2_Loi_NA_trong_tim_kiem_gan_dung_bang_ham_VLOOKUP\" title=\"2. L\u1ed7i # N\/A trong t\u00ecm ki\u1ebfm g\u1ea7n \u0111\u00fang b\u1eb1ng h\u00e0m VLOOKUP\">2. L\u1ed7i # N\/A trong t\u00ecm ki\u1ebfm g\u1ea7n \u0111\u00fang b\u1eb1ng h\u00e0m VLOOKUP<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#3_Loi_NA_trong_tim_kiem_chinh_xac_ham_VLOOKUP\" title=\"3. L\u1ed7i # N\/A trong t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c h\u00e0m VLOOKUP\">3. L\u1ed7i # N\/A trong t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c h\u00e0m VLOOKUP<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#4_Cot_tra_cuu_khong_phai_la_cot_ben_trai_cua_bang_du_lieu\" title=\"4. C\u1ed9t tra c\u1ee9u kh\u00f4ng ph\u1ea3i l\u00e0 c\u1ed9t b\u00ean tr\u00e1i c\u1ee7a b\u1ea3ng d\u1eef li\u1ec7u\">4. C\u1ed9t tra c\u1ee9u kh\u00f4ng ph\u1ea3i l\u00e0 c\u1ed9t b\u00ean tr\u00e1i c\u1ee7a b\u1ea3ng d\u1eef li\u1ec7u<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#5_So_duoc_dinh_dang_van_ban\" title=\"5. S\u1ed1 \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng v\u0103n b\u1ea3n\">5. S\u1ed1 \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng v\u0103n b\u1ea3n<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#6_Cac_khoang_trong_lon_hoac_dau_dau_dong\" title=\"6. C\u00e1c kho\u1ea3ng tr\u1ed1ng l\u1edbn ho\u1eb7c d\u1ea5u \u0111\u1ea7u d\u00f2ng\">6. C\u00e1c kho\u1ea3ng tr\u1ed1ng l\u1edbn ho\u1eb7c d\u1ea5u \u0111\u1ea7u d\u00f2ng<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#Loi_VALUE_trong_cong_thuc_VLOOKUP\" title=\"L\u1ed7i #VALUE trong c\u00f4ng th\u1ee9c VLOOKUP\">L\u1ed7i #VALUE trong c\u00f4ng th\u1ee9c VLOOKUP<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#Gia_tri_tra_cuu_vuot_qua_255_ky_tu\" title=\"Gi\u00e1 tr\u1ecb tra c\u1ee9u v\u01b0\u1ee3t qu\u00e1 255 k\u00fd t\u1ef1\">Gi\u00e1 tr\u1ecb tra c\u1ee9u v\u01b0\u1ee3t qu\u00e1 255 k\u00fd t\u1ef1<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#Khong_cung_cap_duong_dan_day_du_den_bang_tinh_tra_cuu\" title=\"Kh\u00f4ng cung c\u1ea5p \u0111\u01b0\u1eddng d\u1eabn \u0111\u1ea7y \u0111\u1ee7 \u0111\u1ebfn b\u1ea3ng t\u00ednh tra c\u1ee9u\">Kh\u00f4ng cung c\u1ea5p \u0111\u01b0\u1eddng d\u1eabn \u0111\u1ea7y \u0111\u1ee7 \u0111\u1ebfn b\u1ea3ng t\u00ednh tra c\u1ee9u<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#Doi_so_col_index_num_nho_hon_1\" title=\"\u0110\u1ed1i s\u1ed1 col_index_num nh\u1ecf h\u01a1n 1\">\u0110\u1ed1i s\u1ed1 col_index_num nh\u1ecf h\u01a1n 1<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#Loi_VLOOKUP_NAME\" title=\"L\u1ed7i VLOOKUP #NAME\">L\u1ed7i VLOOKUP #NAME<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#VLOOKUP_khong_hoat_dong_han_che_su_co_va_giai_phap\" title=\"VLOOKUP kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng (h\u1ea1n ch\u1ebf, s\u1ef1 c\u1ed1 v\u00e0 gi\u1ea3i ph\u00e1p)\">VLOOKUP kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng (h\u1ea1n ch\u1ebf, s\u1ef1 c\u1ed1 v\u00e0 gi\u1ea3i ph\u00e1p)<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#Vlookup_khong_phan_biet_chu_hoa_chu_thuong\" title=\"Vlookup kh\u00f4ng ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng\">Vlookup kh\u00f4ng ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#VLOOKUP_tra_ve_gia_tri_tim_thay_dau_tien\" title=\"VLOOKUP tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u00ecm th\u1ea5y \u0111\u1ea7u ti\u00ean\">VLOOKUP tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u00ecm th\u1ea5y \u0111\u1ea7u ti\u00ean<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#Mot_cot_moi_duoc_chen_vao_hoac_go_bo_khoi_hang\" title=\"M\u1ed9t c\u1ed9t m\u1edbi \u0111\u01b0\u1ee3c ch\u00e8n v\u00e0o ho\u1eb7c g\u1ee1 b\u1ecf kh\u1ecfi h\u00e0ng\">M\u1ed9t c\u1ed9t m\u1edbi \u0111\u01b0\u1ee3c ch\u00e8n v\u00e0o ho\u1eb7c g\u1ee1 b\u1ecf kh\u1ecfi h\u00e0ng<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-17\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#Cac_o_tham_chieu_thay_doi_khi_sao_chep_cong_thuc_den_o_khac\" title=\"C\u00e1c \u00f4 tham chi\u1ebfu thay \u0111\u1ed5i khi sao ch\u00e9p c\u00f4ng th\u1ee9c \u0111\u1ebfn \u00f4 kh\u00e1c\">C\u00e1c \u00f4 tham chi\u1ebfu thay \u0111\u1ed5i khi sao ch\u00e9p c\u00f4ng th\u1ee9c \u0111\u1ebfn \u00f4 kh\u00e1c<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-18\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#Ham_VLOOKUP_voi_ham_IFERROR_ISERROR\" title=\"H\u00e0m VLOOKUP v\u1edbi h\u00e0m IFERROR \/ ISERROR\">H\u00e0m VLOOKUP v\u1edbi h\u00e0m IFERROR \/ ISERROR<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-19\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#Su_dung_VLOOKUP_voi_IFERROR\" title=\"S\u1eed d\u1ee5ng VLOOKUP v\u1edbi IFERROR\">S\u1eed d\u1ee5ng VLOOKUP v\u1edbi IFERROR<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-20\" href=\"https:\/\/blog.hocexcel.online\/cach-khac-phuc-va-sua-loi-ham-vlookup-khong-hoat-dong.html#Su_dung_ham_VLOOKUP_voi_ISERROR\" title=\"S\u1eed d\u1ee5ng h\u00e0m VLOOKUP v\u1edbi ISERROR\">S\u1eed d\u1ee5ng h\u00e0m VLOOKUP v\u1edbi ISERROR<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Sua_loi_VLOOKUP_NA_trong_Excel\"><\/span><b>S\u1eeda l\u1ed7i VLOOKUP #N\/A trong Excel<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Trong c\u00f4ng th\u1ee9c h\u00e0m Vlookup, th\u00f4ng b\u00e1o l\u1ed7i #N\/A (ngh\u0129a l\u00e0 Not Available \u2013 \u201ckh\u00f4ng c\u00f3 s\u1eb5n\u201d) \u0111\u01b0\u1ee3c hi\u1ec3n th\u1ecb khi Excel kh\u00f4ng th\u1ec3 t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb tra c\u1ee9u. C\u00f3 th\u1ec3 xu\u1ea5t ph\u00e1t b\u1edfi m\u1ed9t s\u1ed1 l\u00fd do sau:<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"1_Loi_danh_may_hoac_sap_xep_sai_gia_tri_tra_cuu\"><\/span><b>1. L\u1ed7i \u0111\u00e1nh m\u00e1y ho\u1eb7c s\u1eafp x\u1ebfp sai gi\u00e1 tr\u1ecb tra c\u1ee9u<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">L\u1ed7i x\u1ebfp nh\u1ea7m ch\u1eef th\u01b0\u1eddng x\u1ea3y ra khi b\u1ea1n l\u00e0m vi\u1ec7c v\u1edbi l\u01b0\u1ee3ng d\u1eef li\u1ec7u l\u1edbn bao g\u1ed3m h\u00e0ng ng\u00e0n d\u00f2ng ho\u1eb7c khi gi\u00e1 tr\u1ecb tra c\u1ee9u \u0111\u01b0\u1ee3c g\u00f5 tr\u1ef1c ti\u1ebfp v\u00e0o c\u00f4ng th\u1ee9c.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"2_Loi_NA_trong_tim_kiem_gan_dung_bang_ham_VLOOKUP\"><\/span><b>2. L\u1ed7i # N\/A trong t\u00ecm ki\u1ebfm g\u1ea7n \u0111\u00fang b\u1eb1ng h\u00e0m VLOOKUP<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n \u0111ang s\u1eed d\u1ee5ng m\u1ed9t c\u00f4ng th\u1ee9c v\u1edbi \u0111\u1ed1i s\u00e1nh g\u1ea7n \u0111\u00fang (\u0111\u1ed1i s\u1ed1 range_lookup l\u00e0 TRUE ho\u1eb7c b\u1ecf qua), c\u00f4ng th\u1ee9c Vlookup c\u1ee7a b\u1ea1n c\u00f3 th\u1ec3 tr\u1ea3 v\u1ec1 l\u1ed7i # N\/A trong hai tr\u01b0\u1eddng h\u1ee3p:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">N\u1ebfu gi\u00e1 tr\u1ecb tra c\u1ee9u nh\u1ecf h\u01a1n gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t trong m\u1ea3ng tra c\u1ee9u.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">N\u1ebfu c\u1ed9t tra c\u1ee9u kh\u00f4ng \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp theo th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n.<\/span><\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"3_Loi_NA_trong_tim_kiem_chinh_xac_ham_VLOOKUP\"><\/span>3. L\u1ed7i <b># N\/A trong t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c h\u00e0m VLOOKUP<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n \u0111ang t\u00ecm ki\u1ebfm v\u1edbi \u0111\u1ed1i s\u00e1nh ch\u00ednh x\u00e1c (\u0111\u1ed1i s\u1ed1 range_lookup \u0111\u01b0\u1ee3c \u0111\u1eb7t th\u00e0nh FALSE) v\u00e0 kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c, th\u00ec l\u1ed7i #N\/A c\u0169ng \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1.<\/span><\/p>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-195.gif\" title=\"ex101_92019-nho-195\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"4_Cot_tra_cuu_khong_phai_la_cot_ben_trai_cua_bang_du_lieu\"><\/span><b>4. C\u1ed9t tra c\u1ee9u kh\u00f4ng ph\u1ea3i l\u00e0 c\u1ed9t b\u00ean tr\u00e1i c\u1ee7a b\u1ea3ng d\u1eef li\u1ec7u<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">C\u00f3 th\u1ec3 b\u1ea1n \u0111\u00e3 bi\u1ebft, m\u1ed9t trong nh\u1eefng h\u1ea1n ch\u1ebf \u0111\u00e1ng k\u1ec3 nh\u1ea5t c\u1ee7a h\u00e0m VLOOKUP l\u00e0 n\u00f3 kh\u00f4ng th\u1ec3 d\u00f2 t\u00ecm ph\u00eda b\u00ean tr\u00e1i c\u1ed9t tra c\u1ee9u, do \u0111\u00f3 c\u1ed9t tra c\u1ee9u lu\u00f4n lu\u00f4n l\u00e0 c\u1ed9t b\u00ean tr\u00e1i ngo\u00e0i c\u00f9ng trong b\u1ea3ng tra c\u1ee9u. Trong th\u1ef1c t\u1ebf, ch\u00fang ta th\u01b0\u1eddng qu\u00ean \u0111i \u0111i\u1ec1u n\u00e0y v\u00e0 d\u1eabn \u0111\u1ebfn vi\u1ec7c h\u00e0m VLOOKUP kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng v\u00ec l\u1ed7i N\/A.<\/span><\/p>\n<figure style=\"width: 488px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"S\u1eeda l\u1ed7i VLOOKUP #N\/A trong Excel\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-na-error-1.png\" alt=\"The lookup column should be the leftmost column of the table array, otherwise VLOOKUP displays the N\/A error.\" width=\"488\" height=\"357\"><figcaption class=\"wp-caption-text\">S\u1eeda l\u1ed7i VLOOKUP #N\/A trong Excel<\/figcaption><\/figure>\n<p><b>Gi\u1ea3i ph\u00e1p<\/b><span style=\"font-weight: 400\">: N\u1ebfu kh\u00f4ng th\u1ec3 t\u00e1i c\u1ea5u tr\u00fac d\u1eef li\u1ec7u \u0111\u1ec3 c\u1ed9t tra c\u1ee9u l\u00e0 c\u1ed9t b\u00ean tr\u00e1i, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p c\u00e1c h\u00e0m INDEX v\u00e0 MATCH \u0111\u1ec3 thay th\u1ebf linh ho\u1ea1t cho h\u00e0m VLOOKUP. B\u1ea1n s\u1ebd t\u00ecm th\u1ea5y th\u00f4ng tin chi ti\u1ebft v\u00e0 v\u00ed d\u1ee5 v\u1ec1 c\u00f4ng th\u1ee9c trong h\u01b0\u1edbng d\u1eabn \u2013 C\u00e1ch d\u00f9ng h\u00e0m <\/span><b>INDEX \/ MATCH<\/b><span style=\"font-weight: 400\"> \u0111\u1ec3 tra c\u1ee9u c\u00e1c gi\u00e1 tr\u1ecb b\u00ean tr\u00e1i.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"5_So_duoc_dinh_dang_van_ban\"><\/span><b>5. S\u1ed1 \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng v\u0103n b\u1ea3n<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">M\u1ed9t l\u1ed7i N\/A kh\u00e1c c\u1ee7a h\u00e0m VLOOKUP l\u00e0 do c\u00e1c con s\u1ed1 \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng d\u01b0\u1edbi d\u1ea1ng v\u0103n b\u1ea3n, b\u1ea3ng ch\u00ednh ho\u1eb7c b\u1ea3ng tra c\u1ee9u.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110i\u1ec1u n\u00e0y th\u01b0\u1eddng x\u1ea3y ra khi b\u1ea1n nh\u1eadp d\u1eef li\u1ec7u t\u1eeb m\u1ed9t c\u01a1 s\u1edf d\u1eef li\u1ec7u b\u00ean ngo\u00e0i ho\u1eb7c do b\u1ea1n \u0111\u00e3 g\u00f5 d\u1ea5u nh\u00e1y \u0111\u01a1n tr\u01b0\u1edbc s\u1ed1.<\/span><\/p>\n<figure style=\"width: 518px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"S\u1ed1 \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng v\u0103n b\u1ea3n\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/number-formatted-as-text-1.png\" alt=\"The indications of numbers being formatted as text in Excel\" width=\"518\" height=\"184\"><figcaption class=\"wp-caption-text\">S\u1ed1 \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng v\u0103n b\u1ea3n<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">C\u00e1c s\u1ed1 c\u0169ng c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c l\u01b0u tr\u1eef \u1edf \u0111\u1ecbnh d\u1ea1ng Chung. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, c\u00f3 m\u1ed9t d\u1ea5u hi\u1ec7u \u0111\u00e1ng ch\u00fa \u00fd \u2013 c\u00e1c s\u1ed1 \u0111\u01b0\u1ee3c c\u0103n ch\u1ec9nh \u1edf ph\u00eda b\u00ean tr\u00e1i c\u1ee7a \u00f4.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Gi\u1ea3i ph\u00e1p: N\u1ebfu \u0111\u00e2y ch\u1ec9 l\u00e0 m\u1ed9t s\u1ed1 duy nh\u1ea5t, ch\u1ec9 c\u1ea7n nh\u1ea5p v\u00e0o bi\u1ec3u t\u01b0\u1ee3ng l\u1ed7i v\u00e0 ch\u1ecdn \u201d Convert to number \u201d t\u1eeb danh s\u00e1ch t\u00f9y ch\u1ecdn.<\/span><\/p>\n<figure style=\"width: 326px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"S\u1ed1 \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng v\u0103n b\u1ea3n 1\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/convert-number-to-text-1.png\" alt=\"Converting numbers formatted as text to the normal numbers format.\" width=\"326\" height=\"254\"><figcaption class=\"wp-caption-text\">S\u1ed1 \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng v\u0103n b\u1ea3n 1<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">N\u1ebfu nhi\u1ec1u s\u1ed1 b\u1ecb \u1ea3nh h\u01b0\u1edfng, h\u00e3y ch\u1ecdn t\u1ea5t c\u1ea3, nh\u1ea5p chu\u1ed9t ph\u1ea3i v\u00e0o v\u00f9ng ch\u1ecdn, sau \u0111\u00f3 ch\u1ecdn Format Cells&gt;Number tab&gt;Number v\u00e0 nh\u1ea5p OK.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"6_Cac_khoang_trong_lon_hoac_dau_dau_dong\"><\/span><b>6. C\u00e1c kho\u1ea3ng tr\u1ed1ng l\u1edbn ho\u1eb7c d\u1ea5u \u0111\u1ea7u d\u00f2ng<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 nguy\u00ean nh\u00e2n kh\u00f3 ph\u00e1t hi\u1ec7n\u00a0 nh\u1ea5t v\u00ec m\u1eaft th\u01b0\u1eddng kh\u00f3 c\u00f3 th\u1ec3 ph\u00e1t hi\u1ec7n ra nh\u1eefng kho\u1ea3ng tr\u1ed1ng th\u1eeba \u0111\u00f3, \u0111\u1eb7c bi\u1ec7t l\u00e0 khi l\u00e0m vi\u1ec7c v\u1edbi c\u00e1c b\u1ea3ng l\u1edbn n\u01a1i h\u1ea7u h\u1ebft c\u00e1c m\u1ee5c nh\u1eadp n\u1eb1m d\u01b0\u1edbi cu\u1ed9n.<\/span><\/p>\n<p><b><i>Tr\u01b0\u1eddng h\u1ee3p 1:<\/i><\/b><span style=\"font-weight: 400\"> Kho\u1ea3ng tr\u1eafng trong c\u1ed9t d\u00f2 t\u00ecm (v\u1edbi c\u00f4ng th\u1ee9c VLOOKUP)<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu c\u00e1c kho\u1ea3ng tr\u1ed1ng d\u01b0 th\u1eeba xu\u1ea5t hi\u1ec7n trong b\u1ea3ng ch\u00ednh c\u1ee7a b\u1ea1n, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c Vlookup b\u1eb1ng c\u00e1ch l\u1ed3ng \u0111\u1ed1i s\u1ed1 lookup_value v\u1edbi h\u00e0m TRIM:<\/span><\/p>\n<p><strong>=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)<\/strong><\/p>\n<figure style=\"width: 641px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"L\u1ed7i kho\u1ea3ng tr\u1eafng trong c\u1ed9t d\u00f2 t\u00ecm\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-na-error-spaces.png\" alt=\"If extra spaces occur in the main table, wrapping the lookup value with the TRIM function.\" width=\"641\" height=\"200\"><figcaption class=\"wp-caption-text\">L\u1ed7i kho\u1ea3ng tr\u1eafng trong c\u1ed9t d\u00f2 t\u00ecm<\/figcaption><\/figure>\n<p><b><i>Tr\u01b0\u1eddng h\u1ee3p 2:<\/i><\/b><span style=\"font-weight: 400\"> Kho\u1ea3ng tr\u1eafng trong c\u1ed9t tra c\u1ee9u<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu c\u00e1c kho\u1ea3ng tr\u1eafng th\u1eeba xu\u1ea5t hi\u1ec7n trong c\u1ed9t tra c\u1ee9u, kh\u00f4ng c\u00f3 c\u00e1ch n\u00e0o \u0111\u1ec3 tr\u00e1nh l\u1ed7i VLOOKUP #N\/A. Thay v\u00ec h\u00e0m VLOOKUP, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c m\u1ea3ng v\u1edbi s\u1ef1 k\u1ebft h\u1ee3p c\u1ee7a c\u00e1c h\u00e0m INDEX \/ MATCH v\u00e0 TRIM:<\/span><\/p>\n<p><strong>=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))<\/strong><\/p>\n<p><span style=\"font-weight: 400\">V\u00ec \u0111\u00e2y l\u00e0 m\u1ed9t c\u00f4ng th\u1ee9c m\u1ea3ng, \u0111\u1eebng qu\u00ean nh\u1ea5n Ctrl + Shift + Enter:<\/span><\/p>\n<figure style=\"width: 630px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"L\u1ed7i kho\u1ea3ng tr\u1eafng trong c\u1ed9t tra c\u1ee9u\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/lookup-column-extra-space.png\" alt=\"If extra spaces occur in the lookup table, use INDEX \/ MATCH \/ TRIM instead of VLOOKUP.\" width=\"630\" height=\"285\"><figcaption class=\"wp-caption-text\">L\u1ed7i kho\u1ea3ng tr\u1eafng trong c\u1ed9t tra c\u1ee9u<\/figcaption><\/figure>\n<h2><span class=\"ez-toc-section\" id=\"Loi_VALUE_trong_cong_thuc_VLOOKUP\"><\/span><b>L\u1ed7i #VALUE trong c\u00f4ng th\u1ee9c VLOOKUP<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Microsoft Excel hi\u1ec3n th\u1ecb l\u1ed7i #VALUE! l\u1ed7i n\u1ebfu m\u1ed9t gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c trong c\u00f4ng th\u1ee9c kh\u00f4ng \u0111\u00fang. \u0110\u1ed1i v\u1edbi h\u00e0m VLOOKUP, l\u1ed7i #VALUE th\u01b0\u1eddng m\u1eafc ph\u1ea3i do 2 l\u00fd do sau \u0111\u00e2y.<\/span><\/p>\n<ul>\n<li>\n<h3><span class=\"ez-toc-section\" id=\"Gia_tri_tra_cuu_vuot_qua_255_ky_tu\"><\/span><b>Gi\u00e1 tr\u1ecb tra c\u1ee9u v\u01b0\u1ee3t qu\u00e1 255 k\u00fd t\u1ef1<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">N\u00ean nh\u1edb VLOOKUP kh\u00f4ng th\u1ec3 tra c\u1ee9u c\u00e1c gi\u00e1 tr\u1ecb ch\u1ee9a 256 k\u00fd t\u1ef1 tr\u1edf l\u00ean. N\u1ebfu c\u00e1c gi\u00e1 tr\u1ecb tra c\u1ee9u c\u1ee7a b\u1ea1n v\u01b0\u1ee3t qu\u00e1 gi\u1edbi h\u1ea1n n\u00e0y th\u00ec s\u1ebd xu\u1ea5t hi\u1ec7n l\u1ed7i VALUE:<\/span><\/p>\n<figure style=\"width: 635px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"L\u1ed7i #VALUE trong c\u00f4ng th\u1ee9c VLOOKUP\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-not-working-255-chars.png\" alt=\"The VALUE error in VLOOKUP when a lookup value exceeds 255 characters.\" width=\"635\" height=\"206\"><figcaption class=\"wp-caption-text\">L\u1ed7i #VALUE trong c\u00f4ng th\u1ee9c VLOOKUP<\/figcaption><\/figure>\n<p><b>Gi\u1ea3i ph\u00e1p :<\/b><span style=\"font-weight: 400\"> S\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c INDEX \/ MATCH t\u01b0\u01a1ng t\u1ef1 \u0111\u1ec3 thay th\u1ebf. Trong v\u00ed d\u1ee5 tr\u00ean, h\u00e0m INDEX \/ MATCH sau \u0111\u00e2y ho\u1ea1t \u0111\u1ed9ng ho\u00e0n h\u1ea3o:<\/span><\/p>\n<p><strong>=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7= F$2,0),0))<\/strong><\/p>\n<figure style=\"width: 613px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"Gi\u1ea3i ph\u00e1p l\u1ed7i #VALUE trong c\u00f4ng th\u1ee9c VLOOKUP\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/index-match-255-chars.png\" alt=\"If a look up value contains 256 characters or more, use INDEX \/ MATCH instead of VLOOKUP\" width=\"613\" height=\"207\"><figcaption class=\"wp-caption-text\">Gi\u1ea3i ph\u00e1p l\u1ed7i #VALUE trong c\u00f4ng th\u1ee9c VLOOKUP<\/figcaption><\/figure>\n<p>\u00a0<\/p>\n<ul>\n<li>\n<h3><span class=\"ez-toc-section\" id=\"Khong_cung_cap_duong_dan_day_du_den_bang_tinh_tra_cuu\"><\/span><b>Kh\u00f4ng cung c\u1ea5p \u0111\u01b0\u1eddng d\u1eabn \u0111\u1ea7y \u0111\u1ee7 \u0111\u1ebfn b\u1ea3ng t\u00ednh tra c\u1ee9u<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n l\u1ea5y d\u1eef li\u1ec7u t\u1eeb m\u1ed9t b\u1ea3ng t\u00ednh kh\u00e1c, b\u1ea1n ph\u1ea3i bao g\u1ed3m \u0111\u01b0\u1eddng d\u1eabn \u0111\u1ea7y \u0111\u1ee7 \u0111\u1ebfn t\u1ec7p \u0111\u00f3. Ch\u00ednh x\u00e1c h\u01a1n, b\u1ea1n ph\u1ea3i k\u00e8m theo t\u00ean c\u1ee7a t\u1ec7p (bao g\u1ed3m c\u1ea3 ph\u1ea7n m\u1edf r\u1ed9ng) trong d\u1ea5u ngo\u1eb7c vu\u00f4ng [], v\u00e0 sau \u0111\u00f3 ch\u1ec9 \u0111\u1ecbnh t\u00ean c\u1ee7a trang t\u00ednh v\u00e0 d\u1ea5u ch\u1ea5m than. Ngo\u00e0i ra, b\u1ea1n c\u0169ng c\u1ea7n ph\u1ea3i \u0111\u1eb7t ch\u00fang trong d\u1ea5u nh\u00e1y \u0111\u01a1n trong tr\u01b0\u1eddng h\u1ee3p t\u1ec7p ho\u1eb7c t\u00ean b\u1ea3ng t\u00ednh c\u00f3 ch\u1ee9a kho\u1ea3ng tr\u1ed1ng.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 c\u1ea5u tr\u00fac c\u1ee7a c\u00f4ng th\u1ee9c ho\u00e0n ch\u1ec9nh \u0111\u1ec3 th\u1ef1c hi\u1ec7n vlookup t\u1eeb m\u1ed9t s\u1ed5 l\u00e0m vi\u1ec7c kh\u00e1c:<\/span><\/p>\n<p><strong>=VLOOKUP(lookup_value, \u2018[workbook name]sheet name\u2019!table_array, col_index_num, FALSE)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">M\u1ed9t c\u00f4ng th\u1ee9c th\u1ef1c s\u1ef1 c\u00f3 th\u1ec3 tr\u00f4ng nh\u01b0 sau:<\/span><\/p>\n<p><strong>=VLOOKUP($A$2,'[New Prices.xls]Sheet1\u2032!$B:$D,3,FALSE)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c tr\u00ean s\u1ebd t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb \u00f4 A2 trong c\u1ed9t B c\u1ee7a Sheet1 t\u1ec7p \u201cNew Prices\u201d v\u00e0 tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p t\u1ea1i c\u1ed9t D.<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea5t k\u1ef3 ph\u1ea7n t\u1eed n\u00e0o c\u1ee7a \u0111\u01b0\u1eddng d\u1eabn b\u1ecb thi\u1ebfu, c\u00f4ng th\u1ee9c VLOOKUP c\u1ee7a b\u1ea1n s\u1ebd kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng v\u00e0 tr\u1ea3 l\u1ea1i l\u1ed7i #VALUE (tr\u1eeb khi b\u1ea3ng t\u00ednh tra c\u1ee9u hi\u1ec7n \u0111ang m\u1edf).<\/span><\/p>\n<ul>\n<li>\n<h3><span class=\"ez-toc-section\" id=\"Doi_so_col_index_num_nho_hon_1\"><\/span><b>\u0110\u1ed1i s\u1ed1 col_index_num nh\u1ecf h\u01a1n 1<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Kh\u00f3 c\u00f3 tr\u01b0\u1eddng h\u1ee3p m\u00e0 nh\u1eadp m\u1ed9t s\u1ed1 nh\u1ecf h\u01a1n \u201c1\u201d \u0111\u1ec3 x\u00e1c \u0111\u1ecbnh c\u1ed9t gi\u00e1 tr\u1ecb tr\u1ea3 v\u1ec1. Tuy nhi\u00ean, n\u00f3 c\u00f3 th\u1ec3 x\u1ea3y ra n\u1ebfu \u0111\u1ed1i s\u1ed1 n\u00e0y \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1 b\u1edfi m\u1ed9t s\u1ed1 h\u00e0m Excel kh\u00e1c \u0111\u01b0\u1ee3c l\u1ed3ng trong c\u00f4ng th\u1ee9c Vlookup c\u1ee7a b\u1ea1n.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ec v\u1eady, n\u1ebfu \u0111\u1ed1i s\u1ed1 col_index_num nh\u1ecf h\u01a1n 1, c\u00f4ng th\u1ee9c Vlookup c\u1ee7a b\u1ea1n s\u1ebd tr\u1ea3 v\u1ec1 l\u1ed7i #VALUE!.<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu \u0111\u1ed1i s\u1ed1 col_index_num l\u1edbn h\u01a1n s\u1ed1 c\u1ed9t trong m\u1ea3ng b\u1ea3ng \u0111\u01b0\u1ee3c ch\u1ec9 \u0111\u1ecbnh, c\u00f4ng th\u1ee9c Vlookup tr\u1ea3 v\u1ec1 l\u1ed7i #REF! l\u1ed7i.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Loi_VLOOKUP_NAME\"><\/span><b>L\u1ed7i VLOOKUP #NAME<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 tr\u01b0\u1eddng h\u1ee3p d\u1ec5 nh\u1ea5t \u2013 l\u1ed7i NAME xu\u1ea5t hi\u1ec7n n\u1ebfu b\u1ea1n v\u00f4 t\u00ecnh vi\u1ebft sai ch\u00ednh t\u1ea3 t\u00ean c\u1ee7a h\u00e0m.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Gi\u1ea3i ph\u00e1p l\u00e0 r\u00f5 r\u00e0ng \u2013 h\u00e3y ki\u1ec3m tra ch\u00ednh t\u1ea3:)<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"VLOOKUP_khong_hoat_dong_han_che_su_co_va_giai_phap\"><\/span><b>VLOOKUP kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng (h\u1ea1n ch\u1ebf, s\u1ef1 c\u1ed1 v\u00e0 gi\u1ea3i ph\u00e1p)<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Ngo\u00e0i vi\u1ec7c c\u00f3 m\u1ed9t c\u00fa ph\u00e1p kh\u00e1 ph\u1ee9c t\u1ea1p, h\u00e0m\u00a0 VLOOKUP c\u00f3 nhi\u1ec1u h\u1ea1n ch\u1ebf \u0111\u00e1ng k\u1ec3 so v\u1edbi b\u1ea5t k\u1ef3 h\u00e0m Excel kh\u00e1c. Do nh\u1eefng h\u1ea1n ch\u1ebf n\u00e0y, c\u00f4ng th\u1ee9c Vlookup th\u01b0\u1eddng mang l\u1ea1i k\u1ebft qu\u1ea3 kh\u00f4ng nh\u01b0 mong \u0111\u1ee3i. D\u01b0\u1edbi \u0111\u00e2y b\u1ea1n s\u1ebd t\u00ecm th\u1ea5y c\u00e1c gi\u1ea3i ph\u00e1p cho m\u1ed9t v\u00e0i t\u00ecnh hu\u1ed1ng ph\u1ed5 bi\u1ebfn khi VLOOKUP kh\u00f4ng th\u00e0nh c\u00f4ng.<\/span><\/p>\n<ul>\n<li>\n<h3><span class=\"ez-toc-section\" id=\"Vlookup_khong_phan_biet_chu_hoa_chu_thuong\"><\/span><b>Vlookup kh\u00f4ng ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">H\u00e0m VLOOKUP kh\u00f4ng ph\u00e2n bi\u1ec7t ch\u1eef hoa v\u00e0 ch\u1eef th\u01b0\u1eddng. V\u00ec v\u1eady, n\u1ebfu b\u1ea3ng c\u1ee7a b\u1ea1n c\u00f3 nhi\u1ec1u m\u1ee5c t\u01b0\u01a1ng t\u1ef1 ch\u1ec9 kh\u00e1c nhau ch\u1eef hoa, ch\u1eef th\u01b0\u1eddng, c\u00f4ng th\u1ee9c Vlookup s\u1ebd tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y \u0111\u1ea7u ti\u00ean b\u1ea5t k\u1ec3 tr\u01b0\u1eddng h\u1ee3p n\u00e0o.<\/span><\/p>\n<p><b>Gi\u1ea3i ph\u00e1p :<\/b><span style=\"font-weight: 400\"> S\u1eed d\u1ee5ng m\u1ed9t h\u00e0m Excel kh\u00e1c c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n tra c\u1ee9u theo chi\u1ec1u d\u1ecdc (LOOKUP, SUMPRODUCT, INDEX \/ MATCH) k\u1ebft h\u1ee3p v\u1edbi h\u00e0m EXACT \u0111\u1ec3 c\u00f3 th\u1ec3 h\u1ed7 tr\u1ee3 b\u1ea1n tra c\u1ee9u. B\u1ea1n c\u00f3 th\u1ec3 t\u00ecm th\u1ea5y c\u00e1c gi\u1ea3i th\u00edch chi ti\u1ebft v\u00e0 v\u00ed d\u1ee5 v\u1ec1 c\u00f4ng th\u1ee9c trong h\u01b0\u1edbng d\u1eabn n\u00e0y \u2013 4 c\u00e1ch \u0111\u1ec3 t\u1ea1o vlookup ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng trong Excel .<\/span><\/p>\n<ul>\n<li>\n<h3><span class=\"ez-toc-section\" id=\"VLOOKUP_tra_ve_gia_tri_tim_thay_dau_tien\"><\/span><b>VLOOKUP tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u00ecm th\u1ea5y \u0111\u1ea7u ti\u00ean<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Nh\u01b0 b\u1ea1n \u0111\u00e3 bi\u1ebft, h\u00e0m VLOOKUP tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb \u0111\u1ea7u ti\u00ean n\u00f3 t\u00ecm th\u1ea5y trong c\u1ed9t tr\u1ea3 v\u1ec1 ph\u00f9 h\u1ee3p v\u1edbi gi\u00e1 tr\u1ecb tra c\u1ee9u. Tuy nhi\u00ean, b\u1ea1n c\u00f3 th\u1ec3 bu\u1ed9c n\u00f3 tr\u1ea3 v\u1ec1 c\u00e1i th\u1ee9 2, 3, 4 ho\u1eb7c b\u1ea5t k\u1ef3 tr\u01b0\u1eddng h\u1ee3p n\u00e0o m\u00e0 b\u1ea1n mu\u1ed1n. N\u1ebfu b\u1ea1n c\u1ea7n ph\u1ea3i nh\u1eadn \u0111\u01b0\u1ee3c t\u1ea5t c\u1ea3 c\u00e1c gi\u00e1 tr\u1ecb, b\u1ea1n s\u1ebd ph\u1ea3i s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p c\u00e1c h\u00e0m INDEX, SMALL v\u00e0 ROW.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Gi\u1ea3i ph\u00e1p: C\u00e1c v\u00ed d\u1ee5 c\u00f4ng th\u1ee9c c\u00f3 s\u1eb5n \u0111\u1ec3 t\u1ea3i v\u1ec1 t\u1ea1i \u0111\u00e2y:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Nh\u1eadn bi\u1ebft c\u00e1c l\u1ea7n xu\u1ea5t hi\u1ec7n th\u1ee9 2, 3, 4, v.v \u2026<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Nh\u1eadn t\u1ea5t c\u1ea3 c\u00e1c l\u1ea7n xu\u1ea5t hi\u1ec7n tr\u00f9ng l\u1eb7p c\u1ee7a gi\u00e1 tr\u1ecb tra c\u1ee9u.<\/span><\/li>\n<li>\n<h3><span class=\"ez-toc-section\" id=\"Mot_cot_moi_duoc_chen_vao_hoac_go_bo_khoi_hang\"><\/span><b>M\u1ed9t c\u1ed9t m\u1edbi \u0111\u01b0\u1ee3c ch\u00e8n v\u00e0o ho\u1eb7c g\u1ee1 b\u1ecf kh\u1ecfi h\u00e0ng<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">C\u00e1c c\u00f4ng th\u1ee9c VLOOKUP s\u1ebd kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng khi m\u1ed9t c\u1ed9t m\u1edbi b\u1ecb x\u00f3a kh\u1ecfi ho\u1eb7c \u0111\u01b0\u1ee3c th\u00eam v\u00e0o b\u1ea3ng tra c\u1ee9u. \u0110i\u1ec1u n\u00e0y x\u1ea3y ra do c\u00fa ph\u00e1p c\u1ee7a h\u00e0m VLOOKUP y\u00eau c\u1ea7u b\u1ea1n cung c\u1ea5p to\u00e0n b\u1ed9 b\u1ea3ng c\u0169ng nh\u01b0 m\u1ed9t s\u1ed1 nh\u1ea5t \u0111\u1ecbnh cho bi\u1ebft b\u1ea1n mu\u1ed1n tr\u1ea3 v\u1ec1 d\u1eef li\u1ec7u t\u1eeb c\u1ed9t n\u00e0o. \u0110\u01b0\u01a1ng nhi\u00ean, c\u1ea3 b\u1ea3ng v\u00e0 s\u1ed1 c\u1ee7a c\u1ed9t tr\u1ea3 v\u1ec1 \u0111\u1ec1u thay \u0111\u1ed5i khi b\u1ea1n x\u00f3a m\u1ed9t c\u1ed9t hi\u1ec7n c\u00f3 ho\u1eb7c ch\u00e8n m\u1ed9t c\u1ed9t m\u1edbi.<\/span><\/p>\n<p><b><i>Gi\u1ea3i ph\u00e1p:<\/i><\/b><span style=\"font-weight: 400\"> INDEX \/ MATCH \u0111\u01b0\u1ee3c d\u00f9ng \u0111\u1ec3 gi\u1ea3i quy\u1ebft v\u1ea5n \u0111\u1ec1 n\u00e0y. Trong h\u00e0m INDEX &amp; MATCH, b\u1ea1n ch\u1ec9 \u0111\u1ecbnh c\u00e1c c\u1ed9t tra c\u1ee9u v\u00e0 c\u1ed9t tr\u1ea3 k\u1ebft qu\u1ea3 ri\u00eang bi\u1ec7t, k\u1ebft qu\u1ea3 l\u00e0 b\u1ea1n c\u00f3 th\u1ec3 x\u00f3a ho\u1eb7c ch\u00e8n nhi\u1ec1u c\u1ed9t t\u00f9y \u00fd m\u00e0 kh\u00f4ng c\u1ea7n ph\u1ea3i c\u1eadp nh\u1eadt m\u1ecdi c\u00f4ng th\u1ee9c vlookup li\u00ean quan .<\/span><\/p>\n<ul>\n<li>\n<h3><span class=\"ez-toc-section\" id=\"Cac_o_tham_chieu_thay_doi_khi_sao_chep_cong_thuc_den_o_khac\"><\/span><b>C\u00e1c \u00f4 tham chi\u1ebfu thay \u0111\u1ed5i khi sao ch\u00e9p c\u00f4ng th\u1ee9c \u0111\u1ebfn \u00f4 kh\u00e1c<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<\/li>\n<\/ul>\n<p><b>Gi\u1ea3i ph\u00e1p:<\/b><span style=\"font-weight: 400\"> Lu\u00f4n s\u1eed d\u1ee5ng tham chi\u1ebfu \u00f4 tuy\u1ec7t \u0111\u1ed1i (v\u1edbi d\u1ea5u $) trong v\u00f9ng ch\u1ecdn, v\u00ed d\u1ee5: $A$2: $C$100 ho\u1eb7c $A:$C. Trong thanh c\u00f4ng th\u1ee9c, b\u1ea1n c\u00f3 th\u1ec3 nhanh ch\u00f3ng chuy\u1ec3n \u0111\u1ed5i gi\u1eefa c\u00e1c lo\u1ea1i tham chi\u1ebfu kh\u00e1c nhau b\u1eb1ng c\u00e1ch nh\u1ea5n F4.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ham_VLOOKUP_voi_ham_IFERROR_ISERROR\"><\/span><b>H\u00e0m VLOOKUP v\u1edbi h\u00e0m IFERROR \/ ISERROR<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n kh\u00f4ng mu\u1ed1n \u0111\u1ec3 ng\u01b0\u1eddi d\u00f9ng th\u1ea5y t\u1ea5t c\u1ea3 c\u00e1c th\u00f4ng b\u00e1o l\u1ed7i N\/A, VALUE ho\u1eb7c NAME, b\u1ea1n c\u00f3 th\u1ec3 tr\u1ea3 l\u1ea1i \u00f4 tr\u1ed1ng ho\u1eb7c hi\u1ec3n th\u1ecb n\u1ed9i dung c\u1ee7a ri\u00eang b\u1ea1n. B\u1ea1n c\u00f3 th\u1ec3 l\u00e0m \u0111i\u1ec1u n\u00e0y b\u1eb1ng c\u00e1ch l\u1ed3ng c\u00f4ng th\u1ee9c VLOOKUP c\u1ee7a b\u1ea1n trong h\u00e0m IFERROR trong Excel 2013, 2010 v\u00e0 2007 ho\u1eb7c h\u00e0m IF \/ ISERROR trong c\u00e1c phi\u00ean b\u1ea3n Excel tr\u01b0\u1edbc \u0111\u00f3.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Su_dung_VLOOKUP_voi_IFERROR\"><\/span><b>S\u1eed d\u1ee5ng VLOOKUP v\u1edbi IFERROR<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">C\u00fa ph\u00e1p c\u1ee7a h\u00e0m IFERROR r\u1ea5t \u0111\u01a1n gi\u1ea3n:<\/span><\/p>\n<p><strong>IFERROR (gi\u00e1 tr\u1ecb, gi\u00e1 tr\u1ecb_if_error)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">B\u1ea1n ti\u1ebfn h\u00e0nh nh\u1eadp gi\u00e1 tr\u1ecb \u0111\u1ec3 ki\u1ec3m tra l\u1ed7i trong \u0111\u1ed1i s\u1ed1 th\u1ee9 nh\u1ea5t, v\u00e0 trong \u0111\u1ed1i s\u1ed1 th\u1ee9 2 b\u1ea1n ch\u1ec9 \u0111\u1ecbnh gi\u00e1 tr\u1ecb tr\u1ea3 v\u1ec1 n\u1ebfu l\u1ed7i x\u1ea3y ra.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ed d\u1ee5, c\u00f4ng th\u1ee9c IFERROR \/ VLOOKUP tr\u1ea3 v\u1ec1 m\u1ed9t \u00f4 tr\u1ed1ng khi kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb tra c\u1ee9u:<\/span><\/p>\n<p><strong>=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),\u201d\u201d)<\/strong><\/p>\n<p><img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-iferror-1.png\" alt=\"The IFERROR \/ VLOOKUP formula returns a blank cell instead of the error message.\" title=\"vlookup-iferror-1\"><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n th\u00edch hi\u1ec3n th\u1ecb n\u1ed9i dung m\u00e0 m\u00ecnh mu\u1ed1n thay v\u00ec nh\u1eefng bi\u1ec3u hi\u1ec7n l\u1ed7i th\u00f4ng th\u01b0\u1eddng, h\u00e3y \u0111\u00e1nh ch\u00fang v\u00e0o d\u1ea5u ngo\u1eb7c k\u00e9p nh\u01b0 th\u1ebf n\u00e0y:<\/span><\/p>\n<p><strong>=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),\u201dOops, no match is found. Please try again!\u201d)<\/strong><\/p>\n<p><img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-iferror-message-1.png\" alt=\"Display your message instead of VLOOKUP error.\" title=\"vlookup-iferror-message-1\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Su_dung_ham_VLOOKUP_voi_ISERROR\"><\/span><b>S\u1eed d\u1ee5ng h\u00e0m VLOOKUP v\u1edbi ISERROR<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">H\u00e0m IFERROR c\u00f3 \u1edf phi\u00ean b\u1ea3n Excel 2007, trong c\u00e1c phi\u00ean b\u1ea3n Excel tr\u01b0\u1edbc b\u1ea1n s\u1ebd ph\u1ea3i s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p h\u00e0m IF v\u00e0 ISERROR nh\u01b0 th\u1ebf n\u00e0y:<\/span><\/p>\n<p><strong>=IF(ISERROR(VLOOKUP formula), \u201cYour message if any\u201d, VLOOKUP formula)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">V\u00ed d\u1ee5: \u0111\u00e2y l\u00e0 c\u00f4ng th\u1ee9c IF \/ ISERROR \/ VLOOKUP t\u01b0\u01a1ng t\u1ef1 v\u1edbi c\u00f4ng th\u1ee9c IFERROR \/ VLOOKUP \u1edf tr\u00ean:<\/span><\/p>\n<p><strong>=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)), \u201c\u201d, VLOOKUP($F$2,$B$2:$C$10,2,FALSE))<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Tr\u00ean \u0111\u00e2y l\u00e0 nh\u1eefng l\u1ed7i hay g\u1eb7p trong h\u00e0m Vlookup. <strong>H\u1ecdc Excel Online<\/strong> hy v\u1ecdng th\u00f4ng qua nh\u1eefng h\u01b0\u1edbng d\u1eabn tr\u00ean b\u1ea1n c\u00f3 th\u1ec3 x\u1eed l\u00fd t\u1ea5t c\u1ea3 c\u00e1c l\u1ed7i c\u00f3 th\u1ec3 x\u1ea3y ra c\u1ee7a h\u00e0m Vlookup.\u00a0<\/span><\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-195.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":2524,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-2523","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-nang-cao"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/2523","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=2523"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/2523\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/2524"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=2523"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=2523"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=2523"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}