{"id":2916,"date":"2024-12-02T09:27:16","date_gmt":"2024-12-02T09:27:16","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/bi-kip-vo-lam-1-index-match-tim-kiem-nhieu-dieu-kien-va-tra-ve-nhieu-ket-qua\/"},"modified":"2024-12-02T09:27:16","modified_gmt":"2024-12-02T09:27:16","slug":"bi-kip-vo-lam-1-index-match-tim-kiem-nhieu-dieu-kien-va-tra-ve-nhieu-ket-qua","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/bi-kip-vo-lam-1-index-match-tim-kiem-nhieu-dieu-kien-va-tra-ve-nhieu-ket-qua\/","title":{"rendered":"B\u00ed k\u00edp v\u00f5 l\u00e2m 1: INDEX\/MATCH \u2013 T\u00ecm ki\u1ebfm nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n, v\u00e0 tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u1ebft qu\u1ea3"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em><a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/a> \u0111i s\u00e2u v\u00e0o c\u00e1ch s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p <strong>h\u00e0m index v\u00e0 match<\/strong>. Cho b\u1ea1n kh\u1ea3 n\u0103ng t\u00ecm ki\u1ebfm nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n c\u0169ng nh\u01b0 tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u1ebft qu\u1ea3<\/em><\/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-674d7d7378748\" 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-3\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/indexmatch-tim-kiem-nhieu-dieu-kien-va-tra-ve-nhieu-ket-qua.html#INDEXMATCH_dung_nhu_the_nao\" title=\"INDEX\/MATCH d\u00f9ng nh\u01b0 th\u1ebf n\u00e0o?\">INDEX\/MATCH d\u00f9ng nh\u01b0 th\u1ebf n\u00e0o?<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/indexmatch-tim-kiem-nhieu-dieu-kien-va-tra-ve-nhieu-ket-qua.html#Vi_sao_lai_noi_dung_INDEXMATCH_de_hon_VLOOKUP\" title=\"V\u00ec sao l\u1ea1i n\u00f3i d\u00f9ng INDEX\/MATCH d\u1ec5 h\u01a1n VLOOKUP?\">V\u00ec sao l\u1ea1i n\u00f3i d\u00f9ng INDEX\/MATCH d\u1ec5 h\u01a1n VLOOKUP?<\/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\/indexmatch-tim-kiem-nhieu-dieu-kien-va-tra-ve-nhieu-ket-qua.html#Dung_VLOOKUPCHOOSE_hoac_INDEXMATCH_tim_theo_nhieu_dieu_kien\" title=\"D\u00f9ng VLOOKUP\/CHOOSE ho\u1eb7c INDEX\/MATCH t\u00ecm theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n\">D\u00f9ng VLOOKUP\/CHOOSE ho\u1eb7c INDEX\/MATCH t\u00ecm theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/indexmatch-tim-kiem-nhieu-dieu-kien-va-tra-ve-nhieu-ket-qua.html#Tai_sao_lai_MATCH_luc_tim_1_luc_lai_la_TRUE_va_luc_nao_phai_nhan_CTRLSHIFTENTER\" title=\"T\u1ea1i sao l\u1ea1i MATCH l\u00fac t\u00ecm 1, l\u00fac l\u1ea1i l\u00e0 TRUE v\u00e0 l\u00fac n\u00e0o ph\u1ea3i nh\u1ea5n CTRL+SHIFT+ENTER?\">T\u1ea1i sao l\u1ea1i MATCH l\u00fac t\u00ecm 1, l\u00fac l\u1ea1i l\u00e0 TRUE v\u00e0 l\u00fac n\u00e0o ph\u1ea3i nh\u1ea5n CTRL+SHIFT+ENTER?<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/indexmatch-tim-kiem-nhieu-dieu-kien-va-tra-ve-nhieu-ket-qua.html#Dung_INDEXMATCH_ket_hop_INDEX_de_chuyen_doi_cong_thuc_mang_thanh_cong_thuc_binh_thuong\" title=\"D\u00f9ng INDEX\/MATCH k\u1ebft h\u1ee3p INDEX \u0111\u1ec3 chuy\u1ec3n \u0111\u1ed5i c\u00f4ng th\u1ee9c m\u1ea3ng th\u00e0nh c\u00f4ng th\u1ee9c b\u00ecnh th\u01b0\u1eddng.\">D\u00f9ng INDEX\/MATCH k\u1ebft h\u1ee3p INDEX \u0111\u1ec3 chuy\u1ec3n \u0111\u1ed5i c\u00f4ng th\u1ee9c m\u1ea3ng th\u00e0nh c\u00f4ng th\u1ee9c b\u00ecnh th\u01b0\u1eddng.<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/indexmatch-tim-kiem-nhieu-dieu-kien-va-tra-ve-nhieu-ket-qua.html#Lam_bao_cao_chi_tiet_trich_loc_hoa_don_voi_INDEX_ket_hop_COUNTIFS_Ket_qua_tra_ve_nhieu_ket_qua_tu_mot_hoac_nhieu_dieu_kien\" title=\"L\u00e0m b\u00e1o c\u00e1o chi ti\u1ebft, tr\u00edch l\u1ecdc h\u00f3a \u0111\u01a1n v\u1edbi INDEX k\u1ebft h\u1ee3p COUNTIFS. K\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u1ebft qu\u1ea3 t\u1eeb m\u1ed9t ho\u1eb7c nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n.\">L\u00e0m b\u00e1o c\u00e1o chi ti\u1ebft, tr\u00edch l\u1ecdc h\u00f3a \u0111\u01a1n v\u1edbi INDEX k\u1ebft h\u1ee3p COUNTIFS. K\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u1ebft qu\u1ea3 t\u1eeb m\u1ed9t ho\u1eb7c nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n.<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blog.hocexcel.online\/indexmatch-tim-kiem-nhieu-dieu-kien-va-tra-ve-nhieu-ket-qua.html#Lam_the_nao_de_in_phieu_xuat_kho_co_nhieu_san_pham_Cung_mot_phieu_co_nhieu_ma_san_pham_khac_nhau\" title=\"L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 in phi\u1ebfu xu\u1ea5t kho c\u00f3 nhi\u1ec1u s\u1ea3n ph\u1ea9m? C\u00f9ng m\u1ed9t phi\u1ebfu, c\u00f3 nhi\u1ec1u m\u00e3 s\u1ea3n ph\u1ea9m kh\u00e1c nhau\">L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 in phi\u1ebfu xu\u1ea5t kho c\u00f3 nhi\u1ec1u s\u1ea3n ph\u1ea9m? C\u00f9ng m\u1ed9t phi\u1ebfu, c\u00f3 nhi\u1ec1u m\u00e3 s\u1ea3n ph\u1ea9m kh\u00e1c nhau<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"INDEXMATCH_dung_nhu_the_nao\"><\/span>INDEX\/MATCH d\u00f9ng nh\u01b0 th\u1ebf n\u00e0o?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ul>\n<li>C\u00fa ph\u00e1p th\u01b0\u1eddng g\u1eb7p, hay d\u00f9ng v\u1edbi VLOOKUP:<\/li>\n<\/ul>\n<p>N\u1ebfu v\u1edbi VLOOKUP, ta c\u00f3 c\u00f4ng th\u1ee9c nh\u01b0 sau:<br \/>\n<strong>=VLOOKUP(Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm, V\u00f9ng d\u1eef li\u1ec7u [c\u00f3: C\u1ed9t d\u00f2 t\u00ecm + C\u1ed9t k\u1ebft qu\u1ea3], Th\u1ee9 t\u1ef1 c\u1ed9t tr\u1ea3 v\u1ec1, T\u00ecm ch\u00ednh x\u00e1c\/g\u1ea7n \u0111\u00fang)<\/strong><\/p>\n<p>Ta c\u00f3 v\u00ed d\u1ee5 sau, v\u1edbi h\u00e0m VLOOKUP c\u00e1c b\u1ea1n l\u01b0u \u00fd c\u00e1c v\u1ea5n \u0111\u1ec1 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-24287 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-tim-kiem-index-match.jpg\" width=\"679\" height=\"305\" title=\"vlookup-tim-kiem-index-match\"><\/p>\n<ul>\n<li>Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm l\u00e0 \u201c<strong>Hu\u1ef3nh V\u0103n V\u00ea<\/strong>\u201c, c\u00f3 trong c\u1ed9t v\u00f9ng t\u1eeb <strong>A1:A8<\/strong>, v\u00e0 c\u1ed9t ch\u1ee9a \u0111\u00f3 lu\u00f4n n\u1eb1m b\u00ean tr\u00e1i ngo\u00e0i c\u00f9ng v\u00f9ng d\u1eef li\u1ec7u (A1:C8).<\/li>\n<li>V\u00f9ng d\u1eef li\u1ec7u: A1:C8, khi c\u00e1c b\u1ea1n qu\u00e9t v\u00f9ng d\u1eef li\u1ec7u lu\u00f4n lu\u00f4n nh\u1edb, ch\u00fang ta s\u1ebd ph\u1ea3i c\u1ed1 \u0111\u1ecbnh b\u1eb1ng ph\u00edm F4, tr\u01b0\u1edbc khi l\u00e0m b\u1ea5t k\u1ef3 vi\u1ec7c g\u00ec ti\u1ebfp theo: $A$1:$C$8. V\u00ec sao? V\u00ec khi ch\u00fang ta k\u00e9o c\u00f4ng th\u1ee9c, v\u00f9ng tham chi\u1ebfu d\u1eef li\u1ec7u s\u1ebd lu\u00f4n \u0111\u01b0\u1ee3c c\u1ed1 \u0111\u1ecbnh.<\/li>\n<li>C\u1ed9t tr\u1ea3 v\u1ec1, ta \u0111\u1ebfm theo th\u1ee9 t\u1ef1 t\u1eeb tr\u00e1i sang ph\u1ea3i, t\u00ednh t\u1eeb c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm. \u1ede v\u00ed d\u1ee5 b\u00ean d\u01b0\u1edbi l\u00e0 c\u1ed9t th\u1ee9 3.<\/li>\n<li>Lu\u00f4n lu\u00f4n nh\u1edb, nh\u1eadp s\u1ed1 0 cu\u1ed1i c\u00f9ng \u1edf h\u00e0m VLOOKUP v\u00e0 h\u00e0m MATCH. T\u1ea1i sao? V\u00ec s\u1ed1 0 t\u01b0\u01a1ng \u1ee9ng v\u1edbi FALSE, l\u00e0 t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c. Lu\u00f4n lu\u00f4n t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c. T\u1ea1i sao kh\u00f4ng t\u00ecm t\u01b0\u01a1ng \u0111\u1ed1i? C\u00f3 m\u1ed9t s\u1ed1 tr\u01b0\u1eddng h\u1ee3p ch\u00fang ta s\u1ebd t\u00ecm t\u01b0\u01a1ng \u0111\u1ed1i, g\u1ea7n \u0111\u00fang, nh\u01b0ng \u0111\u00f3 l\u00e0 v\u00e0i tr\u01b0\u1eddng h\u1ee3p khi b\u1ea1n \u0111\u00e3 n\u1eafm r\u00f5.<\/li>\n<\/ul>\n<p>Xem th\u00eam: <strong>S\u1eed d\u1ee5ng h\u00e0m index v\u00e0 <a href=\"http:\/\/blog.ezworkapp.com\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html\">h\u00e0m match nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/a><\/strong><\/p>\n<p>V\u00e0 l\u00fac n\u00e0y ch\u00fang ta c\u00f3 c\u00f4ng th\u1ee9c: <strong>=VLOOKUP(E2, $A$1:$C$8, 3, 0)<\/strong>. V\u1edbi E2 l\u00e0 gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm, trong v\u00f9ng d\u1eef li\u1ec7u t\u1eeb A1:C8, v\u00e0 d\u1ea5u $ mang \u00fd ngh\u0129a c\u1ed1 \u0111\u1ecbnh v\u00f9ng d\u1eef li\u1ec7u t\u00ecm ki\u1ebfm \u0111\u1ec3 khi k\u00e9o c\u00f4ng th\u1ee9c v\u00f9ng s\u1ebd c\u1ed1 \u0111\u1ecbnh. C\u1ed9t d\u1eef li\u1ec7u tr\u1ea3 v\u1ec1 l\u00e0 c\u1ed9t th\u1ee9 3, t\u00ednh t\u1eeb v\u1ecb tr\u00ed \u0111\u1ebfm t\u1eeb c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm sang b\u00ean ph\u1ea3i. V\u00e0 s\u1ed1 0, l\u00e0 t\u00ecm ch\u00ednh x\u00e1c, lu\u00f4n lu\u00f4n l\u00e0 s\u1ed1 0.<\/p>\n<p>V\u1eady v\u1edbi INDEX th\u00ec s\u1ebd thay \u0111\u1ed5i nh\u01b0 th\u1ebf n\u00e0o:<br \/>\n<strong>=INDEX(C\u1ed9t k\u1ebft qu\u1ea3, MATCH(Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm, C\u1ed9t d\u00f2 t\u00ecm ch\u1ee9a gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm, T\u00ecm ch\u00ednh x\u00e1c\/g\u1ea7n \u0111\u00fang)<\/strong><\/p>\n<div class=\"code-block code-block-3\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<p>D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n<p><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-222.gif\" title=\"ex101_92019-nho-222\"><br \/>\n<\/a>\n<\/div>\n<\/div>\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-92.gif\" title=\"vba101_92019-nho-92\"><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-222.gif\" title=\"ex101_92019-nho-222\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<figure id=\"attachment_24290\" aria-describedby=\"caption-attachment-24290\" style=\"width: 729px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/11\/index-match-tim-kiem-theo-mot-dieu-kien.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-24290 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/index-match-tim-kiem-theo-mot-dieu-kien.jpg\" width=\"729\" height=\"305\" title=\"index-match-tim-kiem-theo-mot-dieu-kien\"><\/a><figcaption id=\"caption-attachment-24290\" class=\"wp-caption-text\">V\u00ed d\u1ee5 1: VLOOKUP vs INDEX\/MATCH<\/figcaption><\/figure>\n<p>L\u00fac n\u00e0y c\u00e1c b\u1ea1n s\u1ebd th\u1ea5y h\u00e0m c\u00f3 c\u00fa ph\u00e1p nh\u01b0 sau: =INDEX(V\u00f9ng k\u1ebft qu\u1ea3, D\u00f2ng, C\u1ed9t).<\/p>\n<ul>\n<li>V\u00f9ng k\u1ebft qu\u1ea3: <strong>$C$1:$C$8<\/strong>, kh\u00e1c v\u1edbi v\u00f9ng d\u1eef li\u1ec7u c\u1ee7a h\u00e0m VLOOKUP, l\u00fac n\u00e0y ta ch\u1ec9 ch\u1ecdn m\u1ed7i v\u00f9ng d\u1eef li\u1ec7u c\u1ed9t \u0110i\u1ec3m thi thay v\u00ec c\u1ea3 c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm.<\/li>\n<li>H\u00e0m MATCH(Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm, V\u00f9ng t\u00ecm ki\u1ebfm, T\u00ecm ch\u00ednh x\u00e1c\/t\u01b0\u01a1ng \u0111\u1ed1i). L\u00fac n\u00e0y E3\u00a0l\u00e0 gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm, v\u00e0 v\u00f9ng t\u00ecm ki\u1ebfm ch\u1ec9 l\u00e0 \u0111\u00fang v\u00f9ng c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm: A1:A8, t\u01b0\u01a1ng t\u1ef1 VLOOKUP, v\u00f9ng t\u00ecm ki\u1ebfm lu\u00f4n ph\u1ea3i c\u1ed1 \u0111\u1ecbnh v\u00f9ng $A$1:$A$8. V\u00e0 lu\u00f4n lu\u00f4n t\u00ecm ch\u00ednh x\u00e1c, l\u00e0 s\u1ed1 0 ho\u1eb7c FALSE.<\/li>\n<li>V\u1edbi c\u00f4ng th\u1ee9c tr\u00ean, ta th\u1ea5y h\u00e0m MATCH s\u1ebd tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb l\u00e0 2, t\u01b0\u01a1ng \u1ee9ng d\u00f2ng t\u00ecm th\u1ea5y t\u1eeb tr\u00ean xu\u1ed1ng. INDEX($C$1:$C$8, 2) =&gt; K\u1ebft qu\u1ea3 l\u00e0 7.<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Vi_sao_lai_noi_dung_INDEXMATCH_de_hon_VLOOKUP\"><\/span>V\u00ec sao l\u1ea1i n\u00f3i d\u00f9ng INDEX\/MATCH d\u1ec5 h\u01a1n VLOOKUP?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>VLOOKUP \u0111\u00f2i h\u1ecfi c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm ph\u1ea3i n\u1eb1m ngo\u00e0i c\u00f9ng b\u00ean tr\u00e1i v\u00f9ng d\u1eef li\u1ec7u. N\u1ebfu n\u1eb1m b\u00ean ph\u1ea3i th\u00ec l\u00fac n\u00e0y ph\u1ea3i d\u00f9ng h\u00e0m m\u1ea3ng k\u1ebft h\u1ee3p v\u1edbi h\u00e0m CHOOSE \u0111\u1ec3 l\u1ea5y k\u1ebft qu\u1ea3 t\u01b0\u01a1ng \u1ee9ng. V\u1eady c\u00f9ng xem l\u1ea1i v\u00ed d\u1ee5, b\u1ea1n ch\u1ec9 vi\u1ec7c qu\u00e9t v\u00f9ng ch\u1ecdn c\u1ed9t k\u1ebft qu\u1ea3, t\u00ecm trong c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm. Th\u1ebf l\u00e0 xong!<\/p>\n<figure id=\"attachment_24291\" aria-describedby=\"caption-attachment-24291\" style=\"width: 819px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/11\/vlookup-tim-kiem-nguoc-ben-phai.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-24291 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-tim-kiem-nguoc-ben-phai.jpg\" width=\"819\" height=\"307\" title=\"vlookup-tim-kiem-nguoc-ben-phai\"><\/a><figcaption id=\"caption-attachment-24291\" class=\"wp-caption-text\">V\u00ed d\u1ee5 2: VLOOKUP k\u1ebft h\u1ee3p CHOOSE \u0111\u1ec3 d\u00f2 t\u00ecm ng\u01b0\u1ee3c<\/figcaption><\/figure>\n<p>Ng\u01b0\u1ee3c l\u1ea1i v\u1edbi v\u00ed d\u1ee5 tr\u01b0\u1edbc \u0111\u00f3, ch\u00fang ta c\u00f3 c\u1ed9t L\u1edbp n\u1eb1m ngo\u00e0i c\u00f9ng b\u00ean tr\u00e1i, v\u00e0 b\u00e0i to\u00e1n l\u00e0 t\u1eeb t\u00ean H\u1ecdc vi\u00ean, ch\u00fang ta s\u1ebd t\u00ecm ra l\u1edbp c\u1ee7a H\u1ecdc vi\u00ean \u0111\u00f3. B\u1ea1n s\u1ebd vi\u1ebft h\u00e0m VLOOKUP theo nh\u01b0 th\u00f4ng th\u01b0\u1eddng th\u1ebf n\u00e0o? Ngh\u0129 xem nh\u00e9? V\u1eady v\u1edbi h\u00e0m VLOOKUP, c\u00e1c b\u1ea1n ph\u1ea3i d\u00f9ng k\u1ebft h\u1ee3p h\u00e0m CHOOSE, v\u1edbi c\u00fa ph\u00e1p <strong>=CHOOSE({1,2}, C\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm, C\u1ed9t K\u1ebft qu\u1ea3)<\/strong>.<\/p>\n<p>V\u1eady ta c\u00f3 c\u00fa ph\u00e1p t\u1ed5ng qu\u00e1t nh\u01b0 sau:\u00a0<strong>=VLOOKUP(Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm, CHOOSE({1,2}, C\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm, C\u1ed9t k\u1ebft qu\u1ea3), C\u1ed9t tr\u1ea3 v\u1ec1[2], T\u00ecm ch\u00ednh x\u00e1c [0])<\/strong><\/p>\n<p>N\u1ebfu d\u1ea5u ph\u00e2n c\u00e1ch c\u1ee7a b\u1ea1n l\u00e0 <strong>d\u1ea5u ch\u1ea5m ph\u1ea9y<\/strong>, th\u00ec c\u00f4ng th\u1ee9c s\u1ebd l\u00e0:\u00a0<strong>=VLOOKUP(Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm; CHOOSE({1  2}; C\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm; C\u1ed9t k\u1ebft qu\u1ea3); C\u1ed9t tr\u1ea3 v\u1ec1[2]; T\u00ecm ch\u00ednh x\u00e1c [0])<\/strong><\/p>\n<p>V\u1edbi INDEX\/MATCH th\u00ec c\u00e1c b\u1ea1n th\u1ea5y v\u1eabn nh\u01b0 v\u00ed d\u1ee5 1, =INDEX(V\u00f9ng k\u1ebft qu\u1ea3, MATCH(Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm, V\u00f9ng d\u00f2 t\u00ecm, T\u00ecm ch\u00ednh x\u00e1c). \u0110\u01a1n gi\u1ea3n r\u1ed3i ph\u1ea3i kh\u00f4ng n\u00e0o?<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Dung_VLOOKUPCHOOSE_hoac_INDEXMATCH_tim_theo_nhieu_dieu_kien\"><\/span>D\u00f9ng VLOOKUP\/CHOOSE ho\u1eb7c INDEX\/MATCH t\u00ecm theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/11\/vlookup-index-match-tim-kiem-nhieu-dieu-kien.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-24293 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-index-match-tim-kiem-nhieu-dieu-kien.jpg\" width=\"974\" height=\"286\" title=\"vlookup-index-match-tim-kiem-nhieu-dieu-kien\"><\/a><\/p>\n<p>Ta c\u00f3 v\u00ed d\u1ee5 nh\u01b0 tr\u00ean, l\u00fac n\u00e0y c\u00f3 2 b\u1ea1n \u201cNguy\u1ec5n Th\u1ecb \u0110\u00e9t\u201d c\u00f9ng t\u00ean h\u1ecdc 2 l\u1edbp kh\u00e1c nhau, t\u01b0\u01a1ng \u0111\u01b0\u01a1ng v\u1edbi 2 \u0111i\u1ec1u ki\u1ec7n \u0111\u1ec3 ch\u00fang ta t\u00ecm ra \u0111i\u1ec3m thi c\u1ee7a t\u1eebng b\u1ea1n. V\u1eady l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 t\u00ecm ra? V\u1eabn l\u00e0 h\u00e0m VLOOKUP\/CHOOSE, l\u00fac n\u00e0y b\u1ea1n c\u1ea7n gh\u00e9p 2 \u0111i\u1ec1u ki\u1ec7n v\u1edbi nhau b\u1eb1ng d\u1ea5u &amp; (d\u1ea5u \u201cand\u201d\/\u201dv\u00e0\u201d), c\u00f9ng v\u1edbi vi\u1ec7c gh\u00e9p 2 c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm v\u1edbi nhau c\u0169ng v\u1edbi d\u1ea5u &amp;. Ta c\u00f3 c\u00fa ph\u00e1p nh\u01b0 sau:<\/p>\n<p><strong>=VLOOKUP([Gi\u00e1 tr\u1ecb d\u00f2 A]&amp;[Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm B]&amp;[Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm n], CHOOSE({1, 2}, [V\u00f9ng c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb A]&amp;[V\u00f9ng c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb B]&amp;[V\u00f9ng c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb n], [V\u00f9ng c\u1ed9t k\u1ebft qu\u1ea3]), 2 l\u00e0 C\u1ed9t tr\u1ea3 v\u1ec1, 0 l\u00e0 T\u00ecm ch\u00ednh x\u00e1c)<\/strong><\/p>\n<p>V\u00e0 \u0111\u00e2y\u00a0l\u00e0 c\u00f4ng th\u1ee9c m\u1ea3ng, \u0111\u00f2i h\u1ecfi c\u00e1c b\u1ea1n ph\u1ea3i nh\u1ea5n CTRL+SHIFT+ENTER, thay v\u00ec Enter (tr\u1ea3 v\u1ec1 #NA), l\u00fac n\u00e0y c\u00e1c b\u1ea1n s\u1ebd th\u1ea5y c\u00f3 m\u00f3c s\u1eebng tr\u00e2u xu\u1ea5t hi\u1ec7n trong c\u00f4ng th\u1ee9c.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Tai_sao_lai_MATCH_luc_tim_1_luc_lai_la_TRUE_va_luc_nao_phai_nhan_CTRLSHIFTENTER\"><\/span>T\u1ea1i sao l\u1ea1i MATCH l\u00fac t\u00ecm 1, l\u00fac l\u1ea1i l\u00e0 TRUE v\u00e0 l\u00fac n\u00e0o ph\u1ea3i nh\u1ea5n CTRL+SHIFT+ENTER?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<figure id=\"attachment_24294\" aria-describedby=\"caption-attachment-24294\" style=\"width: 975px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/11\/index-match-tim-kiem-theo-nhieu-dieu-kien.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-24294 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/index-match-tim-kiem-theo-nhieu-dieu-kien.jpg\" width=\"975\" height=\"305\" title=\"index-match-tim-kiem-theo-nhieu-dieu-kien\"><\/a><figcaption id=\"caption-attachment-24294\" class=\"wp-caption-text\">V\u00ed d\u1ee5 3: INDEX\/MATCH t\u00ecm ki\u1ebfm theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/figcaption><\/figure>\n<p>V\u1edbi INDEX\/MATCH, c\u00e1c b\u1ea1n c\u00f3 c\u00fa ph\u00e1p nh\u01b0 sau:<\/p>\n<p><strong>=INDEX(V\u00f9ng k\u1ebft qu\u1ea3, MATCH(1,([Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm A]=[V\u00f9ng c\u1ed9t gi\u00e1 tr\u1ecb A])*([Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm B]=[V\u00f9ng c\u1ed9t gi\u00e1 tr\u1ecb B]*([Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm n]=[V\u00f9ng c\u1ed9t gi\u00e1 tr\u1ecb n]),0)<\/strong><\/p>\n<p>V\u00e0 \u0111\u00e2y l\u00e0 c\u00f4ng th\u1ee9c m\u1ea3ng, n\u00ean ph\u1ea3i c\u00f3 nh\u1ea5n CTRL+SHIFT+ENTER. V\u00ec sao l\u00fac l\u1ea1i l\u00e0 1, l\u00fac l\u1ea1i TRUE? Khi b\u1ea1n ch\u1ec9 c\u00f3 1 bi\u1ec3u th\u1ee9c, l\u00fac n\u00e0y k\u1ebft qu\u1ea3 s\u1ebd tr\u1ea3 v\u1ec1 TRUE\/FALSE, khi c\u00f3 2 bi\u1ec3u th\u1ee9c TRUE*TRUE, Excel s\u1ebd chuy\u1ec3n TRUE th\u00e0nh 1*1 = 1.<\/p>\n<ul>\n<li>Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm l\u00e0 TRUE, khi c\u00f3 m\u1ed9t bi\u1ec3u th\u1ee9c: (Bi\u1ec3u th\u1ee9c so s\u00e1nh) =&gt; ([Gi\u00e1 tr\u1ecb A]=[V\u00f9ng c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb A])<\/li>\n<li>Gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm l\u00e0 1, khi c\u00f3 hai bi\u1ec3u th\u1ee9c tr\u1edf l\u00ean: (Bi\u1ec3u th\u1ee9c 1)*(Bi\u1ec3u th\u1ee9c 2) =&gt; ([Gi\u00e1 tr\u1ecb A]=[V\u00f9ng c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb A])*([Gi\u00e1 tr\u1ecb B]=[V\u00f9ng c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb B])*([Gi\u00e1 tr\u1ecb n]=[V\u00f9ng c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb n])<\/li>\n<li>T\u00ecm FALSE l\u00fac n\u00e0o? Khi b\u1ea1n c\u1ea7n t\u00ecm gi\u00e1 tr\u1ecb kh\u00f4ng th\u1ecfa theo bi\u1ec3u th\u1ee9c so s\u00e1nh c\u1ee7a m\u00ecnh.<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Dung_INDEXMATCH_ket_hop_INDEX_de_chuyen_doi_cong_thuc_mang_thanh_cong_thuc_binh_thuong\"><\/span>D\u00f9ng INDEX\/MATCH k\u1ebft h\u1ee3p INDEX \u0111\u1ec3 chuy\u1ec3n \u0111\u1ed5i c\u00f4ng th\u1ee9c m\u1ea3ng th\u00e0nh c\u00f4ng th\u1ee9c b\u00ecnh th\u01b0\u1eddng.<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Trong v\u00ed d\u1ee5 3, c\u00e1c b\u1ea1n l\u00e0m quen v\u1edbi c\u00f4ng th\u1ee9c m\u1ea3ng, \u0111\u00f2i h\u1ecfi thao t\u00e1c ph\u1ea3i nh\u1ea5n CTRL+SHIFT+ENTER, \u0111\u1ec3 tr\u00e1nh vi\u1ec7c ph\u1ea3i l\u00e0m thao t\u00e1c n\u00e0y, b\u1ea1n c\u00f3 th\u1ec3 k\u1ebft h\u1ee3p th\u00eam h\u00e0m INDEX b\u00ean trong h\u00e0m MATCH \u0111\u1ec3 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb \u0111\u1ea7u ti\u00ean trong danh s\u00e1ch MATCH t\u00ecm th\u1ea5y.<\/p>\n<figure id=\"attachment_24294\" aria-describedby=\"caption-attachment-24294\" style=\"width: 975px\" class=\"wp-caption aligncenter\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/11\/index-match-tim-kiem-theo-nhieu-dieu-kien.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-24294 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/index-match-tim-kiem-theo-nhieu-dieu-kien.jpg\" width=\"975\" height=\"305\" title=\"index-match-tim-kiem-theo-nhieu-dieu-kien\"><\/a><figcaption id=\"caption-attachment-24294\" class=\"wp-caption-text\">V\u00ed d\u1ee5 4: INDEX\/MATCH d\u00f2 t\u00ecm theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/figcaption><\/figure>\n<p>V\u1edbi c\u00fa ph\u00e1p\u00a0t\u1eeb \u00f4 G7, G4 trong\u00a0v\u00ed d\u1ee5 tr\u00ean ta c\u00f3:<\/p>\n<ul>\n<li>=INDEX(V\u00f9ng k\u1ebft qu\u1ea3, MATCH(TRUE, INDEX(Bi\u1ec3u th\u1ee9c, 0), 0))<\/li>\n<li>=INDEX(V\u00f9ng k\u1ebft qu\u1ea3, MATCH(1, INDEX((Bi\u1ec3u th\u1ee9c 1)*(Bi\u1ec3u th\u1ee9c 2), 0), 0))<\/li>\n<li>L\u01b0u \u00fd, lu\u00f4n c\u00f3 2 c\u00e1i s\u1ed1 \u201c, 0), 0)\u201d, s\u1ed1 0 \u0111\u1ea7u ti\u00ean cho h\u00e0m INDEX(Bi\u1ec3u th\u1ee9c,0). S\u1ed1 0 cu\u1ed1i c\u00f9ng cho h\u00e0m MATCH(,,0).<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Lam_bao_cao_chi_tiet_trich_loc_hoa_don_voi_INDEX_ket_hop_COUNTIFS_Ket_qua_tra_ve_nhieu_ket_qua_tu_mot_hoac_nhieu_dieu_kien\"><\/span>L\u00e0m b\u00e1o c\u00e1o chi ti\u1ebft, tr\u00edch l\u1ecdc h\u00f3a \u0111\u01a1n v\u1edbi INDEX k\u1ebft h\u1ee3p COUNTIFS. K\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u1ebft qu\u1ea3 t\u1eeb m\u1ed9t ho\u1eb7c nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n.<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<figure id=\"attachment_24296\" aria-describedby=\"caption-attachment-24296\" style=\"width: 891px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/11\/index-match-ket-hop-countif-tra-ve-nhieu-ket-qua.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-24296 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/index-match-ket-hop-countif-tra-ve-nhieu-ket-qua.jpg\" width=\"891\" height=\"392\" title=\"index-match-ket-hop-countif-tra-ve-nhieu-ket-qua\"><\/a><figcaption id=\"caption-attachment-24296\" class=\"wp-caption-text\">V\u00ed d\u1ee5 5: T\u00ecm ki\u1ebfm tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u1ebft qu\u1ea3<\/figcaption><\/figure>\n<p>V\u1edbi gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm \u201cNguy\u1ec5n Th\u1ecb \u0110\u00e9t\u201d b\u1ea1n c\u00f3 nhi\u1ec1u k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1, v\u1eady c\u00f3 c\u00e1ch n\u00e0o li\u1ec7t k\u00ea \u0111\u01b0\u1ee3c t\u1ea5t c\u1ea3 k\u1ebft qu\u1ea3 kh\u00f4ng? C\u00e2u tr\u1ea3 l\u1eddi l\u00e0 c\u00f3. V\u1edbi c\u00fa ph\u00e1p (0=COUNTIFS([$[\u00d4 \u0111\u1ea7u ti\u00ean tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3]:[\u00d4 \u0111\u1ea7u ti\u00ean tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3]], V\u00f9ng k\u1ebft qu\u1ea3), trong v\u00ed d\u1ee5: (0=COUNTIFS($F$1:F1,$B$1:$B$8)). Ngh\u0129a l\u00e0: X\u00e9t th\u00eam \u0111i\u1ec1u ki\u1ec7n \u0111\u00e3 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 tr\u01b0\u1edbc \u0111\u00f3 hay ch\u01b0a? N\u1ebfu \u0111\u00e3 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 r\u1ed3i, th\u00ec lo\u1ea1i tr\u1eeb \u0111\u1ec3 l\u1ea5y c\u00e1i ti\u1ebfp theo. L\u00fac n\u00e0y ta s\u1ebd c\u00f3 k\u1ebft qu\u1ea3 mong mu\u1ed1n.<\/p>\n<ul>\n<li>K\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 #NA l\u00e0 kh\u00f4ng t\u00ecm th\u1ea5y n\u1eefa, \u0111\u1ec3 kh\u00f4ng hi\u1ec3n th\u1ecb l\u1ed7i, b\u1ea1n c\u00f3 th\u1ec3 d\u00f9ng h\u00e0m IFERROR(C\u00f4ng th\u1ee9c, \u201c\u201d).<\/li>\n<li>Bi\u1ec3u th\u1ee9c \u0111i\u1ec1u ki\u1ec7n theo t\u00ean \u0111\u1ea7u ti\u00ean, m\u00ecnh c\u1ea7n c\u1ed1 \u0111\u1ecbnh c\u1ea3 \u00f4 $E$2, \u0111\u1ec3 khi k\u00e9o c\u00f4ng th\u1ee9c xu\u1ed1ng s\u1ebd c\u1ed1 \u0111\u1ecbnh \u00f4 gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm.<\/li>\n<li>$F$1:F1, v\u00ec sao ch\u1ec9 c\u1ed1 \u0111\u1ecbnh c\u00e1i \u0111\u1ea7u ti\u00ean, v\u00ec \u0111\u1ec3 khi k\u00e9o xu\u1ed1ng b\u00ean d\u01b0\u1edbi, n\u00f3 s\u1ebd tr\u1edf th\u00e0nh $F$1:F[2-&gt;n].<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Lam_the_nao_de_in_phieu_xuat_kho_co_nhieu_san_pham_Cung_mot_phieu_co_nhieu_ma_san_pham_khac_nhau\"><\/span>L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 in phi\u1ebfu xu\u1ea5t kho c\u00f3 nhi\u1ec1u s\u1ea3n ph\u1ea9m? C\u00f9ng m\u1ed9t phi\u1ebfu, c\u00f3 nhi\u1ec1u m\u00e3 s\u1ea3n ph\u1ea9m kh\u00e1c nhau<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/11\/index-match-countifs-ung-dung-tra-ve-nhieu-ket-qua.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-24298\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/index-match-countifs-ung-dung-tra-ve-nhieu-ket-qua.jpg\" width=\"979\" height=\"382\" title=\"index-match-countifs-ung-dung-tra-ve-nhieu-ket-qua\"><\/a><\/p>\n<p>V\u00ed d\u1ee5 v\u1edbi 1 m\u00e3 xu\u1ea5t kho, b\u1ea1n s\u1ebd xu\u1ea5t ra nhi\u1ec1u s\u1ea3n ph\u1ea9m kh\u00e1c nhau. L\u00fac n\u00e0y in phi\u1ebfu xu\u1ea5t kho, b\u1ea1n ch\u1ec9 vi\u1ec7c nh\u1eadp m\u00e3 phi\u1ebfu xu\u1ea5t kho, s\u1ebd tr\u1ea3 v\u1ec1 danh s\u00e1ch s\u1ea3n ph\u1ea9m t\u01b0\u01a1ng \u1ee9ng.<\/p>\n<p>Ta l\u1eadp c\u1ed9t ph\u1ee5 tham chi\u1ebfu theo m\u00e3 phi\u1ebfu xu\u1ea5t kho, l\u00fac n\u00e0y COUNTIFS l\u00e0m nhi\u1ec7m v\u1ee5 \u0111\u00e1nh s\u1ed1 th\u1ee9 t\u1ef1 gi\u00fap ch\u00fang ta. V\u1eabn l\u00e0 c\u1ed9t d\u00e2y v\u00e0o 1 \u0111\u1ea7u c\u1ed9t, d\u00e2y c\u00f2n l\u1ea1i th\u1ea3 t\u1ef1 do \u0111\u1ec3 di\u1ec1u bay cao: $A$2:A2 =&gt; COUNTIF($A$2:A2,\u00a0$E$9), v\u00e0 $E$9 l\u00e0 gi\u00e1 tr\u1ecb d\u00f2 t\u00ecm, c\u0169ng ph\u1ea3i c\u1ed1 \u0111\u1ecbnh \u0111\u1ec3 khi k\u00e9o xu\u1ed1ng ch\u00fang ta kh\u00f4ng thay \u0111\u1ed5i \u0111i\u1ec1u ki\u1ec7n t\u00ecm ki\u1ebfm. L\u00fac n\u00e0y c\u00e1c b\u1ea1n s\u1ebd th\u1ea5y s\u1ed1 t\u0103ng d\u1ea7n theo v\u00f9ng M\u00e3 phi\u1ebfu xu\u1ea5t kho, n\u1ebfu kh\u00f4ng t\u00ecm th\u1ea5y n\u1eefa, th\u00ec ch\u1ec9 l\u00e0 l\u1eb7p l\u1ea1i c\u00e1i cu\u1ed1i c\u00f9ng t\u00ecm th\u1ea5y.<\/p>\n<p>L\u00fac n\u00e0y tr\u1edf v\u1ec1 Sheet Phi\u1ebfu Xu\u1ea5t Kho \u0111\u1ec3 in \u1ea5n, ch\u1ec9 vi\u1ec7c l\u1eadp c\u00f4ng th\u1ee9c t\u01b0\u01a1ng \u1ee9ng nh\u01b0 sau, m\u00ecnh gi\u1ea3i th\u00edch t\u1eeb tr\u00e1i sang:<\/p>\n<ul>\n<li>STT: N\u1ebfu S\u1ea3n ph\u1ea9m tr\u1ea3 v\u1ec1 r\u1ed7ng, th\u00ec s\u1ebd tr\u1ea3 v\u1ec1 r\u1ed7ng, ng\u01b0\u1ee3c l\u1ea1i l\u1ea5y gi\u00e1 tr\u1ecb \u00f4 ngay b\u00ean tr\u00ean + 1. H\u00e0m N() \u1edf \u0111\u00e2y \u0111\u1ec3 ki\u1ec3m tra n\u1ebfu l\u00e0 ch\u1eef s\u1ebd tr\u1ea3 v\u1ec1 0, n\u1ebfu l\u00e0 s\u1ed1 th\u00ec tr\u1ea3 v\u1ec1 s\u1ed1 t\u01b0\u01a1ng \u1ee9ng. N(\u201cSTT\u201d) =&gt; 0+1 = 1, n\u1ebfu s\u1ea3n ph\u1ea9m c\u00f3 k\u1ebft qu\u1ea3. T\u00ecm hi\u1ec3u th\u00eam v\u1ec1 <a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-su-dung-ham-n-trong-excel.html\" target=\"_blank\" rel=\"noopener noreferrer\"><strong>H\u01b0\u1edbng d\u1eabn s\u1eed d\u1ee5ng h\u00e0m N trong Excel<\/strong><\/a>.<\/li>\n<li>S\u1ea3n ph\u1ea9m: <strong>=INDEX(V\u00f9ng k\u1ebft qu\u1ea3 [ nhi\u1ec1u c\u1ed9t], MATCH(ROW(1:1), V\u00f9ng c\u1ed9t ph\u1ee5,0), C\u1ed9t tr\u1ea3 v\u1ec1)<\/strong>. Ta c\u00f3 V\u00f9ng k\u1ebft qu\u1ea3 l\u00e0 B1:D6, l\u00fac n\u00e0y b\u1ea1n c\u00f3 th\u1ec3 v\u1eadn d\u1ee5ng ch\u1ec9 c\u1ed9t B1:B6 c\u0169ng \u0111\u01b0\u1ee3c, nh\u01b0ng ch\u00fang ta c\u00f3 th\u1ec3 tr\u1ea3 v\u1ec1 c\u1ed9t t\u01b0\u01a1ng \u1ee9ng ta mong mu\u1ed1n. \u1ede \u0111\u00e2y ta c\u00f3 v\u00f9ng B1:D6, c\u1ed9t tr\u1ea3 v\u1ec1 l\u00e0 1 = c\u1ed9t B. <strong>C\u00f2n ROW(1:1) l\u00e0 g\u00ec?<\/strong> ROW(1:1) tr\u1ea3 v\u1ec1 1. V\u00e0 khi ch\u00fang ta k\u00e9o c\u00f4ng th\u1ee9c xu\u1ed1ng B13, n\u00f3 tr\u1edf th\u00e0nh ROW(2:2) = 2.<\/li>\n<li>S\u1ed1 l\u01b0\u1ee3ng: \u1ede \u0111\u00e2y b\u1ea1n th\u1ea5y ch\u1ec9 kh\u00e1c m\u1ed7i <strong>C\u1ed9t tr\u1ea3 v\u1ec1<\/strong> \u0111\u00fang kh\u00f4ng? T\u1ea1i sao l\u1ea1i l\u00e0 <strong>COLUMN(B1)<\/strong>? H\u00e0m COLUMN(B1) s\u1ebd tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 c\u1ed9t B1 l\u00e0 c\u1ed9t bao nhi\u00eau, t\u1ee9c l\u00e0 2. Khi k\u00e9o sang ph\u1ea3i, n\u00f3 tr\u1edf th\u00e0nh COLUMN(C1), t\u1ee9c l\u00e0 3. V\u1eady khi k\u00e9o sang tr\u00e1i ch\u1ed7 c\u1ed9t S\u1ea3n ph\u1ea9m th\u00ec n\u00f3 th\u00e0nh g\u00ec? B\u1ea1n \u0111o\u00e1n xem? L\u00e0 COLUMN(A1), t\u1ee9c l\u00e0 1. Gi\u1edd th\u00ec b\u1ea1n hi\u1ec3u v\u00ec sao m\u00ecnh \u0111\u1ec3 c\u00f4ng th\u1ee9c cho c\u00e1c b\u1ea1n th\u1ea5y r\u1ed3i ph\u1ea3i kh\u00f4ng?<\/li>\n<li>Ch\u1ed7 #NA c\u1ee7a STT v\u00e0 S\u1ea3n ph\u1ea9m, m\u00ecnh c\u1ed1 t\u00ecnh \u0111\u1ec3 k\u1ebft qu\u1ea3 nh\u01b0 v\u1eady, n\u1ebfu b\u1ea1n mu\u1ed1n kh\u00f4ng hi\u1ec3n th\u1ecb #NA, h\u00e3y d\u00f9ng IFERROR theo c\u1ed9t S\u1ed1 l\u01b0\u1ee3ng v\u00e0 Kho nh\u00e9!<\/li>\n<\/ul>\n<p>\u0110\u1ec3 t\u00ecm hi\u1ec3u th\u00eam c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng t\u00ecm ki\u1ebfm tr\u00ean web t\u1ea1i \u00f4 t\u00ecm ki\u1ebfm, ho\u1eb7c t\u00ecm ki\u1ebfm v\u1edbi Google, h\u00e3y th\u00eam t\u1eeb kh\u00f3a \u201cblog.hocexcel.online\u201d + \u201ct\u1eeb kh\u00f3a\u201d. V\u00ed d\u1ee5:<strong> \u201cblog.hocexcel.online\u201d,\u201dINDEX\/MATCH\u201d.<\/strong><\/p>\n<p>\u0110\u00f3n xem: \u2013 <strong><a href=\"http:\/\/blog.ezworkapp.com\/bi-kip-vo-lam-sumproduct-than-chuong.html\" target=\"_blank\" rel=\"noopener noreferrer\">B\u00ed k\u00edp v\u00f5 l\u00e2m \u2013 T\u1eadp 2: SUMPRODUCT th\u1ea7n ch\u01b0\u1edfng<\/a>.<\/strong><\/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<p>D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n<p><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-222.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":2917,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-2916","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\/2916","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=2916"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/2916\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/2917"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=2916"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=2916"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=2916"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}