{"id":7229,"date":"2024-12-21T17:08:30","date_gmt":"2024-12-21T17:08:30","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc\/"},"modified":"2024-12-21T17:08:30","modified_gmt":"2024-12-21T17:08:30","slug":"ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc\/","title":{"rendered":"H\u00e0m VLOOKUP b\u1ecb l\u1ed7i NA, l\u1ed7i NAME, l\u1ed7i VALUE v\u00e0 c\u00e1ch kh\u1eafc ph\u1ee5c"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em>Sau khi \u0111\u1ecdc b\u00e0i <strong>c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m VLOOKUP chi ti\u1ebft <\/strong>b\u1ea1n b\u1eaft tay v\u00e0o \u1ee9ng d\u1ee5ng \u0111\u1ec3 gi\u1ea3i quy\u1ebft c\u00e1c b\u00e0i to\u00e1n trong th\u1ef1c t\u1ebf, v\u00e0 t\u1ea5t nhi\u00ean, t\u1eeb l\u00fd thuy\u1ebft t\u1edbi th\u1ef1c t\u1ebf s\u1ebd c\u00f3 nhi\u1ec1u kho\u1ea3ng tr\u1ed1ng, m\u1ed9t trong nh\u1eefng kho\u1ea3ng tr\u1ed1ng ch\u00fang ta s\u1ebd \u0111i l\u1ea5p \u0111\u1ea7y trong b\u00e0i vi\u1ebft n\u00e0y l\u00e0 <a href=\"http:\/\/blog.ezworkapp.com\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc.html\"><strong>H\u00e0m VLOOKUP b\u1ecb l\u1ed7i<\/strong><\/a> th\u00ec ph\u1ea3i gi\u1ea3i quy\u1ebft nh\u01b0 th\u1ebf n\u00e0o.\u00a0 C\u00e1c l\u1ed7i g\u1eb7p ph\u1ea3i c\u1ee7a <a href=\"http:\/\/blog.ezworkapp.com\/ham-vlookup-trong-excel-huong-dan-su-dung-chi-tiet-va-co-vi-du-cu-the.html\"><strong>h\u00e0m\u00a0 VLOOKUP<\/strong><\/a> l\u00e0 l\u1ed7i NA khi s\u1eed d\u1ee5ng VLOOKUP, l\u1ed7i NAME khi s\u1eed d\u1ee5ng VLOOKUP v\u00e0 l\u1ed7i VALUE khi s\u1eed d\u1ee5ng VLOOKUP.<\/em><\/p>\n<p>Tr\u01b0\u1edbc khi t\u00ecm hi\u1ec3u k\u0129 h\u01a1n v\u1ec1 c\u00e1ch gi\u1ea3i quy\u1ebft c\u00e1c l\u1ed7i c\u01a1 b\u1ea3n trong qu\u00e1 tr\u00ecnh s\u1eed d\u1ee5ng VLOOKUP, ch\u00fang ta s\u1ebd nh\u1eafc l\u1ea1i c\u00fa ph\u00e1p h\u00e0m VLOOKUP nh\u01b0 sau:<\/p>\n<p><code class=\"excel-formula\">=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])<\/code><\/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-6766f60dbdfc1\" 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\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc.html#Loi_NA_khi_su_dung_VLOOKUP\" title=\"L\u1ed7i NA khi s\u1eed d\u1ee5ng VLOOKUP\">L\u1ed7i NA khi s\u1eed d\u1ee5ng VLOOKUP<\/a>\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\">\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc.html#1_Sai_chinh_ta_khi_viet_gia_tri_can_tim_kiem\" title=\"1. Sai ch\u00ednh t\u1ea3 khi vi\u1ebft gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm\">1. Sai ch\u00ednh t\u1ea3 khi vi\u1ebft gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc.html#2_Loi_NA_khi_su_dung_VLOOKUP_tim_kiem_gan_dung\" title=\"2. L\u1ed7i #N\/A khi s\u1eed d\u1ee5ng VLOOKUP t\u00ecm ki\u1ebfm g\u1ea7n \u0111\u00fang\">2. L\u1ed7i #N\/A khi s\u1eed d\u1ee5ng VLOOKUP t\u00ecm ki\u1ebfm g\u1ea7n \u0111\u00fang<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc.html#3_Loi_NA_khi_gia_tri_can_tim_khong_co_mat_trong_bang_tra_cuu\" title=\"3. L\u1ed7i #N\/A khi gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm kh\u00f4ng c\u00f3 m\u1eb7t trong b\u1ea3ng tra c\u1ee9u\">3. L\u1ed7i #N\/A khi gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm kh\u00f4ng c\u00f3 m\u1eb7t trong b\u1ea3ng tra c\u1ee9u<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc.html#4_Cot_chua_gia_tri_tra_cuu_khong_nam_ngoai_cung_ben_trai_trong_table_array\" title=\"4. C\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb tra c\u1ee9u kh\u00f4ng n\u1eb1m ngo\u00e0i c\u00f9ng b\u00ean tr\u00e1i trong table_array\">4. C\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb tra c\u1ee9u kh\u00f4ng n\u1eb1m ngo\u00e0i c\u00f9ng b\u00ean tr\u00e1i trong table_array<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc.html#5_Du_lieu_so_nhung_dang_duoc_dinh_dang_la_text\" title=\"5. D\u1eef li\u1ec7u s\u1ed1, nh\u01b0ng \u0111ang \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng l\u00e0 text\">5. D\u1eef li\u1ec7u s\u1ed1, nh\u01b0ng \u0111ang \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng l\u00e0 text<\/a><\/li>\n<\/ul>\n<\/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-7\" href=\"https:\/\/blog.hocexcel.online\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc.html#Loi_VALUE_khi_su_dung_VLOOKUP\" title=\"L\u1ed7i VALUE khi s\u1eed d\u1ee5ng VLOOKUP\">L\u1ed7i VALUE khi s\u1eed d\u1ee5ng VLOOKUP<\/a>\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\">\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/blog.hocexcel.online\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc.html#1_Gia_tri_can_tim_kiem_qua_dai\" title=\"1. Gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm qu\u00e1 d\u00e0i\">1. Gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm qu\u00e1 d\u00e0i<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/blog.hocexcel.online\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc.html#2_Gia_tri_cua_tham_so_col_index_num_nho_hon_1\" title=\"2. Gi\u00e1 tr\u1ecb c\u1ee7a tham s\u1ed1 col_index_num nh\u1ecf h\u01a1n 1\">2. Gi\u00e1 tr\u1ecb c\u1ee7a tham s\u1ed1 col_index_num nh\u1ecf h\u01a1n 1<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/blog.hocexcel.online\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc.html#3_Van_de_duong_dan_khi_bang_tra_cuu_nam_trong_1_workbook_khac\" title=\"3. V\u1ea5n \u0111\u1ec1 \u0111\u01b0\u1eddng d\u1eabn khi b\u1ea3ng tra c\u1ee9u n\u1eb1m trong 1 workbook kh\u00e1c\">3. V\u1ea5n \u0111\u1ec1 \u0111\u01b0\u1eddng d\u1eabn khi b\u1ea3ng tra c\u1ee9u n\u1eb1m trong 1 workbook kh\u00e1c<\/a><\/li>\n<\/ul>\n<\/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-11\" href=\"https:\/\/blog.hocexcel.online\/ham-vlookup-bi-loi-na-loi-name-loi-value-va-cach-khac-phuc.html#Loi_NAME_khi_su_dung_VLOOKUP\" title=\"L\u1ed7i #NAME? khi s\u1eed d\u1ee5ng VLOOKUP\">L\u1ed7i #NAME? khi s\u1eed d\u1ee5ng VLOOKUP<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Loi_NA_khi_su_dung_VLOOKUP\"><\/span>L\u1ed7i NA khi s\u1eed d\u1ee5ng VLOOKUP<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Khi s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c VLOOKUP, <strong>l\u1ed7i #N\/A\u00a0<\/strong>ngh\u0129a l\u00e0 gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm \u2013\u00a0<em>lookup_value<\/em> kh\u00f4ng th\u1ec3 \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y (Not Available). C\u00f3 m\u1ed9t s\u1ed1 l\u00fd do sau \u0111\u00e2y cho l\u1ed7i n\u00e0y:<\/p>\n<h4><span class=\"ez-toc-section\" id=\"1_Sai_chinh_ta_khi_viet_gia_tri_can_tim_kiem\"><\/span>1. Sai ch\u00ednh t\u1ea3 khi vi\u1ebft gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Vi\u1ec7c \u0111\u1ea7u ti\u00ean ch\u00fang ta c\u1ea7n xem khi g\u1eb7p l\u1ed7i NA khi s\u1eed d\u1ee5ng VLOOKUP l\u00e0 ki\u1ec3m tra l\u1ea1i l\u1ed7i ch\u00ednh t\u1ea3 c\u1ee7a gi\u00e1 tr\u1ecb c\u1ea7n tra c\u1ee9u.<\/p>\n<p>M\u1ed9t tr\u01b0\u1eddng h\u1ee3p ph\u1ed5 bi\u1ebfn m\u00e0 nhi\u1ec1u b\u1ea1n g\u1eb7p ph\u1ea3i, \u0111\u00f3 l\u00e0 gi\u00e1 tr\u1ecb c\u1ea7n tra c\u1ee9u th\u1eeba d\u1ea5u c\u00e1ch \u1edf cu\u1ed1i, \u0111\u1ed1i v\u1edbi tr\u01b0\u1eddng h\u1ee3p n\u00e0y, b\u1ea1n kh\u00f4ng th\u1ec3 ki\u1ec3m tra b\u1eb1ng m\u1eaft th\u01b0\u1eddng. \u0110\u1ec3 ph\u00e1t hi\u1ec7n l\u1ed7i th\u1eeba d\u1ea5u c\u00e1ch, k\u00fd t\u1ef1 kho\u1ea3ng tr\u1eafng trong m\u1ed9t \u00f4, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m LEN.<\/p>\n<p>V\u00ed d\u1ee5:<\/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-653.gif\" title=\"ex101_92019-nho-653\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<figure id=\"attachment_31528\" aria-describedby=\"caption-attachment-31528\" style=\"width: 256px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2020\/02\/cach-xu-ly-loi-na-vlookup-01.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-31528\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cach-xu-ly-loi-na-vlookup-01-1.png\" alt=\"X\u1eed l\u00fd l\u1ed7i NA khi s\u1eed d\u1ee5ng VLOOKUP\" width=\"256\" height=\"143\" title=\"cach-xu-ly-loi-na-vlookup-01-1\"><\/a><figcaption id=\"caption-attachment-31528\" class=\"wp-caption-text\">D\u00f9ng h\u00e0m LEN ph\u00e1t hi\u1ec7n kho\u1ea3ng tr\u1eafng trong gi\u00e1 tr\u1ecb tra c\u1ee9u<\/figcaption><\/figure>\n<p>Trong v\u00ed d\u1ee5 tr\u00ean, n\u1ebfu nh\u00ecn b\u1eb1ng m\u1eaft th\u01b0\u1eddng th\u00ec gi\u00e1 tr\u1ecb c\u1ee7a A2 v\u00e0 A3 \u0111\u1ec1u nh\u01b0 nhau. Nh\u01b0ng s\u1ed1 k\u00fd t\u1ef1 c\u1ee7a A2 l\u00e0 5 v\u00e0 c\u1ee7a A3 l\u00e0 6. \u0110\u1ec3 kh\u1eafc ph\u1ee5c v\u1ea5n \u0111\u1ec1 n\u00e0y, c\u00e1ch \u0111\u01a1n gi\u1ea3n nh\u1ea5t b\u1ea1n c\u00f3 th\u1ec3 tham kh\u1ea3o l\u00e0 d\u00f9ng th\u00eam h\u00e0m TRIM \u0111\u1ec3 lo\u1ea1i b\u1ecf kho\u1ea3ng tr\u1eafng trong gi\u00e1 tr\u1ecb c\u1ea7n tra c\u1ee9u. C\u00f4ng th\u1ee9c v\u00ed d\u1ee5 nh\u01b0 sau:<\/p>\n<p><code class=\"excel-formula\">=VLOOKUP(TRIM(lookup_value), table_array, col_index_num, [range_lookup])<\/code><\/p>\n<h4><span class=\"ez-toc-section\" id=\"2_Loi_NA_khi_su_dung_VLOOKUP_tim_kiem_gan_dung\"><\/span>2. L\u1ed7i #N\/A khi s\u1eed d\u1ee5ng VLOOKUP t\u00ecm ki\u1ebfm g\u1ea7n \u0111\u00fang<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>N\u1ebfu b\u1ea1n s\u1eed d\u1ee5ng VLOOKUP v\u1edbi tham s\u1ed1 tra c\u1ee9u l\u00e0 g\u1ea7n \u0111\u00fang ([range_lookup] l\u00e0 TRUE ho\u1eb7c b\u1ecf qua), th\u00ec vi\u1ec7c g\u1eb7p l\u1ed7i #N\/A c\u00f3 th\u1ec3 do m\u1ed9t trong 2 l\u00fd do sau \u0111\u00e2y<\/p>\n<ul style=\"list-style-type: square\">\n<li>Gi\u00e1 tr\u1ecb c\u1ea7n tra c\u1ee9u\u00a0<em>\u2013 lookup_value\u00a0<\/em>nh\u1ecf h\u01a1n gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t trong v\u00f9ng tra c\u1ee9u nh\u01b0 trong v\u00ed d\u1ee5 sau \u0111\u00e2y,m\u1ee9c doanh thu l\u00e0 95,nh\u1ecf h\u01a1n s\u1ed1 nh\u1ecf nh\u1ea5t trong c\u1ed9t ch\u1ee9a d\u1eef li\u1ec7u tra c\u1ee9u l\u00e0 100.\n<p><figure id=\"attachment_31530\" aria-describedby=\"caption-attachment-31530\" style=\"width: 212px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2020\/02\/xu-ly-loi-na-trong-vlookup.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-31530\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/xu-ly-loi-na-trong-vlookup-1.png\" alt=\"X\u1eed l\u00fd l\u1ed7i NA trong VLOOKUP\" width=\"212\" height=\"262\" title=\"xu-ly-loi-na-trong-vlookup-1\"><\/a><figcaption id=\"caption-attachment-31530\" class=\"wp-caption-text\">X\u1eed l\u00fd l\u1ed7i NA trong VLOOKUP<\/figcaption><\/figure>\n<\/p>\n<\/li>\n<li>C\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm kh\u00f4ng \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp theo th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n: c\u1ed9t doanh thu kh\u00f4ng \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp theo th\u1ee9 t\u1ef1 t\u1eeb nh\u1ecf t\u1edbi l\u1edbn.\u00a0 C\u00e1ch kh\u1eafc ph\u1ee5c l\u00e0 b\u1ea1n c\u1ea7n s\u1eafp x\u1ebfp c\u1ed9t doanh thu l\u1ea1i cho \u0111\u00fang th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n.\n<p><figure id=\"attachment_31532\" aria-describedby=\"caption-attachment-31532\" style=\"width: 221px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2020\/02\/xu-ly-loi-NA-trong-cong-thuc-vlookup.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-31532\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/xu-ly-loi-na-trong-cong-thuc-vlookup-1.png\" alt=\"X\u1eed l\u00fd l\u1ed7i NA trong vlookup\" width=\"221\" height=\"274\" title=\"xu-ly-loi-na-trong-cong-thuc-vlookup-1\"><\/a><figcaption id=\"caption-attachment-31532\" class=\"wp-caption-text\">X\u1eed l\u00fd l\u1ed7i NA trong vlookup<\/figcaption><\/figure>\n<\/p>\n<\/li>\n<\/ul>\n<h4><span class=\"ez-toc-section\" id=\"3_Loi_NA_khi_gia_tri_can_tim_khong_co_mat_trong_bang_tra_cuu\"><\/span>3. L\u1ed7i #N\/A khi gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm kh\u00f4ng c\u00f3 m\u1eb7t trong b\u1ea3ng tra c\u1ee9u<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>V\u1edbi tr\u01b0\u1eddng h\u1ee3p l\u1ed7i #N\/A n\u00e0y, khi b\u1ea1n s\u1eed d\u1ee5ng VLOOKUP \u0111\u1ec3 tra c\u1ee9u ch\u00ednh x\u00e1c, th\u00ec gi\u00e1 tr\u1ecb c\u1ea7n tra c\u1ee9u kh\u00f4ng c\u00f3 m\u1eb7t trong b\u1ea3ng d\u1eef li\u1ec7u c\u0169ng c\u00f3 th\u1ec3 l\u00e0 l\u00fd do g\u00e2y ra l\u1ed7i #N\/A trong c\u00f4ng th\u1ee9c VLOOKUP.<\/p>\n<h4><span class=\"ez-toc-section\" id=\"4_Cot_chua_gia_tri_tra_cuu_khong_nam_ngoai_cung_ben_trai_trong_table_array\"><\/span>4. C\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb tra c\u1ee9u kh\u00f4ng n\u1eb1m ngo\u00e0i c\u00f9ng b\u00ean tr\u00e1i trong table_array<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>V\u1ea5n \u0111\u1ec1 n\u00e0y c\u0169ng th\u01b0\u1eddng x\u1ea3y ra khi b\u1ea1n kh\u00f4ng th\u1ec3 thay \u0111\u1ed5i v\u1ecb tr\u00ed c\u00e1c c\u1ed9t trong b\u1ea3ng d\u1eef li\u1ec7u, c\u1ed9t ch\u1ee9a d\u1eef li\u1ec7u tra c\u1ee9u kh\u00f4ng n\u1eb1m \u1edf v\u1ecb tr\u00ed ngo\u00e0i c\u00f9ng b\u00ean tr\u00e1i c\u1ee7a v\u00f9ng table_array. \u0110\u1ec3 kh\u1eafc ph\u1ee5c v\u1ea5n \u0111\u1ec1 n\u00e0y, c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 <a href=\"http:\/\/blog.ezworkapp.com\/vi-sao-dung-index-va-match-tot-hon-dung-vlookup-trong-excel.html\">s\u1eed d\u1ee5ng h\u00e0m Index k\u1ebft h\u1ee3p h\u00e0m Match trong Excel<\/a><\/p>\n<h4><span class=\"ez-toc-section\" id=\"5_Du_lieu_so_nhung_dang_duoc_dinh_dang_la_text\"><\/span>5. D\u1eef li\u1ec7u s\u1ed1, nh\u01b0ng \u0111ang \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng l\u00e0 text<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>M\u1ed9t nguy\u00ean nh\u00e2n n\u1eefa c\u00f3 th\u1ec3 g\u00e2y ra l\u1ed7i #N\/A khi s\u1eed d\u1ee5ng VLOOKUP \u0111\u00f3 l\u00e0 vi\u1ec7c d\u1eef li\u1ec7u s\u1ed1 nh\u01b0ng \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng d\u01b0\u1edbi d\u1ea1ng text. \u0110\u1ec3 kh\u1eafc ph\u1ee5c v\u1ea5n \u0111\u1ec1 n\u00e0y, b\u1ea1n c\u1ea7n chuy\u1ec3n nh\u1eefng \u00f4 d\u1eef li\u1ec7u \u0111ang \u1edf d\u1ea1ng text, \u0111ang \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng l\u00e0 text sang d\u1ea1ng s\u1ed1 b\u1eb1ng c\u00e1ch nh\u00e2n to\u00e0n b\u1ed9 nh\u1eefng gi\u00e1 tr\u1ecb ho\u1eb7c c\u1ed9t d\u1eef li\u1ec7u \u0111\u00f3 v\u1edbi s\u1ed1 1 nh\u01b0 h\u00ecnh minh ho\u1ea1:<\/p>\n<ul style=\"list-style-type: square\">\n<li>Ghi s\u1ed1 1 ra 1 \u00f4 b\u1ea5t k\u00ec, trong \u1ea3nh minh ho\u1ea1 l\u00e0 \u00f4 C1, b\u1ea5m Ctrl + C \u0111\u1ec3 copy<\/li>\n<li>Ch\u1ecdn A1:A4 r\u1ed3i b\u1ea5m chu\u1ed9t ph\u1ea3i, ch\u1ecdn\u00a0<em>Paste Special \u2026<\/em><\/li>\n<li>Trong h\u1ed9p tho\u1ea1i\u00a0<em>Paste Special\u00a0<\/em>ch\u1ecdn\u00a0<em>Multiply\u00a0<\/em>r\u1ed3i b\u1ea5m OK<\/li>\n<\/ul>\n<figure id=\"attachment_31536\" aria-describedby=\"caption-attachment-31536\" style=\"width: 334px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2020\/02\/xu-ly-loi-NA-trong-cong-thuc-vlookup-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-31536\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/xu-ly-loi-na-trong-cong-thuc-vlookup-1-1.png\" alt=\"X\u1eed l\u00fd l\u1ed7i NA trong VLOOKUP\" width=\"334\" height=\"324\" title=\"xu-ly-loi-na-trong-cong-thuc-vlookup-1-1\"><\/a><figcaption id=\"caption-attachment-31536\" class=\"wp-caption-text\">X\u1eed l\u00fd l\u1ed7i NA trong VLOOKUP<\/figcaption><\/figure>\n<h2><span class=\"ez-toc-section\" id=\"Loi_VALUE_khi_su_dung_VLOOKUP\"><\/span>L\u1ed7i VALUE khi s\u1eed d\u1ee5ng VLOOKUP<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>B\u00ecnh th\u01b0\u1eddng th\u00ec Microsoft Excel tr\u1ea3 v\u1ec1 l\u1ed7i #VALUE! khi d\u1eef li\u1ec7u trong c\u00f4ng th\u1ee9c \u1edf sai \u0111\u1ecbnh d\u1ea1ng m\u00e0 c\u00f4ng th\u1ee9c Excel y\u00eau c\u1ea7u. V\u1edbi c\u00f4ng th\u1ee9c VLOOKUP th\u00ec l\u1ed7i #VALUE! c\u00f3 kh\u1ea3 n\u0103ng do 3 nguy\u00ean nh\u00e2n:<\/p>\n<h4><span class=\"ez-toc-section\" id=\"1_Gia_tri_can_tim_kiem_qua_dai\"><\/span>1. Gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm qu\u00e1 d\u00e0i<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>L\u1ed7i #VALUE! c\u00f3 th\u1ec3 x\u1ea3y ra khi gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm \u2013\u00a0<em>lookup_value<\/em> qu\u00e1 d\u00e0i trong c\u00f4ng th\u1ee9c VLOOKUP, gi\u1ea3i ph\u00e1p cho tr\u01b0\u1eddng h\u1ee3p n\u00e0y l\u00e0 ch\u00fang ta s\u1ebd k\u1ebft h\u1ee3p h\u00e0m Index v\u00e0 Match nh\u01b0 trong \u1ea3nh minh ho\u1ea1 d\u01b0\u1edbi \u0111\u00e2y<\/p>\n<figure id=\"attachment_31537\" aria-describedby=\"caption-attachment-31537\" style=\"width: 2026px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2020\/02\/xu-ly-loi-value-trong-vlookup.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-31537\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/xu-ly-loi-value-trong-vlookup.png\" alt=\"X\u1eed l\u00fd l\u1ed7i VALUE trong c\u00f4ng th\u1ee9c VLOOKUP\" width=\"2026\" height=\"544\" title=\"xu-ly-loi-value-trong-vlookup\"><\/a><figcaption id=\"caption-attachment-31537\" class=\"wp-caption-text\">X\u1eed l\u00fd l\u1ed7i VALUE trong c\u00f4ng th\u1ee9c VLOOKUP<\/figcaption><\/figure>\n<h4><span class=\"ez-toc-section\" id=\"2_Gia_tri_cua_tham_so_col_index_num_nho_hon_1\"><\/span>2. Gi\u00e1 tr\u1ecb c\u1ee7a tham s\u1ed1 col_index_num nh\u1ecf h\u01a1n 1<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Th\u01b0\u1eddng th\u00ec ch\u00fang ta s\u1ebd kh\u00f4ng nh\u1ea7m l\u1eabn trong vi\u1ec7c \u0111\u1ec3 gi\u00e1 tr\u1ecb c\u1ee7a tham s\u1ed1\u00a0<em>col_index_num\u00a0<\/em>nh\u1ecf h\u01a1n 1, tuy nhi\u00ean, n\u1ebfu trong tr\u01b0\u1eddng h\u1ee3p gi\u00e1 tr\u1ecb c\u1ee7a tham s\u1ed1 n\u00e0y \u0111\u01b0\u1ee3c t\u00ednh to\u00e1n d\u1ef1a tr\u00ean c\u00f4ng th\u1ee9c, v\u00e0 c\u00f3 kh\u1ea3 n\u0103ng c\u00f4ng th\u1ee9c n\u00e0y tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb nh\u1ecf h\u01a1n 1 th\u00ec k\u1ebft qu\u1ea3 h\u00e0m VLOOKUP s\u1ebd l\u00e0 #VALUE!. Nh\u01b0 v\u1eady khi g\u1eb7p l\u1ed7i #VALUE! trong c\u00f4ng th\u1ee9c VLOOKUP, b\u1ea1n n\u00ean ki\u1ec3m tra l\u1ea1i tham s\u1ed1 col_index_num n\u00e0y \u0111\u1ec3 \u0111\u1ea3m b\u1ea3o gi\u00e1 tr\u1ecb c\u1ee7a n\u00f3 l\u1edbn h\u01a1n 1.<\/p>\n<h4><span class=\"ez-toc-section\" id=\"3_Van_de_duong_dan_khi_bang_tra_cuu_nam_trong_1_workbook_khac\"><\/span>3. V\u1ea5n \u0111\u1ec1 \u0111\u01b0\u1eddng d\u1eabn khi b\u1ea3ng tra c\u1ee9u n\u1eb1m trong 1 workbook kh\u00e1c<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Khi tra c\u1ee9u d\u1eef li\u1ec7u trong 1 workbook kh\u00e1c, ch\u00fang ta s\u1ebd c\u00f3 c\u00f4ng th\u1ee9c VLOOKUP nh\u01b0 sau<\/p>\n<p><code class=\"excel-formula\">=VLOOKUP(lookup_value, '[ten workbook]ten sheet'!table_array, col_index_num, [range_lookup])<\/code><\/p>\n<p>Trong tr\u01b0\u1eddng h\u1ee3p b\u1ea1n th\u1ef1c hi\u1ec7n tra c\u1ee9u VLOOKUP t\u1eeb 1 workbook kh\u00e1c nh\u01b0 mi\u00eau t\u1ea3 trong <a href=\"http:\/\/blog.ezworkapp.com\/ham-vlookup-trong-excel-huong-dan-chi-tiet-cho-nguoi-moi-bat-dau.html#Su_dung_ham_VLOOKUP_tra_cuu_o_1_file_Excel_khac\">b\u00e0i vi\u1ebft n\u00e0y<\/a> th\u00ec c\u00f4ng th\u1ee9c VLOOKUP \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng l\u00e0:<\/p>\n<p><code class=\"excel-formula\">=VLOOKUP(A2,'[wb kh\u00e1c.xlsx]V\u00ed d\u1ee5 02'!$A$2:$C$7,2,FALSE)<\/code><\/p>\n<p>Trong tr\u01b0\u1eddng h\u1ee3p tham s\u1ed1 table_array b\u1ecb thi\u1ebfu ho\u1eb7c sai b\u1ea5t c\u1ee9 th\u00e0nh ph\u1ea7n n\u00e0o, th\u00ec c\u00f4ng th\u1ee9c VLOOKUP c\u0169ng s\u1ebd tr\u1ea3 v\u1ec1 l\u1ed7i #VALUE!. Nh\u01b0 v\u1eady, ch\u00fang ta c\u1ea7n \u0111\u1eb7t ra c\u00e2u h\u1ecfi khi s\u1eed d\u1ee5ng VLOOKUP m\u00e0 g\u1eb7p l\u1ed7i #VALUE! l\u00e0, li\u1ec7u ch\u00fang ta c\u00f3 tra c\u1ee9u d\u1eef li\u1ec7u t\u1eeb 1 workbook kh\u00e1c kh\u00f4ng, v\u00e0 \u0111\u01b0\u1eddng d\u1eabn c\u00f3 \u0111\u00fang hay ch\u01b0a, b\u1edfi v\u00ec \u0111\u01b0\u1eddng d\u1eabn c\u00f3 th\u1ec3 s\u1ebd thay \u0111\u1ed5i do ch\u00fang ta \u0111\u1ed5i t\u00ean th\u01b0 m\u1ee5c, ho\u1eb7c copy \/ di chuy\u1ec3n file t\u1eeb th\u01b0 m\u1ee5c n\u00e0y qua th\u01b0 m\u1ee5c kh\u00e1c.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Loi_NAME_khi_su_dung_VLOOKUP\"><\/span>L\u1ed7i #NAME? khi s\u1eed d\u1ee5ng VLOOKUP<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Ai c\u0169ng c\u00f3 th\u1ec3 vi\u1ebft sai ch\u00ednh t\u1ea3, v\u1ea5n \u0111\u1ec1 l\u00e0 sai \u1edf \u0111\u00e2u v\u00e0 khi n\u00e0o, trong tr\u01b0\u1eddng h\u1ee3p b\u1ea1n \u0111ang s\u1eed d\u1ee5ng h\u00e0m VLOOKUP m\u00e0 g\u1eb7p ph\u1ea3i l\u1ed7i #NAME?, th\u00ec nhi\u1ec1u kh\u1ea3 n\u0103ng ch\u1eef VLOOKUP b\u1ea1n \u0111\u00e3 vi\u1ebft sai ho\u1eb7c 1 h\u00e0m n\u00e0o \u0111\u00f3 \u0111ang \u0111\u01b0\u1ee3c l\u1ed3ng gh\u00e9p trong h\u00e0m VLOOKUP b\u1ecb vi\u1ebft sai. Gi\u1ea3i ph\u00e1p trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y r\u00f5 r\u00e0ng l\u00e0: ki\u1ec3m tra l\u1ea1i.<\/p>\n<figure id=\"attachment_31539\" aria-describedby=\"caption-attachment-31539\" style=\"width: 422px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2020\/02\/xu-ly-loi-name-trong-vlookup.png\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-31539\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/xu-ly-loi-name-trong-vlookup.png\" alt=\"X\u1eed l\u00fd l\u1ed7i Name trong c\u00f4ng th\u1ee9c VLOOKUP\" width=\"422\" height=\"125\" title=\"xu-ly-loi-name-trong-vlookup\"><\/a><figcaption id=\"caption-attachment-31539\" class=\"wp-caption-text\">X\u1eed l\u00fd l\u1ed7i Name trong c\u00f4ng th\u1ee9c VLOOKUP<\/figcaption><\/figure>\n<p>L\u1ed7i #NAME? trong c\u00f4ng th\u1ee9c VLOOKUP: do vi\u1ebft sai ch\u00ednh t\u1ea3 t\u00ean h\u00e0m trimm, t\u00ean \u0111\u00fang l\u00e0 TRIM<\/p>\n<p>Nh\u01b0 v\u1eady, qua b\u00e0i vi\u1ebft n\u00e0y, c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 bi\u1ebft c\u00e1ch x\u1eed l\u00fd m\u1ed9t s\u1ed1 l\u1ed7i c\u01a1 b\u1ea3n khi s\u1eed d\u1ee5ng h\u00e0m VLOOKUP trong Excel. C\u1ea3m \u01a1n c\u00e1c b\u1ea1n \u0111\u00e3 \u0111\u1ecdc t\u1edbi \u0111\u00e2y, ch\u00fac th\u00e0nh c\u00f4ng.<\/p>\n<p>B\u1ea1n \u0111ang c\u1ea7n m\u1ed9t n\u01a1i \u0111\u1ec3 b\u1eaft \u0111\u1ea7u h\u1ecdc Excel t\u1eeb c\u01a1 b\u1ea3n? H\u00e3y tham kh\u1ea3o t\u1eeb <a href=\"http:\/\/blog.ezworkapp.com\"><strong>H\u1ecdc Excel Online<\/strong><\/a>\u00a0 <a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\">kho\u00e1 h\u1ecdc Excel t\u1eeb c\u01a1 b\u1ea3n \u0111\u1ebfn chuy\u00ean gia d\u00e0nh cho ng\u01b0\u1eddi \u0111i l\u00e0m.<\/a><\/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-653.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":7230,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-7229","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/7229","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=7229"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/7229\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/7230"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=7229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=7229"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=7229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}