{"id":5021,"date":"2024-12-09T01:43:23","date_gmt":"2024-12-09T01:43:23","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/09\/cach-sua-loi-n-a-khi-ket-hop-ham-iferror-voi-ham-vlookup\/"},"modified":"2024-12-09T01:43:23","modified_gmt":"2024-12-09T01:43:23","slug":"cach-sua-loi-n-a-khi-ket-hop-ham-iferror-voi-ham-vlookup","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/09\/cach-sua-loi-n-a-khi-ket-hop-ham-iferror-voi-ham-vlookup\/","title":{"rendered":"C\u00e1ch s\u1eeda l\u1ed7i #N\/A khi k\u1ebft h\u1ee3p h\u00e0m IFERROR v\u1edbi h\u00e0m VLOOKUP"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em><span style=\"font-weight: 400\">Trong b\u00e0i vi\u1ebft n\u00e0y,<a href=\"http:\/\/blog.ezworkapp.com\/\"><strong> H\u1ecdc Excel Online<\/strong> <\/a>s\u1ebd xem x\u00e9t c\u00e1ch s\u1eed d\u1ee5ng v\u00e0 <strong>c\u00e1ch s\u1eeda l\u1ed7i #N\/A khi k\u1ebft h\u1ee3p h\u00e0m IFERROR v\u1edbi h\u00e0m VLOOKUP<\/strong> trong Excel.\u00a0Ngo\u00e0i ra, b\u1ea1n s\u1ebd h\u1ecdc c\u00e1ch th\u1ef1c hi\u1ec7n li\u00ean ti\u1ebfp c\u00e1c VLOOKUP trong Excel b\u1eb1ng c\u00e1ch ch\u00e8n nhi\u1ec1u<strong> h\u00e0m IFERROR<\/strong> v\u00e0o m\u1ed9t h\u00e0m kh\u00e1c nhau.<\/span><\/em><\/p>\n<p><span style=\"font-weight: 400\">VLOOKUP v\u00e0 IFERROR trong Excel \u2013 hai h\u00e0m n\u00e0y n\u00e0y c\u00f3 th\u1ec3 kh\u00e1 kh\u00f3 hi\u1ec3u, ch\u01b0a n\u00f3i \u0111\u1ebfn khi ch\u00fang \u0111\u01b0\u1ee3c k\u1ebft h\u1ee3p v\u1edbi nhau. Trong b\u00e0i vi\u1ebft n\u00e0y, b\u1ea1n s\u1ebd \u0111\u01b0\u1ee3c h\u1ecdc m\u1ed9t v\u00e0i v\u00ed d\u1ee5 \u0111\u1ec3 d\u1ec5 d\u00e0ng gi\u1ea3i quy\u1ebft c\u00e1ch s\u1eed d\u1ee5ng v\u00e0 c\u00e1c minh h\u1ecda r\u00f5 r\u00e0ng c\u1ee7a c\u00f4ng th\u1ee9c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n kh\u00f4ng c\u00f3 nhi\u1ec1u kinh nghi\u1ec7m v\u1edbi h\u00e0m IFERROR v\u00e0 VLOOKUP, c\u00f3 th\u1ec3 b\u1ea1n n\u00ean tham kh\u1ea3o c\u00e1c kh\u00e1i ni\u1ec7m c\u01a1 b\u1ea3n c\u1ee7a ch\u00fang tr\u01b0\u1edbc b\u1eb1ng c\u00e1ch l\u00e0m theo nh\u1ea5n v\u00e0o c\u00e1c li\u00ean k\u1ebft.<\/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-67564b3aa7591\" 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\/iferror-voi-ham-vlookup-trong-excel-cach-de-dang-de-sua-loi-n-a.html#Ham_IFERROR_VLOOKUP_de_xu_ly_NA_va_cac_loi_khac\" title=\"H\u00e0m IFERROR VLOOKUP \u0111\u1ec3 x\u1eed l\u00fd #N\/A v\u00e0 c\u00e1c l\u1ed7i kh\u00e1c\">H\u00e0m IFERROR VLOOKUP \u0111\u1ec3 x\u1eed l\u00fd #N\/A v\u00e0 c\u00e1c l\u1ed7i kh\u00e1c<\/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\/iferror-voi-ham-vlookup-trong-excel-cach-de-dang-de-sua-loi-n-a.html#Vi_du_1_Cong_thuc_IFERROR_VLOOKUP_thay_the_tat_ca_cac_loi_bang_van_ban\" title=\"V\u00ed d\u1ee5 1: C\u00f4ng th\u1ee9c IFERROR VLOOKUP thay th\u1ebf t\u1ea5t c\u1ea3 c\u00e1c l\u1ed7i b\u1eb1ng v\u0103n b\u1ea3n.\">V\u00ed d\u1ee5 1: C\u00f4ng th\u1ee9c IFERROR VLOOKUP thay th\u1ebf t\u1ea5t c\u1ea3 c\u00e1c l\u1ed7i b\u1eb1ng 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-3\" href=\"https:\/\/blog.hocexcel.online\/iferror-voi-ham-vlookup-trong-excel-cach-de-dang-de-sua-loi-n-a.html#Vi_du_2_IFERROR_voi_ham_VLOOKUP_de_tra_ve_trong_hoac_0_neu_khong_tim_thay_gi\" title=\"V\u00ed d\u1ee5 2: IFERROR v\u1edbi h\u00e0m VLOOKUP \u0111\u1ec3 tr\u1ea3 v\u1ec1 tr\u1ed1ng ho\u1eb7c 0 n\u1ebfu kh\u00f4ng t\u00ecm th\u1ea5y g\u00ec\">V\u00ed d\u1ee5 2: IFERROR v\u1edbi h\u00e0m VLOOKUP \u0111\u1ec3 tr\u1ea3 v\u1ec1 tr\u1ed1ng ho\u1eb7c 0 n\u1ebfu kh\u00f4ng t\u00ecm th\u1ea5y g\u00ec<\/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-4\" href=\"https:\/\/blog.hocexcel.online\/iferror-voi-ham-vlookup-trong-excel-cach-de-dang-de-sua-loi-n-a.html#Chen_IFERROR_vao_trong_ham_VLOOKUP_de_tim_kiem\" title=\"Ch\u00e8n IFERROR v\u00e0o trong h\u00e0m VLOOKUP \u0111\u1ec3 t\u00ecm ki\u1ebfm\">Ch\u00e8n IFERROR v\u00e0o trong h\u00e0m VLOOKUP \u0111\u1ec3 t\u00ecm ki\u1ebfm<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/iferror-voi-ham-vlookup-trong-excel-cach-de-dang-de-sua-loi-n-a.html#Cach_thuc_hien_day_ham_VLOOKUP_trong_Excel\" title=\"C\u00e1ch th\u1ef1c hi\u1ec7n d\u00e3y h\u00e0m VLOOKUP trong Excel\u00a0\">C\u00e1ch th\u1ef1c hi\u1ec7n d\u00e3y h\u00e0m VLOOKUP trong Excel\u00a0<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Ham_IFERROR_VLOOKUP_de_xu_ly_NA_va_cac_loi_khac\"><\/span><b>H\u00e0m IFERROR VLOOKUP \u0111\u1ec3 x\u1eed l\u00fd #N\/A v\u00e0 c\u00e1c l\u1ed7i kh\u00e1c<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Khi Excel VLOOKUP kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb tra c\u1ee9u, n\u00f3 s\u1ebd xu\u1ea5t hi\u1ec7n l\u1ed7i #N\/A, nh\u01b0 sau:<\/span><\/p>\n<figure style=\"width: 526px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"H\u00e0m IFERROR VLOOKUP \u0111\u1ec3 x\u1eed l\u00fd #N\/A v\u00e0 c\u00e1c l\u1ed7i kh\u00e1c\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-vlookup-error.png\" alt=\"When Excel Vlookup fails to find a lookup value, it throws an #N\/A error.\" width=\"526\" height=\"190\"><figcaption class=\"wp-caption-text\"><em>H\u00e0m IFERROR VLOOKUP \u0111\u1ec3 x\u1eed l\u00fd #N\/A v\u00e0 c\u00e1c l\u1ed7i kh\u00e1c<\/em><\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">T\u00f9y thu\u1ed9c v\u00e0o doanh nghi\u1ec7p c\u1ee7a b\u1ea1n, b\u1ea1n c\u00f3 th\u1ec3 mu\u1ed1n che gi\u1ea5u l\u1ed7 b\u1eb1ng v\u0103n b\u1ea3n, kh\u00f4ng ho\u1eb7c kho\u1ea3ng tr\u1ed1ng.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Vi_du_1_Cong_thuc_IFERROR_VLOOKUP_thay_the_tat_ca_cac_loi_bang_van_ban\"><\/span><b>V\u00ed d\u1ee5 1: C\u00f4ng th\u1ee9c IFERROR VLOOKUP thay th\u1ebf t\u1ea5t c\u1ea3 c\u00e1c l\u1ed7i b\u1eb1ng v\u0103n b\u1ea3n.<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n mu\u1ed1n thay th\u1ebf k\u00fd hi\u1ec7u l\u1ed7i b\u1eb1ng v\u0103n b\u1ea3n t\u00f9y ch\u1ec9nh, h\u00e3y ch\u00e8n c\u00f4ng th\u1ee9c IFERROR trong VLOOKUP v\u00e0 nh\u1eadp b\u1ea5t k\u1ef3 v\u0103n b\u1ea3n n\u00e0o b\u1ea1n mu\u1ed1n v\u00e0o tham s\u1ed1 th\u1ee9 hai (value_if_error), v\u00ed d\u1ee5: \u201cNot found\u201d:<\/span><\/p>\n<p><span style=\"font-weight: 400\">IFERROR(VLOOKUP(\u2026),\u201dNot found\u201d)<\/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-420.gif\" title=\"ex101_92019-nho-420\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><span style=\"font-weight: 400\">V\u1edbi gi\u00e1 tr\u1ecb tra c\u1ee9u \u1edf B2 trong b\u1ea3ng ch\u00ednh v\u00e0 ph\u1ea1m vi tra c\u1ee9u A2:B4 trong b\u1ea3ng tra c\u1ee9u, c\u00f4ng th\u1ee9c c\u00f3 d\u1ea1ng sau:<\/span><\/p>\n<p><strong>=IFERROR(VLOOKUP(B2,\u2019Lookup table\u2019!$A$2:$B$5, 2, FALSE), \u201cNot found\u201d)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">H\u00ecnh b\u00ean d\u01b0\u1edbi hi\u1ec3n th\u1ecb c\u00f4ng th\u1ee9c h\u00e0m IFERROR VLOOKUP trong Excel:<\/span><\/p>\n<figure style=\"width: 476px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"C\u00f4ng th\u1ee9c IFERROR VLOOKUP thay th\u1ebf t\u1ea5t c\u1ea3 c\u00e1c l\u1ed7i b\u1eb1ng v\u0103n b\u1ea3n\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-iferror-vlookup.png\" alt=\"Iferror Vlookup formula to replace errors with your own text.\" width=\"476\" height=\"160\"><figcaption class=\"wp-caption-text\">C\u00f4ng th\u1ee9c IFERROR VLOOKUP thay th\u1ebf t\u1ea5t c\u1ea3 c\u00e1c l\u1ed7i b\u1eb1ng v\u0103n b\u1ea3n<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">K\u1ebft qu\u1ea3 tr\u00f4ng d\u1ec5 hi\u1ec3u h\u01a1n v\u00e0 \u00edt \u0111\u00e1ng s\u1ee3 h\u01a1n nhi\u1ec1u, ph\u1ea3i kh\u00f4ng?<\/span><\/p>\n<p><span style=\"font-weight: 400\">Theo c\u00e1ch t\u01b0\u01a1ng t\u1ef1, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng INDEX MATCH c\u00f9ng v\u1edbi IFERROR:<\/span><\/p>\n<p><strong>=IFERROR(INDEX(\u2018Lookup table\u2019!$B$2:$B$5,MATCH(B2,\u2019Lookup table\u2019!$A$2:$A$5,0)), \u201cNot found\u201d)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c IFERROR INDEX MATCH \u0111\u1eb7c bi\u1ec7t h\u1eefu \u00edch khi b\u1ea1n mu\u1ed1n k\u00e9o c\u00e1c gi\u00e1 tr\u1ecb t\u1eeb m\u1ed9t c\u1ed9t n\u1eb1m \u1edf b\u00ean tr\u00e1i c\u1ed9t tra c\u1ee9u (tra c\u1ee9u b\u00ean tr\u00e1i) v\u00e0 tr\u1ea3 v\u1ec1 v\u0103n b\u1ea3n khi kh\u00f4ng t\u00ecm th\u1ea5y g\u00ec.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Vi_du_2_IFERROR_voi_ham_VLOOKUP_de_tra_ve_trong_hoac_0_neu_khong_tim_thay_gi\"><\/span><b>V\u00ed d\u1ee5 2: IFERROR v\u1edbi h\u00e0m VLOOKUP \u0111\u1ec3 tr\u1ea3 v\u1ec1 tr\u1ed1ng ho\u1eb7c 0 n\u1ebfu kh\u00f4ng t\u00ecm th\u1ea5y g\u00ec<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n kh\u00f4ng mu\u1ed1n hi\u1ec3n th\u1ecb b\u1ea5t k\u1ef3 th\u1ee9 g\u00ec khi kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb tra c\u1ee9u, h\u00e3y \u0111\u1ec3 h\u00e0m IFERROR hi\u1ec3n th\u1ecb m\u1ed9t chu\u1ed7i tr\u1ed1ng (\u201c\u201d):<\/span><\/p>\n<p><span style=\"font-weight: 400\">IFERROR(VLOOKUP(\u2026),\u201d\u201d)<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong v\u00ed d\u1ee5 n\u00e0y, c\u00f4ng th\u1ee9c nh\u01b0 sau:<\/span><\/p>\n<p><strong>=IFERROR(VLOOKUP(B2,\u2019Lookup table\u2019!$A$2:$B$5, 2, FALSE), \u201c\u201d)\u00a0<\/strong><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu nh\u01b0 b\u1ea1n th\u1ea5y, n\u00f3 kh\u00f4ng tr\u1ea3 v\u1ec1 g\u00ec khi gi\u00e1 tr\u1ecb tra c\u1ee9u kh\u00f4ng c\u00f3 trong danh s\u00e1ch t\u00ecm ki\u1ebfm.<\/span><\/p>\n<figure style=\"width: 414px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"IFERROR v\u1edbi h\u00e0m VLOOKUP\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-iferror-vlookup-blank.png\" alt=\"Iferror with Vlookup to return blank cell if nothing is found\" width=\"414\" height=\"159\"><figcaption class=\"wp-caption-text\">IFERROR v\u1edbi h\u00e0m VLOOKUP<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n mu\u1ed1n thay th\u1ebf l\u1ed7i b\u1eb1ng gi\u00e1 tr\u1ecb 0, h\u00e3y \u0111\u1eb7t 0 v\u00e0o tham s\u1ed1 cu\u1ed1i c\u00f9ng:<\/span><\/p>\n<p><strong>=IFERROR(VLOOKUP(B2,\u2019Lookup table\u2019!$A$2:$B$5, 2, FALSE), 0)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Ch\u00fa \u00fd: H\u00e0m IFERROR trong Excel b\u1eaft t\u1ea5t c\u1ea3 c\u00e1c l\u1ed7i, kh\u00f4ng ch\u1ec9 l\u1ed7i #N\/A. \u0110i\u1ec1u \u0111\u00f3 l\u00e0 t\u1ed1t hay x\u1ea5u? T\u1ea5t c\u1ea3 ph\u1ee5 thu\u1ed9c v\u00e0o m\u1ee5c ti\u00eau c\u1ee7a b\u1ea1n. N\u1ebfu b\u1ea1n mu\u1ed1n che gi\u1ea5u t\u1ea5t c\u1ea3 c\u00e1c l\u1ed7i c\u00f3 th\u1ec3 x\u1ea3y ra, IFERROR VLOOKUP l\u00e0 c\u00e1ch \u0111\u1ec3 th\u1ef1c hi\u1ec7n. Nh\u01b0ng n\u00f3 c\u00f3 th\u1ec3 l\u00e0 m\u1ed9t k\u1ef9 thu\u1eadt kh\u00f4ng kh\u00f4n ngoan trong v\u00ed d\u1ee5 n\u00e0y.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ed d\u1ee5: N\u1ebfu b\u1ea1n \u0111\u00e3 t\u1ea1o m\u1ed9t d\u1ea3i \u00f4 \u0111\u00e3 \u0111\u1eb7t t\u00ean cho d\u1eef li\u1ec7u b\u1ea3ng c\u1ee7a m\u00ecnh v\u00e0 vi\u1ebft sai ch\u00ednh t\u1ea3 t\u00ean \u0111\u00f3 trong c\u00f4ng th\u1ee9c VLOOKUP, IFERROR s\u1ebd b\u1eaft l\u1ed7i #NAME? v\u00e0 thay th\u1ebf n\u00f3 b\u1eb1ng \u201cnot found\u201d hay b\u1ea5t k\u1ef3 v\u0103n b\u1ea3n n\u00e0o kh\u00e1c b\u1ea1n cung c\u1ea5p. Do \u0111\u00f3, b\u1ea1n c\u00f3 th\u1ec3 kh\u00f4ng bao gi\u1edd bi\u1ebft c\u00f4ng th\u1ee9c c\u1ee7a m\u00ecnh \u0111\u01b0a ra k\u1ebft qu\u1ea3 sai tr\u1eeb khi b\u1ea1n t\u1ef1 ph\u00e1t hi\u1ec7n l\u1ed7i typo. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, m\u1ed9t c\u00e1ch ti\u1ebfp c\u1eadn h\u1ee3p l\u00fd h\u01a1n s\u1ebd ch\u1ec9 m\u1eafc l\u1ed7i #N\/A. \u0110\u1ed1i v\u1edbi \u0111i\u1ec1u n\u00e0y, h\u00e3y s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c IFNA VLOOKUP trong Excel cho Office 365, Excel 2009, Excel 2016 v\u00e0 Excel 3013, IF ISNA VLOOKUP trong t\u1ea5t c\u1ea3 phi\u00ean b\u1ea3n c\u1ee7a Excel.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110i\u1ec1u m\u1ea5u ch\u1ed1t l\u00e0 h\u00e3y c\u1ea9n th\u1eadn khi ch\u1ecdn h\u00e0m ph\u00f9 h\u1ee3p cho c\u00f4ng th\u1ee9c VLOOKUP.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Chen_IFERROR_vao_trong_ham_VLOOKUP_de_tim_kiem\"><\/span><b>Ch\u00e8n IFERROR v\u00e0o trong h\u00e0m VLOOKUP \u0111\u1ec3 t\u00ecm ki\u1ebfm<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">H\u00e3y t\u01b0\u1edfng t\u01b0\u1ee3ng t\u00ecnh hu\u1ed1ng sau: b\u1ea1n t\u00ecm ki\u1ebfm m\u1ed9t gi\u00e1 tr\u1ecb c\u1ee5 th\u1ec3 trong m\u1ed9t danh s\u00e1ch v\u00e0 kh\u00f4ng t\u00ecm th\u1ea5y n\u00f3. B\u1ea1n c\u00f3 nh\u1eefng l\u1ef1a ch\u1ecdn n\u00e0o? G\u1eb7p l\u1ed7i N\/A hay hi\u1ec3n th\u1ecb th\u00f4ng b\u00e1o. Tr\u00ean th\u1ef1c t\u1ebf, c\u00f3 m\u1ed9t t\u00f9y ch\u1ecdn th\u1ee9 ba \u2013 n\u1ebfu VLOOKUP c\u1ee7a b\u1ea1n g\u1eb7p s\u1ef1 c\u1ed1, th\u00ec h\u00e3y t\u00ecm ki\u1ebfm th\u1ee9 kh\u00e1c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ed d\u1ee5: h\u00e3y t\u1ea1o m\u1ed9t s\u1ed1 lo\u1ea1i b\u1ea3ng th\u00f4ng tin cho ng\u01b0\u1eddi d\u00f9ng m\u00e0 n\u00f3 s\u1ebd hi\u1ec3n th\u1ecb s\u1ed1 m\u00e1y chi nh\u00e1nh cho t\u1eebng v\u0103n ph\u00f2ng c\u1ee5 th\u1ec3.\u00a0<\/span><\/p>\n<figure style=\"width: 460px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"Ch\u00e8n IFERROR v\u00e0o trong h\u00e0m VLOOKUP \u0111\u1ec3 t\u00ecm ki\u1ebfm\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/iferror-vlookups-source-data.png\" alt=\"source data for VLOOKUP with nested IFERROR\" width=\"460\" height=\"160\"><figcaption class=\"wp-caption-text\">Ch\u00e8n IFERROR v\u00e0o trong h\u00e0m VLOOKUP \u0111\u1ec3 t\u00ecm ki\u1ebfm<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">V\u00ec v\u1eady, l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 b\u1ea1n k\u00e9o ph\u1ea7n m\u1edf r\u1ed9ng t\u1eeb c\u1ed9t B d\u1ef1a tr\u00ean s\u1ed1 v\u0103n ph\u00f2ng trong D2? V\u1edbi c\u00f4ng th\u1ee9c VLOOKUP n\u00e0y:<\/span><\/p>\n<p><strong>=VLOOKUP($D$2,$A$2:$B$7,2,FALSE)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">V\u00e0 n\u00f3 s\u1ebd ho\u1ea1t \u0111\u1ed9ng t\u1ed1t mi\u1ec5n l\u00e0 ng\u01b0\u1eddi d\u00f9ng nh\u1eadp m\u1ed9t s\u1ed1 h\u1ee3p l\u1ec7 v\u00e0o D2. Nh\u01b0ng n\u1ebfu ng\u01b0\u1eddi d\u00f9ng nh\u1eadp m\u1ed9t s\u1ed1 kh\u00f4ng t\u1ed3n t\u1ea1i th\u00ec sao? Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, h\u00e3y \u0111\u1ec3 h\u1ecd g\u1ecdi cho v\u0103n ph\u00f2ng trung t\u00e2m! \u0110\u1ed1i v\u1edbi \u0111i\u1ec1u n\u00e0y, b\u1ea1n g\u00e1n c\u00f4ng th\u1ee9c tr\u00ean v\u00e0o tham s\u1ed1 gi\u00e1 tr\u1ecb c\u1ee7a IFERROR v\u00e0 \u0111\u1eb7t VLOOKUP kh\u00e1c trong tham s\u1ed1 value_if_error<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c ho\u00e0n ch\u1ec9nh h\u01a1i d\u00e0i nh\u01b0ng ho\u1ea1t \u0111\u1ed9ng r\u1ea5t hi\u1ec7u qu\u1ea3:<\/span><\/p>\n<p><strong>=IFERROR(VLOOKUP(\u201coffice \u201c&amp;$D$2,$A$2:$B$7,2,FALSE),VLOOKUP(\u201ccentral office\u201d,$A$2:$B$7,2,FALSE))<\/strong><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu oso v\u0103n ph\u00f2ng \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y, ng\u01b0\u1eddi d\u00f9ng s\u1ebd nh\u1eadn \u0111\u01b0\u1ee3c s\u1ed1 m\u00e1y t\u01b0\u01a1ng \u1ee9ng t\u1eeb chi nh\u00e1nh:<\/span><\/p>\n<figure style=\"width: 617px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"IFERROR formula with two vlookups\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/iferror-two-vlookups.png\" alt=\"IFERROR formula with two vlookups\" width=\"617\" height=\"201\"><figcaption class=\"wp-caption-text\">Ch\u00e8n IFERROR v\u00e0o trong h\u00e0m VLOOKUP<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">N\u1ebfu kh\u00f4ng t\u00ecm th\u1ea5y s\u1ed1 chi nh\u00e1nh, ph\u1ea7n m\u1edf r\u1ed9ng v\u0103n ph\u00f2ng trung t\u00e2m s\u1ebd hi\u1ec3n th\u1ecb:<\/span><\/p>\n<figure style=\"width: 617px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"V\u00ed d\u1ee5 v\u1ec1 ch\u00e8n IFERROR v\u00e0o trong h\u00e0m VLOOKUP\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/iferror-two-vlookups2.png\" alt=\"If the first vlookup does not find anything, the second vlookup pulls a common value.\" width=\"617\" height=\"200\"><figcaption class=\"wp-caption-text\">V\u00ed d\u1ee5 v\u1ec1 ch\u00e8n IFERROR v\u00e0o trong h\u00e0m VLOOKUP<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 c\u00f4ng th\u1ee9c thu g\u1ecdn h\u01a1n, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng m\u1ed9t c\u00e1ch ti\u1ebfp c\u1eadn kh\u00e1c:<\/span><\/p>\n<p><span style=\"font-weight: 400\">Tr\u01b0\u1edbc ti\u00ean, h\u00e3y ki\u1ec3m tra xem s\u1ed1 trong D2 c\u00f3 trong c\u1ed9t tra c\u1ee9u hay kh\u00f4ng (vui l\u00f2ng l\u01b0u \u00fd r\u1eb1ng ch\u00fang ta \u0111\u1eb7t col_index_num th\u00e0nh 1 \u0111\u1ec3 c\u00f4ng th\u1ee9c t\u00ecm ki\u1ebfm v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb \u1edf c\u1ed9t A): VLOOKUP(D2,$A$2:$B$7,1,FALSE)<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu s\u1ed1 v\u0103n ph\u00f2ng \u0111\u01b0\u1ee3c ch\u1ec9 \u0111\u1ecbnh kh\u00f4ng \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y, th\u00ec ch\u00fang ta t\u00ecm ki\u1ebfm chu\u1ed7i \u201cv\u0103n ph\u00f2ng trung t\u00e2m\u201d (central office), chu\u1ed7i n\u00e0y ch\u1eafc ch\u1eafn n\u1eb1m trong danh s\u00e1ch tra c\u1ee9u. \u0110\u1ed1i v\u1edbi \u0111i\u1ec1u n\u00e0y, b\u1ea1n ch\u00e8n\u00a0 h\u00e0m IFERROR v\u00e0o h\u00e0m VLOOKUP v\u00e0 l\u1ed3ng to\u00e0n b\u1ed9 t\u1ed5 h\u1ee3p n\u00e0y v\u00e0o b\u00ean trong m\u1ed9t h\u00e0m VLOOKUP kh\u00e1c:<\/span><\/p>\n<p><strong>=VLOOKUP(IFERROR(VLOOKUP(D2,$A$2:$B$7,1,FALSE),\u201dcentral office\u201d),$A$2:$B$7,2)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 m\u1ed9t c\u00f4ng th\u1ee9c kh\u00e1c nh\u01b0ng cho ra c\u00f9ng k\u1ebft qu\u1ea3:<\/span><\/p>\n<figure style=\"width: 545px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"K\u1ebft qu\u1ea3 ch\u00e8n IFERROR v\u00e0o trong h\u00e0m VLOOKUP\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-vlookup-iferror.png\" alt=\"Vlookup with the nested IFERROR function\" width=\"545\" height=\"200\"><figcaption class=\"wp-caption-text\">K\u1ebft qu\u1ea3 ch\u00e8n IFERROR v\u00e0o trong h\u00e0m VLOOKUP<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">Nh\u01b0ng l\u00fd do \u0111\u1ec3 tra c\u1ee9u \u201cv\u0103n ph\u00f2ng trung t\u00e2m\u201d (central office) l\u00e0 g\u00ec. T\u1ea1i sao kh\u00f4ng cung c\u1ea5p tr\u1ef1c ti\u1ebfp s\u1ed1 m\u00e1y chi nh\u00e1nh trong IFERROR? V\u00ec ph\u1ea7n m\u1edf r\u1ed9ng c\u00f3 th\u1ec3 thay \u0111\u1ed5i v\u00e0o m\u1ed9t th\u1eddi \u0111i\u1ec3m trong t\u01b0\u01a1ng lai. N\u1ebfu \u0111i\u1ec1u n\u00e0y x\u1ea3y ra, b\u1ea1n s\u1ebd ph\u1ea3i c\u1eadp nh\u1eadt d\u1eef li\u1ec7u c\u1ee7a m\u00ecnh m\u1ed9t l\u1ea7n trong b\u1ea3ng ngu\u1ed3n m\u00e0 kh\u00f4ng c\u1ea7n lo l\u1eafng v\u1ec1 vi\u1ec7c c\u1eadp nh\u1eadt t\u1eebng c\u00f4ng th\u1ee9c VLOOKUP.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_thuc_hien_day_ham_VLOOKUP_trong_Excel\"><\/span><b>C\u00e1ch th\u1ef1c hi\u1ec7n d\u00e3y h\u00e0m VLOOKUP trong Excel\u00a0<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Trong t\u00ecnh hu\u1ed1ng khi b\u1ea1n c\u1ea7n th\u1ef1c hi\u1ec7n c\u00e1i g\u1ecdi l\u00e0 VLOOKUP li\u00ean ho\u00e0n ho\u1eb7c chu\u1ed7i trong Excel t\u00f9y thu\u1ed9c v\u00e0o vi\u1ec7c tra c\u1ee9u tr\u01b0\u1edbc \u0111\u00f3 th\u00e0nh c\u00f4ng hay th\u1ea5t b\u1ea1i, h\u00e3y ch\u00e8n hai hay nhi\u1ec1u h\u00e0m IFERROR \u0111\u1ec3 ch\u1ea1y VLOOKUP t\u1eebng c\u00e1i m\u1ed9t:<\/span><\/p>\n<p><strong>IFERROR(VLOOKUP(\u2026), IFERROR(VLOOKUP(\u2026), IFERROR(VLOOKUP(\u2026),\u201dNot found\u201d)))<\/strong><\/p>\n<p><em><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c ho\u1ea1t \u0111\u1ed9ng logic nh\u01b0 sau:<\/span><\/em><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu h\u00e0m VLOOKUP \u0111\u1ea7u ti\u00ean kh\u00f4ng t\u00ecm th\u1ea5y g\u00ec, h\u00e0m IFERROR \u0111\u1ea7u ti\u00ean m\u1eafc l\u1ed7i v\u00e0 ch\u1ea1y m\u1ed9t <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 VLOOKUP<\/strong><\/a> kh\u00e1c. N\u1ebfu l\u00e0m VLOOKUP th\u1ee9 hai th\u1ea5t b\u1ea1i , IFERROR th\u1ee9 hai b\u1eaft l\u1ed7i v\u00e0 ch\u1ea1y h\u00e0m VLOOKUP th\u1ee9 3,\u2026 N\u1ebfu t\u1ea5t c\u1ea3 c\u00e1c VLOOKUP g\u1eb7p s\u1ef1 c\u1ed1, IFERROR cu\u1ed1i c\u00f9ng s\u1ebd tr\u1ea3 v\u1ec1 th\u00f4ng b\u00e1o.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c chu\u1ed7i IFERROR n\u00e0y \u0111\u1eb7c bijet h\u1eefu \u00edch khi b\u1ea1n ph\u1ea3i VLOOKUP tr\u00ean nhi\u1ec1u trang t\u00ednh nh\u01b0 th\u1ec3 hi\u1ec7n trong v\u00ed d\u1ee5 d\u01b0\u1edbi \u0111\u00e2y.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Gi\u1ea3 s\u1eed, b\u1ea1n c\u00f3 ba danh s\u00e1ch d\u1eef li\u1ec7u \u0111\u1ed3ng nh\u1ea5t trong ba trang t\u00ednh kh\u00e1c nhau (s\u1ed1 v\u0103n ph\u00f2ng trong v\u00ed d\u1ee5 n\u00e0y) v\u00e0 b\u1ea1n mu\u1ed1n nh\u1eadn ph\u1ea7n m\u1edf r\u1ed9ng cho m\u1ed9t s\u1ed1 nh\u1ea5t \u0111\u1ecbnh.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Gi\u1ea3 s\u1eed gi\u00e1 tr\u1ecb tra c\u1ee9u n\u1eb1m trong \u00f4 A2 trong trang t\u00ednh hi\u1ec7n t\u1ea1i v\u00e0 ph\u1ea1m vi tra c\u1ee9u l\u00e0 A2:B5 trong 3 trang t\u00ednh kh\u00e1c nhau (North, South v\u00e0 West), c\u00f4ng th\u1ee9c sau ho\u1ea1t \u0111\u1ed9ng t\u1ed1t:<\/span><\/p>\n<p><strong>=IFERROR(VLOOKUP(A2,North!$A$2:$B$5,2,FALSE), IFERROR(VLOOKUP(A2,South!$A$2:$B$5,2,FALSE),\u00a0 IFERROR(VLOOKUP(A2,West!$A$2:$B$5,2,FALSE),\u201dNot found\u201d)))<\/strong><\/p>\n<p><span style=\"font-weight: 400\">v\u00ec v\u1eady, c\u00f4ng th\u1ee9c \u201cchu\u1ed7i VLOOKUP\u201d t\u00ecm ki\u1ebfm trong c\u1ea3 ba trang t\u00ednh theo th\u1ee9 t\u1ef1 \u0111\u00e3 ch\u00e8n ch\u00fang v\u00e0o trong c\u00f4ng th\u1ee9c v\u00e0 mang l\u1ea1i hi\u1ec7u qu\u1ea3 ph\u00f9 h\u1ee3p \u0111\u1ea7u ti\u00ean m\u00e0 n\u00f3 t\u00ecm th\u1ea5y:<\/span><\/p>\n<figure style=\"width: 591px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"C\u00e1ch th\u1ef1c hi\u1ec7n d\u00e3y h\u00e0m VLOOKUP trong Excel\u00a0\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-nested-iferror-vlookup.png\" alt=\"Nested IFERROR functions to do sequential Vlookups\" width=\"591\" height=\"315\"><figcaption class=\"wp-caption-text\">C\u00e1ch th\u1ef1c hi\u1ec7n d\u00e3y h\u00e0m VLOOKUP trong Excel<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 <strong>c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m IFERROR v\u1edbi h\u00e0m VLOOKUP<\/strong> trong Excel. T\u00f4i c\u1ea3m \u01a1n b\u1ea1n \u0111\u00e3 \u0111\u1ecdc v\u00e0 hy v\u1ecdng s\u1ebd g\u1eb7p b\u1ea1n tr\u00ean blog c\u1ee7a ch\u00fang t\u00f4i t\u1edbi v\u1edbi nh\u1eefng ki\u1ebfn th\u1ee9c b\u1ed5 \u00edch v\u1ec1 <a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-excel-ke-toan.html\"><strong>K\u1ebf to\u00e1n b\u1eb1ng Excel<\/strong><\/a><\/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-420.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5022,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-5021","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\/5021","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=5021"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5021\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5022"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5021"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5021"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5021"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}