{"id":1804,"date":"2024-12-02T06:04:00","date_gmt":"2024-12-02T06:04:00","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-xu-ly-loi-n-a-name-value-cua-ham-vlookup\/"},"modified":"2024-12-02T06:04:00","modified_gmt":"2024-12-02T06:04:00","slug":"cach-xu-ly-loi-n-a-name-value-cua-ham-vlookup","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-xu-ly-loi-n-a-name-value-cua-ham-vlookup\/","title":{"rendered":"C\u00e1ch x\u1eed l\u00fd l\u1ed7i N\/A, NAME, VALUE c\u1ee7a h\u00e0m VLOOKUP"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Trong b\u00e0i vi\u1ebft n\u00e0y, Blog <a href=\"http:\/\/blog.ezworkapp.com\">H\u1ecdc Excel Online<\/a> s\u1ebd h\u01b0\u1edbng d\u1eabn c\u00e1ch c\u00e1ch gi\u1ea3i quy\u1ebft v\u1ea5n \u0111\u1ec1 <a href=\"http:\/\/blog.ezworkapp.com\/ham-hlookup-trong-excel-va-cac-ung-dung-nang-cao-thuong-gap.html\" target=\"_blank\" rel=\"noopener noreferrer\">h\u00e0m VLOOKUP<\/a> kh\u00f4ng l\u00e0m vi\u1ec7c trong Excel 2013, 2010, 2007 v\u00e0 2003, t\u00ecm ra v\u00e0 kh\u1eafc ph\u1ee5c c\u00e1c l\u1ed7i c\u1ee7a h\u00e0m VLOOKUP.<\/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-674d4dcf22f7f\" 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-xu-ly-loi-na-name-value-cua-ham-vlookup.html#Sua_loi_NA_cua_VLOOKUP_trong_Excel\" title=\"S\u1eeda l\u1ed7i #N\/A c\u1ee7a VLOOKUP trong Excel\">S\u1eeda l\u1ed7i #N\/A c\u1ee7a VLOOKUP 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-xu-ly-loi-na-name-value-cua-ham-vlookup.html#Loi_NAME_ham_VLOOKUP\" title=\"L\u1ed7i #NAME h\u00e0m VLOOKUP \">L\u1ed7i #NAME h\u00e0m VLOOKUP <\/a>\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/cach-xu-ly-loi-na-name-value-cua-ham-vlookup.html#Dung_ham_VLOOKUP_voi_ISERROR\" title=\"D\u00f9ng h\u00e0m VLOOKUP v\u1edbi ISERROR\">D\u00f9ng h\u00e0m VLOOKUP v\u1edbi ISERROR<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Sua_loi_NA_cua_VLOOKUP_trong_Excel\"><\/span>S\u1eeda l\u1ed7i #N\/A c\u1ee7a VLOOKUP trong Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Trong c\u00f4ng th\u1ee9c Vlookup, th\u00f4ng b\u00e1o <a href=\"http:\/\/blog.ezworkapp.com\/tim-hieu-ve-loi-na-trong-excel-va-cach-khac-phuc.html\">l\u1ed7i #N\/A <\/a>(c\u00f3 ngh\u0129a l\u00e0 \u201ckh\u00f4ng kh\u1ea3 d\u1ee5ng\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 v\u00e0i l\u00fd do khi\u1ebfn \u0111i\u1ec1u \u0111\u00f3 c\u00f3 th\u1ec3 x\u1ea3y ra.<\/p>\n<p><strong>1. L\u1ed7i \u0111\u00e1nh m\u00e1y ho\u1eb7c s\u1eafp x\u1ebfp nh\u1ea7m trong gi\u00e1 tr\u1ecb tra c\u1ee9u <\/strong><\/p>\n<p>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 h\u00e0ng 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.<\/p>\n<p><strong>2. L\u1ed7i # N\/A trong t\u00ecm ki\u1ebfm g\u1ea7n \u0111\u00fang b\u1eb1ng h\u00e0m VLOOKUP<\/strong><\/p>\n<p>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:<\/p>\n<ul>\n<li>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.<\/li>\n<li>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.<\/li>\n<\/ul>\n<p>3. <strong>L\u1ed7i # N\/A trong t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c h\u00e0m VLOOKUP<\/strong><\/p>\n<div class=\"code-block code-block-2\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-53.gif\" title=\"vba101_92019-nho-53\"><br \/>\n<\/a>\n<\/div>\n<\/div>\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-129.gif\" title=\"ex101_92019-nho-129\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>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\u1eb7t th\u00e0nh FALSE) v\u00e0 kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c, c\u0169ng s\u1ebd xu\u1ea5t hi\u1ec7n l\u1ed7i # N\/A<\/p>\n<p>4. <strong>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<\/strong><\/p>\n<p>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 d\u00f2 t\u00ecm. 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.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-na-error.png\" alt=\"The lookup column should be the leftmost column of the table array, otherwise VLOOKUP displays the N\/A error.\" title=\"vlookup-na-error\"><\/p>\n<p><span lang=\"vi\"><strong>Gi\u1ea3i ph\u00e1p<\/strong>: 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 <strong>INDEX<\/strong> v\u00e0 <strong>MATCH <\/strong>\u0111\u1ec3\u00a0thay 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 INDEX \/ MATCH \u0111\u1ec3 tra c\u1ee9u c\u00e1c gi\u00e1 tr\u1ecb b\u00ean tr\u00e1i.<\/span><\/p>\n<p><strong>Xem th\u00eam<\/strong>:\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/vi-sao-dung-index-va-match-tot-hon-dung-vlookup-trong-excel.html\">V\u00ec sao d\u00f9ng INDEX v\u00e0 MATCH t\u1ed1t h\u01a1n d\u00f9ng VLOOKUP trong Excel<\/a><\/p>\n<p><span lang=\"vi\">5. <strong>S\u1ed1 \u0111\u01b0\u1ee3c \u0111\u1ecbnh d\u1ea1ng d\u01b0\u1edbi d\u1ea1ng v\u0103n b\u1ea3n<\/strong> <\/span><\/p>\n<p><span lang=\"vi\">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, trong c\u1ed9t d\u00f2 t\u00ecm ho\u1eb7c v\u00f9ng tra c\u1ee9u. <\/span><\/p>\n<p><span lang=\"vi\">\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<p><span lang=\"vi\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/number-formatted-as-text.png\" alt=\"The indications of numbers being formatted as text in Excel\" title=\"number-formatted-as-text\"><\/span><\/p>\n<p><span lang=\"vi\">C\u00e1c s\u1ed1 n\u00e0y 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 s\u1ed1 \u0111\u01b0\u1ee3c canh l\u1ec1 tr\u00e1i c\u1ee7a \u00f4.<\/span><\/p>\n<p><span lang=\"vi\"><strong>Gi\u1ea3i ph\u00e1p:<\/strong> 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 \u201c<strong>Convert To Number<\/strong>\u201d t\u1eeb danh s\u00e1ch t\u00f9y ch\u1ecdn.<\/span><\/p>\n<p><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/convert-number-to-text.png\" alt=\"Converting numbers formatted as text to the normal numbers format.\" title=\"convert-number-to-text\"><\/p>\n<p>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 <strong>Format Cells&gt;Number tab&gt;Number<\/strong> v\u00e0 nh\u1ea5p <strong>OK<\/strong>.<\/p>\n<blockquote>\n<p>Xem ngay:\u00a0H\u1ecdc Excel n\u00e2ng cao \u1edf \u0111\u00e2u<\/p>\n<\/blockquote>\n<p>6. <strong>C\u00e1c kho\u1ea3ng tr\u1ed1ng l\u1edbn ho\u1eb7c d\u1ea5u \u0111\u1ea7u d\u00f2ng<\/strong><\/p>\n<p>\u0110\u00e2y l\u00e0 nguy\u00ean nh\u00e2n r\u00f5 r\u00e0ng nh\u1ea5t c\u1ee7a l\u1ed7i Vlookup N\/A v\u00ec m\u1eaft ng\u01b0\u1eddi kh\u00f4ng th\u1ec3 nh\u00ecn th\u1ea5y nh\u1eefng kh\u00f4ng gian \u0111\u1eb7c bi\u1ec7t, nh\u1ea5t 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 n\u1eb1m d\u01b0\u1edbi thanh cu\u1ed9n.<\/p>\n<p><strong>Tr\u01b0\u1eddng h\u1ee3p 1: Kho\u1ea3ng tr\u1eafng trong c\u1ed9t d\u00f2 t\u00ecm (v\u1edbi c\u00f4ng th\u1ee9c VLOOKUP)<\/strong><\/p>\n<p>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 \u0111\u1ea3m b\u1ea3o c\u00f4ng th\u1ee9c Vlookup b\u1eb1ng c\u00e1ch g\u00f3i \u0111\u1ed1i s\u1ed1 lookup_value v\u1edbi <a href=\"http:\/\/blog.ezworkapp.com\/ham-trim-trong-excel.html\">h\u00e0m TRIM<\/a>:<\/p>\n<p><strong>= VLOOKUP (TRIM ($F2), $A$2: $C$10,3, FALSE)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-5642\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-dialog-box-launcher-1-26.png\" width=\"600\" height=\"187\" title=\"excel-dialog-box-launcher-1-26\"><\/p>\n<p><strong>Tr\u01b0\u1eddng h\u1ee3p 2: Kho\u1ea3ng tr\u1eafng trong c\u1ed9t tra c\u1ee9u<\/strong><\/p>\n<p>N\u1ebfu c\u00f3 kho\u1ea3ng tr\u1eafng xu\u1ea5t hi\u1ec7n trong c\u1ed9t tra c\u1ee9u, kh\u00f4ng c\u00f3 c\u00e1ch n\u00e0o \u0111\u1ec3 tr\u00e1nh \u0111\u01b0\u1ee3c l\u1ed7i VLOOKUP # N\/A. Thay v\u00ec d\u00f9ng h\u00e0m VLOOKUP, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng m\u1ed9t c\u00f4ng th\u1ee9c m\u1ea3ng v\u1edbi s\u1ef1 k\u1ebft h\u1ee3p c\u00e1c h\u00e0m INDEX \/ MATCH v\u00e0 TRIM:<\/p>\n<p><strong>= INDEX ($C$2: $C$10, MATCH (TRUE, TRIM ($A$2: $A$10) = TRIM ($F$2), 0))<\/strong><\/p>\n<p>V\u00ec \u0111\u00e2y l\u00e0 m\u1ed9t c\u00f4ng th\u1ee9c m\u1ea3ng, \u0111\u1eebng qu\u00ean nh\u1ea5n <strong>Ctrl + Shift + Enter<\/strong> ch\u1ee9 kh\u00f4ng ph\u1ea3i l\u00e0 ph\u00edm <strong>Enter<\/strong>\u00a0\u0111\u1ec3 ho\u00e0n th\u00e0nh n\u00f3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-5648\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-dialog-box-launcher-1-27.png\" width=\"600\" height=\"271\" title=\"excel-dialog-box-launcher-1-27\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Loi_NAME_ham_VLOOKUP\"><\/span><strong><span lang=\"vi\">L\u1ed7i #NAME h\u00e0m VLOOKUP <\/span><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span lang=\"vi\">\u0110\u00e2y l\u00e0 tr\u01b0\u1eddng h\u1ee3p d\u1ec5 x\u1ea3y ra nh\u1ea5t \u2013 <a href=\"http:\/\/blog.ezworkapp.com\/loi-value-ref-num-na-div0-name-null-trong-excel-va-cach-sua.html\">l\u1ed7i #NAME<\/a> xu\u1ea5t hi\u1ec7n n\u1ebfu b\u1ea1n v\u00f4 t\u00ecnh \u0111\u00e1nh sai t\u00ean h\u00e0m. Gi\u1ea3i ph\u00e1p hi\u1ec3n nhi\u00ean l\u00e0 \u2013 ki\u1ec3m tra ch\u00ednh t\u1ea3:)<\/span><\/p>\n<p><span lang=\"vi\"><strong>H\u00e0m VLOOKUP kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng (gi\u1edbi h\u1ea1n c\u1ee7a h\u00e0m, v\u1ea5n \u0111\u1ec1 v\u00e0 gi\u1ea3i ph\u00e1p)<\/strong> <\/span><\/p>\n<p><span lang=\"vi\">Ngo\u00e0i vi\u1ec7c c\u00f3 m\u1ed9t c\u00fa ph\u00e1p kh\u00e1 ph\u1ee9c t\u1ea1p, 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, b\u1ea1n d\u01b0\u1eddng nh\u01b0 kh\u00f3 \u0111\u1ec3 t\u1ea1o ch\u00ednh x\u00e1c c\u00f4ng th\u1ee9c Vlookup. 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<p><span lang=\"vi\"><strong>1. VLOOKUP kh\u00f4ng ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng<\/strong> <\/span><\/p>\n<p><span lang=\"vi\">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 l\u1ea1i gi\u00e1 tr\u1ecb \u0111\u1ea7u ti\u00ean \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y. <\/span><\/p>\n<p><span lang=\"vi\"><strong>Gi\u1ea3i ph\u00e1p:<\/strong> 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 ch\u1ecdn \u0111\u00fang tr\u01b0\u1eddng h\u1ee3p. B\u1ea1n c\u00f3 th\u1ec3 t\u00ecm th\u1ea5y c\u00e1c gi\u1ea3i th\u00edch chi ti\u1ebft v\u00e0 c\u00e1c v\u00ed d\u1ee5 c\u00f4ng th\u1ee9c trong h\u01b0\u1edbng d\u1eabn \u2013 <strong>4 c\u00e1ch \u0111\u1ec3 l\u00e0m m\u1ed9t vlookup ph\u00e2n bi\u1ec7t ch\u1eef th\u01b0\u1eddng v\u00e0 ch\u1eef hoa trong Excel.<\/strong><\/span><\/p>\n<blockquote>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/thu-thuat-excel\">Th\u1ee7 thu\u1eadt Excel n\u00e2ng cao<\/a> hay nh\u00e2\u0301t 2018<\/p>\n<\/blockquote>\n<p><span lang=\"vi\"><strong> 2. VLOOKUP tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u00ecm th\u1ea5y \u0111\u1ea7u ti\u00ean<\/strong> <\/span><\/p>\n<p><span lang=\"vi\">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 lang=\"vi\"><strong>Gi\u1ea3i ph\u00e1p<\/strong>: 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><span lang=\"vi\">Nh\u1eadn bi\u1ebft c\u00e1c l\u1ea7n xu\u1ea5t hi\u1ec7n th\u1ee9 2, 3, 4, v.v \u2026 <\/span><\/li>\n<li><span lang=\"vi\">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<\/ul>\n<p>3. <strong>M\u1ed9t c\u1ed9t m\u1edbi \u0111\u01b0\u1ee3c ch\u00e8n v\u00e0o ho\u1eb7c g\u1ee1 b\u1ecf kh\u1ecfi b\u1ea3ng <\/strong><\/p>\n<p>\u0110\u00e1ng ti\u1ebfc l\u00e0 c\u00e1c c\u00f4ng th\u1ee9c VLOOKUP ng\u1eebng ho\u1ea1t \u0111\u1ed9ng m\u1ed7i khi m\u1ed9t c\u1ed9t m\u1edbi b\u1ecb x\u00f3a ho\u1eb7c \u0111\u01b0\u1ee3c th\u00eam v\u00e0o b\u1ea3ng tra c\u1ee9u. \u0110i\u1ec1u n\u00e0y x\u1ea3y ra b\u1edfi v\u00ec 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 c\u1ed9t n\u00e0o b\u1ea1n mu\u1ed1n tr\u1ea3 l\u1ea1i d\u1eef li\u1ec7u. \u0110\u01b0\u01a1ng nhi\u00ean, c\u1ea3 b\u1ea3ng v\u00e0 s\u1ed1 c\u1ed9t tr\u1ea3 l\u1ea1i 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.<\/p>\n<p><strong>Gi\u1ea3i ph\u00e1p:<\/strong> INDEX \/ MATCH l\u1ea1i \u0111\u01b0\u1ee3c d\u00f9ng l\u1ea7n n\u1eefa \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 m\u1ed9t c\u00e1ch 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 nh\u01b0 b\u1ea1n mu\u1ed1n m\u00e0 kh\u00f4ng c\u1ea7n lo l\u1eafng v\u1ec1 vi\u1ec7c c\u1eadp nh\u1eadt m\u1ecdi c\u00f4ng th\u1ee9c vlookup li\u00ean quan .<\/p>\n<p>4.<strong> C\u00e1c \u00f4 tham chi\u1ebfu thay \u0111\u1ed5i khi sao ch\u00e9p c\u00f4ng th\u1ee9c \u0111\u1ebfn \u00f4 kh\u00e1c<\/strong><\/p>\n<p><strong>Gi\u1ea3i ph\u00e1p:<\/strong> 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 <strong>F4<\/strong>.<\/p>\n<p><strong>H\u00e0m VLOOKUP v\u1edbi h\u00e0m IFERROR \/ ISERROR<\/strong><\/p>\n<p>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 th\u00f4ng \u0111i\u1ec7p 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.<\/p>\n<p><strong> S\u1eed d\u1ee5ng VLOOKUP v\u1edbi IFERROR<\/strong><\/p>\n<p>C\u00fa ph\u00e1p c\u1ee7a h\u00e0m IFERROR r\u1ea5t \u0111\u01a1n gi\u1ea3n<\/p>\n<p><strong>IFERROR (value, value_if_error)<\/strong><\/p>\n<p>B\u1ea1n 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. V\u00ed d\u1ee5, c\u00f4ng th\u1ee9c IFERROR \/ VLOOKUP sau tr\u1ea3 v\u1ec1 m\u1ed9t \u00f4 tr\u1ed1ng khi kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb tra c\u1ee9u:<\/p>\n<p><strong>= IFERROR (VLOOKUP ($F$2, $B$2: $C$10,2, FALSE), \u201c\u201d)<\/strong><\/p>\n<p><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-iferror.png\" alt=\"The IFERROR \/ VLOOKUP formula returns a blank cell instead of the error message.\" title=\"vlookup-iferror\"><\/p>\n<p>N\u1ebfu b\u1ea1n th\u00edch hi\u1ec3n th\u1ecb th\u00f4ng \u0111i\u1ec7p c\u1ee7a m\u00ecnh 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:<\/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\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-iferror-message.png\" alt=\"Display your message instead of VLOOKUP error.\" title=\"vlookup-iferror-message\"><\/p>\n<h4><span class=\"ez-toc-section\" id=\"Dung_ham_VLOOKUP_voi_ISERROR\"><\/span>D\u00f9ng h\u00e0m VLOOKUP v\u1edbi ISERROR<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>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:<\/p>\n<p><strong>=IF(ISERROR(<em>VLOOKUP formula<\/em>), \u201c<em>Your message if any<\/em>\u201c, <em>VLOOKUP formula<\/em>)<\/strong><\/p>\n<p>V\u00ed d\u1ee5, \u0111\u00e2y l\u00e0 c\u00f4ng th\u1ee9c IF \/ ISERROR \/ VLOOKUP t\u01b0\u01a1ng t\u1ef1 nh\u01b0 c\u00f4ng th\u1ee9c IFERROR \/ VLOOKUP \u1edf tr\u00ean:<\/p>\n<p><strong><code>=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),\"\",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))<\/code><\/strong><\/p>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 bi\u1ebft th\u00eam nhi\u1ec1u h\u00e0m n\u00e2ng cao trong Excel c\u0169ng nh\u01b0 s\u1eed d\u1ee5ng t\u1ed1t h\u01a1n c\u00e1c c\u00f4ng c\u1ee5 c\u1ee7a Excel, c\u00e1c b\u1ea1n h\u00e3y tham gia ngay kh\u00f3a h\u1ecdc\u00a0<a href=\"http:\/\/bit.ly\/2MQoP1P\">EX101 \u2013 Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi n\u00e2ng cao<\/a>\u00a0c\u1ee7a H\u1ecdc Excel Online. \u0110\u00e2y l\u00e0 kh\u00f3a h\u1ecdc chi ti\u1ebft v\u00e0 \u0111\u1ea7y \u0111\u1ee7 nh\u1ea5t, gi\u00fap b\u1ea1n ph\u00e1t tri\u1ec3n ki\u1ebfn th\u1ee9c m\u1ed9t c\u00e1ch \u0111\u1ea7y \u0111\u1ee7, c\u00f3 h\u1ec7 th\u1ed1ng. \u0110\u1eb7c bi\u1ec7t kh\u00f3a h\u1ecdc n\u00e0y kh\u00f4ng gi\u1edbi h\u1ea1n th\u1eddi gian h\u1ecdc t\u1eadp, n\u00ean b\u1ea1n c\u00f3 th\u1ec3 h\u1ecdc tho\u1ea3i m\u00e1i v\u00e0 xem l\u1ea1i b\u1ea5t c\u1ee9 khi n\u00e0o.<\/p>\n<p>Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 nhi\u1ec1u \u01b0u \u0111\u00e3i khi b\u1ea1n \u0111\u0103ng k\u00fd kh\u00f3a h\u1ecdc n\u00e0y. Chi ti\u1ebft xem t\u1ea1i:<\/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\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-53.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":1805,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1804","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\/1804","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=1804"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/1804\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/1805"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=1804"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=1804"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=1804"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}