{"id":8846,"date":"2024-12-29T05:47:28","date_gmt":"2024-12-29T05:47:28","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/29\/huong-dan-tim-kiem-du-lieu-tuong-ung-tu-mot-trong-nhieu-cot-dieu-kien\/"},"modified":"2024-12-29T05:47:28","modified_gmt":"2024-12-29T05:47:28","slug":"huong-dan-tim-kiem-du-lieu-tuong-ung-tu-mot-trong-nhieu-cot-dieu-kien","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/29\/huong-dan-tim-kiem-du-lieu-tuong-ung-tu-mot-trong-nhieu-cot-dieu-kien\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn t\u00ecm ki\u1ebfm d\u1eef li\u1ec7u t\u01b0\u01a1ng \u1ee9ng t\u1eeb m\u1ed9t trong nhi\u1ec1u c\u1ed9t \u0111i\u1ec1u ki\u1ec7n"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Khi s\u1eed d\u1ee5ng Excel, ch\u1eafc h\u1eb3n ta \u0111\u00e3 quen v\u1edbi vi\u1ec7c tham chi\u1ebfu b\u1eb1ng c\u00e1c h\u00e0m, t\u1eeb m\u1ed9t c\u1ed9t kh\u00f3a \u0111i\u1ec1u ki\u1ec7n \u0111\u1ec3 tr\u1ea3 v\u1ec1 d\u1eef li\u1ec7u t\u01b0\u01a1ng \u1ee9ng. V\u1eady trong tr\u01b0\u1eddng h\u1ee3p ng\u01b0\u1ee3c l\u1ea1i th\u00ec sao? Trong b\u00e0i vi\u1ebft n\u00e0y, H\u1ecdc Excel Online s\u1ebd h\u01b0\u1edbng d\u1eabn b\u1ea1n t\u00ecm ki\u1ebfm t\u1eeb m\u1ed9t trong nhi\u1ec1u c\u1ed9t \u0111i\u1ec1u ki\u1ec7n \u0111\u1ed3ng th\u1eddi tr\u1ea3 ra gi\u00e1 tr\u1ecb \u0111\u00f3.<\/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-6770e26fad47f\" 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\/tim-kiem-tu-mot-trong-nhieu-cot-dieu-kien.html#Bai_toan_tim_kiem_tu_mot_trong_nhieu_cot_dieu_kien_khoi_dau\" title=\"B\u00e0i to\u00e1n\u00a0t\u00ecm ki\u1ebfm t\u1eeb m\u1ed9t trong nhi\u1ec1u c\u1ed9t \u0111i\u1ec1u ki\u1ec7n kh\u1edfi \u0111\u1ea7u\">B\u00e0i to\u00e1n\u00a0t\u00ecm ki\u1ebfm t\u1eeb m\u1ed9t trong nhi\u1ec1u c\u1ed9t \u0111i\u1ec1u ki\u1ec7n kh\u1edfi \u0111\u1ea7u<\/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\/tim-kiem-tu-mot-trong-nhieu-cot-dieu-kien.html#Tra_cuu_nguoc_thu_cong_cho_tung_doi_tuong\" title=\"Tra c\u1ee9u ng\u01b0\u1ee3c th\u1ee7 c\u00f4ng cho t\u1eebng \u0111\u1ed1i t\u01b0\u1ee3ng\">Tra c\u1ee9u ng\u01b0\u1ee3c th\u1ee7 c\u00f4ng cho t\u1eebng \u0111\u1ed1i t\u01b0\u1ee3ng<\/a>\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/tim-kiem-tu-mot-trong-nhieu-cot-dieu-kien.html#INDEXMATCH\" title=\"INDEX\/MATCH\">INDEX\/MATCH<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/tim-kiem-tu-mot-trong-nhieu-cot-dieu-kien.html#VLOOKUP_va_CHOOSE\" title=\"VLOOKUP v\u00e0 CHOOSE\">VLOOKUP v\u00e0 CHOOSE<\/a><\/li>\n<\/ul>\n<\/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\/tim-kiem-tu-mot-trong-nhieu-cot-dieu-kien.html#Tim_kiem_tu_mot_trong_nhieu_cot_dieu_kien_va_tra_ve_gia_tri_do\" title=\"T\u00ecm ki\u1ebfm t\u1eeb m\u1ed9t trong nhi\u1ec1u c\u1ed9t \u0111i\u1ec1u ki\u1ec7n v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb \u0111\u00f3\">T\u00ecm ki\u1ebfm t\u1eeb m\u1ed9t trong nhi\u1ec1u c\u1ed9t \u0111i\u1ec1u ki\u1ec7n v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb \u0111\u00f3<\/a>\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/tim-kiem-tu-mot-trong-nhieu-cot-dieu-kien.html#Thiet_lap_logic_cong_thuc\" title=\"Thi\u1ebft l\u1eadp logic c\u00f4ng th\u1ee9c\">Thi\u1ebft l\u1eadp logic c\u00f4ng th\u1ee9c<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blog.hocexcel.online\/tim-kiem-tu-mot-trong-nhieu-cot-dieu-kien.html#Tao_cong_thuc\" title=\"T\u1ea1o c\u00f4ng th\u1ee9c\">T\u1ea1o c\u00f4ng th\u1ee9c<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Bai_toan_tim_kiem_tu_mot_trong_nhieu_cot_dieu_kien_khoi_dau\"><\/span>B\u00e0i to\u00e1n\u00a0t\u00ecm ki\u1ebfm t\u1eeb m\u1ed9t trong nhi\u1ec1u c\u1ed9t \u0111i\u1ec1u ki\u1ec7n kh\u1edfi \u0111\u1ea7u<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>T\u1eeb th\u1eddi c\u00f2n ng\u1ed3i tr\u00ean gh\u1ebf nh\u00e0 tr\u01b0\u1eddng, ta \u0111\u00e3 \u0111\u01b0\u1ee3c l\u00e0m quen v\u1edbi nh\u1eefng b\u00e0i to\u00e1n s\u1eed d\u1ee5ng VLOOKUP, m\u00e0 d\u1ea1ng b\u00e0i c\u01a1 b\u1ea3n nh\u1ea5t nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-26553\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-1-1024x209-1.png\" width=\"1024\" height=\"209\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-1-1024x209-1\"><\/p>\n<p><strong>\u0110\u1ec1 b\u00e0i<\/strong>: S\u1eed d\u1ee5ng h\u00e0m VLOOKUP t\u00ecm ra<strong> Ph\u01b0\u01a1ng ti\u1ec7n 1<\/strong> \u1ee9ng v\u1edbi\u00a0<strong>\u0110\u01b0\u1eddng th\u1ee7y<\/strong>.<\/p>\n<p>Khi \u0111\u00f3, \u0111\u01a1n gi\u1ea3n ta l\u00e0m nh\u01b0 sau:\u00a0<em><strong>=VLOOKUP(gi\u00e1 tr\u1ecb d\u00f9ng \u0111\u1ec3 t\u00ecm ki\u1ebfm, b\u1ea3ng t\u00ecm ki\u1ebfn, c\u1ed9t t\u00ecm ki\u1ebfm, ki\u1ec3u t\u00ecm ki\u1ebfm)<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-26554\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-2.png\" width=\"1023\" height=\"320\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-2\"><\/p>\n<p>\u201cL\u1edbn\u201d h\u01a1n ch\u00fat n\u1eefa, bi\u1ebft s\u1eed d\u1ee5ng c\u00e1c h\u00e0m \u0111a d\u1ea1ng h\u01a1n, ta c\u00f3 <a href=\"http:\/\/blog.ezworkapp.com\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html\">t\u1ed5 h\u1ee3p INDEX\/MATCH thay th\u1ebf VLOOKUP<\/a>:\u00a0<em><strong>=INDEX(c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm, MATCH(gi\u00e1 tr\u1ecb d\u00f9ng \u0111\u1ec3 t\u00ecm ki\u1ebfm, c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb d\u00f9ng \u0111\u1ec3 t\u00ecm ki\u1ebfm, ki\u1ec3u t\u00ecm ki\u1ebfm))<\/strong><\/em><\/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-828.gif\" title=\"ex101_92019-nho-828\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-26555\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-3-1024x358-1.png\" width=\"1024\" height=\"358\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-3-1024x358-1\"><\/p>\n<p>\u00a0<\/p>\n<p>B\u00e2y gi\u1edd, h\u00e3y \u0111\u1ebfn v\u1edbi b\u00e0i to\u00e1n t\u1ed5ng qu\u00e1t h\u01a1n:\u00a0<em>Li\u1ec7t k\u00ea to\u00e0n b\u1ed9 ph\u01b0\u01a1ng ti\u1ec7n theo m\u1ed7i lo\u1ea1i h\u00ecnh.<\/em><\/p>\n<p>\u0110\u00e2y l\u00e0 b\u00e0i to\u00e1n \u0111\u00e3 c\u00f3 l\u1eddi gi\u1ea3i. C\u1ee5 th\u1ec3 b\u1ea1n click v\u00e0o\u00a0<strong><a href=\"http:\/\/blog.ezworkapp.com\/cach-viet-ham-vlookup-tra-ve-nhieu-gia-tri-ngan-cach-boi-dau-phay.html\">\u0111\u00e2y<\/a><\/strong>\u00a0ho\u1eb7c\u00a0<strong><a href=\"http:\/\/blog.ezworkapp.com\/ham-vlookup-tra-cuu-tra-ve-nhieu-gia-tri.html\">\u0111\u00e2y<\/a>.<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-26639\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-10.png\" width=\"473\" height=\"132\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-10\"><\/p>\n<p>T\u1eeb b\u00e0i to\u00e1n tr\u00ean, ta s\u1ebd \u0111\u1ebfn v\u1edbi\u00a0b\u00e0i to\u00e1n ch\u00ednh l\u1ea7n n\u00e0y:<\/p>\n<p><em>L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 tra c\u1ee9u t\u1ed5ng qu\u00e1t ng\u01b0\u1ee3c l\u1ea1i?<\/em><\/p>\n<p><em><strong>C\u1ee5 th\u1ec3: Cho t\u00ean ph\u01b0\u01a1ng ti\u1ec7n b\u00ean d\u01b0\u1edbi, h\u00e3y l\u1eadp b\u1ea3ng lo\u1ea1i h\u00ecnh t\u01b0\u01a1ng \u1ee9ng.<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-26556\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-4.png\" width=\"775\" height=\"394\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-4\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Tra_cuu_nguoc_thu_cong_cho_tung_doi_tuong\"><\/span>Tra c\u1ee9u ng\u01b0\u1ee3c th\u1ee7 c\u00f4ng cho t\u1eebng \u0111\u1ed1i t\u01b0\u1ee3ng<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"INDEXMATCH\"><\/span>INDEX\/MATCH<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/53-%E2%97%8F-cach-su-dung-ham-index-va-match-de-loc-du-lieu-nang-cao-trong-excel.html\"><strong>INDEX k\u1ebft h\u1ee3p MATCH<\/strong><\/a> lu\u00f4n l\u00e0 m\u1ed9t tr\u1ee3 th\u1ee7 \u0111\u1eafc l\u1ef1c trong vi\u1ec7c tra c\u1ee9u. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c v\u1edbi t\u1eebng ph\u01b0\u01a1ng ti\u1ec7n nh\u01b0 sau:<\/p>\n<p><strong><em>=INDEX($A$2:$A$5,MATCH(A8,$B$2:$B$5,0))<\/em><\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-26557\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-5.png\" width=\"772\" height=\"435\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-5\"><\/p>\n<h4><span class=\"ez-toc-section\" id=\"VLOOKUP_va_CHOOSE\"><\/span>VLOOKUP v\u00e0 CHOOSE<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Ta c\u0169ng c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng\u00a0<strong><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-ket-hop-vlookup-va-choose-tim-kiem-tu-phai-qua-trai.html\">VLOOKUP k\u1ebft h\u1ee3p CHOOSE<\/a><\/strong> nh\u01b0 m\u1ed9t ph\u01b0\u01a1ng ph\u00e1p tra c\u1ee9u ng\u01b0\u1ee3c. C\u1ee5 th\u1ec3:<\/p>\n<p><em><strong>=VLOOKUP(A8,CHOOSE({1,2},$B$2:$B$5,$A$2:$A$5),2,0)<\/strong><\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-26558\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-6.png\" width=\"775\" height=\"440\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-6\"><\/p>\n<p>\u00a0<\/p>\n<p>R\u1ed3i, sau \u0111\u00f3, ta l\u00e0m ti\u1ebfp nh\u01b0 n\u00e0o?<\/p>\n<p>T\u1ea5t nhi\u00ean l\u00e0 kh\u00f4ng th\u1ec3 Autofill xu\u1ed1ng \u0111\u01b0\u1ee3c r\u1ed3i!<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-26559\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-7.png\" width=\"778\" height=\"464\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-7\"><\/p>\n<p>Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, ta bu\u1ed9c ph\u1ea3i ngh\u0129 \u0111\u1ebfn nh\u1eefng h\u01b0\u1edbng l\u00e0m kh\u00e1c. H\u1ecdc Excel Online s\u1ebd h\u01b0\u1edbng d\u1eabn b\u1ea1n m\u1ed9t c\u00e1ch l\u00e0m d\u1ef1a tr\u00ean h\u00e0m\u00a0<strong>INDEX.<\/strong><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Tim_kiem_tu_mot_trong_nhieu_cot_dieu_kien_va_tra_ve_gia_tri_do\"><\/span>T\u00ecm ki\u1ebfm t\u1eeb m\u1ed9t trong nhi\u1ec1u c\u1ed9t \u0111i\u1ec1u ki\u1ec7n v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb \u0111\u00f3<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"Thiet_lap_logic_cong_thuc\"><\/span>Thi\u1ebft l\u1eadp logic c\u00f4ng th\u1ee9c<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>C\u00f4ng th\u1ee9c ta mong mu\u1ed1n s\u1ebd \u0111\u01b0\u1ee3c th\u1ec3 hi\u1ec7n nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-26638\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-9.png\" width=\"827\" height=\"393\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-9\"><\/p>\n<p>\u00a0<\/p>\n<p>V\u1eady l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 \u201ct\u1ef1 \u0111\u1ed9ng\u201d m\u1ee5c\u00a0<strong>row_num<\/strong>? H\u00e3y \u0111\u1ec3 \u00fd t\u1edbi 2 \u0111i\u1ec1u ki\u1ec7n sau:<\/p>\n<p><em>1. row_num ch\u00ednh l\u00e0 d\u00f2ng t\u01b0\u01a1ng \u1ee9ng v\u1edbi c\u00e1c ph\u01b0\u01a1ng ti\u1ec7n. V\u00ed d\u1ee5: \u00d4 t\u00f4 \u1edf d\u00f2ng 2, T\u00e0u th\u1ee7y \u1edf d\u00f2ng 3, T\u00e0u h\u1ecfa \u1edf d\u00f2ng 4\u2026<\/em><\/p>\n<p><em>2. D\u1eef li\u1ec7u c\u1ee7a ph\u01b0\u01a1ng ti\u1ec7n \u0111\u01b0\u1ee3c l\u1ea5y t\u1eeb c\u1ed9t \u201cPh\u01b0\u01a1ng ti\u1ec7n\u201d b\u00ean d\u01b0\u1edbi v\u00e0 c\u00f3 kh\u1ea3 n\u0103ng thay \u0111\u1ed5i theo 2 chi\u1ec1u c\u1ed9t v\u00e0 h\u00e0ng.<\/em><\/p>\n<p>Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y ta kh\u00f4ng th\u1ec3 s\u1eed d\u1ee5ng\u00a0<strong>MATCH<\/strong> b\u1edfi \u0111\u1ed1i t\u01b0\u1ee3ng ta c\u1ea7n tham chi\u1ebfu t\u1edbi l\u00e0 m\u1ed9t m\u1ea3ng bao g\u1ed3m c\u1ea3 chi\u1ec1u ngang v\u00e0 d\u1ecdc.<\/p>\n<p>C\u1ee5 th\u1ec3, ta ph\u1ea3i t\u00ecm \u0111\u01b0\u1ee3c m\u1ed1i li\u00ean quan sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-26640\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-11-1024x359-1.png\" width=\"1024\" height=\"359\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-11-1024x359-1\"><\/p>\n<p>T\u1eeb c\u1ed9t \u0111i\u1ec1u ki\u1ec7n\u00a0<strong>Ph\u01b0\u01a1ng ti\u1ec7n<\/strong><em><strong>\u00a0<\/strong><\/em>tham chi\u1ebfu sang <strong>b\u1ea3ng gi\u00e1 tr\u1ecb ban \u0111\u1ea7u<\/strong>, sau \u0111\u00f3 t\u1ea1o ra m\u1ed9t m\u1ea3ng m\u1edbi v\u00e0\u00a0<em>nh\u00e2n v\u1edbi<\/em> <strong>s\u1ed1 d\u00f2ng<\/strong>, cu\u1ed1i c\u00f9ng \u0111\u01b0a v\u00e0o\u00a0<strong>row_num\u00a0<\/strong>v\u00e0 tr\u1ea3 ng\u01b0\u1ee3c k\u1ebft qu\u1ea3 b\u1eb1ng\u00a0<strong>INDEX<\/strong>.<\/p>\n<ul>\n<li>\u0110\u1ec3 th\u1ef1c hi\u1ec7n vi\u1ec7c tham chi\u1ebfu tr\u1ea3 k\u1ebft qu\u1ea3 m\u1ea3ng, ta s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c m\u1ea3ng<\/li>\n<li>S\u1ed1 d\u00f2ng c\u1ee7a lo\u1ea1i h\u00ecnh t\u01b0\u01a1ng \u1ee9ng v\u1edbi ph\u01b0\u01a1ng ti\u1ec7n v\u00e0 ng\u01b0\u1ee3c l\u1ea1i. B\u1edfi v\u1eady ta s\u1ebd s\u1eed d\u1ee5ng s\u1ed1 d\u00f2ng c\u1ee7a b\u1ea3ng\u00a0<strong>Lo\u1ea1i h\u00ecnh<\/strong> sang\u00a0<strong>Ph\u01b0\u01a1ng ti\u1ec7n<\/strong>.<\/li>\n<\/ul>\n<h4><span class=\"ez-toc-section\" id=\"Tao_cong_thuc\"><\/span>T\u1ea1o c\u00f4ng th\u1ee9c<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>T\u1eeb trong ra ngo\u00e0i, c\u00f4ng th\u1ee9c c\u1ee7a ta nh\u01b0 sau:<\/p>\n<p><strong><em>=$B$2:$E$5=A8<\/em><\/strong><\/p>\n<p>Sau \u0111\u00f3 ta nh\u1ea5n\u00a0<strong>Ctrl + Shift + Enter<\/strong>. M\u1ea3ng c\u1ee7a ta s\u1ebd bi\u1ec3u di\u1ec5n nh\u01b0 sau (\u1ea5n F9 \u0111\u1ec3 hi\u1ec3n th\u1ecb tr\u00ean thanh c\u00f4ng th\u1ee9c):<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-26641\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-12-1024x323-1.png\" width=\"1024\" height=\"323\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-12-1024x323-1\"><\/p>\n<p>Ti\u1ebfp theo, \u0111\u1ec3 chuy\u1ec3n\u00a0<strong>TRUE<\/strong> v\u1ec1 1<strong>, FALSE<\/strong> v\u1ec1\u00a00, ta s\u1eed d\u1ee5ng:<\/p>\n<p><strong><em>=\u2013($B$2:$E$5=A8)\u00a0<\/em><\/strong><em>ho\u1eb7c\u00a0<strong>=N($B$2:$E$5=A8)<\/strong><\/em><\/p>\n<p><span style=\"color: #800000\"><strong><span style=\"color: #008080\">\u0110\u1ecdc th\u00eam v\u1ec1 h\u00e0m N<\/span>\u00a0<\/strong><strong><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-su-dung-ham-n-trong-excel.html\">t\u1ea1i \u0111\u00e2y<\/a><\/strong><\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-26642\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-13-1024x327-1.png\" width=\"1024\" height=\"327\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-13-1024x327-1\"><\/p>\n<p>Khi \u0111\u00e3 c\u00f3 gi\u00e1 tr\u1ecb s\u1ed1, ta ti\u1ebfn h\u00e0nh\u00a0<strong>nh\u00e2n<\/strong> v\u1edbi v\u1ecb tr\u00ed d\u00f2ng \u0111\u1ec3 ra gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng:<\/p>\n<p>H\u00e0m s\u1eed d\u1ee5ng l\u1ea5y ra v\u1ecb tr\u00ed d\u00f2ng:\u00a0<strong><a href=\"http:\/\/blog.ezworkapp.com\/ham-row-tra-ve-vi-tri-hang-cua-tham-chieu.html\">H\u00e0m ROW<\/a><\/strong><\/p>\n<p>\u0110\u1ed1i v\u1edbi ph\u00e9p nh\u00e2n m\u1ea3ng, ta c\u0169ng s\u1eed d\u1ee5ng m\u1ea3ng cho h\u00e0m ROW. C\u1ee5 th\u1ec3:\u00a0<strong><em>=ROW($A$2:$A$5).<\/em><\/strong><\/p>\n<p>Ph\u00e9p nh\u00e2n t\u01b0\u01a1ng \u1ee9ng:<\/p>\n<p><em><strong>=N($B$2:$E$5=A8)*ROW($A$2:$A$5) (<\/strong>b\u1ea5m Ctrl+Shift+Enter<strong>)<\/strong><\/em><\/p>\n<p>Ta c\u00f3 th\u1ec3 bi\u1ec3u th\u1ecb k\u1ebft qu\u1ea3 d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-26643\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-14-1024x392-1.png\" width=\"1024\" height=\"392\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-14-1024x392-1\"><\/p>\n<p>V\u1eady \u0111\u1ec3 c\u00f3 th\u1ec3 \u0111\u01b0a\u00a0k\u1ebft qu\u1ea3 v\u1ec1 s\u1ed1 ta ph\u1ea3i l\u00e0m th\u1ebf n\u00e0o? \u0110\u00e1p \u00e1n l\u00e0\u00a0<strong><a href=\"http:\/\/blog.ezworkapp.com\/bi-kip-vo-lam-sumproduct-than-chuong.html\">h\u00e0m SUMPRODUCT<\/a>,\u00a0<\/strong>ho\u1eb7c\u00a0<strong><a href=\"http:\/\/blog.ezworkapp.com\/4-phuong-phap-dem-nhieu-dieu-kien-dang-hoac-ham-sumif-dang-mang.html\">h\u00e0m SUM cho m\u1ea3ng<\/a>.<\/strong><\/p>\n<p><em>V\u1edbi h\u00e0m SUMPRODUCT ta ch\u1ec9 c\u1ea7n \u1ea5n Enter, trong khi v\u1edbi h\u00e0m SUM ta c\u1ea7n Ctrl + Shift + Enter.<\/em><\/p>\n<p>Trong tr\u01b0\u1eddng h\u1ee3p b\u1ea1n v\u1eabn mu\u1ed1n s\u1eed d\u1ee5ng h\u00e0m SUM? H\u00e3y ch\u00e8n th\u00eam <strong><a href=\"http:\/\/blog.ezworkapp.com\/indexmatch-tim-kiem-nhieu-dieu-kien-va-tra-ve-nhieu-ket-qua.html\">m\u1ed9t h\u00e0m\u00a0INDEX chuy\u1ec3n c\u00f4ng th\u1ee9c m\u1ea3ng v\u1ec1 b\u00ecnh th\u01b0\u1eddng<\/a>.<\/strong><\/p>\n<p><em><strong>=INDEX(N($B$2:$E$5=A8)*ROW($A$2:$A$5),0)<\/strong><\/em><\/p>\n<ul style=\"list-style-type: square\">\n<li><em><strong>=SUM(INDEX(N($B$2:$E$5=A8)*ROW($A$2:$A$5),0))<\/strong><\/em><\/li>\n<\/ul>\n<p>Cu\u1ed1i c\u00f9ng, \u0111\u1eb7t c\u00f4ng th\u1ee9c v\u00e0o trong h\u00e0m\u00a0<strong>INDEX<\/strong> ban \u0111\u1ea7u \u0111\u1ec3 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 v\u00e0 Autofill cho nh\u1eefng \u00f4 c\u00f2n l\u1ea1i:<\/p>\n<blockquote>\n<p><strong>=INDEX($A$1:$A$5,SUM(INDEX(N($B$2:$E$5=A8)*ROW($A$2:$A$5),0)))<\/strong><\/p>\n<\/blockquote>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-26644\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tim-kiem-tra-ve-du-lieu-theo-dieu-kien-15.png\" width=\"769\" height=\"435\" title=\"tim-kiem-tra-ve-du-lieu-theo-dieu-kien-15\"><\/p>\n<hr>\n<p>\u0110\u1ecdc th\u00eam v\u1ec1 c\u00e1c h\u00e0m:<\/p>\n<p><strong><a href=\"http:\/\/blog.ezworkapp.com\/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\">B\u00ed k\u00edp v\u00f5 l\u00e2m 1: INDEX\/MATCH<\/a><\/strong><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/bi-kip-vo-lam-sumproduct-than-chuong.html\"><strong>B\u00ed k\u00edp v\u00f5 l\u00e2m 2: SUMPRODUCT<\/strong><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/4-tuyet-chieu-tra-cuu-voi-vlookup-index-match-sumproduct-va-names-range-trong-excel.html\"><strong>4 tuy\u1ec7t chi\u00eau tra c\u1ee9u trong Excel<\/strong><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/lam-nao-de-tra-cuu-vlookup-index-match-tra-ve-hinh-anh.html\"><strong>VLOOKUP, INDEX tr\u1ea3 v\u1ec1 h\u00ecnh \u1ea3nh<\/strong><\/a><\/p>\n<p>\u00a0<\/p>\n<p>\u00a0<\/p>\n<p>\u00a0<\/p>\n<p>\u00a0<\/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-828.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":8847,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-8846","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\/8846","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=8846"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/8846\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/8847"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=8846"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=8846"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=8846"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}