{"id":5104,"date":"2024-12-09T05:35:38","date_gmt":"2024-12-09T05:35:38","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/09\/cach-tra-cuu-du-lieu-tu-nhieu-bang-tinh-voi-ham-vlookup-va-switch\/"},"modified":"2024-12-09T05:35:38","modified_gmt":"2024-12-09T05:35:38","slug":"cach-tra-cuu-du-lieu-tu-nhieu-bang-tinh-voi-ham-vlookup-va-switch","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/09\/cach-tra-cuu-du-lieu-tu-nhieu-bang-tinh-voi-ham-vlookup-va-switch\/","title":{"rendered":"C\u00e1ch tra c\u1ee9u d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u b\u1ea3ng t\u00ednh v\u1edbi h\u00e0m Vlookup v\u00e0 Switch"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>S\u1eed d\u1ee5ng h\u00e0m Vlookup\u00a0\u0111\u1ec3 t\u00ecm ki\u1ebfm d\u1eef li\u1ec7u c\u00f3 trong nhi\u1ec1u b\u1ea3ng t\u00ednh l\u00e0 y\u00eau c\u1ea7u kh\u00e1 th\u01b0\u1eddng g\u1eb7p khi l\u00e0m vi\u1ec7c tr\u00ean\u00a0Excel. Trong b\u00e0i vi\u1ebft n\u00e0y ch\u00fang ta s\u1ebd t\u00ecm hi\u1ec3u v\u1ec1 vi\u1ec7c k\u1ebft h\u1ee3p h\u00e0m SWITCH \u2013 1 h\u00e0m m\u1edbi c\u00f3 trong phi\u00ean b\u1ea3n Microsoft\u00a0Excel 2016 \/\u00a0Office 365\u00a0\u0111\u1ec3 c\u00f3 th\u1ec3 tra c\u1ee9u d\u1eef li\u1ec7u trong nhi\u1ec1u b\u1ea3ng t\u00ednh v\u1edbi h\u00e0m VLOOKUP.<\/p>\n<p>X\u00e9t y\u00eau c\u1ea7u sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14994\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-vlookup-switch-multi-table-lookup-01.png\" width=\"536\" height=\"196\" title=\"heo-vlookup-switch-multi-table-lookup-01\"><\/p>\n<p>T\u00ecm ki\u1ebfm s\u1ed1 l\u01b0\u1ee3ng h\u00e0ng h\u00f3a b\u00e1n\u00a0\u0111\u01b0\u1ee3c theo th\u00e1ng, trong\u00a0\u0111\u00f3: thay\u00a0\u0111\u1ed5i t\u00ean h\u00e0ng t\u1ea1i\u00a0\u00f4 A8 ho\u1eb7c thay\u00a0\u0111\u1ed5i Th\u00e1ng\u00a0\u1edf\u00a0\u00f4 B8\u00a0\u0111\u1ec1u c\u00f3\u00a0\u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 t\u01b0\u01a1ng\u00a0\u1ee9ng.<\/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-675681aa15680\" 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\/cach-tra-cuu-du-lieu-tu-nhieu-bang-tinh-voi-ham-vlookup-va-switch.html#1_Phan_tich_yeu_cau\" title=\"1. Ph\u00e2n t\u00edch y\u00eau c\u1ea7u\">1. Ph\u00e2n t\u00edch y\u00eau c\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\/cach-tra-cuu-du-lieu-tu-nhieu-bang-tinh-voi-ham-vlookup-va-switch.html#2_Cach_su_dung_ham_IF\" title=\"2. C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m\u00a0IF\">2. C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m\u00a0IF<\/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\/cach-tra-cuu-du-lieu-tu-nhieu-bang-tinh-voi-ham-vlookup-va-switch.html#3_Cach_su_dung_ham_SWITCH_thay_cho_ham_IF\" title=\"3. C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m SWITCH thay cho h\u00e0m\u00a0IF\">3. C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m SWITCH thay cho h\u00e0m\u00a0IF<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"1_Phan_tich_yeu_cau\"><\/span>1. Ph\u00e2n t\u00edch y\u00eau c\u1ea7u<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><strong>T\u00ean h\u00e0ng<\/strong> ch\u00ednh l\u00e0 <strong>Lookup_Value<\/strong> trong h\u00e0m Vlookup.<\/p>\n<p><strong>Th\u00e1ng<\/strong> ch\u00ednh l\u00e0 <strong>Lookup_Array<\/strong> trong h\u00e0m Vlookup.<\/p>\n<p>\u1ede\u00a0\u0111\u00e2y ch\u00fang ta th\u1ea5y y\u00eau c\u1ea7u b\u00e0i to\u00e1n x\u00e9t trong 3 b\u1ea3ng: th\u00e1ng 1, th\u00e1ng 2, th\u00e1ng 3. Ba b\u1ea3ng n\u00e0y c\u00f3 th\u1ec3 n\u1eb1m c\u00f9ng sheet ho\u1eb7c kh\u00e1c sheet. Mu\u1ed1n thay\u00a0\u0111\u1ed5i lookup_array th\u01b0\u1eddng ch\u00fang ta ph\u1ea3i s\u1eed d\u1ee5ng h\u00e0m\u00a0IF.<\/p>\n<div class=\"code-block code-block-2\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-169.gif\" title=\"vba101_92019-nho-169\"><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-431.gif\" title=\"ex101_92019-nho-431\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"2_Cach_su_dung_ham_IF\"><\/span>2. C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m\u00a0IF<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>C\u00e1c b\u1ea3ng c\u00f3 t\u1ecda\u00a0\u0111\u1ed9 l\u1ea7n l\u01b0\u1ee3t l\u00e0:<\/p>\n<ul>\n<li>B\u1ea3ng 1: A2:B5<\/li>\n<li>B\u1ea3ng 2: D2:E5<\/li>\n<li>B\u1ea3ng 3: G2:H5<\/li>\n<\/ul>\n<p>Logic c\u1ee7a vi\u1ec7c thay\u00a0\u0111\u1ed5i nh\u01b0 sau:<\/p>\n<ul>\n<li>N\u1ebfu B8 l\u00e0 Th\u00e1ng 1 th\u00ec s\u1ebd tham chi\u1ebfu t\u1edbi v\u00f9ng A2:B5<\/li>\n<li>N\u1ebfu B8 l\u00e0 Th\u00e1ng 2 th\u00ec s\u1ebd tham chi\u1ebfu t\u1edbi v\u00f9ng D2:E5<\/li>\n<li>N\u1ebfu B8 l\u00e0 Th\u00e1ng 3 th\u00ec s\u1ebd tham chi\u1ebfu t\u1edbi v\u00f9ng G2:H5<\/li>\n<\/ul>\n<p>Nh\u01b0 v\u1eady t\u1eeb logic tr\u00ean ch\u00fang ta c\u00f3 th\u1ec3 vi\u1ebft h\u00e0m\u00a0IF nh\u01b0 sau:<\/p>\n<blockquote>\n<p>IF(B8=\u201dTh\u00e1ng 1\u2033,A2:B5,IF(B8=\u201dTh\u00e1ng 2\u2033,D2:E5,IF(B8=\u201dTh\u00e1ng 3\u2033,G2:H5,\u201d\u201d)))<\/p>\n<\/blockquote>\n<p>Khi\u00a0\u0111\u1eb7t trong h\u00e0m Vlookup t\u1ea1i v\u1ecb tr\u00ed Table_Array ch\u00fang ta c\u00f3 k\u1ebft qu\u1ea3 nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14996\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-vlookup-switch-multi-table-lookup-02.png\" width=\"819\" height=\"197\" title=\"heo-vlookup-switch-multi-table-lookup-02\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"3_Cach_su_dung_ham_SWITCH_thay_cho_ham_IF\"><\/span>3. C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m SWITCH thay cho h\u00e0m\u00a0IF<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>H\u00e0m SWITCH l\u00e0 h\u00e0m m\u1edbi, th\u01b0\u1eddng s\u1eed d\u1ee5ng thay cho h\u00e0m\u00a0IF. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m n\u00e0y nh\u01b0 sau:<\/p>\n<p>SWITCH(B8,B1,A2:B5,E1,D2:E5,H1,G2:H5)<\/p>\n<ul>\n<li>B8 l\u00e0 v\u1ecb tr\u00ed l\u00e0m c\u0103n c\u1ee9 x\u00e1c\u00a0\u0111\u1ecbnh vi\u1ec7c thay\u00a0\u0111\u1ed5i b\u1ea3ng tham chi\u1ebfu.<\/li>\n<li>B1 l\u00e0 \u0111\u1ed1i t\u01b0\u1ee3ng\u00a0tham chi\u1ebfu\u00a0\u0111\u1ea7u ti\u00ean<\/li>\n<li>A2:B5 l\u00e0 v\u00f9ng tham chi\u1ebfu\u00a0\u0111\u1ea7u ti\u00ean. N\u1ebfu B8=B1 th\u00ec s\u1ebd tham chi\u1ebfu t\u1edbi v\u00f9ng n\u00e0y.<\/li>\n<li>E1\u00a0l\u00e0 \u0111\u1ed1i t\u01b0\u1ee3ng\u00a0tham chi\u1ebfu th\u1ee9 hai<\/li>\n<li>D2:E5 l\u00e0 v\u00f9ng tham chi\u1ebfu th\u1ee9 hai. N\u1ebfu gi\u00e1 tr\u1ecb\u00a0\u1edf B8=E1 th\u00ec s\u1ebd tham chi\u1ebfu t\u1edbi v\u00f9ng n\u00e0y.<\/li>\n<li>H1\u00a0l\u00e0 \u0111\u1ed1i t\u01b0\u1ee3ng\u00a0tham chi\u1ebfu th\u1ee9\u00a0ba<\/li>\n<li>G2:H5 l\u00e0 v\u00f9ng tham chi\u1ebfu th\u1ee9 ba. N\u1ebfu gi\u00e1 tr\u1ecb\u00a0\u1edf B8=H1 th\u00ec s\u1ebd tham chi\u1ebfu t\u1edbi v\u00f9ng n\u00e0y.<\/li>\n<\/ul>\n<p>Nh\u01b0 v\u1eady c\u0103n c\u1ee9 v\u00e0o gi\u00e1 tr\u1ecb\u00a0\u1edf B8 ch\u00fang ta c\u00f3 th\u1ec3 t\u00f9y ch\u1ecdn\u00a0\u0111\u01b0\u1ee3c v\u00f9ng tham chi\u1ebfu m\u00e0 kh\u00f4ng c\u1ea7n t\u1edbi logic nh\u01b0 h\u00e0m\u00a0IF.<\/p>\n<p>K\u1ebft qu\u1ea3 thu\u00a0\u0111\u01b0\u1ee3c nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14997\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/heo-vlookup-switch-multi-table-lookup-03.png\" width=\"824\" height=\"215\" title=\"heo-vlookup-switch-multi-table-lookup-03\"><\/p>\n<p>C\u1ea3 2 c\u00e1ch\u00a0\u0111\u1ec1u ra k\u1ebft qu\u1ea3 gi\u1ed1ng nhau. Tuy nhi\u00ean c\u00e1c\u00a0\u0111\u1ed1i t\u01b0\u1ee3ng h\u00e0m SWITCH vi\u1ebft d\u1ec5 h\u01a1n, g\u1ecdn h\u01a1n so v\u1edbi h\u00e0m\u00a0IF.<\/p>\n<p>Vi\u1ec7c ph\u00e1t tri\u1ec3n h\u00e0m SWITCH c\u0169ng gi\u1ed1ng nh\u01b0 h\u00e0m SUMIFS, COUNTIFS v\u1eady.<\/p>\n<p>Ch\u00fac c\u00e1c b\u1ea1n h\u1ecdc t\u1ed1t c\u00f9ng H\u1ecdc\u00a0Excel Online!<\/p>\n<p>\u2014\u2014\u2014<\/p>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 \u1ee9ng d\u1ee5ng t\u1ed1t Excel v\u00e0o trong c\u00f4ng vi\u1ec7c, ch\u00fang ta kh\u00f4ng ch\u1ec9 n\u1eafm v\u1eefng \u0111\u01b0\u1ee3c c\u00e1c h\u00e0m m\u00e0 c\u00f2n ph\u1ea3i s\u1eed d\u1ee5ng t\u1ed1t c\u1ea3 c\u00e1c c\u00f4ng c\u1ee5 c\u1ee7a Excel. Nh\u1eefng h\u00e0m n\u00e2ng cao gi\u00fap \u00e1p d\u1ee5ng t\u1ed1t v\u00e0o c\u00f4ng vi\u1ec7c nh\u01b0 SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH\u2026 Nh\u1eefng c\u00f4ng c\u1ee5 th\u01b0\u1eddng s\u1eed d\u1ee5ng l\u00e0 Data validation, Conditional formatting, Pivot table\u2026<\/p>\n<p>To\u00e0n b\u1ed9 nh\u1eefng ki\u1ebfn th\u1ee9c n\u00e0y c\u00e1c b\u1ea1n \u0111\u1ec1u c\u00f3 th\u1ec3 h\u1ecdc \u0111\u01b0\u1ee3c trong kh\u00f3a h\u1ecdc\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\/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-169.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5105,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-5104","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\/5104","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=5104"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5104\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5105"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}