{"id":4973,"date":"2024-12-08T18:44:03","date_gmt":"2024-12-08T18:44:03","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/08\/cach-dung-ham-index-va-match-trong-excel\/"},"modified":"2024-12-08T18:44:03","modified_gmt":"2024-12-08T18:44:03","slug":"cach-dung-ham-index-va-match-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/08\/cach-dung-ham-index-va-match-trong-excel\/","title":{"rendered":"C\u00c1CH D\u00d9NG H\u00c0M INDEX V\u00c0 MATCH TRONG EXCEL"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em><span style=\"font-weight: 400\">B\u00e0i vi\u1ebft sau \u0111\u00e2y,<strong> H\u1ecdc Excel Online<\/strong> s\u1ebd h\u01b0\u1edbng d\u1eabn <strong>c\u00e1ch s\u1eed d\u1ee5ng INDEX v\u00e0 MATCH trong Excel<\/strong>. Khi s\u1eed d\u1ee5ng h\u00e0m n\u00e0y s\u1ebd c\u00f3 nh\u1eefng \u01b0u \u0111i\u1ec3m t\u1ed1t h\u01a1n so v\u1edbi <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><\/span><\/em><\/p>\n<p><span style=\"font-weight: 400\">Nh\u1eefng b\u00e0i vi\u1ebft g\u1ea7n \u0111\u00e2y H\u1ecdc Excel Online \u0111\u00e3 cung c\u1ea5p cho c\u00e1c b\u1ea1n nh\u1eefng ki\u1ebfn th\u1ee9c c\u01a1 b\u1ea3n c\u1ee7a h\u00e0m Vlookup cho ng\u01b0\u1eddi m\u1edbi b\u1eaft \u0111\u1ea7u c\u0169ng nh\u01b0 c\u00e1c v\u00ed d\u1ee5 v\u1ec1 h\u00e0m n\u00e0y cho ng\u01b0\u1eddi d\u00f9ng. Trong b\u00e0i vi\u1ebft h\u00f4m nay <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/a> s\u1ebd h\u01b0\u1edbng d\u1eabn b\u1ea1n m\u1ed9t s\u1ed1 c\u00e1ch thay th\u1ebf h\u00e0m Vlookup \u0111\u1ec3 th\u1ef1c hi\u1ec7n tra c\u1ee9u theo chi\u1ec1u d\u1ecdc trong Excel.<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00e0m Vlookup kh\u00f4ng ph\u1ea3i l\u00e0 h\u00e0m tra c\u1ee9u duy nh\u1ea5t trong Excel. H\u00e0m Vlookup c\u0169ng c\u00f3 nhi\u1ec1u h\u1ea1n ch\u1ebf c\u00f3 th\u1ec3 khi\u1ebfn b\u1ea1n kh\u00f4ng nh\u1eadn \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 mong mu\u1ed1n trong nhi\u1ec1u tr\u01b0\u1eddng h\u1ee3p. Thay v\u00e0o \u0111\u00f3, Index Match trong Excel linh ho\u1ea1t h\u01a1n v\u00e0 c\u00f3 m\u1ed9t s\u1ed1 t\u00ednh n\u0103ng v\u01b0\u1ee3t tr\u1ed9i h\u01a1n so v\u1edbi h\u00e0m Vlookup \u1edf nhi\u1ec1u kh\u00eda c\u1ea1nh.\u00a0<\/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-6755e8f258f03\" 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\/cach-dung-ham-index-va-match-trong-excel.html#Ham_INDEX_va_MATCH_trong_Excel_%E2%80%93_kien_thuc_co_ban\" title=\"H\u00e0m INDEX v\u00e0 MATCH trong Excel \u2013 ki\u1ebfn th\u1ee9c c\u01a1 b\u1ea3n\">H\u00e0m INDEX v\u00e0 MATCH trong Excel \u2013 ki\u1ebfn th\u1ee9c c\u01a1 b\u1ea3n<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-index-va-match-trong-excel.html#Ham_INDEX_%E2%80%93_cu_phap_va_cach_su_dung\" title=\"H\u00e0m INDEX \u2013 c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng\">H\u00e0m INDEX \u2013 c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng<\/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-dung-ham-index-va-match-trong-excel.html#Ham_MATCH_%E2%80%93_cu_phap_va_cach_su_dung\" title=\"H\u00e0m MATCH \u2013 c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng\">H\u00e0m MATCH \u2013 c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-index-va-match-trong-excel.html#Cach_su_dung_ham_INDEX_MATCH_trong_Excel\" title=\"C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m INDEX MATCH trong Excel\">C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m INDEX MATCH trong Excel<\/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\/cach-dung-ham-index-va-match-trong-excel.html#So_sanh_INDEX_MATCH_voi_VLOOKUP\" title=\"So s\u00e1nh INDEX &amp; MATCH\u00a0 v\u1edbi VLOOKUP\">So s\u00e1nh INDEX &amp; MATCH\u00a0 v\u1edbi VLOOKUP<\/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\/cach-dung-ham-index-va-match-trong-excel.html#Excel_INDEX_MATCH_%E2%80%93_vi_du_ve_cong_thuc\" title=\"Excel INDEX MATCH \u2013 v\u00ed d\u1ee5 v\u1ec1 c\u00f4ng th\u1ee9c\">Excel INDEX MATCH \u2013 v\u00ed d\u1ee5 v\u1ec1 c\u00f4ng th\u1ee9c<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-index-va-match-trong-excel.html#Cong_thuc_INDEX_MATCH_de_tra_cuu_tu_phai_sang_trai\" title=\"C\u00f4ng th\u1ee9c INDEX MATCH \u0111\u1ec3 tra c\u1ee9u t\u1eeb ph\u1ea3i sang tr\u00e1i\">C\u00f4ng th\u1ee9c INDEX MATCH \u0111\u1ec3 tra c\u1ee9u t\u1eeb ph\u1ea3i sang tr\u00e1i<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-index-va-match-trong-excel.html#INDEX_MATCH_MATCH_de_tim_kiem_theo_hang_va_cot\" title=\"INDEX MATCH MATCH \u0111\u1ec3 t\u00ecm ki\u1ebfm theo h\u00e0ng v\u00e0 c\u1ed9t\">INDEX MATCH MATCH \u0111\u1ec3 t\u00ecm ki\u1ebfm theo h\u00e0ng v\u00e0 c\u1ed9t<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-index-va-match-trong-excel.html#Excel_INDEX_MATCH_de_tra_cuu_nhieu_tieu_chi\" title=\"Excel INDEX MATCH \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u ti\u00eau ch\u00ed\">Excel INDEX MATCH \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u ti\u00eau ch\u00ed<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-index-va-match-trong-excel.html#Excel_INDEX_MATCH_voi_AVERAGE_MAX_MIN\" title=\"Excel INDEX MATCH v\u1edbi AVERAGE, MAX, MIN\">Excel INDEX MATCH v\u1edbi AVERAGE, MAX, MIN<\/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-11\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-index-va-match-trong-excel.html#INDEX_MATCH_voi_MAX\" title=\"INDEX MATCH v\u1edbi MAX\">INDEX MATCH v\u1edbi MAX<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-index-va-match-trong-excel.html#INDEX_MATCH_voi_MIN\" title=\"INDEX MATCH v\u1edbi MIN\">INDEX MATCH v\u1edbi MIN<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-index-va-match-trong-excel.html#INDEX_MATCH_voi_AVERAGE\" title=\"INDEX MATCH v\u1edbi AVERAGE\">INDEX MATCH v\u1edbi AVERAGE<\/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-14\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-index-va-match-trong-excel.html#Su_dung_INDEX_MATCH_voi_IFNAIFERROR\" title=\"S\u1eed d\u1ee5ng INDEX MATCH v\u1edbi IFNA\/IFERROR\">S\u1eed d\u1ee5ng INDEX MATCH v\u1edbi IFNA\/IFERROR<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Ham_INDEX_va_MATCH_trong_Excel_%E2%80%93_kien_thuc_co_ban\"><\/span><b>H\u00e0m INDEX v\u00e0 MATCH trong Excel \u2013 ki\u1ebfn th\u1ee9c c\u01a1 b\u1ea3n<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">B\u00e0i vi\u1ebft n\u00e0y nh\u1eb1m m\u1ee5c \u0111\u00edch th\u1ec3 hi\u1ec7n hi\u1ec7n r\u00f5 h\u01a1n ch\u1ee9c n\u0103ng c\u1ee7a h\u00e0m INDEX &amp; MATCH v\u00e0 s\u1ebd kh\u00f4ng t\u1eadp trung nhi\u1ec1u v\u00e0o c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng c\u1ee7a ch\u00fang. D\u01b0\u1edbi \u0111\u00e2y s\u1ebd \u0111\u1ec1 c\u1eadp \u0111\u1ebfn nh\u1eefng ki\u1ebfn th\u1ee9c c\u01a1 b\u1ea3n v\u00e0 sau \u0111\u00f3 xem x\u00e9t c\u00e1c v\u00ed d\u1ee5 chuy\u00ean s\u00e2u v\u1ec1 c\u00f4ng th\u1ee9c \u0111\u1ec3 th\u1ea5y \u0111\u01b0\u1ee3c c\u00e1c l\u1ee3i th\u1ebf c\u1ee7a vi\u1ec7c s\u1eed d\u1ee5ng INDEX MATCH thay v\u00ec VLOOKUP.<\/span><\/p>\n<p>Xem th\u00eam:<strong><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-dung-ham-index-va-match-qua-bai-tap-co-loi-giai.html\"> C\u00e1ch s\u1eed d\u1ee5ng v\u00e0 c\u00fa ph\u00e1p h\u00e0m Index v\u00e0 Match<\/a><\/strong><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Ham_INDEX_%E2%80%93_cu_phap_va_cach_su_dung\"><\/span><b>H\u00e0m INDEX \u2013 c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\"><strong><a href=\"http:\/\/blog.ezworkapp.com\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel.html\">H\u00e0m INDEX<\/a> <\/strong>trong Excel tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb trong m\u1ed9t m\u1ea3ng d\u1ef1a tr\u00ean s\u1ed1 h\u00e0ng v\u00e0 c\u1ed9t m\u00e0 b\u1ea1n ch\u1ec9 \u0111\u1ecbnh. C\u00fa ph\u00e1p c\u1ee7a h\u00e0m INDEX r\u1ea5t \u0111\u01a1n gi\u1ea3n:<\/span><\/p>\n<p><strong><i>INDEX (array, row_num, [column_num])<\/i><\/strong><\/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-413.gif\" title=\"ex101_92019-nho-413\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><span style=\"font-weight: 400\">Gi\u1ea3i th\u00edch v\u1ec1 t\u1eebng tham s\u1ed1:<\/span><\/p>\n<p><i><span style=\"font-weight: 400\">Array (m\u1ea3ng)<\/span><\/i><span style=\"font-weight: 400\"> \u2013 m\u1ed9t d\u1ea3i \u00f4 m\u00e0 b\u1ea1n mu\u1ed1n tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb.<\/span><\/p>\n<p><i><span style=\"font-weight: 400\">row_num \u2013<\/span><\/i><span style=\"font-weight: 400\"> s\u1ed1 h\u00e0ng trong m\u1ea3ng m\u00e0 b\u1ea1n mu\u1ed1n tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb. N\u1ebfu b\u1ecf qua, th\u00ec column_num l\u00e0 b\u1eaft bu\u1ed9c.<\/span><\/p>\n<p><i><span style=\"font-weight: 400\">column_num \u2013<\/span><\/i><span style=\"font-weight: 400\"> s\u1ed1 c\u1ed9t trong m\u1ea3ng m\u00e0 b\u1ea1n mu\u1ed1n tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb. N\u1ebfu b\u1ecf qua, row_num l\u00e0 b\u1eaft bu\u1ed9c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 m\u1ed9t v\u00ed d\u1ee5 v\u1ec1 c\u00f4ng th\u1ee9c INDEX \u1edf d\u1ea1ng \u0111\u01a1n gi\u1ea3n nh\u1ea5t:<\/span><\/p>\n<p><strong><i>=INDEX(A1:C10,2,3)<\/i><\/strong><\/p>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c t\u00ecm ki\u1ebfm trong c\u00e1c \u00f4 t\u1eeb A1 \u0111\u1ebfn C10 v\u00e0 tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb c\u1ee7a \u00f4 \u1edf h\u00e0ng th\u1ee9 2 v\u00e0 c\u1ed9t th\u1ee9 3, t\u1ee9c l\u00e0 \u00f4 C2.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Kh\u00f4ng c\u00f3 g\u00ec qu\u00e1 ph\u1ee9c t\u1ea1p. Tuy nhi\u00ean, khi l\u00e0m vi\u1ec7c v\u1edbi d\u1eef li\u1ec7u th\u1ef1c, b\u1ea1n s\u1ebd kh\u00f3 bi\u1ebft h\u00e0ng v\u00e0 c\u1ed9t n\u00e0o b\u1ea1n mu\u1ed1n, \u0111\u00f3 l\u00e0 b\u1ea1n s\u1ebd c\u1ea7n ph\u1ea3i s\u1eed d\u1ee5ng \u0111\u1ebfn h\u00e0m Match.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Ham_MATCH_%E2%80%93_cu_phap_va_cach_su_dung\"><\/span><b>H\u00e0m MATCH \u2013 c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">H\u00e0m MATCH trong Excel t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb tra c\u1ee9u trong m\u1ed9t d\u1ea3i \u00f4 v\u00e0 tr\u1ea3 v\u1ec1 v\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a gi\u00e1 tr\u1ecb \u0111\u00f3 trong d\u1ea3i \u00f4.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00fa ph\u00e1p c\u1ee7a h\u00e0m MATCH nh\u01b0 sau:<\/span><\/p>\n<p><strong><i>MATCH (lookup_value, lookup_array, [match_type])<\/i><\/strong><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u00a0<\/span><i><span style=\"font-weight: 400\">Lookup_value \u2013<\/span><\/i><span style=\"font-weight: 400\"> s\u1ed1 ho\u1eb7c gi\u00e1 tr\u1ecb v\u0103n b\u1ea3n b\u1ea1n \u0111ang t\u00ecm ki\u1ebfm.<\/span><\/li>\n<li style=\"font-weight: 400\"><i><span style=\"font-weight: 400\">lookup_array \u2013<\/span><\/i><span style=\"font-weight: 400\"> m\u1ed9t d\u1ea3i \u00f4 \u0111ang \u0111\u01b0\u1ee3c t\u00ecm ki\u1ebfm<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u00a0<\/span><i><span style=\"font-weight: 400\">Match_type<\/span><\/i><span style=\"font-weight: 400\"> \u2013 ch\u1ec9 \u0111\u1ecbnh tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 kh\u1edbp ch\u00ednh x\u00e1c hay k\u1ebft qu\u1ea3 kh\u1edbp g\u1ea7n nh\u1ea5t:<\/span><\/li>\n<\/ul>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">1 ho\u1eb7c b\u1ecb b\u1ecf qua \u2013 t\u00ecm gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t nh\u1ecf h\u01a1n ho\u1eb7c b\u1eb1ng gi\u00e1 tr\u1ecb tra c\u1ee9u. Y\u00eau c\u1ea7u s\u1eafp x\u1ebfp m\u1ea3ng tra c\u1ee9u theo th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u00a00 \u2013 t\u00ecm gi\u00e1 tr\u1ecb \u0111\u1ea7u ti\u00ean ch\u00ednh x\u00e1c b\u1eb1ng gi\u00e1 tr\u1ecb tra c\u1ee9u. Trong k\u1ebft h\u1ee3p INDEX \/ MATCH, b\u1ea1n h\u1ea7u nh\u01b0 lu\u00f4n c\u1ea7n m\u1ed9t k\u1ebft h\u1ee3p ch\u00ednh x\u00e1c, v\u00ec v\u1eady b\u1ea1n \u0111\u1eb7t \u0111\u1ed1i s\u1ed1 th\u1ee9 ba c\u1ee7a h\u00e0m MATCH th\u00e0nh 0.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u00a0-1 \u2013 t\u00ecm gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng gi\u00e1 tr\u1ecb lookup_value. Y\u00eau c\u1ea7u s\u1eafp x\u1ebfp m\u1ea3ng tra c\u1ee9u theo th\u1ee9 t\u1ef1 gi\u1ea3m d\u1ea7n.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">V\u00ed d\u1ee5: n\u1ebfu ph\u1ea1m vi B1: B3 ch\u1ee9a c\u00e1c gi\u00e1 tr\u1ecb \u201cNew-York\u201d, \u201cParis\u201d, \u201cLondon\u201d, c\u00f4ng th\u1ee9c d\u01b0\u1edbi \u0111\u00e2y tr\u1ea3 v\u1ec1 s\u1ed1 3, v\u00ec \u201cLondon\u201d l\u00e0 m\u1ee5c nh\u1eadp th\u1ee9 ba trong ph\u1ea1m vi:<\/span><\/p>\n<p><strong><i>=MATCH(\u201cLondon\u201d,B1:B3,0)<\/i><\/strong><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 bi\u1ebft th\u00eam th\u00f4ng tin, vui l\u00f2ng xem h\u00e0m MATCH trong Excel.<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u1ea1n n\u00ean nh\u1edb r\u1eb1ng v\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a gi\u00e1 tr\u1ecb tra c\u1ee9u (t\u1ee9c l\u00e0 s\u1ed1 h\u00e0ng v\u00e0 c\u1ed9t) l\u00e0 nh\u1eefng g\u00ec b\u1ea1n c\u1ea7n cung c\u1ea5p cho c\u00e1c \u0111\u1ed1i s\u1ed1 row_num v\u00e0 column_num c\u1ee7a <a href=\"http:\/\/blog.ezworkapp.com\/su-dung-ham-match.html\"><strong>h\u00e0m INDEX<\/strong><\/a>. Excel INDEX c\u00f3 th\u1ec3 t\u00ecm gi\u00e1 tr\u1ecb t\u1ea1i \u0111i\u1ec3m n\u1ed1i c\u1ee7a m\u1ed9t h\u00e0ng v\u00e0 c\u1ed9t nh\u1ea5t \u0111\u1ecbnh, nh\u01b0ng n\u00f3 kh\u00f4ng th\u1ec3 x\u00e1c \u0111\u1ecbnh ch\u00ednh x\u00e1c h\u00e0ng v\u00e0 c\u1ed9t n\u00e0o b\u1ea1n mu\u1ed1n.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_su_dung_ham_INDEX_MATCH_trong_Excel\"><\/span><b>C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m INDEX MATCH trong Excel<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">N\u00f3i m\u1ed9t c\u00e1ch \u0111\u01a1n gi\u1ea3n INDEX t\u00ecm gi\u00e1 tr\u1ecb tra c\u1ee9u theo s\u1ed1 c\u1ed9t v\u00e0 h\u00e0ng, v\u00e0 MATCH cung c\u1ea5p c\u00e1c s\u1ed1 \u0111\u00f3.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ed1i v\u1edbi tra c\u1ee9u theo chi\u1ec1u d\u1ecdc, b\u1ea1n ch\u1ec9 s\u1eed d\u1ee5ng h\u00e0m MATCH \u0111\u1ec3 x\u00e1c \u0111\u1ecbnh s\u1ed1 h\u00e0ng v\u00e0 cung c\u1ea5p ph\u1ea1m vi c\u1ed9t tr\u1ef1c ti\u1ebfp cho INDEX:<\/span><\/p>\n<table>\n<tbody>\n<tr>\n<td><strong>INDEX ( c\u1ed9t \u0111\u1ec3 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u1eeb , MATCH ( gi\u00e1 tr\u1ecb tra c\u1ee9u , c\u1ed9t \u0111\u1ec3 tra c\u1ee9u , 0)<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 h\u00ecnh dung r\u00f5 h\u01a1n ch\u00fang ta s\u1ebd th\u1eed nghi\u00ean c\u1ee9u qua m\u1ed9t v\u00ed d\u1ee5. gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t danh s\u00e1ch c\u00e1c th\u1ee7 \u0111\u00f4 qu\u1ed1c gia v\u00e0 d\u00e2n s\u1ed1 c\u1ee7a h\u1ecd:<\/span><\/p>\n<figure style=\"width: 296px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m INDEX MATCH trong Excel\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/index-match-source-data.png\" alt=\"Source data for INDEX MATCH formula\" width=\"296\" height=\"220\"><figcaption class=\"wp-caption-text\">C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m INDEX MATCH trong Excel<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 t\u00ecm d\u00e2n s\u1ed1 c\u1ee7a m\u1ed9t th\u1ee7 \u0111\u00f4 nh\u1ea5t \u0111\u1ecbnh, ch\u1eb3ng h\u1ea1n nh\u01b0 th\u1ee7 \u0111\u00f4 c\u1ee7a Nh\u1eadt B\u1ea3n, h\u00e3y s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c INDEX MATCH sau:<\/span><\/p>\n<p><strong>=INDEX(C2:C10, MATCH(\u201cJapan\u201d, A2:A10, 0))<\/strong><\/p>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd s\u1ebd ph\u00e2n t\u00edch c\u00f4ng th\u1ee9c:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u00a0H\u00e0m MATCH t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb tra c\u1ee9u \u201cNh\u1eadt B\u1ea3n\u201d trong ph\u1ea1m vi A2: A10 v\u00e0 tr\u1ea3 v\u1ec1 s\u1ed1 3, v\u00ec \u201cNh\u1eadt B\u1ea3n\u201d \u0111\u1ee9ng th\u1ee9 ba trong m\u1ea3ng tra c\u1ee9u.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">S\u1ed1 h\u00e0ng chuy\u1ec3n tr\u1ef1c ti\u1ebfp \u0111\u1ebfn \u0111\u1ed1i s\u1ed1 row_num c\u1ee7a INDEX h\u01b0\u1edbng d\u1eabn n\u00f3 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u1eeb h\u00e0ng \u0111\u00f3.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">V\u00ec v\u1eady, c\u00f4ng th\u1ee9c tr\u00ean bi\u1ebfn th\u00e0nh m\u1ed9t INDEX \u0111\u01a1n gi\u1ea3n (C2: C3) t\u00ecm ki\u1ebfm trong c\u00e1c \u00f4 t\u1eeb C2 \u0111\u1ebfn C10 v\u00e0 k\u00e9o \u0111\u1ebfn gi\u00e1 tr\u1ecb t\u1eeb \u00f4 th\u1ee9 3 trong ph\u1ea1m vi \u0111\u00f3, t\u1ee9c l\u00e0 C4 v\u00ec ch\u00fang ta b\u1eaft \u0111\u1ea7u \u0111\u1ebfm t\u1eeb h\u00e0ng th\u1ee9 hai.<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n kh\u00f4ng mu\u1ed1n m\u00e3 h\u00f3a th\u00e0nh ph\u1ed1 trong c\u00f4ng th\u1ee9c th\u00ec c\u00f3 th\u1ec3 nh\u1eadp n\u00f3 ch\u1eb3ng h\u1ea1n F1, cung c\u1ea5p tham chi\u1ebfu \u00f4 cho MATCH v\u00e0 b\u1ea1n s\u1ebd nh\u1eadn \u0111\u01b0\u1ee3c c\u00f4ng th\u1ee9c tra c\u1ee9u \u0111\u1ed9ng:<\/span><\/p>\n<p><strong>=INDEX(C2:C10, MATCH(F1,A2:A10,0))<\/strong><\/p>\n<figure style=\"width: 580px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"V\u00ed d\u1ee5 v\u1ec1 h\u00e0m INDEX MATCH\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-match-function.png\" alt=\"Excel INDEX MATCH formula\" width=\"580\" height=\"258\"><figcaption class=\"wp-caption-text\">V\u00ed d\u1ee5 v\u1ec1 h\u00e0m INDEX MATCH<\/figcaption><\/figure>\n<p><b>L\u01b0u \u00fd quan tr\u1ecdng<\/b><span style=\"font-weight: 400\">: S\u1ed1 h\u00e0ng trong \u0111\u1ed1i s\u1ed1 m\u1ea3ng c\u1ee7a INDEX ph\u1ea3i kh\u1edbp v\u1edbi s\u1ed1 h\u00e0ng trong \u0111\u1ed1i s\u1ed1 lookup_array c\u1ee7a MATCH, n\u1ebfu kh\u00f4ng c\u00f4ng th\u1ee9c s\u1ebd t\u1ea1o ra k\u1ebft qu\u1ea3 kh\u00f4ng ch\u00ednh x\u00e1c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Tuy nhi\u00ean t\u1ea1i sao kh\u00f4ng s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c Vlookup v\u00e0 ph\u1ea3i c\u1ed1 g\u1eafng t\u00ecm ra nh\u1eefng \u0111i\u1ec3m ph\u1ee9c t\u1ea1p c\u1ee7a Excel Match Index?<\/span><span style=\"font-weight: 400\">V\u00ed d\u1ee5 n\u00e0y ch\u1ec9 d\u00e0nh cho m\u1ee5c \u0111\u00edch gi\u1ea3i th\u00edch \u0111\u1ec3 b\u1ea1n hi\u1ec3u c\u00e1ch h\u00e0m INDEX v\u00e0 MATCH ho\u1ea1t \u0111\u1ed9ng c\u00f9ng nhau. C\u00e1c v\u00ed d\u1ee5 kh\u00e1c sau \u0111\u00e2y s\u1ebd cho b\u1ea1n th\u1ea5y s\u1ee9c m\u1ea1nh th\u1ef1c s\u1ef1 c\u1ee7a s\u1ef1 k\u1ebft h\u1ee3p n\u00e0y, d\u1ec5 d\u00e0ng x\u1eed l\u00fd v\u1edbi nhi\u1ec1u t\u00ecnh hu\u1ed1ng ph\u1ee9c t\u1ea1p khi VLOOKUP g\u1eb7p s\u1ef1 c\u1ed1.<\/span><\/p>\n<p><strong>=VLOOKUP(F1, A2:C10, 3, FALSE)<\/strong><\/p>\n<h2><span class=\"ez-toc-section\" id=\"So_sanh_INDEX_MATCH_voi_VLOOKUP\"><\/span><b>So s\u00e1nh INDEX &amp; MATCH\u00a0 v\u1edbi VLOOKUP<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Khi quy\u1ebft \u0111\u1ecbnh s\u1eed d\u1ee5ng h\u00e0m n\u00e0o \u0111\u1ec3 tra c\u1ee9u theo chi\u1ec1u d\u1ecdc, h\u1ea7u h\u1ebft c\u00e1c chuy\u00ean gia Excel \u0111\u1ec1u cho r\u1eb1ng h\u00e0m INDEX &amp; MATCH t\u1ed1t h\u01a1n nhi\u1ec1u so v\u1edbi h\u00e0m VLOOKUP. Tuy nhi\u00ean, nhi\u1ec1u ng\u01b0\u1eddi v\u1eabn ch\u1ecdn h\u00e0m VLOOKUP, th\u1ee9 nh\u1ea5t l\u00e0 v\u00ec n\u00f3 \u0111\u01a1n gi\u1ea3n h\u01a1n v\u00e0 th\u1ee9 hai v\u00ec h\u1ecd kh\u00f4ng hi\u1ec3u \u0111\u1ea7y \u0111\u1ee7 t\u1ea5t c\u1ea3 c\u00e1c l\u1ee3i \u00edch c\u1ee7a vi\u1ec7c s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c INDEX MATCH trong Excel. N\u1ebfu kh\u00f4ng c\u00f3 s\u1ef1 hi\u1ec3u bi\u1ebft nh\u01b0 v\u1eady, kh\u00f4ng ai s\u1eb5n s\u00e0ng \u0111\u1ea7u t\u01b0 th\u1eddi gian c\u1ee7a h\u1ecd \u0111\u1ec3 h\u1ecdc m\u1ed9t c\u00fa ph\u00e1p ph\u1ee9c t\u1ea1p h\u01a1n.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Nh\u01b0ng n\u1ebfu t\u00ecm hi\u1ec3u b\u1ea1n s\u1ebd th\u1ea5y MATCH INDEX c\u00f3 nh\u1eefng \u01b0u \u0111i\u1ec3m v\u01b0\u1ee3t tr\u1ed9i so v\u1edbi VLOOKUP .<\/span><\/p>\n<ol>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Tra c\u1ee9u t\u1eeb ph\u1ea3i sang tr\u00e1i. Nh\u01b0 b\u1ea5t k\u1ef3 ng\u01b0\u1eddi d\u00f9ng th\u00f4ng th\u1ea1o n\u00e0o c\u0169ng bi\u1ebft, h\u00e0m VLOOKUP kh\u00f4ng t\u00ecm ki\u1ebfm d\u1eef li\u1ec7u t\u1eeb b\u00ean tr\u00e1i, c\u00f3 ngh\u0129a l\u00e0 gi\u00e1 tr\u1ecb tra c\u1ee9u c\u1ee7a b\u1ea1n ph\u1ea3i lu\u00f4n n\u1eb1m \u1edf c\u1ed9t ngo\u00e0i c\u00f9ng b\u00ean tr\u00e1i c\u1ee7a b\u1ea3ng. INDEX MATCH c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n tra c\u1ee9u b\u00ean tr\u00e1i m\u1ed9t c\u00e1ch d\u1ec5 d\u00e0ng.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Ch\u00e8n ho\u1eb7c x\u00f3a c\u1ed9t m\u1ed9t c\u00e1ch an to\u00e0n. C\u00f4ng th\u1ee9c VLOOKUP s\u1ebd b\u1ecb ph\u00e1 v\u1ee1 ho\u1eb7c \u0111\u01b0a ra\u00a0 k\u1ebft qu\u1ea3 kh\u00f4ng ch\u00ednh x\u00e1c khi m\u1ed9t c\u1ed9t m\u1edbi b\u1ecb x\u00f3a ho\u1eb7c \u0111\u01b0\u1ee3c th\u00eam v\u00e0o b\u1ea3ng tra c\u1ee9u v\u00ec c\u00fa ph\u00e1p c\u1ee7a h\u00e0m VLOOKUP y\u00eau c\u1ea7u ch\u1ec9 \u0111\u1ecbnh s\u1ed1 index c\u1ee7a c\u1ed9t b\u1ea1n mu\u1ed1n l\u1ea5y d\u1eef li\u1ec7u. \u0110\u01b0\u01a1ng nhi\u00ean, khi b\u1ea1n th\u00eam ho\u1eb7c x\u00f3a c\u1ed9t, s\u1ed1 ch\u1ec9 m\u1ee5c (index) s\u1ebd thay \u0111\u1ed5i.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">Kh\u00f4ng c\u00f3 gi\u1edbi h\u1ea1n cho k\u00edch th\u01b0\u1edbc c\u1ee7a gi\u00e1 tr\u1ecb tra c\u1ee9u. Khi s\u1eed d\u1ee5ng h\u00e0m VLOOKUP, c\u00e1c ti\u00eau ch\u00ed tra c\u1ee9u c\u1ee7a b\u1ea1n kh\u00f4ng \u0111\u01b0\u1ee3c v\u01b0\u1ee3t qu\u00e1 255 k\u00fd t\u1ef1, n\u1ebfu kh\u00f4ng s\u1ebd xu\u1ea5t hi\u1ec7n #VALUE! l\u1ed7i. V\u00ec v\u1eady, n\u1ebfu t\u1eadp d\u1eef li\u1ec7u c\u1ee7a b\u1ea1n ch\u1ee9a c\u00e1c chu\u1ed7i d\u00e0i, INDEX MATCH l\u00e0 gi\u1ea3i ph\u00e1p hi\u1ec7u qu\u1ea3 duy nh\u1ea5t.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">T\u1ed1c \u0111\u1ed9 x\u1eed l\u00fd cao h\u01a1n. N\u1ebfu c\u00e1c b\u1ea3ng c\u1ee7a b\u1ea1n nh\u1ecf th\u00ec s\u1ebd kh\u00f4ng c\u00f3 b\u1ea5t k\u1ef3 s\u1ef1 kh\u00e1c bi\u1ec7t \u0111\u00e1ng k\u1ec3 n\u00e0o v\u1ec1 hi\u1ec7u su\u1ea5t Excel. Nh\u01b0ng n\u1ebfu trang t\u00ednh c\u1ee7a b\u1ea1n ch\u1ee9a h\u00e0ng tr\u0103m ho\u1eb7c h\u00e0ng ngh\u00ecn h\u00e0ng v\u00e0 s\u1ebd ph\u00e1t sinh h\u00e0ng tr\u0103m ho\u1eb7c h\u00e0ng ngh\u00ecn c\u00f4ng th\u1ee9c, MATCH INDEX s\u1ebd ho\u1ea1t \u0111\u1ed9ng nhanh h\u01a1n nhi\u1ec1u so v\u1edbi h\u00e0m VLOOKUP v\u00ec Excel s\u1ebd ch\u1ec9 ph\u1ea3i x\u1eed l\u00fd c\u00e1c c\u1ed9t tra c\u1ee9u v\u00e0 tr\u1ea3 v\u1ec1 thay v\u00ec to\u00e0n b\u1ed9 m\u1ea3ng b\u1ea3ng.<\/span><\/li>\n<\/ol>\n<p><span style=\"font-weight: 400\">T\u00e1c \u0111\u1ed9ng c\u1ee7a h\u00e0m VLOOKUP \u0111\u1ed1i v\u1edbi hi\u1ec7u su\u1ea5t c\u1ee7a Excel c\u00f3 th\u1ec3 \u0111\u1eb7c bi\u1ec7t \u0111\u00e1ng ch\u00fa \u00fd n\u1ebfu c\u00e1c file l\u00e0m vi\u1ec7c c\u1ee7a b\u1ea1n ch\u1ee9a c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng ph\u1ee9c t\u1ea1p nh\u01b0 VLOOKUP v\u00e0 SUM . V\u1ea5n \u0111\u1ec1 l\u00e0 vi\u1ec7c ki\u1ec3m tra t\u1eebng gi\u00e1 tr\u1ecb trong m\u1ea3ng y\u00eau c\u1ea7u m\u1ed9t l\u1ec7nh g\u1ecdi ri\u00eang c\u1ee7a h\u00e0m VLOOKUP. V\u00ec v\u1eady, m\u1ea3ng c\u1ee7a b\u1ea1n c\u00e0ng ch\u1ee9a nhi\u1ec1u gi\u00e1 tr\u1ecb v\u00e0 c\u00e0ng c\u00f3 nhi\u1ec1u c\u00f4ng th\u1ee9c m\u1ea3ng trong s\u1ed5 l\u00e0m vi\u1ec7c, th\u00ec Excel c\u00e0ng ho\u1ea1t \u0111\u1ed9ng ch\u1eadm h\u01a1n.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Excel_INDEX_MATCH_%E2%80%93_vi_du_ve_cong_thuc\"><\/span><b>Excel INDEX MATCH \u2013 v\u00ed d\u1ee5 v\u1ec1 c\u00f4ng th\u1ee9c<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">D\u01b0\u1edbi \u0111\u00e2y l\u00e0 m\u1ed9t s\u1ed1 v\u00ed d\u1ee5 \u0111\u1ec3 b\u1ea1n c\u00f3 th\u1ec3 hi\u1ec3u r\u00f5 h\u01a1n c\u00e1ch \u00e1p d\u1ee5ng Index Match v\u00e0o th\u1ef1c t\u1ebf.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Cong_thuc_INDEX_MATCH_de_tra_cuu_tu_phai_sang_trai\"><\/span><b>C\u00f4ng th\u1ee9c INDEX MATCH \u0111\u1ec3 tra c\u1ee9u t\u1eeb ph\u1ea3i sang tr\u00e1i<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">Nh\u01b0 \u0111\u00e3 \u0111\u1ec1 c\u1eadp, h\u00e0m VLOOKUP kh\u00f4ng th\u1ec3 ki\u1ec3m tra d\u1eef li\u1ec7u b\u00ean tr\u00e1i c\u1ee7a n\u00f3. H\u00e0m INDEX MATCH trong Excel linh ho\u1ea1t h\u01a1n v\u00e0 kh\u00f4ng th\u1ef1c s\u1ef1 quan t\u00e2m \u0111\u1ebfn v\u1ecb tr\u00ed c\u1ee7a c\u00e1c c\u1ed9t tra c\u1ee9u.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ed1i v\u1edbi v\u00ed d\u1ee5 n\u00e0y, s\u1ebd ti\u1ebfn h\u00e0nh th\u00eam c\u1ed9t X\u1ebfp h\u1ea1ng (Rank) v\u00e0o b\u00ean tr\u00e1i b\u1ea3ng m\u1eabu c\u1ee7a m\u00ecnh v\u00e0 c\u1ed1 g\u1eafng t\u00ecm ra c\u00e1ch th\u1ee7 \u0111\u00f4 Moscow c\u1ee7a Nga x\u1ebfp h\u1ea1ng v\u1ec1 d\u00e2n s\u1ed1.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u1edbi gi\u00e1 tr\u1ecb tra c\u1ee9u trong G1, h\u00e3y s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c sau \u0111\u1ec3 t\u00ecm ki\u1ebfm trong C2: C10 v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng t\u1eeb A2: A10:<\/span><\/p>\n<p><strong>=INDEX(A2:A10,MATCH(G1,C2:C10,0))<\/strong><\/p>\n<figure style=\"width: 631px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"C\u00f4ng th\u1ee9c INDEX MATCH \u0111\u1ec3 tra c\u1ee9u t\u1eeb ph\u1ea3i sang tr\u00e1i\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/index-match-left-lookup.png\" alt=\"Look up values to left using INDEX MATCH\" width=\"631\" height=\"258\"><figcaption class=\"wp-caption-text\">C\u00f4ng th\u1ee9c INDEX MATCH \u0111\u1ec3 tra c\u1ee9u t\u1eeb ph\u1ea3i sang tr\u00e1i<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\"><strong>Ch\u00fa \u00fd:<\/strong>\u00a0N\u1ebfu b\u1ea1n \u0111\u1ecbnh s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c INDEX MATCH c\u1ee7a m\u00ecnh cho nhi\u1ec1u \u00f4, n\u00ean nh\u1edb\u00a0 kh\u00f3a c\u1ea3 hai ph\u1ea1m vi b\u1eb1ng tham chi\u1ebfu \u00f4 tuy\u1ec7t \u0111\u1ed1i (nh\u01b0 $ A $ 2: $ A $ 10 v\u00e0 $ C $ 2: 4C $ 10) \u0111\u1ec3 ch\u00fang kh\u00f4ng b\u1ecb sai l\u1ec7ch khi sao ch\u00e9p c\u00f4ng th\u1ee9c.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"INDEX_MATCH_MATCH_de_tim_kiem_theo_hang_va_cot\"><\/span><b>INDEX MATCH MATCH \u0111\u1ec3 t\u00ecm ki\u1ebfm theo h\u00e0ng v\u00e0 c\u1ed9t<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">Trong c\u00e1c v\u00ed d\u1ee5 tr\u00ean, h\u00e0m\u00a0 INDEX MATCH s\u1eed d\u1ee5ng \u0111\u1ec3 thay th\u1ebf cho h\u00e0m VLOOKUP c\u1ed5 \u0111i\u1ec3n \u0111\u1ec3 t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb t\u1eeb ph\u1ea1m vi m\u1ed9t c\u1ed9t \u0111\u01b0\u1ee3c x\u00e1c \u0111\u1ecbnh tr\u01b0\u1edbc. Nh\u01b0ng n\u1ebfu b\u1ea1n c\u1ea7n tra c\u1ee9u trong nhi\u1ec1u h\u00e0ng v\u00e0 nhi\u1ec1u c\u1ed9t th\u00ec sao? N\u00f3i c\u00e1ch kh\u00e1c, n\u1ebfu b\u1ea1n mu\u1ed1n th\u1ef1c hi\u1ec7n c\u00e1i g\u1ecdi l\u00e0 <\/span><i><span style=\"font-weight: 400\">ma tr\u1eadn<\/span><\/i><span style=\"font-weight: 400\"> (matrix) ho\u1eb7c tra c\u1ee9u <\/span><i><span style=\"font-weight: 400\">hai chi\u1ec1u<\/span><\/i><span style=\"font-weight: 400\"> th\u00ec l\u00e0m nh\u01b0 th\u1ebf n\u00e0o?<\/span><\/p>\n<p><span style=\"font-weight: 400\">Nghe c\u00f3 v\u1ebb ph\u1ee9c t\u1ea1p, nh\u01b0ng c\u00f4ng th\u1ee9c r\u1ea5t gi\u1ed1ng v\u1edbi h\u00e0m INDEX MATCH c\u01a1 b\u1ea3n c\u1ee7a Excel, ch\u1ec9 kh\u00e1c m\u1ed9t \u0111i\u1ec3m kh\u00e1c bi\u1ec7t. \u0110\u01a1n gi\u1ea3n, s\u1eed d\u1ee5ng hai h\u00e0m MATCH \u2013 1 MATCH \u0111\u1ec3 l\u1ea5y s\u1ed1 h\u00e0ng v\u00e0 MATCH c\u00f2n l\u1ea1i \u0111\u1ec3 l\u1ea5y s\u1ed1 c\u1ed9t.<\/span><\/p>\n<p><i><span style=\"font-weight: 400\">INDEX (array, MATCH (vlookup value, column to look up against, 0), MATCH (hlookup value, row to look up against, 0))<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400\">H\u00e3y xem b\u1ea3ng d\u01b0\u1edbi \u0111\u00e2y v\u00e0 x\u00e2y d\u1ef1ng m\u1ed9t c\u00f4ng th\u1ee9c INDEX MATCH MATCH \u0111\u1ec3 t\u00ecm d\u00e2n s\u1ed1 (t\u00ednh b\u1eb1ng tri\u1ec7u) \u1edf m\u1ed9t qu\u1ed1c gia nh\u1ea5t \u0111\u1ecbnh trong m\u1ed9t n\u0103m nh\u1ea5t \u0111\u1ecbnh.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u1edbi qu\u1ed1c gia m\u1ee5c ti\u00eau \u1edf G1 (gi\u00e1 tr\u1ecb vlookup) v\u00e0 n\u0103m m\u1ee5c ti\u00eau \u1edf G2 (gi\u00e1 tr\u1ecb hlookup), c\u00f4ng th\u1ee9c c\u00f3 d\u1ea1ng nh\u01b0 sau:\u00a0<\/span><\/p>\n<p><strong>=INDEX(B2:D11, MATCH(G1,A2:A11,0), MATCH(G2,B1:D1,0))<\/strong><\/p>\n<figure style=\"width: 612px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX MATCH MATCH \u0111\u1ec3 t\u00ecm ki\u1ebfm theo h\u00e0ng v\u00e0 c\u1ed9t\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/index-match-rows-columns.png\" alt=\"INDEX MATCH MATCH formula for two-way lookup in Excel\" width=\"612\" height=\"278\"><figcaption class=\"wp-caption-text\">INDEX MATCH MATCH \u0111\u1ec3 t\u00ecm ki\u1ebfm theo h\u00e0ng v\u00e0 c\u1ed9t<\/figcaption><\/figure>\n<p><b><i>C\u00f4ng th\u1ee9c n\u00e0y ho\u1ea1t \u0111\u1ed9ng nh\u01b0 th\u1ebf n\u00e0o<\/i><\/b><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 hi\u1ec3u m\u1ed9t c\u00f4ng th\u1ee9c ph\u1ee9c t\u1ea1p b\u1ea1n ch\u1ec9 c\u1ea7n chia n\u00f3 th\u00e0nh c\u00e1c ph\u1ea7n nh\u1ecf h\u01a1n v\u00e0 xem t\u1eebng h\u00e0m ri\u00eang l\u1ebb th\u1ef1c hi\u1ec7n nh\u1eefng g\u00ec:<\/span><\/p>\n<p><span style=\"font-weight: 400\">MATCH(G1,A2:A11,0) \u2013 t\u00ecm ki\u1ebfm qua A2: A11 cho gi\u00e1 tr\u1ecb trong \u00f4 G1 (\u201cTrung Qu\u1ed1c\u201d) v\u00e0 tr\u1ea3 v\u1ec1 v\u1ecb tr\u00ed c\u1ee7a n\u00f3, l\u00e0 2.<\/span><\/p>\n<p><span style=\"font-weight: 400\">MATCH(G2,B1:D1,0)) \u2013 t\u00ecm ki\u1ebfm th\u00f4ng qua B1: D1 \u0111\u1ec3 l\u1ea5y v\u1ecb tr\u00ed c\u1ee7a gi\u00e1 tr\u1ecb trong \u00f4 G2 (\u201c2015\u201d), l\u00e0 3.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00e1c s\u1ed1 h\u00e0ng v\u00e0 c\u1ed9t \u1edf tr\u00ean chuy\u1ec3n \u0111\u1ebfn c\u00e1c \u0111\u1ed1i s\u1ed1 t\u01b0\u01a1ng \u1ee9ng c\u1ee7a h\u00e0m INDEX:<\/span><\/p>\n<p><span style=\"font-weight: 400\">INDEX(B2:D11, 2, 3)<\/span><\/p>\n<p><span style=\"font-weight: 400\">K\u1ebft qu\u1ea3 l\u00e0 b\u1ea1n nh\u1eadn \u0111\u01b0\u1ee3c m\u1ed9t gi\u00e1 tr\u1ecb t\u1ea1i giao \u0111i\u1ec3m c\u1ee7a h\u00e0ng th\u1ee9 2 v\u00e0 c\u1ed9t th\u1ee9 3 trong ph\u1ea1m vi B2: D11, l\u00e0 gi\u00e1 tr\u1ecb trong \u00f4 D3.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Excel_INDEX_MATCH_de_tra_cuu_nhieu_tieu_chi\"><\/span><b>Excel INDEX MATCH \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u ti\u00eau ch\u00ed<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n \u0111\u00e3 \u0111\u1ecdc v\u00e0 t\u00ecm hi\u1ec3u nh\u1eefng b\u00e0i h\u01b0\u1edbng d\u1eabn Excel Vlookup tr\u01b0\u1edbc th\u00ec b\u1ea1n s\u1ebd bi\u1ebft \u0111\u1ebfn c\u00f4ng th\u1ee9c Vlookup v\u1edbi nhi\u1ec1u ti\u00eau ch\u00ed. Tuy nhi\u00ean, m\u1ed9t h\u1ea1n ch\u1ebf \u0111\u00e1ng k\u1ec3 \u0111\u00f3 l\u00e0 b\u1ea1n ph\u1ea3i th\u00eam c\u1ed9t tr\u1ee3 gi\u00fap. Tuy nhi\u00ean h\u00e0m INDEX MATCH c\u1ee7a Excel c\u0169ng c\u00f3 th\u1ec3 tra c\u1ee9u v\u1edbi 2 ti\u00eau ch\u00ed tr\u1edf l\u00ean m\u00e0 kh\u00f4ng c\u1ea7n s\u1eeda \u0111\u1ed5i ho\u1eb7c c\u01a1 c\u1ea5u l\u1ea1i d\u1eef li\u1ec7u \u0111\u1ea7u v\u00e0o c\u1ee7a b\u1ea1n.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 c\u00f4ng th\u1ee9c INDEX MATCH chung v\u1edbi nhi\u1ec1u ti\u00eau ch\u00ed:<\/span><\/p>\n<p><strong>{= INDEX ( return_range , MATCH (1, ( Criteria1 = RANGE1 ) * ( criteria2 = RANGE2 ), 0))}<\/strong><\/p>\n<p><i><span style=\"font-weight: 400\">Ghi ch\u00fa.<\/span><\/i><span style=\"font-weight: 400\"> C\u00f4ng\u00a0 th\u1ee9c m\u1ea3ng ph\u1ea3i \u0111\u01b0\u1ee3c ho\u00e0n th\u00e0nh b\u1eb1ng ph\u00edm t\u1eaft Ctrl + Shift + Enter.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong b\u1ea3ng m\u1eabu b\u00ean d\u01b0\u1edbi, gi\u1ea3 s\u1eed b\u1ea1n mu\u1ed1n t\u00ecm s\u1ed1 ti\u1ec1n d\u1ef1a tr\u00ean 2 ti\u00eau ch\u00ed Kh\u00e1ch h\u00e0ng (Customer) v\u00e0 S\u1ea3n ph\u1ea9m (Product).<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c INDEX MATCH ho\u1ea1t \u0111\u1ed9ng nh\u01b0 sau:<\/span><\/p>\n<p><strong>=INDEX(C2:C10, MATCH(1, (F1=A2:A10) * (F2=B2:B10), 0))<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Trong \u0111\u00f3 C2: C10 l\u00e0 ph\u1ea1m vi tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb, F1 l\u00e0 ti\u00eau ch\u00ed 1, A2: A10 l\u00e0 ph\u1ea1m vi \u0111\u1ec3 so s\u00e1nh v\u1edbi ti\u00eau ch\u00ed 1, F2 l\u00e0 ti\u00eau ch\u00ed 2 v\u00e0 B2: B10 l\u00e0 ph\u1ea1m vi \u0111\u1ec3 so s\u00e1nh v\u1edbi ti\u00eau ch\u00ed 2.<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00e3y nh\u1edb nh\u1eadp c\u00f4ng th\u1ee9c ch\u00ednh x\u00e1c b\u1eb1ng c\u00e1ch nh\u1ea5n Ctrl + Shift + Enter v\u00e0 Excel s\u1ebd t\u1ef1 \u0111\u1ed9ng \u0111\u1eb7t n\u00f3 v\u00e0o d\u1ea5u ngo\u1eb7c nh\u1ecdn nh\u01b0 trong \u1ea3nh ch\u1ee5p m\u00e0n h\u00ecnh:<\/span><\/p>\n<figure style=\"width: 464px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel INDEX MATCH \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u ti\u00eau ch\u00ed\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/index-match-multiple-criteria-formula.png\" alt=\"The INDEX MATCH formula to look up multiple criteria\" width=\"464\" height=\"257\"><figcaption class=\"wp-caption-text\">Excel INDEX MATCH \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u ti\u00eau ch\u00ed<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n kh\u00f4ng mu\u1ed1n s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c m\u1ea3ng trong trang t\u00ednh c\u1ee7a m\u00ecnh, h\u00e3y th\u00eam m\u1ed9t h\u00e0m INDEX n\u1eefa v\u00e0o c\u00f4ng th\u1ee9c v\u00e0 k\u1ebft th\u00fac n\u00f3 b\u1eb1ng m\u1ed9t l\u1ea7n nh\u1ea5n Enter th\u00f4ng th\u01b0\u1eddng:<\/span><\/p>\n<figure style=\"width: 460px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"Excel INDEX MATCH \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u ti\u00eau ch\u00ed 1\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/nonarray-index-match-multiple-criteria.png\" alt=\"A non-array INDEX MATCH formula with multiple criteria\" width=\"460\" height=\"257\"><figcaption class=\"wp-caption-text\">Excel INDEX MATCH \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u ti\u00eau ch\u00ed 1<\/figcaption><\/figure>\n<p><b><i>C\u00e1ch ho\u1ea1t \u0111\u1ed9ng c\u1ee7a c\u00f4ng th\u1ee9c n\u00e0y:<\/i><\/b><\/p>\n<p><span style=\"font-weight: 400\">C\u00e1c c\u00f4ng th\u1ee9c ti\u1ebfp c\u1eadn t\u01b0\u01a1ng t\u1ef1 nh\u01b0 h\u00e0m INDEX MATCH c\u01a1 b\u1ea3n xem x\u00e9t qua m\u1ed9t c\u1ed9t duy nh\u1ea5t. \u0110\u1ec3 \u0111\u00e1nh gi\u00e1 nhi\u1ec1u ti\u00eau ch\u00ed, b\u1ea1n t\u1ea1o hai ho\u1eb7c nhi\u1ec1u m\u1ea3ng gi\u00e1 tr\u1ecb TRUE v\u00e0 FALSE \u0111\u1ea1i di\u1ec7n cho c\u00e1c gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p v\u00e0 kh\u00f4ng kh\u1edbp cho t\u1eebng ti\u00eau ch\u00ed ri\u00eang l\u1ebb, sau \u0111\u00f3 nh\u00e2n c\u00e1c ph\u1ea7n t\u1eed t\u01b0\u01a1ng \u1ee9ng c\u1ee7a c\u00e1c m\u1ea3ng n\u00e0y. Ph\u00e9p nh\u00e2n chuy\u1ec3n \u0111\u1ed5i TRUE v\u00e0 FALSE th\u00e0nh 1 v\u00e0 0 t\u01b0\u01a1ng \u1ee9ng v\u00e0 t\u1ea1o ra m\u1ed9t m\u1ea3ng trong \u0111\u00f3 1 t\u01b0\u01a1ng \u1ee9ng v\u1edbi c\u00e1c h\u00e0ng kh\u1edbp v\u1edbi t\u1ea5t c\u1ea3 c\u00e1c ti\u00eau ch\u00ed. H\u00e0m MATCH v\u1edbi gi\u00e1 tr\u1ecb tra c\u1ee9u l\u00e0 1 t\u00ecm s\u1ed1 \u201c1\u201d \u0111\u1ea7u ti\u00ean trong m\u1ea3ng v\u00e0 chuy\u1ec3n v\u1ecb tr\u00ed c\u1ee7a n\u00f3 t\u1edbi INDEX, h\u00e0m n\u00e0y tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb trong h\u00e0ng n\u00e0y t\u1eeb c\u1ed9t \u0111\u00e3 ch\u1ec9 \u0111\u1ecbnh.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c non- array d\u1ef1a v\u00e0o kh\u1ea3 n\u0103ng c\u1ee7a h\u00e0m INDEX \u0111\u1ec3 x\u1eed l\u00fd m\u1ea3ng nguy\u00ean b\u1ea3n. INDEX th\u1ee9 hai \u0111\u01b0\u1ee3c \u0111\u1ecbnh c\u1ea5u h\u00ecnh v\u1edbi 0 row_num \u0111\u1ec3 n\u00f3 s\u1ebd chuy\u1ec3n to\u00e0n b\u1ed9 m\u1ea3ng c\u1ed9t th\u00e0nh MATCH.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Excel_INDEX_MATCH_voi_AVERAGE_MAX_MIN\"><\/span><b>Excel INDEX MATCH v\u1edbi AVERAGE, MAX, MIN<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">Microsoft Excel c\u00f3 c\u00e1c h\u00e0m \u0111\u1eb7c bi\u1ec7t \u0111\u1ec3 t\u00ecm gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t, l\u1edbn nh\u1ea5t v\u00e0 trung b\u00ecnh trong m\u1ed9t ph\u1ea1m vi. Nh\u01b0ng n\u1ebfu b\u1ea1n c\u1ea7n l\u1ea5y m\u1ed9t gi\u00e1 tr\u1ecb t\u1eeb m\u1ed9t \u00f4 kh\u00e1c \u0111\u01b0\u1ee3c li\u00ean k\u1ebft v\u1edbi c\u00e1c gi\u00e1 tr\u1ecb \u0111\u00f3 th\u00ec l\u00e0m th\u1ebf n\u00e0o? Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, h\u00e3y s\u1eed d\u1ee5ng h\u00e0m MAX , MIN ho\u1eb7c AVERAGE c\u00f9ng v\u1edbi INDEX MATCH.<\/span><\/p>\n<h4><span class=\"ez-toc-section\" id=\"INDEX_MATCH_voi_MAX\"><\/span><strong>INDEX MATCH v\u1edbi MAX<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 t\u00ecm gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t trong c\u1ed9t D v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u1eeb c\u1ed9t C trong c\u00f9ng m\u1ed9t h\u00e0ng, h\u00e3y s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c sau:<\/span><\/p>\n<p><strong>=INDEX(C2:C10, MATCH(MAX(D2:D10), D2:D10, 0))<\/strong><\/p>\n<h4><span class=\"ez-toc-section\" id=\"INDEX_MATCH_voi_MIN\"><\/span><strong>INDEX MATCH v\u1edbi MIN<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 x\u00e1c \u0111\u1ecbnh gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t trong c\u1ed9t D v\u00e0 k\u00e9o m\u1ed9t gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c li\u00ean k\u1ebft t\u1eeb c\u1ed9t C, h\u00e3y s\u1eed d\u1ee5ng gi\u00e1 tr\u1ecb sau:<\/span><\/p>\n<p><strong>=INDEX(C2:C10, MATCH(MIN(D2:D10), D2:D10, 0))<\/strong><\/p>\n<h4><span class=\"ez-toc-section\" id=\"INDEX_MATCH_voi_AVERAGE\"><\/span><strong>INDEX MATCH v\u1edbi AVERAGE<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 t\u00ednh ra gi\u00e1 tr\u1ecb g\u1ea7n nh\u1ea5t v\u1edbi gi\u00e1 tr\u1ecb trung b\u00ecnh trong D2: D10 v\u00e0 nh\u1eadn gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng t\u1eeb c\u1ed9t C, \u0111\u00e2y l\u00e0 c\u00f4ng th\u1ee9c \u0111\u1ec3 s\u1eed d\u1ee5ng:<\/span><\/p>\n<p><strong>=INDEX(C2:C10, MATCH(AVERAGE(D2:D10), D2:D10, -1 ))<\/strong><\/p>\n<p><span style=\"font-weight: 400\">T\u00f9y thu\u1ed9c v\u00e0o c\u00e1ch s\u1eafp x\u1ebfp d\u1eef li\u1ec7u c\u1ee7a b\u1ea1n m\u00e0 l\u1ef1a ch\u1ecdn 1 ho\u1eb7c -1 cho \u0111\u1ed1i s\u1ed1 th\u1ee9 ba (match_type) c\u1ee7a h\u00e0m MATCH:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">N\u1ebfu c\u1ed9t tra c\u1ee9u c\u1ee7a b\u1ea1n (trong tr\u01b0\u1eddng h\u1ee3p tr\u00ean l\u00e0 c\u1ed9t D) \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp t\u0103ng d\u1ea7n , h\u00e3y \u0111\u1eb7t 1. C\u00f4ng th\u1ee9c s\u1ebd t\u00ednh gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t nh\u1ecf h\u01a1n ho\u1eb7c b\u1eb1ng gi\u00e1 tr\u1ecb trung b\u00ecnh.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u00a0N\u1ebfu c\u1ed9t tra c\u1ee9u c\u1ee7a b\u1ea1n \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp gi\u1ea3m d\u1ea7n , h\u00e3y nh\u1eadp -1. C\u00f4ng th\u1ee9c s\u1ebd t\u00ednh gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng gi\u00e1 tr\u1ecb trung b\u00ecnh.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">\u00a0N\u1ebfu m\u1ea3ng tra c\u1ee9u c\u1ee7a b\u1ea1n ch\u1ee9a m\u1ed9t gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c b\u1eb1ng gi\u00e1 tr\u1ecb trung b\u00ecnh, b\u1ea1n c\u00f3 th\u1ec3 nh\u1eadp 0 \u0111\u1ec3 kh\u1edbp ch\u00ednh x\u00e1c. Kh\u00f4ng c\u1ea7n ph\u00e2n lo\u1ea1i.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">Trong v\u00ed d\u1ee5 tr\u00ean, c\u00e1c gi\u00e1 tr\u1ecb trong c\u1ed9t D \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp theo th\u1ee9 t\u1ef1 gi\u1ea3m d\u1ea7n, v\u00ec v\u1eady s\u1eed d\u1ee5ng -1 cho lo\u1ea1i \u0111\u1ed1i s\u00e1nh. K\u1ebft qu\u1ea3\u00a0 nh\u1eadn \u0111\u01b0\u1ee3c l\u00e0 \u201cTokyo\u201d v\u00ec d\u00e2n s\u1ed1 c\u1ee7a n\u00f3 (13.189.000) l\u00e0 \u0111\u1ed1i s\u00e1nh g\u1ea7n nh\u1ea5t l\u1edbn h\u01a1n trung b\u00ecnh (12.269.006).<\/span><\/p>\n<figure style=\"width: 619px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"INDEX MATCH v\u1edbi AVERAGE\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/index-match-other-functions.png\" alt=\"Using INDEX MATCH with AVERAGE, MAX, MIN\" width=\"619\" height=\"258\"><figcaption class=\"wp-caption-text\">INDEX MATCH v\u1edbi AVERAGE<\/figcaption><\/figure>\n<h3><span class=\"ez-toc-section\" id=\"Su_dung_INDEX_MATCH_voi_IFNAIFERROR\"><\/span><b>S\u1eed d\u1ee5ng INDEX MATCH v\u1edbi IFNA\/IFERROR<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">Nh\u01b0 b\u1ea1n c\u00f3 th\u1ec3 \u0111\u00e3 nh\u1eadn th\u1ea5y, n\u1ebfu m\u1ed9t <strong>c\u00f4ng th\u1ee9c INDEX MATCH<\/strong> trong Excel kh\u00f4ng th\u1ec3 t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb tra c\u1ee9u, n\u00f3 s\u1ebd t\u1ea1o ra l\u1ed7i # N \/ A. N\u1ebfu b\u1ea1n mu\u1ed1n thay th\u1ebf k\u00fd hi\u1ec7u l\u1ed7i ti\u00eau chu\u1ea9n b\u1eb1ng m\u1ed9t c\u00e1i g\u00ec \u0111\u00f3 c\u00f3 \u00fd ngh\u0129a h\u01a1n, h\u00e3y \u0111\u01b0a c\u00f4ng th\u1ee9c INDEX MATCH c\u1ee7a b\u1ea1n v\u00e0o <a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-su-dung-ham-ifna-de-tranh-loi-na-trong-excel.html\"><strong>h\u00e0m IFNA<\/strong><\/a>. V\u00ed d\u1ee5:<\/span><\/p>\n<p><strong>=IFNA(INDEX(C2:C10, MATCH(F1,A2:A10,0)), \u201cNo match is found\u201d)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">V\u00e0 b\u00e2y gi\u1edd, n\u1ebfu ai \u0111\u00f3 nh\u1eadp b\u1ea3ng tra c\u1ee9u kh\u00f4ng t\u1ed3n t\u1ea1i trong ph\u1ea1m vi tra c\u1ee9u, c\u00f4ng th\u1ee9c s\u1ebd th\u00f4ng b\u00e1o r\u00f5 r\u00e0ng cho ng\u01b0\u1eddi d\u00f9ng r\u1eb1ng kh\u00f4ng t\u00ecm th\u1ea5y k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p n\u00e0o:<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n mu\u1ed1n b\u1eaft t\u1ea5t c\u1ea3 c\u00e1c l\u1ed7i, kh\u00f4ng ch\u1ec9 l\u1ed7i # N \/ A, h\u00e3y s\u1eed d\u1ee5ng h\u00e0m IFERROR thay v\u00ec IFNA:<\/span><\/p>\n<p><strong>=IFERROR(INDEX(C2:C10, MATCH(F1,A2:A10,0)), \u201cOops, something went wrong!\u201d)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u00f3 l\u00e0 <em><strong>c\u00e1ch s\u1eed d\u1ee5ng INDEX v\u00e0 MATCH trong Excel<\/strong><\/em>. <strong>H\u1ecdc Excel Online<\/strong> hy v\u1ecdng c\u00e1c v\u00ed d\u1ee5 v\u1ec1 c\u00f4ng th\u1ee9c c\u1ee7a tr\u00ean s\u1ebd h\u1eefu \u00edch cho b\u1ea1n v\u00e0 mong \u0111\u01b0\u1ee3c g\u1eb7p b\u1ea1n tr\u00ean blog c\u1ee7a ch\u00fang t\u00f4i 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-413.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":4974,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-4973","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\/4973","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=4973"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/4973\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/4974"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=4973"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=4973"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=4973"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}