{"id":2481,"date":"2024-12-02T07:55:21","date_gmt":"2024-12-02T07:55:21","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/4-cach-viet-ham-vlookup-de-phan-biet-chu-hoa-chu-thuong\/"},"modified":"2024-12-02T07:55:21","modified_gmt":"2024-12-02T07:55:21","slug":"4-cach-viet-ham-vlookup-de-phan-biet-chu-hoa-chu-thuong","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/4-cach-viet-ham-vlookup-de-phan-biet-chu-hoa-chu-thuong\/","title":{"rendered":"4 C\u00c1CH VI\u1ebeT H\u00c0M VLOOKUP \u0110\u1ec2 PH\u00c2N BI\u1ec6T CH\u1eee HOA, CH\u1eee TH\u01af\u1edcNG"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em><span style=\"font-weight: 400\">B\u00e0i n\u00e0y<a href=\"http:\/\/blog.ezworkapp.com\/\"><strong> H\u1ecdc Excel Online<\/strong> <\/a>s\u1ebd h\u01b0\u1edbng d\u1eabn c\u00e1ch vi\u1ebft <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> <strong>ph\u00e2n bi\u1ec7t ch\u1eef hoa v\u00e0 ch\u1eef th\u01b0\u1eddng<\/strong>, h\u01b0\u1edbng d\u1eabn m\u1ed9t v\u00e0i c\u00f4ng th\u1ee9c c\u00f3 th\u1ec3 tra c\u1ee9u c\u00e1c tr\u01b0\u1eddng h\u1ee3p trong Excel v\u00e0 ch\u1ec9 ra \u0111i\u1ec3m m\u1ea1nh v\u00e0 h\u1ea1n ch\u1ebf c\u1ee7a m\u1ed7i h\u00e0m.<\/span><\/em><\/p>\n<p><span style=\"font-weight: 400\">H\u1ea7u h\u1ebft m\u1ecdi ng\u01b0\u1eddi d\u00f9ng Excel \u0111\u1ec1u bi\u1ebft h\u00e0m n\u00e0o th\u1ef1c hi\u1ec7n tra c\u1ee9u theo chi\u1ec1u d\u1ecdc trong Excel. \u0110\u00fang, h\u00e0m VLOOKUP. Tuy nhi\u00ean, r\u1ea5t \u00edt ng\u01b0\u1eddi bi\u1ebft r\u1eb1ng h\u00e0m VLOOKUP trong Excel kh\u00f4ng ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng, ngh\u0129a l\u00e0 n\u00f3 x\u1eed l\u00fd c\u00e1c k\u00fd t\u1ef1 th\u01b0\u1eddng v\u00e0 ch\u1eef hoa gi\u1ed1ng h\u1ec7t nhau.<\/span><\/p>\n<p><span style=\"font-weight: 400\">D\u01b0\u1edbi \u0111\u00e2y l\u00e0 m\u1ed9t v\u00ed d\u1ee5 nhanh cho th\u1ea5y h\u00e0m VLOOKUP kh\u00f4ng c\u00f3 kh\u1ea3 n\u0103ng ph\u00e2n bi\u1ec7t. Gi\u1ea3 s\u1eed, n\u1ebfu b\u1ea1n c\u00f3 \u201cbill\u201d v\u00ec n\u00f3 xu\u1ea5t hi\u1ec7n \u0111\u1ea7u ti\u00ean trong m\u1ea3ng tra c\u1ee9u v\u00e0 tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb t\u1eeb \u00f4 B1.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ti\u1ebfp theo trong b\u00e0i vi\u1ebft n\u00e0y, s\u1ebd ch\u1ec9 ra cho b\u1ea1n c\u00e1ch t\u1ea1o <strong>h\u00e0m VLOOKUP ph\u00e2n bi\u1ec7t ch\u1eef hoa v\u00e0 ch\u1eef th\u01b0\u1eddng<\/strong>. Ch\u00fang ta c\u0169ng s\u1ebd kh\u00e1m ph\u00e1 m\u1ed9t s\u1ed1 h\u00e0m kh\u00e1c c\u00f3 th\u1ec3 ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng trong Excel.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ch\u00fang ta b\u1eaft \u0111\u1ea7u v\u1edbi h\u00e0m VLOOKUP v\u00e0 SUMPRODUCT kh\u00e1 \u0111\u01a1n gi\u1ea3n, tuy nhi\u00ean c\u00f3 m\u1ed9t v\u00e0i h\u1ea1n ch\u1ebf \u0111\u00e1ng k\u1ec3, Sau \u0111\u00f3, ch\u00fang ta s\u1ebd c\u00f3 m\u1ed9t c\u00e1i nh\u00ecn s\u00e2u h\u01a1n v\u1ec1 c\u00f4ng th\u1ee9c INDEX\/MATCH ph\u1ee9c t\u1ea1p h\u01a1n m\u1ed9t ch\u00fat, ho\u1ea1t \u0111\u1ed9ng h\u1ea7u nh\u01b0 kh\u00f4ng c\u00f3 l\u1ed7i sai n\u00e0o trong t\u1ea5t c\u1ea3 c\u00e1c t\u00ecnh hu\u1ed1ng v\u00e0 tr\u00ean t\u1ea5t c\u1ea3 c\u00e1c t\u1eadp d\u1eef li\u1ec7u.<\/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-674d67e8a4ebf\" 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\/4-cach-de-viet-ham-vlookup-phan-biet-chu-hoa-va-chu-thuong-trong-excel.html#Cong_thuc_VLOOKUP_phan_biet_chu_hoa_va_chu_thuong\" title=\"C\u00f4ng th\u1ee9c VLOOKUP ph\u00e2n bi\u1ec7t ch\u1eef hoa v\u00e0 ch\u1eef th\u01b0\u1eddng\">C\u00f4ng th\u1ee9c VLOOKUP ph\u00e2n bi\u1ec7t ch\u1eef hoa v\u00e0 ch\u1eef th\u01b0\u1eddng<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/4-cach-de-viet-ham-vlookup-phan-biet-chu-hoa-va-chu-thuong-trong-excel.html#Cach_su_dung_cong_thuc_CODE_mot_cach_chinh_xac\" title=\"C\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c CODE m\u1ed9t c\u00e1ch ch\u00ednh x\u00e1c\">C\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c CODE m\u1ed9t c\u00e1ch ch\u00ednh x\u00e1c<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/4-cach-de-viet-ham-vlookup-phan-biet-chu-hoa-va-chu-thuong-trong-excel.html#Cong_thuc_LOOKUP_phu_hop_cho_chu_hoa_chu_thuong\" title=\"C\u00f4ng th\u1ee9c LOOKUP ph\u00f9 h\u1ee3p cho ch\u1eef hoa ch\u1eef th\u01b0\u1eddng\">C\u00f4ng th\u1ee9c LOOKUP ph\u00f9 h\u1ee3p cho ch\u1eef hoa ch\u1eef th\u01b0\u1eddng<\/a><\/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\/4-cach-de-viet-ham-vlookup-phan-biet-chu-hoa-va-chu-thuong-trong-excel.html#SUMPRODUCT_%E2%80%93_tra_cuu_truong_hop_gia_tri_van_ban_va_tra_ve_so_phu_hop\" title=\"SUMPRODUCT \u2013 tra c\u1ee9u tr\u01b0\u1eddng h\u1ee3p gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n v\u00e0 tr\u1ea3 v\u1ec1 s\u1ed1 ph\u00f9 h\u1ee3p\">SUMPRODUCT \u2013 tra c\u1ee9u tr\u01b0\u1eddng h\u1ee3p gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n v\u00e0 tr\u1ea3 v\u1ec1 s\u1ed1 ph\u00f9 h\u1ee3p<\/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\/4-cach-de-viet-ham-vlookup-phan-biet-chu-hoa-va-chu-thuong-trong-excel.html#MATCH_INDEX_%E2%80%93_tra_cuu_chu_hoa_chu_thuong_cho_tat_ca_cac_loai_du_lieu\" title=\"MATCH\/ INDEX \u2013 tra c\u1ee9u ch\u1eef hoa ch\u1eef th\u01b0\u1eddng cho t\u1ea5t c\u1ea3 c\u00e1c lo\u1ea1i d\u1eef li\u1ec7u\">MATCH\/ INDEX \u2013 tra c\u1ee9u ch\u1eef hoa ch\u1eef th\u01b0\u1eddng cho t\u1ea5t c\u1ea3 c\u00e1c lo\u1ea1i d\u1eef li\u1ec7u<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/4-cach-de-viet-ham-vlookup-phan-biet-chu-hoa-va-chu-thuong-trong-excel.html#Tai_sao_INDEX_MATCH_la_giai_phap_tot_nhat_de_vlookup_phan_biet_chu_hoa_chu_thuong\" title=\"T\u1ea1i sao INDEX\/ MATCH l\u00e0 gi\u1ea3i ph\u00e1p t\u1ed1t nh\u1ea5t \u0111\u1ec3 vlookup ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng\">T\u1ea1i sao INDEX\/ MATCH l\u00e0 gi\u1ea3i ph\u00e1p t\u1ed1t nh\u1ea5t \u0111\u1ec3 vlookup ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Cong_thuc_VLOOKUP_phan_biet_chu_hoa_va_chu_thuong\"><\/span><b>C\u00f4ng th\u1ee9c VLOOKUP ph\u00e2n bi\u1ec7t ch\u1eef hoa v\u00e0 ch\u1eef th\u01b0\u1eddng<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Nh\u01b0 b\u1ea1n \u0111\u00e3 bi\u1ebft, c\u00f4ng th\u1ee9c VLOOKUP th\u00f4ng th\u01b0\u1eddng kh\u00f4ng ph\u00e2n bi\u1ec7t \u0111\u01b0\u1ee3c ch\u1eef hoa ch\u1eef th\u01b0\u1eddng. Tuy nhi\u00ean, c\u00f3 m\u1ed9t c\u00e1ch \u0111\u1ec3 l\u00e0m cho h\u00e0m VLOOKUP trong Excel ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng. B\u1ea1n c\u00f3 th\u1ec3 l\u00e0m n\u00f3 ph\u00f9 h\u1ee3p v\u1edbi t\u1eebng tr\u01b0\u1eddng h\u1ee3p b\u1eb1ng c\u00e1ch th\u00eam c\u1ed9t h\u1ed7 tr\u1ee3 v\u00e0o trang t\u00ednh c\u1ee7a b\u1ea1n, nh\u01b0 minh h\u1ecda trong v\u00ed d\u1ee5 sau.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 \u201cID m\u1eb7t h\u00e0ng\u201d trong c\u1ed9t B v\u00e0 b\u1ea1n mu\u1ed1n l\u1ea5y gi\u00e1 c\u1ee7a m\u1eb7t h\u00e0ng v\u00e0 nh\u1eadn x\u00e9t \u0111\u01b0\u1ee3c li\u00ean k\u1ebft t\u1eeb c\u1ed9t C v\u00e0 D. V\u1ea5n \u0111\u1ec1 l\u00e0 ID m\u1eb7t h\u00e0ng bao g\u1ed3m c\u1ea3 k\u00fd t\u1ef1 ch\u1eef th\u01b0\u1eddng v\u00e0 ch\u1eef hoa. V\u00ed d\u1ee5: c\u00e1c gi\u00e1 tr\u1ecb trong B4 (001Tvci3u) v\u00e0 B5 (001Tvci3U) ch\u1ec9 kh\u00e1c nhau \u1edf ch\u1eef c\u00e1i cu\u1ed1i c\u00f9ng, \u201cu\u201d v\u00e0 \u201cU\u201d t\u01b0\u01a1ng \u1ee9ng:<\/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-192.gif\" title=\"ex101_92019-nho-192\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<figure id=\"attachment_33422\" aria-describedby=\"caption-attachment-33422\" style=\"width: 217px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-33422\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/1-4-cc381ch-vie1babet-hc380m-vlookup-c490e1bb82-phc382n-bie1bb86t-che1bbae-hoa-che1bbae-thc6afe1bb9cng.png\" alt=\"Ph\u00e2n bi\u1ec7t ch\u1eef hoa v\u00e0 ch\u1eef th\u01b0\u1eddng\" width=\"217\" height=\"160\" title=\"1-4-cc381ch-vie1babet-hc380m-vlookup-c490e1bb82-phc382n-bie1bb86t-che1bbae-hoa-che1bbae-thc6afe1bb9cng\"><figcaption id=\"caption-attachment-33422\" class=\"wp-caption-text\">Ph\u00e2n bi\u1ec7t ch\u1eef hoa v\u00e0 ch\u1eef th\u01b0\u1eddng<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">Nh\u01b0 b\u1ea1n c\u00f3 th\u1ec3 \u0111o\u00e1n \u0111\u01b0\u1ee3c, c\u00f4ng th\u1ee9c VLOOKUP th\u00f4ng th\u01b0\u1eddng =VLOOKUP(\u201c001Tvci3U\u201d,$A$2:$C$7,2,FALSE) s\u1ebd t\u00ecm n\u1ea1p $90 \u0111\u01b0\u1ee3c li\u00ean k\u1ebft v\u1edbi \u201c001Tvci3u\u201d v\u00ec n\u00f3 \u0111\u1ee9ng tr\u01b0\u1edbc \u201c001Tvci3U\u2019 trong m\u1ea3ng tra c\u1ee9u. Nh\u1eefng \u0111\u00e2y kh\u00f4ng ph\u1ea3i l\u00e0 nh\u1eefng g\u00ec b\u1ea1n mu\u1ed1n, ph\u1ea3i kh\u00f4ng?<\/span><\/p>\n<figure id=\"attachment_33423\" aria-describedby=\"caption-attachment-33423\" style=\"width: 548px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-33423\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/2-4-cc381ch-vie1babet-hc380m-vlookup-c490e1bb82-phc382n-bie1bb86t-che1bbae-hoa-che1bbae-thc6afe1bb9cng.png\" alt=\"C\u00f4ng th\u1ee9c VLOOKUP ph\u00e2n bi\u1ec7t ch\u1eef hoa v\u00e0 ch\u1eef th\u01b0\u1eddng\" width=\"548\" height=\"205\" title=\"2-4-cc381ch-vie1babet-hc380m-vlookup-c490e1bb82-phc382n-bie1bb86t-che1bbae-hoa-che1bbae-thc6afe1bb9cng\"><figcaption id=\"caption-attachment-33423\" class=\"wp-caption-text\">C\u00f4ng th\u1ee9c VLOOKUP ph\u00e2n bi\u1ec7t ch\u1eef hoa v\u00e0 ch\u1eef th\u01b0\u1eddng<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n h\u00e0m VLOOKUP ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng trong Excel, b\u1ea1n c\u1ea7n th\u00eam m\u1ed9t c\u1ed9t h\u1ed7 tr\u1ee3 v\u00e0 \u0111i\u1ec1n n\u00f3 b\u1eb1ng c\u00f4ng th\u1ee9c b\u00ean d\u01b0\u1edbi (trong \u0111\u00f3 c\u1ed9t B l\u00e0 c\u1ed9t tra c\u1ee9u):<\/span><\/p>\n<p><strong>=CODE(MID(B2,1,1)) &amp; CODE(MID(B2,2,1)) &amp; CODE(MID(B2,3,1)) &amp; CODE(MID(B2,4,1)) &amp; CODE(MID(B2,5,1)) &amp; CODE(MID(B2,6,1)) &amp; CODE(MID(B2,7,1)) &amp; CODE(MID(B2,8,1)) &amp; IFERROR(CODE(MID(B2,9,1)),\u201d\u201d)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c ph\u00e2n t\u00edch c\u00fa ph\u00e1p gi\u00e1 tra c\u1ee9u th\u00e0nh c\u00e1c k\u00fd t\u1ef1 ri\u00eang l\u1ebb, chuy\u1ec3n \u0111\u1ed5i t\u1eebng h\u00e0ng h\u00f3a th\u00e0nh m\u00e3 c\u1ee7a n\u00f3 (v\u00ed d\u1ee5 \u201cA\u201d l\u00e0 65 v\u00e0 \u201ca\u201d l\u00e0 97), sau \u0111\u00f3 n\u1ed1i c\u00e1c m\u00e3 n\u00e0y th\u00e0nh m\u1ed9t chu\u1ed7i s\u1ed1 duy nh\u1ea5t.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Sau \u0111\u00f3, b\u1ea1n s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c VLOOKUP \u0111\u01a1n gi\u1ea3n ph\u00f9 h\u1ee3p v\u1edbi tr\u01b0\u1eddng h\u1ee3p:<\/span><\/p>\n<p><strong>=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)<\/strong><\/p>\n<figure id=\"attachment_33424\" aria-describedby=\"caption-attachment-33424\" style=\"width: 637px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-33424\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/3-4-cc381ch-vie1babet-hc380m-vlookup-c490e1bb82-phc382n-bie1bb86t-che1bbae-hoa-che1bbae-thc6afe1bb9cng.png\" alt=\"S\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c VLOOKUP \u0111\u01a1n gi\u1ea3n\" width=\"637\" height=\"203\" title=\"3-4-cc381ch-vie1babet-hc380m-vlookup-c490e1bb82-phc382n-bie1bb86t-che1bbae-hoa-che1bbae-thc6afe1bb9cng\"><figcaption id=\"caption-attachment-33424\" class=\"wp-caption-text\">S\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c VLOOKUP \u0111\u01a1n gi\u1ea3n<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">Hai \u0111i\u1ec1u sau \u0111\u00e2y l\u00e0 \u0111i\u1ec1u c\u1ea7n thi\u1ebft \u0111\u1ec3 c\u00f4ng th\u1ee9c ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng Vlookup c\u1ee7a b\u1ea1n ho\u1ea1t \u0111\u1ed9ng ch\u00ednh x\u00e1c:<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u1ed9t h\u1ed7 tr\u1ee3 ph\u1ea3i l\u00e0 c\u1ed9t ngo\u00e0i c\u00f9ng b\u00ean tr\u00e1i trong ph\u1ea1m vi tra c\u1ee9u (table_array argument). Gi\u00e1 tr\u1ecb tra c\u1ee9u (lookup_value argument) ph\u1ea3i l\u00e0 \u201cm\u00e3 k\u00fd t\u1ef1\u201d ch\u1ee9 kh\u00f4ng ph\u1ea3i l\u00e0 gi\u00e1 tr\u1ecb th\u1ef1c.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_su_dung_cong_thuc_CODE_mot_cach_chinh_xac\"><\/span><b>C\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c CODE m\u1ed9t c\u00e1ch ch\u00ednh x\u00e1c<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c CODE \u0111\u01b0\u1ee3c sao ch\u00e9p tr\u00ean c\u1ed9t tr\u1ee3 gi\u00fap ngh\u0129a l\u00e0 t\u1ea5t c\u1ea3 c\u00e1c gi\u00e1 tr\u1ecb tra c\u1ee9u c\u00f3 c\u00f9ng k\u00fd t\u1ef1. N\u1ebfu kh\u00f4ng, b\u1ea1n c\u1ea7n bi\u1ebft s\u1ed1 t\u1ed1i thi\u1ec3u v\u00e0 s\u1ed1 t\u1ed1i \u0111a v\u00e0 th\u00eam c\u00e0ng nhi\u1ec1u IFERROR c\u00e0ng nhi\u1ec1u k\u00fd t\u1ef1 s\u1ebd t\u1ea1o s\u1ef1 kh\u00e1c bi\u1ec7t gi\u1eefa gi\u00e1 tr\u1ecb tra c\u1ee9u nh\u1ecf nh\u1ea5t v\u00e0 l\u1edbn nh\u1ea5t.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ed d\u1ee5: n\u1ebfu gi\u00e1 tr\u1ecb tra c\u1ee9u nh\u1ecf nh\u1ea5t c\u00f3 3 k\u00fd t\u1ef1 v\u00e0 l\u1edbn nh\u1ea5t \u2013 5 k\u00fd t\u1ef1, b\u1ea1n h\u00e3y s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c n\u00e0y:<\/span><\/p>\n<p><strong>=CODE(MID(B2,1,1)) &amp; CODE(MID(B2,2,1)) &amp; CODE(MID(B2,3,1)) &amp; IFERROR(CODE(MID(B2,3,1)),\u201d\u201d) &amp; IFERROR(CODE(MID(B2,4,1)),\u201d\u201d)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Trong h\u00e0m MID, b\u1ea1n ch\u1ec9 \u0111\u1ecbnh c\u00e1c tham s\u1ed1 sau:<\/span><\/p>\n<p><span style=\"font-weight: 400\">Tham s\u1ed1 th\u1ee9 nh\u1ea5t (v\u0103n b\u1ea3n) \u2013 \u0111\u00e2y l\u00e0 v\u0103n b\u1ea3n hay tham chi\u1ebfu \u00f4 c\u00f3 ch\u1eef c\u00e1c k\u00fd t\u1ef1 b\u1ea1n mu\u1ed1n tr\u00edch xu\u1ea5t. (B2 trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y)<\/span><\/p>\n<p><span style=\"font-weight: 400\">Tham s\u1ed1 th\u1ee9 2 (start_num) \u2013 v\u1ecb tr\u00ed c\u1ee7a k\u00fd t\u1ef1 \u0111\u1ea7u ti\u00ean b\u1ea1n mu\u1ed1n tr\u00edch xu\u1ea5t. B\u1ea1n nh\u1eadp 1 v\u00e0o h\u00e0m MID \u0111\u1ea7u ti\u00ean, 2 v\u00e0o h\u00e0m th\u1ee9 hai,\u2026<\/span><\/p>\n<p><span style=\"font-weight: 400\">Tham s\u1ed1 th\u1ee9 3 (num_chars) \u2013 ghi r\u00f5 s\u1ed1 k\u00fd t\u1ef1 b\u1ea1n mu\u1ed1n tr\u1ea3 v\u1ec1 t\u1eeb v\u0103n b\u1ea3n. V\u00ec b\u1ea1n lu\u00f4n mu\u1ed1n gi\u00e1 tr\u1ecb 1, b\u1ea1n nh\u1eadp \u201c1\u201d v\u00e0o t\u1ea5t c\u1ea3 c\u00e1c h\u00e0m.<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u1ea1n ch\u1ebf c\u1ee7a h\u00e0m VLOOKUP: H\u00e0m VLOOKUP kh\u00f4ng ph\u1ea3i l\u00e0 c\u00e1ch t\u1ed1t nh\u1ea5t \u0111\u1ec3 th\u1ef1c hi\u1ec7n tra c\u1ee9u ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng trong Excel v\u00ec th\u1ee9 nh\u1ea5t, n\u00f3 y\u00eau c\u1ea7u th\u00eam m\u1ed9t c\u1ed9t h\u1ed7 tr\u1ee3 v\u00e0 th\u1ee9 hai, n\u00f3 ho\u1ea1t \u0111\u1ed9ng t\u1ed1t h\u01a1n tr\u00ean c\u00e1c t\u1eadp d\u1eef li\u1ec7u \u0111\u1ed3ng nh\u1ea5t ho\u1eb7c \u00edt nh\u1ea5t l\u00e0 khi b\u1ea1n bi\u1ebft ch\u00ednh x\u00e1c s\u1ed1 k\u00fd hi\u1ec7u trong c\u00e1c gi\u00e1 tr\u1ecb tra c\u1ee9u. N\u1ebfu \u0111\u00e2y kh\u00f4ng ph\u1ea3i l\u00e0 tr\u01b0\u1eddng h\u1ee3p c\u1ee7a b\u1ea1n, vui l\u00f2ng th\u1eed c\u00e1c gi\u1ea3i ph\u00e1p kh\u00e1c sau \u0111\u00e2y.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cong_thuc_LOOKUP_phu_hop_cho_chu_hoa_chu_thuong\"><\/span><b>C\u00f4ng th\u1ee9c LOOKUP ph\u00f9 h\u1ee3p cho ch\u1eef hoa ch\u1eef th\u01b0\u1eddng<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">H\u00e0m LOOKUP trong Excel r\u1ea5t gi\u1ed1ng h\u00e0m VLOOKUP, tuy nhi\u00ean, c\u00fa ph\u00e1p c\u1ee7a n\u00f3 cho ph\u00e9p b\u1ea1n so s\u00e1nh tr\u01b0\u1eddng h\u1ee3p ph\u00f9 h\u1ee3p m\u00e0 kh\u00f4ng c\u1ea7n th\u00eam c\u1ed9t h\u1ed7 tr\u1ee3. \u0110\u1ec3 l\u00e0m \u0111\u01b0\u1ee3c \u0111i\u1ec1u n\u00e0y, b\u1ea1n ph\u1ea3i s\u1eed d\u1ee5ng LOOKUP c\u00f9ng v\u1edbi h\u00e0m EXACT.<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu ch\u00fang ta l\u1ea5y d\u1eef li\u1ec7u t\u1eeb v\u00ed d\u1ee5 tr\u01b0\u1edbc (kh\u00f4ng c\u00f3 c\u1ed9t h\u1ed7 tr\u1ee3 A v\u1edbi m\u00e3), c\u00f4ng th\u1ee9c Lookup\/ Exact sau s\u1ebd ho\u1ea1t \u0111\u1ed9ng:<\/span><\/p>\n<p><strong>=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c t\u00ecm ki\u1ebfm qua c\u00e1c \u00f4 A2:A7 \u0111\u1ec3 t\u00ecm gi\u00e1 tr\u1ecb ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng ch\u00ednh x\u00e1c trong \u00f4 F2 v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u1eeb c\u1ed9t B trong c\u00f9ng m\u1ed9t h\u00e0ng.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u0169ng nh\u01b0 h\u00e0m VLOOKUP, c\u00e1c h\u00e0m LOOKUP ho\u1ea1t \u0111\u1ed9ng t\u1ed1t cho c\u00e1c gi\u00e1 tr\u1ecb s\u1ed1 v\u00e0 v\u0103n b\u1ea3n v\u00e0 h\u00ecnh b\u00ean d\u01b0\u1edbi \u0111\u01b0\u1ee3c minh h\u1ecda c\u1ee5 th\u1ec3:<\/span><\/p>\n<figure id=\"attachment_33425\" aria-describedby=\"caption-attachment-33425\" style=\"width: 604px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-33425\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/4-4-cc381ch-vie1babet-hc380m-vlookup-c490e1bb82-phc382n-bie1bb86t-che1bbae-hoa-che1bbae-thc6afe1bb9cng.png\" alt=\"C\u00f4ng th\u1ee9c LOOKUP ph\u00f9 h\u1ee3p cho ch\u1eef hoa ch\u1eef th\u01b0\u1eddng\" width=\"604\" height=\"207\" title=\"4-4-cc381ch-vie1babet-hc380m-vlookup-c490e1bb82-phc382n-bie1bb86t-che1bbae-hoa-che1bbae-thc6afe1bb9cng\"><figcaption id=\"caption-attachment-33425\" class=\"wp-caption-text\">C\u00f4ng th\u1ee9c LOOKUP ph\u00f9 h\u1ee3p cho ch\u1eef hoa ch\u1eef th\u01b0\u1eddng<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\"><strong>L\u01b0u \u00fd<\/strong>: \u0110\u1ec3 c\u00f4ng th\u1ee9c LOOKUP ho\u1ea1t \u0111\u1ed9ng ch\u00ednh x\u00e1c, c\u00e1c gi\u00e1 tr\u1ecb trong c\u1ed9t tra c\u1ee9u ph\u1ea3i \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp theo th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n, t\u1ee9c l\u00e0 t\u1eeb nh\u1ecf nh\u1ea5t \u0111\u1ebfn l\u1edbn nh\u1ea5t.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Gi\u1ea3i th\u00edch ng\u1eafn g\u1ecdn vi\u1ec7c s\u1eed d\u1ee5ng h\u00e0m EXACT trong h\u00e0m tr\u00ean:<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00e0m EXACT trong Excel so s\u00e1nh hai gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n trong tham s\u1ed1 th\u1ee9 nh\u1ea5t v\u00e0 th\u1ee9 hai v\u00e0 tr\u1ea3 v\u1ec1 TRUE n\u1ebfu ch\u00fang ho\u00e0n to\u00e0n gi\u1ed1ng nhau, n\u1ebfu kh\u00f4ng th\u00ec FALSE. \u0110i\u1ec1u th\u1ef1c s\u1ef1 quan tr\u1ecdng \u0111\u1ed1i v\u1edbi ch\u00fang ta l\u00e0 EXACT <strong>ph\u00e2n bi\u1ec7t ch\u1eef hoa v\u00e0 ch\u1eef th\u01b0\u1eddng.<\/strong><\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd, h\u00e3y ph\u00e2n tichs c\u00e1ch ho\u1ea1t \u0111\u1ed9ng c\u1ee7a LOOKUP\/ EXACT: =LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00e0m EXACT ki\u1ec3m tra gi\u00e1 tr\u1ecb c\u1ee7a \u00f4 F2 so v\u1edbi t\u1ea5t c\u1ea3 c\u00e1c m\u1ee5c trong ph\u1ea1m vi A2:A7 v\u00e0 n\u1ebfu th\u1ea5y k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p c\u00f3 ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng, tr\u1ea3 v\u1ec1 TRUE v\u00e0 ng\u01b0\u1ee3c l\u1ea1i \u2013 FALSE.\u00a0\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ec v\u1eady b\u1ea1n \u0111\u00e3 nh\u1eadp TRUE trong tham s\u1ed1 \u0111\u1ea7u ti\u00ean (lookup_value) c\u1ee7a h\u00e0m LOOKUP, n\u00f3 s\u1ebd l\u1ea5y m\u1ed9t gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng t\u1eeb c\u1ed9t tr\u1ea3 v\u1ec1 (c\u1ed9t B trong v\u00ed d\u1ee5), ch\u1ec9 khi th\u1ea5y k\u1ebft qu\u1ea3 ch\u1eef hoa v\u00e0 ch\u1eef th\u01b0\u1eddng ph\u00f9 h\u1ee3p.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400\">Hy v\u1ecdng r\u1eb1ng l\u1eddi gi\u1ea3i th\u00edch tr\u00ean c\u00f3 l\u00fd v\u00e0 b\u1ea1n \u0111\u00e3 hi\u1ec3u \u0111\u01b0\u1ee3c \u00fd t\u01b0\u1edfng chung ngay b\u00e2y gi\u1edd. N\u1ebfu b\u1ea1n l\u00e0m v\u1eady, b\u1ea1n s\u1ebd kh\u00f4ng g\u1eb7p b\u1ea5t k\u1ef3 kh\u00f3 kh\u0103n n\u00e0o v\u1edbi c\u00e1c ch\u1ee9c n\u0103ng kh\u00e1c m\u00e0 ch\u00fang ta s\u1ebd t\u00ecm hi\u1ec3u ti\u1ebfp, v\u00ec t\u1ea5t c\u1ea3 ch\u00fang \u0111\u1ec1u d\u1ef1a tr\u00ean c\u00f9ng m\u1ed9t nguy\u00ean t\u1eafc.<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u1ea1n ch\u1ebf c\u1ee7a LOOKUP: y\u00eau c\u1ea7u s\u1eafp x\u1ebfp c\u1ed9t tra c\u1ee9u theo th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"SUMPRODUCT_%E2%80%93_tra_cuu_truong_hop_gia_tri_van_ban_va_tra_ve_so_phu_hop\"><\/span><b>SUMPRODUCT \u2013 tra c\u1ee9u tr\u01b0\u1eddng h\u1ee3p gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n v\u00e0 tr\u1ea3 v\u1ec1 s\u1ed1 ph\u00f9 h\u1ee3p<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Nh\u01b0 b\u1ea1n \u0111\u00e3 bi\u1ebft, SUMPRODUCT l\u00e0 m\u1ed9t h\u00e0m Excel kh\u00e1c c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n tra c\u1ee9u ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng nh\u01b0ng ch\u1ec9 c\u00f3 th\u1ec3 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb s\u1ed1. N\u1ebfu \u0111\u00e2y kh\u00f4ng ph\u1ea3i l\u00e0 tr\u01b0\u1eddng h\u1ee3p c\u1ee7a b\u1ea1n, b\u1ea1n c\u00f3 th\u1ec3 b\u1ecf qua tr\u1ef1c ti\u1ebfp INDEX MATCH cung c\u1ea5p gi\u1ea3i ph\u00e1p th\u00f4ng th\u01b0\u1eddng v\u00e0 l\u00e0m vi\u1ec7c v\u1edbi t\u1ea5t c\u1ea3 c\u00e1c lo\u1ea1i d\u1eef li\u1ec7u.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ea7u ti\u00ean, gi\u1ea3i th\u00edch m\u1ed9t c\u00e1ch ng\u1eafn g\u1ecdn c\u00fa ph\u00e1p c\u1ee7a h\u00e0m n\u00e0y s\u1ebd gi\u00fap b\u1ea1n hi\u1ec3u r\u00f5 h\u01a1n v\u1ec1 h\u00e0m SUMPRODUCT ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng:<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00e0m SUMPRODUCT trong Excel c\u00f3 nhi\u1ec1u th\u00e0nh ph\u1ea7n trong t\u1eebng m\u1ea3ng c\u1ee5 th\u1ec3 v\u00e0 tr\u1ea3 v\u1ec1 t\u1ed5ng s\u1ea3n ph\u1ea9m. C\u00fa ph\u00e1p c\u1ee7a n\u00f3 l\u00e0:<\/span><\/p>\n<p><strong>SUMPRODUCT(array1,array2,array3, \u2026)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">V\u00ec ch\u00fang ta mu\u1ed1n ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng khi tra c\u1ee9u, ch\u00fang ta s\u1eed d\u1ee5ng h\u00e0m EXACT t\u1eeb v\u00ed d\u1ee5 tr\u01b0\u1edbc l\u00e0m m\u1ed9t trong c\u00e1c s\u1ed1 nh\u00e2n:<\/span><\/p>\n<p><strong>=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Nh\u01b0 b\u1ea1n \u0111\u00e3 bi\u1ebft, h\u00e0m EXACT so s\u00e1nh gi\u00e1 tr\u1ecb trong \u00f4 F2 v\u1edbi t\u1ea5t c\u1ea3 c\u00e1c m\u1ee5c trong c\u1ed9t \u00c2 v\u00e0 n\u1ebfu t\u00ecm th\u1ea5y k\u1ebft qu\u1ea3 kh\u1edbp ch\u00ednh x\u00e1c c\u00f3 ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng, th\u00ec tr\u1ea3 v\u1ec1 TRUE, ng\u01b0\u1ee3c l\u1ea1i l\u00e0 FALSE. Trong c\u00e1c ph\u00e9p to\u00e1n, Excel coi TRUE l\u00e0 \u201c1\u201d v\u00e0 FALSE l\u00e0 :0\u201d, do \u0111\u00f3, h\u00e0m SUMPRODUCT nh\u00e2n c\u00e1c s\u1ed1 tr\u1ea3 v\u1ec1 t\u00ednh t\u1ed5ng c\u00e1c t\u00edch.<\/span><\/p>\n<p><span style=\"font-weight: 400\">0 kh\u00f4ng t\u00ednh, b\u1edfi v\u00ec 0 lu\u00f4n t\u1ea1o ra \u201c0\u201d b\u1ea5t k\u1ef3 s\u1ed1 n\u00e0o m\u00e0 n\u00f3 nh\u00e2n v\u1edbi. V\u00ec v\u1eady, ch\u00fang ta h\u00e3y xem x\u00e9t k\u1ef9 h\u01a1n \u0111i\u1ec1u g\u00ec s\u1ebd x\u1ea3y ra khi t\u00ecm th\u1ea5y k\u1ebft qu\u1ea3 kh\u1edbp ch\u00ednh x\u00e1c trong c\u1ed9t A v\u00e0 \u201c1\u201d \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1. H\u00e0m SUMPRODUCT nh\u00e2n 1 v\u1edbi m\u1ed9t s\u1ed1 trong c\u1ed9t B trong c\u00f9ng m\u1ed9t h\u00e0ng v\u00e0 tr\u1ea3 v\u1ec1 ch\u00ednh x\u00e1c s\u1ed1 n\u00e0y. \u0110i\u1ec1u n\u00e0y l\u00e0 do k\u1ebft qu\u1ea3 c\u1ee7a c\u00e1c ph\u00e9p nh\u00e2n kh\u00e1c 0, kh\u00f4ng \u1ea3nh h\u01b0\u1edfng \u0111\u1ebfn gi\u00e1 tr\u1ecb tr\u1ea3 v\u1ec1 theo b\u1ea5t k\u1ef3 c\u00e1ch n\u00e0o.<\/span><\/p>\n<p><span style=\"font-weight: 400\">R\u1ea5t ti\u1ebfc, h\u00e0m SUMPRODUCT kh\u00f4ng th\u1ec3 x\u1eed l\u00fd c\u00e1c gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n v\u00e0 ng\u00e0y th\u00e1ng v\u00ec ch\u00fang kh\u00f4ng th\u1ec3 \u0111\u01b0\u1ee3c nh\u00e2n l\u00ean. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, b\u1ea1n s\u1ebd \u0111\u01b0\u1ee3c th\u00f4ng b\u00e1o l\u1ed7i #VALUE! th\u00f4ng b\u00e1o l\u1ed7i nh\u01b0 trong \u00f4 F4:<\/span><\/p>\n<figure id=\"attachment_33426\" aria-describedby=\"caption-attachment-33426\" style=\"width: 610px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-33426\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/5-4-cc381ch-vie1babet-hc380m-vlookup-c490e1bb82-phc382n-bie1bb86t-che1bbae-hoa-che1bbae-thc6afe1bb9cng.png\" alt=\"SUMPRODUCT - tra c\u1ee9u tr\u01b0\u1eddng h\u1ee3p gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n\" width=\"610\" height=\"204\" title=\"5-4-cc381ch-vie1babet-hc380m-vlookup-c490e1bb82-phc382n-bie1bb86t-che1bbae-hoa-che1bbae-thc6afe1bb9cng\"><figcaption id=\"caption-attachment-33426\" class=\"wp-caption-text\">SUMPRODUCT \u2013 tra c\u1ee9u tr\u01b0\u1eddng h\u1ee3p gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">H\u1ea1n ch\u1ebf c\u1ee7a h\u00e0m SUMPRODUCT: ch\u1ec9 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb s\u1ed1.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"MATCH_INDEX_%E2%80%93_tra_cuu_chu_hoa_chu_thuong_cho_tat_ca_cac_loai_du_lieu\"><\/span><b>MATCH\/ INDEX \u2013 tra c\u1ee9u ch\u1eef hoa ch\u1eef th\u01b0\u1eddng cho t\u1ea5t c\u1ea3 c\u00e1c lo\u1ea1i d\u1eef li\u1ec7u<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Cu\u1ed1i c\u00f9ng, c\u00f4ng th\u1ee9c ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng kh\u00f4ng gi\u1edbi h\u1ea1n \u0111\u00e3 xu\u1ea5t hi\u1ec7n, ho\u1ea1t \u0111\u1ed9ng tr\u00ean t\u1ea5t c\u1ea3 c\u00e1c t\u1eadp d\u1eef li\u1ec7u.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 v\u00ed d\u1ee5 \u0111\u01b0\u1ee3c v\u1eadn d\u1ee5ng t\u1eeb nh\u1eefng v\u00ed d\u1ee5 tr\u01b0\u1edbc m\u00e0 b\u1ea1n \u0111\u00e3 xem qua v\u00e0 d\u1ec5 d\u00e0ng gi\u00fap b\u1ea1n hi\u1ec3u c\u00f4ng th\u1ee9c MATCH\/ INDEX ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Nh\u01b0 b\u1ea1n \u0111\u00e3 bi\u1ebft, s\u1ef1 k\u1ebft h\u1ee3p c\u1ee7a INDEX v\u00e0 MATCH \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng trong Excel nh\u01b0 m\u1ed9t s\u1ef1 thay th\u1ebf linh ho\u1ea1t v\u00e0 ti\u1ec7n l\u1ee3i h\u01a1n l\u00e0m VLOOKUP.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 nh\u1eefng \u0111i\u1ec3m ch\u00ednh b\u1ea1n c\u1ea7n l\u01b0u \u00fd:<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00e0m MATCH t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb tra c\u1ee9u trong m\u1ed9t ph\u1ea1m vi \u0111\u01b0\u1ee3c khoanh v\u00f9ng (lookup_array) v\u00e0 tr\u1ea3 v\u1ec1 v\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a n\u00f3 trong m\u1ea3ng. V\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a gi\u00e1 tr\u1ecb tra c\u1ee9u chuy\u1ec3n tr\u1ef1c ti\u1ebfp \u0111\u1ebfn tham s\u1ed1 row_num c\u1ee7a h\u00e0m INDEX cho ph\u00e9p n\u00f3 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u1eeb h\u00e0ng \u0111\u00f3. \u0110\u1ec3 c\u00f4ng th\u1ee9c c\u00f3 th\u1ec3 ph\u00e2n bi\u1ec7t \u0111\u01b0\u1ee3c, b\u1ea1n c\u1ea7n th\u00eam m\u1ed9t h\u00e0m n\u1eefa v\u00e0o t\u1ed5 h\u1ee3p INDEX\/ MATCH. Nh\u01b0 b\u1ea1n c\u00f3 th\u1ec3 d\u1ec5 d\u00e0ng \u0111o\u00e1n \u0111\u01b0\u1ee3c, b\u1ea1n s\u1eed d\u1ee5ng <strong>h\u00e0m EXACT<\/strong> m\u1ed9t l\u1ea7n n\u1eefa:<\/span><\/p>\n<p><strong>=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Tuy nhi\u00ean, trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, ch\u00fang ta c\u1ea7n m\u1ed9t c\u00f4ng th\u1ee9c m\u1ea3ng \u0111\u1ec3 n\u00f3 ho\u1ea1t \u0111\u1ed9ng, v\u00ec v\u1eady h\u00e3y nh\u1edb nh\u1ea5n t\u1ed5 h\u1ee3p ph\u00edm <strong>Ctrl + Shift + Enter<\/strong> \u0111\u1ec3 ti\u1ebfn h\u00e0nh c\u00f4ng th\u1ee9c. N\u1ebfu \u0111\u01b0\u1ee3c th\u1ef1c hi\u1ec7n \u0111\u00fang, c\u00f4ng th\u1ee9c s\u1ebd \u0111\u01b0\u1ee3c \u0111\u1eb7t trong d\u1ea5u ngo\u1eb7c nh\u1ecdn, nh\u01b0 th\u1ec3 hi\u1ec7n trong h\u00ecnh minh h\u1ecda b\u00ean d\u01b0\u1edbi:<\/span><\/p>\n<figure id=\"attachment_33427\" aria-describedby=\"caption-attachment-33427\" style=\"width: 646px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-33427\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/6-4-cc381ch-vie1babet-hc380m-vlookup-c490e1bb82-phc382n-bie1bb86t-che1bbae-hoa-che1bbae-thc6afe1bb9cng.png\" alt=\"MATCH\/ INDEX - tra c\u1ee9u ch\u1eef hoa ch\u1eef th\u01b0\u1eddng cho t\u1ea5t c\u1ea3 c\u00e1c lo\u1ea1i d\u1eef li\u1ec7u\" width=\"646\" height=\"207\" title=\"6-4-cc381ch-vie1babet-hc380m-vlookup-c490e1bb82-phc382n-bie1bb86t-che1bbae-hoa-che1bbae-thc6afe1bb9cng\"><figcaption id=\"caption-attachment-33427\" class=\"wp-caption-text\">MATCH\/ INDEX \u2013 tra c\u1ee9u ch\u1eef hoa ch\u1eef th\u01b0\u1eddng cho t\u1ea5t c\u1ea3 c\u00e1c lo\u1ea1i d\u1eef li\u1ec7u<\/figcaption><\/figure>\n<h2><span class=\"ez-toc-section\" id=\"Tai_sao_INDEX_MATCH_la_giai_phap_tot_nhat_de_vlookup_phan_biet_chu_hoa_chu_thuong\"><\/span><b>T\u1ea1i sao INDEX\/ MATCH l\u00e0 gi\u1ea3i ph\u00e1p t\u1ed1t nh\u1ea5t \u0111\u1ec3 vlookup ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Nh\u1eefng \u01b0u \u0111i\u1ec3m ch\u00ednh c\u1ee7a vi\u1ec7c s\u1eed d\u1ee5ng INDEX\/MATCh l\u00e0:<\/span><\/p>\n<ul>\n<li><span style=\"font-weight: 400\">N\u00f3 kh\u00f4ng y\u00eau c\u1ea7u th\u00eam c\u1ed9t h\u1ed7 tr\u1ee3 nh\u01b0 h\u00e0m VLOOKUP.<\/span><\/li>\n<li><span style=\"font-weight: 400\">N\u00f3 kh\u00f4ng y\u00eau c\u1ea7u s\u1eafp x\u1ebfp m\u1ed9t c\u1ed9t tra c\u1ee9u nh\u01b0 h\u00e0m LOOKUP.<\/span><\/li>\n<li><span style=\"font-weight: 400\">N\u00f3 ho\u1ea1t \u0111\u1ed9ng v\u1edbi t\u1ea5t c\u1ea3 c\u00e1c lo\u1ea1i d\u1eef li\u1ec7u \u2013 s\u1ed1, v\u0103n b\u1ea3n v\u00e0 ng\u00e0y th\u00e1ng.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c INDEX\/ MATCH ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng m\u1ed9t c\u00e1ch d\u1ec5 d\u00e0ng nh\u1ea5t.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Gi\u1ea3 s\u1eed, m\u1ed9t \u00f4 trong c\u1ed9t tr\u1ea3 v\u1ec1 t\u01b0\u01a1ng \u1ee9ng v\u1edbi gi\u00e1 tr\u1ecb tra c\u1ee9u l\u00e0 tr\u1ed1ng. C\u00f4ng th\u1ee9c s\u1ebd tr\u1ea3 v\u1ec1 g\u00ec? Kh\u00f4ng c\u00f3 g\u00ec. V\u00e0 b\u00e2y gi\u1edd, h\u00e3y xem nh\u1eefng g\u00ec n\u00f3 th\u1ef1c s\u1ef1 tr\u1ea3 v\u1ec1:<\/span><\/p>\n<p><span style=\"font-weight: 400\">R\u1ea5t ti\u1ebfc, c\u00f4ng th\u1ee9c tr\u1ea3 v\u1ec1 s\u1ed1 0. C\u00f3 th\u1ec3, \u0111\u00e2y kh\u00f4ng ph\u1ea3i l\u00e0 v\u1ea5n \u0111\u1ec1 l\u1edbn n\u1ebfu b\u1ea1n ch\u1ec9 l\u00e0m vi\u1ec7c v\u1edbi gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n. Tuy nhi\u00ean, n\u1ebfu trang t\u00ednh c\u1ee7a b\u1ea1n ch\u1ee9a c\u00e1c s\u1ed1 v\u00e0 m\u1ed9t s\u1ed1 trong \u0111\u00f3 l\u00e0 s\u1ed1 0 th\u00ec \u0111\u00e2y l\u00e0 m\u1ed9t v\u1ea5n \u0111\u1ec1.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Tr\u00ean th\u1ef1c t\u1ebf, t\u1ea5t c\u1ea3 c\u00e1c c\u00f4ng th\u1ee9c tra c\u1ee9u kh\u00e1c (VLOOKUP, LOOKUP v\u00e0 SUMPRODUCT) m\u00e0 ch\u00fang ta \u0111\u00e3 th\u1ea3o lu\u1eadn tr\u01b0\u1edbc \u0111\u00f3 ho\u1ea1t \u0111\u1ed9ng theo c\u00f9ng m\u1ed9t c\u00e1ch. Nh\u01b0ng b\u00e2y gi\u1edd b\u1ea1n mu\u1ed1n m\u1ed9t c\u00f4ng th\u1ee9c t\u1ed1i \u01b0u nh\u1ea5t c\u00f3 th\u1ec3, ph\u1ea3i kh\u00f4ng?<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 l\u00e0m cho c\u00f4ng th\u1ee9c INDEX\/ MATCH ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng m\u1ed9t c\u00e1ch ch\u00ednh x\u00e1c nh\u1ea5t, b\u1ea1n c\u1ea7n \u0111\u1eb7t n\u00f3 v\u00e0o h\u00e0m IF \u0111\u1ec3 ki\u1ec3m tra xem \u00f4 tr\u1ea3 v\u1ec1\u00a0 c\u00f3 tr\u1ed1ng kh\u00f4ng v\u00e0 kh\u00f4ng tr\u1ea3 v\u1ec1 g\u00ec trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y:<\/span><\/p>\n<p><strong>=IF(INDIRECT(\u201cB\u201d&amp;(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))&lt;&gt;\u201d\u201d,INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),\u201d\u201d)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Trong c\u00f4ng th\u1ee9c tr\u00ean:<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u201cB\u201d l\u00e0 c\u1ed9t tr\u1ea3 v\u1ec1<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u201c1+\u201d l\u00e0 s\u1ed1 bi\u1ebfn v\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a \u00f4 do h\u00e0m MATCH tr\u1ea3 v\u1ec1. V\u00ed d\u1ee5: m\u1ea3ng tra c\u1ee9u trong h\u00e0m MATCH l\u00e0 A2:A7 c\u00f3 ngh\u0129a l\u00e0 v\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a \u00f4 A2 l\u00e0 \u201c1\u201d, v\u00ec \u0111\u00e2y l\u00e0 \u00f4 \u0111\u1ea7u ti\u00ean trong m\u1ea3ng. Nh\u01b0ng v\u1ecb tr\u00ed th\u1ef1c c\u1ee7a \u00f4 A2 trong c\u1ed9t \u201c2\u201d, v\u00ec v\u1eady ch\u00fang ta th\u00eam 1 \u0111\u1ec3 b\u00f9 ch\u00eanh l\u1ec7ch, cho <a href=\"http:\/\/blog.ezworkapp.com\/cach-su-dung-ham-indirect-trong-excel-cong-thuc-va-vi-du-minh-hoa.html\"><strong>h\u00e0m INDIRECT<\/strong> <\/a>tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u1eeb \u00f4 b\u00ean ph\u1ea3i.<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00ecnh \u1ea3nh minh h\u1ecda b\u00ean d\u01b0\u1edbi ch\u1ee9ng minh h\u00e0m INDEX\/ MATCH ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng \u0111\u01b0\u1ee3c \u0111i\u1ec1u ch\u1ec9nh. C\u00f4ng th\u1ee9c \u0111\u01b0\u1ee3c vi\u1ebft l\u1ea1i cho c\u00e1c c\u1ed9t B:D \u0111\u1ec3 thanh c\u00f4ng th\u1ee9c c\u00f3 th\u1ec3\u00a0 v\u1eeba v\u1edbi khung h\u00ecnh.<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00e0m INDEX\/ MATCH kh\u00f4ng tr\u1ea3 v\u1ec1 g\u00ec n\u1ebfu \u00f4 tr\u1ea3 v\u1ec1 tr\u1ed1ng:<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00e0m tr\u1ea3 v\u1ec1 \u201c0\u201d n\u1ebfu n\u1ebfu \u00f4 tr\u1ea3 v\u1ec1 l\u00e0 0:<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n mu\u1ed1n h\u00e0m INDEX\/ MATCH hi\u1ec3n th\u1ecb m\u1ed9t s\u1ed1 th\u00f4ng b\u00e1o khi gi\u00e1 tr\u1ecb tr\u1ea3 v\u1ec1 tr\u1ed1ng, b\u1ea1n c\u00f3 th\u1ec3 vi\u1ebft th\u00eam n\u1ed9i dung gi\u1eefa c\u00e1c d\u1ea5u ngo\u1eb7c k\u00e9p (\u201c\u201d) trong \u1edf cu\u1ed1i c\u00f4ng th\u1ee9c, nh\u01b0 sau:<\/span><\/p>\n<p><strong>=IF(INDIRECT(\u201cD\u201d&amp;(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))&lt;&gt;\u201d\u201d,INDEX($D$2:$D$7,MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),\u201d There is nothing to return, sorry.\u201d)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 c\u00e1ch tra c\u1ee9u trong Excel c\u00f3 t\u00ednh \u0111\u1ebfn tr\u01b0\u1eddng h\u1ee3p v\u0103n b\u1ea3n.<strong> H\u1ecdc Excel Online<\/strong> c\u1ea3m \u01a1n b\u1ea1n \u0111\u00e3 \u0111\u1ed9c v\u00e0 hy v\u1ecdng s\u1ebd g\u1eb7p b\u1ea1n trong nh\u1eefng blog ti\u1ebfp theo v\u00e0o tu\u1ea7n t\u1edbi!<\/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-192.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":2482,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-2481","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\/2481","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=2481"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/2481\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/2482"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=2481"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=2481"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=2481"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}