{"id":1847,"date":"2024-12-02T06:16:24","date_gmt":"2024-12-02T06:16:24","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/ham-index-va-ham-match-trong-excel-su-thay-the-tot-hon-ca-ham-vlookup\/"},"modified":"2024-12-02T06:16:24","modified_gmt":"2024-12-02T06:16:24","slug":"ham-index-va-ham-match-trong-excel-su-thay-the-tot-hon-ca-ham-vlookup","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/ham-index-va-ham-match-trong-excel-su-thay-the-tot-hon-ca-ham-vlookup\/","title":{"rendered":"H\u00c0M INDEX V\u00c0 H\u00c0M MATCH TRONG EXCEL \u2013 S\u1ef0 THAY TH\u1ebe T\u1ed0T H\u01a0N C\u1ea2 H\u00c0M VLOOKUP"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p style=\"text-align: justify\"><em>B\u00e0i vi\u1ebft n\u00e0y di\u1ec5n gi\u1ea3i \u01b0u \u0111i\u1ec3m ch\u00ednh c\u1ee7a <strong>h\u00e0m INDEX<\/strong> v\u00e0 <strong>h\u00e0m MATCH<\/strong> trong Excel \u2013 c\u00e1c \u01b0u \u0111i\u1ec3m khi\u1ebfn n\u00f3 ph\u00f9 h\u1ee3p h\u01a1n so v\u1edbi h\u00e0m VLOOKUP. B\u1ea1n s\u1ebd t\u00ecm th\u1ea5y nhi\u1ec1u v\u00ed d\u1ee5 c\u00f4ng th\u1ee9c \u2013 nh\u1eefng v\u00ed d\u1ee5 gi\u00fap b\u1ea1n d\u1ec5 d\u00e0ng x\u1eed l\u00fd c\u00e1c nhi\u1ec7m v\u1ee5 ph\u1ee9c t\u1ea1p khi h\u00e0m VLOOKUP kh\u00f4ng l\u00e0m \u0111\u01b0\u1ee3c.<\/em><\/p>\n<p style=\"text-align: justify\">Trong c\u00e1c b\u00e0i vi\u1ebft g\u1ea7n \u0111\u00e2y t\u00f4i \u0111\u00e3 c\u1ed1 g\u1eafng h\u1ebft m\u00ecnh \u0111\u1ec3 gi\u1ea3i th\u00edch nh\u1eefng \u0111i\u1ec3m c\u01a1 b\u1ea3n c\u1ee7a h\u00e0m <a href=\"http:\/\/blog.ezworkapp.com\/ham-vlookup-trong-excel-huong-dan-su-dung-chi-tiet-va-co-vi-du-cu-the.html\">VLOOKUP trong Excel<\/a> cho ng\u01b0\u1eddi m\u1edbi b\u1eaft \u0111\u1ea7u v\u00e0 cung c\u1ea5p c\u00e1c v\u00ed d\u1ee5 c\u00f4ng th\u1ee9c <a href=\"http:\/\/blog.ezworkapp.com\/ham-hlookup-trong-excel-va-cac-ung-dung-nang-cao-thuong-gap.html\" target=\"_blank\" rel=\"noopener noreferrer\">h\u00e0m\u00a0VLOOKUP n\u00e2ng cao<\/a>. V\u00e0 b\u00e2y gi\u1edd, t\u00f4i s\u1ebd c\u1ed1 g\u1eafng n\u1ebfu \u0111\u00e3 kh\u00f4ng n\u00f3i v\u1ec1 vi\u1ec7c s\u1eed d\u1ee5ng h\u00e0m VLOOKUP, th\u00ec \u00edt nh\u1ea5t s\u1ebd cung c\u1ea5p c\u00e1ch thay th\u1ebf \u0111\u1ec3 th\u1ef1c hi\u1ec7n ph\u00e9p t\u00ecm ki\u1ebfm theo c\u1ed9t trong Excel.<\/p>\n<p style=\"text-align: justify\">\u201cT\u00f4i c\u1ea7n n\u00f3 \u0111\u1ec3 l\u00e0m g\u00ec?\u201d b\u1ea1n c\u00f3 th\u1ec3 h\u1ecfi t\u00f4i nh\u01b0 th\u1ebf. B\u1edfi v\u00ec h\u00e0m VLOOKUP kh\u00f4ng ph\u1ea3i l\u00e0 h\u00e0m t\u00ecm ki\u1ebfm duy nh\u1ea5t c\u00f3 s\u1eb5n trong Excel, v\u00e0 vi\u1ec7c c\u00f3 nhi\u1ec1u h\u1ea1n ch\u1ebf c\u00f3 th\u1ec3 ng\u0103n c\u1ea3n b\u1ea1n \u0111\u1ea1t \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 mong mu\u1ed1n trong nhi\u1ec1u t\u00ecnh hu\u1ed1ng. M\u1eb7t kh\u00e1c, h\u00e0m INDEX v\u00e0 h\u00e0m MATCH trong Excel th\u00ec linh ho\u1ea1t h\u01a1n v\u00e0 c\u00f3 nh\u1eefng t\u00ednh n\u0103ng nh\u1ea5t \u0111\u1ecbnh khi\u1ebfn ch\u00fang v\u01b0\u1ee3t tr\u1ed9i h\u01a1n h\u00e0m VLOOKUP tr\u00ean nhi\u1ec1u ph\u01b0\u01a1ng di\u1ec7n.<\/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-674d50b6d1d9a\" 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\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#HAM_INDEX_VA_HAM_MATCH_TRONG_EXCEL_%E2%80%93_NHUNG_DIEU_CO_BAN\" title=\"H\u00c0M INDEX V\u00c0 H\u00c0M MATCH TRONG EXCEL \u2013 NH\u1eeeNG \u0110I\u1ec0U C\u01a0 B\u1ea2N\">H\u00c0M INDEX V\u00c0 H\u00c0M MATCH TRONG EXCEL \u2013 NH\u1eeeNG \u0110I\u1ec0U C\u01a0 B\u1ea2N<\/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\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#CU_PHAP_VA_CACH_DUNG_CUA_HAM_INDEX\" title=\"C\u00da PH\u00c1P V\u00c0 C\u00c1CH D\u00d9NG C\u1ee6A H\u00c0M INDEX:\">C\u00da PH\u00c1P V\u00c0 C\u00c1CH D\u00d9NG C\u1ee6A H\u00c0M INDEX:<\/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\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#CU_PHAP_VA_CACH_DUNG_CUA_HAM_MATCH\" title=\"C\u00da PH\u00c1P V\u00c0 C\u00c1CH D\u00d9NG C\u1ee6A H\u00c0M MATCH:\">C\u00da PH\u00c1P V\u00c0 C\u00c1CH D\u00d9NG C\u1ee6A H\u00c0M MATCH:<\/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\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#CACH_SU_DUNG_HAM_INDEX_MATCH_TRONG_EXCEL\" title=\"C\u00c1CH S\u1eec D\u1ee4NG H\u00c0M INDEX MATCH TRONG EXCEL:\">C\u00c1CH S\u1eec D\u1ee4NG H\u00c0M INDEX MATCH TRONG EXCEL:<\/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-5\" href=\"https:\/\/blog.hocexcel.online\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#TAI_SAO_SU_KET_HOP_HAM_INDEX_VA_HAM_MATCH_TRONG_EXCEL_LAI_TOT_HON_HAM_VLOOKUP\" title=\"T\u1ea0I SAO S\u1ef0 K\u1ebeT H\u1ee2P H\u00c0M INDEX V\u00c0 H\u00c0M MATCH TRONG EXCEL L\u1ea0I T\u1ed0T H\u01a0N H\u00c0M VLOOKUP\">T\u1ea0I SAO S\u1ef0 K\u1ebeT H\u1ee2P H\u00c0M INDEX V\u00c0 H\u00c0M MATCH TRONG EXCEL L\u1ea0I T\u1ed0T H\u01a0N H\u00c0M VLOOKUP<\/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\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#4_LOI_ICH_CHINH_CUA_VIEC_KET_HOP_HAM_INDEX_VOI_HAM_MATCH_TRONG_EXCEL\" title=\"4 L\u1ee2I \u00cdCH CH\u00cdNH C\u1ee6A VI\u1ec6C K\u1ebeT H\u1ee2P H\u00c0M INDEX V\u1edaI H\u00c0M MATCH TRONG EXCEL:\">4 L\u1ee2I \u00cdCH CH\u00cdNH C\u1ee6A VI\u1ec6C K\u1ebeT H\u1ee2P H\u00c0M INDEX V\u1edaI H\u00c0M MATCH TRONG EXCEL:<\/a><\/li>\n<\/ul>\n<\/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-7\" href=\"https:\/\/blog.hocexcel.online\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#HAM_INDEX_VA_HAM_MATCH_TRONG_EXCEL_%E2%80%93_VI_DU_CONG_THUC\" title=\"H\u00c0M INDEX V\u00c0 H\u00c0M MATCH TRONG EXCEL \u2013 V\u00cd D\u1ee4 C\u00d4NG TH\u1ee8C\">H\u00c0M INDEX V\u00c0 H\u00c0M MATCH TRONG EXCEL \u2013 V\u00cd D\u1ee4 C\u00d4NG TH\u1ee8C<\/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-8\" href=\"https:\/\/blog.hocexcel.online\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#CACH_TIM_KIEM_GIA_TRI_SANG_TRAI_VOI_HAM_INDEX_VA_HAM_MATCH\" title=\"C\u00c1CH T\u00ccM KI\u1ebeM GI\u00c1 TR\u1eca SANG TR\u00c1I V\u1edaI H\u00c0M INDEX V\u00c0 H\u00c0M MATCH\">C\u00c1CH T\u00ccM KI\u1ebeM GI\u00c1 TR\u1eca SANG TR\u00c1I V\u1edaI H\u00c0M INDEX V\u00c0 H\u00c0M MATCH<\/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\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#TINH_TOAN_VOI_HAM_INDEX_MATCH_TRONG_EXCEL_AVERAGE_MAX_MIN\" title=\"T\u00cdNH TO\u00c1N V\u1edaI H\u00c0M INDEX MATCH TRONG EXCEL (AVERAGE, MAX, MIN)\">T\u00cdNH TO\u00c1N V\u1edaI H\u00c0M INDEX MATCH TRONG EXCEL (AVERAGE, MAX, MIN)<\/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\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#NHUNG_DIEU_CAN_NHO_KHI_SU_DUNG_HAM_AVERAGE_VOI_HAM_INDEX_MATCH_TRONG_EXCEL\" title=\"NH\u1eeeNG \u0110I\u1ec0U C\u1ea6N NH\u1eda KHI S\u1eec D\u1ee4NG H\u00c0M AVERAGE V\u1edaI H\u00c0M INDEX MATCH TRONG EXCEL\">NH\u1eeeNG \u0110I\u1ec0U C\u1ea6N NH\u1eda KHI S\u1eec D\u1ee4NG H\u00c0M AVERAGE V\u1edaI H\u00c0M INDEX MATCH TRONG EXCEL<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/blog.hocexcel.online\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#CACH_SU_DUNG_HAM_INDEX_MATCH_DE_KIEM_GIA_TRI_THEO_HANG_VA_THEO_COT\" title=\"C\u00c1CH S\u1eec D\u1ee4NG H\u00c0M INDEX MATCH \u0110\u1ec2 KI\u1ebeM GI\u00c1 TR\u1eca THEO H\u00c0NG V\u00c0 THEO C\u1ed8T\">C\u00c1CH S\u1eec D\u1ee4NG H\u00c0M INDEX MATCH \u0110\u1ec2 KI\u1ebeM GI\u00c1 TR\u1eca THEO H\u00c0NG V\u00c0 THEO C\u1ed8T<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/blog.hocexcel.online\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#SU_DUNG_HAM_INDEX_MATCH_TIM_KIEM_KEM_THEO_NHIEU_DIEU_KIEN\" title=\"S\u1eec D\u1ee4NG H\u00c0M INDEX MATCH T\u00ccM KI\u1ebeM K\u00c8M THEO NHI\u1ec0U \u0110I\u1ec0U KI\u1ec6N\">S\u1eec D\u1ee4NG H\u00c0M INDEX MATCH T\u00ccM KI\u1ebeM K\u00c8M THEO NHI\u1ec0U \u0110I\u1ec0U KI\u1ec6N<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/blog.hocexcel.online\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html#SU_DUNG_HAM_INDEXMACTH_VOI_HAM_IFERROR_TRONG_EXCEL\" title=\"S\u1eec D\u1ee4NG H\u00c0M INDEX\/MACTH V\u1edaI H\u00c0M IFERROR TRONG EXCEL\">S\u1eec D\u1ee4NG H\u00c0M INDEX\/MACTH V\u1edaI H\u00c0M IFERROR TRONG EXCEL<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"HAM_INDEX_VA_HAM_MATCH_TRONG_EXCEL_%E2%80%93_NHUNG_DIEU_CO_BAN\"><\/span><strong>H\u00c0M INDEX V\u00c0 H\u00c0M MATCH TRONG EXCEL \u2013 NH\u1eeeNG \u0110I\u1ec0U C\u01a0 B\u1ea2N<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">V\u00ec m\u1ee5c \u0111\u00edch c\u1ee7a b\u00e0i vi\u1ebft n\u00e0y l\u00e0 \u0111\u1ec3 n\u00f3i v\u1ec1 c\u00e1ch t\u00ecm ki\u1ebfm thay th\u1ebf trong Excel b\u1eb1ng c\u00e1ch k\u1ebft h\u1ee3p h\u00e0m INDEX v\u1edbi h\u00e0m MATCH, n\u00ean ch\u00fang ta s\u1ebd kh\u00f4ng n\u00f3i nhi\u1ec1u v\u1ec1 c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng. Ch\u00fang ta s\u1ebd ch\u1ec9 n\u00f3i \u0111\u1ee7 \u0111\u1ec3 hi\u1ec3u \u00fd t\u01b0\u1edfng chung r\u1ed3i n\u00f3i k\u1ef9 h\u01a1n v\u1ec1 c\u00e1c v\u00ed d\u1ee5 c\u00f4ng th\u1ee9c \u2013 c\u00e1c v\u00ed d\u1ee5 cho th\u1ea5y \u01b0u \u0111i\u1ec3m c\u1ee7a vi\u1ec7c s\u1eed d\u1ee5ng h\u00e0m INDEX v\u00e0 h\u00e0m MATCH thay cho h\u00e0m VLOOKUP.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"CU_PHAP_VA_CACH_DUNG_CUA_HAM_INDEX\"><\/span>C\u00da PH\u00c1P V\u00c0 C\u00c1CH D\u00d9NG C\u1ee6A H\u00c0M INDEX:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">H\u00e0m INDEX trong Excel tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u1eeb m\u1ea3ng d\u1ef1a tr\u00ean s\u1ed1 th\u1ee9 t\u1ef1 h\u00e0ng v\u00e0 c\u1ed9t b\u1ea1n x\u00e1c \u0111\u1ecbnh. C\u00fa ph\u00e1p h\u00e0m INDEX r\u1ea5t \u0111\u01a1n gi\u1ea3n:<\/p>\n<p style=\"text-align: justify\"><strong>INDEX(array, row_num, [column_num])<\/strong><\/p>\n<p style=\"text-align: justify\">D\u01b0\u1edbi \u0111\u00e2y l\u00e0 c\u00e1ch gi\u1ea3i th\u00edch \u0111\u01a1n gi\u1ea3n cho m\u1ed7i th\u00f4ng s\u1ed1:<\/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-135.gif\" title=\"ex101_92019-nho-135\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p style=\"text-align: justify\"><strong>array<\/strong> \u2013 \u0111\u00e2y l\u00e0 d\u1ea3i \u00f4 n\u01a1i b\u1ea1n mu\u1ed1n gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1.<\/p>\n<p style=\"text-align: justify\"><strong>row_num<\/strong> \u2013 s\u1ed1 th\u1ee9 t\u1ef1 c\u1ee7a h\u00e0ng trong m\u1ea3ng m\u00e0 b\u1ea1n mu\u1ed1n gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1. N\u1ebfu b\u1ecf tr\u1ed1ng, th\u00ec colum_num l\u00e0 b\u1eaft bu\u1ed9c.<\/p>\n<p style=\"text-align: justify\"><strong>colum_num<\/strong> \u2013 s\u1ed1 th\u1ee9 t\u1ef1 c\u1ee7a c\u1ed9t trong m\u1ea3ng m\u00e0 b\u1ea1n mu\u1ed1n gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1. N\u1ebfu b\u1ecf tr\u1ed1ng, th\u00ec row_num l\u00e0 b\u1eaft bu\u1ed9c.<\/p>\n<p style=\"text-align: justify\">N\u1ebfu c\u1ea3 th\u00f4ng s\u1ed1 row_num v\u00e0 colum_num \u0111\u1ec1u \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng, th\u00ec h\u00e0m INDEX s\u1ebd tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb \u1edf \u00f4 l\u00e0 giao \u0111i\u1ec3m c\u1ee7a h\u00e0ng v\u00e0 c\u1ed9t x\u00e1c \u0111\u1ecbnh.<\/p>\n<p style=\"text-align: justify\"><strong>V\u00e0 \u0111\u00e2y l\u00e0 v\u00ed d\u1ee5 \u0111\u01a1n gi\u1ea3n nh\u1ea5t c\u1ee7a h\u00e0m INDEX:<\/strong><\/p>\n<p style=\"text-align: justify\"><strong>=INDEX(A1:C10,2,3)<\/strong><\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c t\u00ecm ki\u1ebfm d\u1ea3i \u00f4 A1:C10 r\u1ed3i tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb c\u1ee7a \u00f4 \u1edf h\u00e0ng th\u1ee9 hai v\u00e0 c\u1ed9t th\u1ee9 ba, c\u1ee5 th\u1ec3 l\u00e0 \u00f4 C2.<\/p>\n<p style=\"text-align: justify\">R\u1ea5t d\u1ec5, \u0111\u00fang kh\u00f4ng? Tuy nhi\u00ean, khi x\u1eed l\u00fd d\u1eef li\u1ec7u th\u1ef1c, b\u1ea1n h\u1ea7u nh\u01b0 kh\u00f4ng bi\u1ebft \u0111\u01b0\u1ee3c m\u00ecnh mu\u1ed1n h\u00e0ng n\u00e0o c\u1ed9t n\u00e0o, \u0111\u00e2y l\u00e0 l\u00fd do b\u1ea1n c\u1ea7n s\u1ef1 gi\u00fap \u0111\u1ee1 c\u1ee7a h\u00e0m MATCH.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"CU_PHAP_VA_CACH_DUNG_CUA_HAM_MATCH\"><\/span>C\u00da PH\u00c1P V\u00c0 C\u00c1CH D\u00d9NG C\u1ee6A H\u00c0M MATCH:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">H\u00e0m MATCH trong Excel t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm trong d\u1ea3i \u00f4, r\u1ed3i tr\u1ea3 v\u1ec1 v\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a \u00f4 \u0111\u00f3 trong d\u1ea3i \u00f4.<\/p>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5, n\u1ebfu d\u1ea3i \u00f4 B1:B3 ch\u1ee9a c\u00e1c gi\u00e1 tr\u1ecb \u201cNew-York\u201d, \u201cParis\u201d, \u201cLondon\u201d, r\u1ed3i c\u00f4ng th\u1ee9c =MATCH(\u201cLondon\u201d,B1:B3,0) tr\u1ea3 v\u1ec1 s\u1ed1 3 b\u1edfi v\u00ec \u201cLondon\u201d l\u00e0 m\u1ee5c th\u1ee9 ba trong d\u1ea3i \u00f4.<\/p>\n<p style=\"text-align: justify\">C\u00fa ph\u00e1p h\u00e0m MATCH c\u00f3 d\u1ea1ng d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\"><strong>MATCH(lookup_value, lookup_array, [match_type])<\/strong><\/p>\n<p style=\"text-align: justify\"><strong>lookup_value<\/strong> \u2013 \u0111\u00e2y l\u00e0 ch\u1eef s\u00f4 hay chu\u1ed7i k\u00fd t\u1ef1 m\u00e0 b\u1ea1n t\u00ecm ki\u1ebfm. \u0110\u00e2y c\u00f3 th\u1ec3 l\u00e0 m\u1ed9t gi\u00e1 tr\u1ecb, m\u1ed9t tham chi\u1ebfu \u00f4 hay m\u1ed9t gi\u00e1 tr\u1ecb l\u00f4 gic.<\/p>\n<p style=\"text-align: justify\"><strong>lookup_array<\/strong> \u2013 d\u1ea3i \u00f4 \u0111\u01b0\u1ee3c t\u00ecm ki\u1ebfm.<\/p>\n<p style=\"text-align: justify\"><strong>match_type<\/strong> \u2013 th\u00f4ng s\u1ed1 n\u00e0y n\u00f3i cho h\u00e0m MATCH bi\u1ebft b\u1ea1n mu\u1ed1n tr\u1ea3 v\u1ec1 s\u1ef1 ph\u00f9 h\u1ee3p tuy\u1ec7t \u0111\u1ed1i hay s\u1ef1 ph\u00f9 h\u1ee3p t\u01b0\u01a1ng \u0111\u1ed1i.<\/p>\n<p style=\"text-align: justify\"><strong>1 hay b\u1ecf tr\u1ed1ng<\/strong> \u2013 t\u00ecm gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t \u2013 gi\u00e1 tr\u1ecb nh\u1ecf h\u01a1n hay b\u1eb1ng gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm. C\u00e1c gi\u00e1 tr\u1ecb trong m\u1ea3ng c\u1ea7n t\u00ecm ph\u1ea3i \u0111\u01b0\u1ee3c l\u1ecdc theo th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n, c\u1ee5 th\u1ec3 l\u00e0 t\u1eeb nh\u1ecf nh\u1ea5t \u0111\u1ebfn l\u1edbn nh\u1ea5t.<\/p>\n<p style=\"text-align: justify\"><strong>0<\/strong> \u2013 t\u00ecm gi\u00e1 tr\u1ecb \u0111\u1ea7u ti\u00ean b\u1eb1ng gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm. Trong s\u1ef1 k\u1ebft h\u1ee3p c\u1ee7a h\u00e0m INDEX v\u00e0 h\u00e0m MATCH, b\u1ea1n h\u1ea7u nh\u01b0 lu\u00f4n lu\u00f4n c\u1ea7n s\u1ef1 ph\u00f9 h\u1ee3p tuy\u1ec7t \u0111\u1ed1i, v\u00ec th\u1ebf c\u00e2u l\u1ec7nh th\u1ee9 ba c\u1ee7a h\u00e0m MATCH l\u00e0 \u201c0\u201d.<\/p>\n<p style=\"text-align: justify\"><strong>-1<\/strong> \u2013 t\u00ecm gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t l\u1edbn h\u01a1n hay b\u1eb1ng gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm. C\u00e1c gi\u00e1 tr\u1ecb trong m\u1ea3ng c\u1ea7n t\u00ecm ph\u1ea3i \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp theo th\u1ee9 t\u1ef1 gi\u1ea3m d\u1ea7n, c\u1ee5 th\u1ec3 l\u00e0 t\u1eeb l\u1edbn nh\u1ea5t \u0111\u1ebfn nh\u1ecf nh\u1ea5t.<\/p>\n<p style=\"text-align: justify\">L\u00fac \u0111\u1ea7u, t\u00ednh h\u1eefu \u00edch c\u1ee7a h\u00e0m MATCH c\u00f3 th\u1ec3 kh\u00f4ng r\u00f5 r\u00e0ng. Ai quan t\u00e2m \u0111\u1ebfn v\u1ecb tr\u00ed c\u1ee7a gi\u00e1 tr\u1ecb trong d\u1ea3i \u00f4 ch\u1ee9? \u0110i\u1ec1u ta th\u1eadt s\u1ef1 mu\u1ed1n bi\u1ebft \u0111\u00f3 ch\u00ednh l\u00e0 gi\u00e1 tr\u1ecb.<\/p>\n<p style=\"text-align: justify\">H\u00e3y \u0111\u1ec3 t\u00f4i nh\u1eafc b\u1ea1n nh\u1edb r\u1eb1ng v\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm (c\u1ee5 th\u1ec3 l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 c\u1ee7a c\u1ed9t hay\/v\u00e0 h\u00e0ng) ch\u00ednh l\u00e0 t\u1ea5t c\u1ea3 nh\u1eefng g\u00ec b\u1ea1n c\u1ea7n \u0111\u1ec3 nh\u1eadp v\u00e0o c\u00e2u l\u1ec7nh\u00a0row_num hay\/v\u00e0 column_num c\u1ee7a h\u00e0m INDEX. C\u00f3 th\u1ec3 b\u1ea1n v\u1eabn c\u00f2n nh\u1edb, h\u00e0m INDEX c\u00f3 th\u1ec3 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb \u1edf giao \u0111i\u1ec3m c\u1ee7a h\u00e0ng v\u00e0 c\u1ed9t x\u00e1c \u0111\u1ecbnh, nh\u01b0ng n\u00f3 kh\u00f4ng th\u1ec3 x\u00e1c \u0111\u1ecbnh m\u1ed9t c\u00e1ch ch\u00ednh x\u00e1c b\u1ea1n mu\u1ed1n c\u1ed9t n\u00e0o h\u00e0ng n\u00e0o.<\/p>\n<p style=\"text-align: justify\">Xem th\u00eam:\u00a0H\u01b0\u1edbng d\u1eabn h\u1ecdc <a href=\"http:\/\/blog.ezworkapp.com\/excel-co-ban\">Excel c\u01a1 b\u1ea3n<\/a><\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"CACH_SU_DUNG_HAM_INDEX_MATCH_TRONG_EXCEL\"><\/span>C\u00c1CH S\u1eec D\u1ee4NG H\u00c0M INDEX MATCH TRONG EXCEL:<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">B\u00e2y gi\u1edd b\u1ea1n \u0111\u00e3 bi\u1ebft nh\u1eefng \u0111i\u1ec1u c\u01a1 b\u1ea3n c\u1ee7a hai h\u00e0m tr\u00ean, t\u00f4i tin r\u1eb1ng b\u1ea1n c\u00f3 th\u1ec3 m\u01b0\u1eddng t\u01b0\u1ee3ng hai h\u00e0m n\u00e0y s\u1ebd k\u1ebft h\u1ee3p nh\u01b0 th\u1ebf n\u00e0o.<\/p>\n<p style=\"text-align: justify\">H\u00e0m MATCH x\u00e1c \u0111\u1ecbnh v\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm trong d\u1ea3i \u00f4 x\u00e1c \u0111\u1ecbnh. T\u1eeb \u0111\u00f3, h\u00e0m INDEX s\u1eed d\u1ee5ng con s\u1ed1 \u0111\u00f3, hay c\u00e1c con s\u1ed1 \u0111\u00f3, r\u1ed3i tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb \u1edf \u00f4 t\u01b0\u01a1ng \u1ee9ng.<\/p>\n<p style=\"text-align: justify\">V\u1eabn g\u1eb7p kh\u00f3 kh\u0103n trong vi\u1ec7c m\u01b0\u1eddng t\u01b0\u1ee3ng ra \u0111\u00fang kh\u00f4ng? H\u00e3y ngh\u0129 v\u1ec1 h\u00e0m INDEX\/MATCH theo c\u00e1ch n\u00e0y:<\/p>\n<p style=\"text-align: justify\"><strong>=INDEX (<em>column to return a value from<\/em>, MATCH (<em>lookup value<\/em>, <em>column to lookup against<\/em>, 0))<\/strong><\/p>\n<p style=\"text-align: justify\">T\u00f4i tin r\u1eb1ng vi\u1ec7c xem v\u00ed d\u1ee5 s\u1ebd d\u1ec5 hi\u1ec3u h\u01a1n. Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t danh s\u00e1ch c\u00e1c th\u1ee7 \u0111\u00f4 nh\u01b0 th\u1ebf n\u00e0y:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-6266 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/101-300x189-1.png\" width=\"300\" height=\"189\" title=\"101-300x189-1\"><\/p>\n<p style=\"text-align: justify\">H\u00e3y t\u00ecm d\u00e2n s\u1ed1 c\u1ee7a m\u1ed9t th\u1ee7 \u0111\u00f4 n\u00e0o \u0111\u00f3, gi\u1ea3 s\u1eed th\u1ee7 \u0111\u00f4 c\u1ee7a Nh\u1eadt B\u1ea3n \u0111i, h\u00e3y d\u00f9ng c\u00f4ng th\u1ee9c INDEX\/MATCH d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\">=INDEX($D$2:$D$10,MATCH(\u201cJapan\u201d,$B$2:$B$10,0))<\/p>\n<p style=\"text-align: justify\">B\u00e2y gi\u1edd, h\u00e3y ph\u00e2n t\u00edch xem m\u1ed7i th\u00e0nh ph\u1ea7n c\u1ee7a c\u00f4ng th\u1ee9c c\u00f3 t\u00e1c d\u1ee5ng g\u00ec:<\/p>\n<p style=\"text-align: justify\">H\u00e0m MATCH t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm \u201cNh\u1eadt B\u1ea3n\u201d \u1edf c\u1ed9t B, ch\u00ednh x\u00e1c h\u01a1n l\u00e0 d\u1ea3i \u00f4 B2:B10, \u00a0r\u1ed3i tr\u1ea3 v\u1ec1 s\u1ed1 3, b\u1edfi v\u00ec \u201cNh\u1eadt B\u1ea3n\u201d n\u1eb1m th\u1ee9 ba trong danh s\u00e1ch.<\/p>\n<p style=\"text-align: justify\">H\u00e0m INDEX l\u1ea5y s\u1ed1 3 t\u1eeb th\u00f4ng s\u1ed1 th\u1ee9 hai (row_num), th\u00f4ng s\u1ed1 n\u00e0y ch\u1ec9 r\u00f5 b\u1ea1n mu\u1ed1n tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u1eeb h\u00e0ng n\u00e0o, r\u1ed3i tr\u1edf th\u00e0nh c\u00f4ng th\u1ee9c \u0111\u01a1n gi\u1ea3n nh\u01b0 sau\u00a0=INDEX($D$2:$D$10,3). N\u00f3i m\u1ed9t c\u00e1ch \u0111\u01a1n gi\u1ea3n, c\u00f4ng th\u1ee9c \u0111\u01b0\u1ee3c \u0111\u1ecdc nh\u01b0 sau: t\u00ecm trong d\u1ea3i \u00f4 D2:D10 r\u1ed3i tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb c\u1ee7a \u00f4 \u1edf h\u00e0ng th\u1ee9 ba, c\u1ee5 th\u1ec3 l\u00e0 \u00f4 D4, b\u1edfi v\u00ec ch\u00fang ta b\u1eaft \u0111\u1ea7u \u0111\u1ebfm t\u1eeb h\u00e0ng th\u1ee9 hai.<\/p>\n<p style=\"text-align: justify\">V\u00e0 \u0111\u00e2y l\u00e0 k\u1ebft qu\u1ea3 b\u1ea1n c\u00f3 \u0111\u01b0\u1ee3c trong Excel:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-6267 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/102-300x136-1.png\" width=\"300\" height=\"136\" title=\"102-300x136-1\"><\/p>\n<p style=\"text-align: justify\"><strong>Quan tr\u1ecdng!<\/strong> S\u1ed1 h\u00e0ng v\u00e0 s\u1ed1 c\u1ed9t trong m\u1ea3ng INDEX n\u00ean l\u1ea7n l\u01b0\u1ee3t kh\u1edbp v\u1edbi s\u1ed1 h\u00e0ng v\u00e0 s\u1ed1 c\u1ed9t trong th\u00f4ng s\u1ed1\u00a0row_num hay\/v\u00e0 column_num trong h\u00e0m MATCH. N\u1ebfu kh\u00f4ng th\u00ec, c\u00f4ng th\u1ee9c s\u1ebd tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 kh\u00f4ng ch\u00ednh x\u00e1c.<\/p>\n<p style=\"text-align: justify\">\u0110\u1ee3i \u0111\u00e3\u2026t\u1ea1i sao ch\u00fang ta kh\u00f4ng d\u00f9ng c\u00f4ng th\u1ee9c VLOOKUP sau? M\u1ee5c \u0111\u00edch c\u1ee7a vi\u1ec7c l\u00e3ng ph\u00ed th\u1eddi gian c\u1ed1 g\u1eafng t\u00ecm ki\u1ebfm s\u1ef1 thay th\u1ebf b\u00ed \u1ea9n c\u1ee7a h\u00e0m INDEX\/MATCH trong Excel l\u00e0 g\u00ec?<\/p>\n<p style=\"text-align: justify\">=VLOOKUP(\u201cJapan\u201d,$B$2:$D$2,3)<\/p>\n<p style=\"text-align: justify\">Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, ch\u1eb3ng c\u00f3 m\u1ee5c \u0111\u00edch n\u00e0o c\u1ea3 \ud83d\ude42 V\u00ed d\u1ee5 \u0111\u01a1n gi\u1ea3n n\u00e0y ch\u1ec9 d\u00e0nh cho vi\u1ec7c di\u1ec5n gi\u1ea3i m\u00e0 th\u00f4i, \u0111\u1ec3 b\u1ea1n th\u1ea5y \u0111\u01b0\u1ee3c c\u00e1ch h\u00e0m INDEX v\u00e0 h\u00e0m MATCH k\u1ebft h\u1ee3p v\u1edbi nhau. C\u00e1c v\u00ed d\u1ee5 kh\u00e1c b\u00ean d\u01b0\u1edbi s\u1ebd cho b\u1ea1n th\u1ea5y kh\u1ea3 n\u0103ng th\u1eadt s\u1ef1 c\u1ee7a s\u1ef1 k\u1ebft h\u1ee3p h\u00e0m MATCH\/INDEX \u2013 \u0111i\u1ec1u n\u00e0y gi\u00fap b\u1ea1n d\u1ec5 d\u00e0ng x\u1eed l\u00fd nhi\u1ec1u vi\u1ec5n c\u1ea3nh ph\u1ee9c t\u1ea1p khi h\u00e0m VLOOKUP kh\u00f4ng x\u1eed l\u00fd \u0111\u01b0\u1ee3c.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"TAI_SAO_SU_KET_HOP_HAM_INDEX_VA_HAM_MATCH_TRONG_EXCEL_LAI_TOT_HON_HAM_VLOOKUP\"><\/span>T\u1ea0I SAO S\u1ef0 K\u1ebeT H\u1ee2P H\u00c0M INDEX V\u00c0 H\u00c0M MATCH TRONG EXCEL L\u1ea0I T\u1ed0T H\u01a0N H\u00c0M VLOOKUP<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">Khi quy\u1ebft \u0111\u1ecbnh c\u00f4ng th\u1ee9c d\u00f9ng t\u00ecm ki\u1ebfm theo c\u1ed9t, ph\u1ea7n l\u1edbn c\u00e1c chuy\u00ean gia Excel cho r\u1eb1ng h\u00e0m INDEX k\u1ebft h\u1ee3p v\u1edbi h\u00e0m MATCH th\u00ec h\u1eefu \u00edch h\u01a1n h\u00e0m VLOOKUP r\u1ea5t nhi\u1ec1u. Tuy nhi\u00ean, r\u1ea5t nhi\u1ec1u ng\u01b0\u1eddi d\u00f9ng Excel v\u1eabn vi\u1ec7n \u0111\u1ebfn h\u00e0m VLOOKUP v\u00ec n\u00f3 \u0111\u01a1n gi\u1ea3n. \u0110i\u1ec1u n\u00e0y di\u1ec5n ra v\u00ec r\u1ea5t \u00edt ng\u01b0\u1eddi hi\u1ec3u tr\u1ecdn v\u1eb9n t\u1ea5t c\u1ea3 c\u00e1c l\u1ee3i \u00edch c\u1ee7a vi\u1ec7c \u0111\u1ed5i t\u1eeb h\u00e0m VLOOKUP th\u00e0nh h\u00e0m INDEX k\u1ebft h\u1ee3p v\u1edbi h\u00e0m MATCH, v\u00e0 v\u00ec l\u00fd do n\u00e0y n\u00ean ch\u1eb3ng ai ch\u1ecbu d\u00e0nh th\u1eddi gian h\u1ecdc m\u1ed9t c\u00f4ng th\u1ee9c ph\u1ee9c t\u1ea1p h\u01a1n.<\/p>\n<p style=\"text-align: justify\">D\u01b0\u1edbi \u0111\u00e2y, t\u00f4i s\u1ebd c\u1ed1 g\u1eafng ch\u1ec9 ra c\u00e1c l\u1ee3i \u00edch ch\u00ednh c\u1ee7a vi\u1ec7c k\u1ebft h\u1ee3p h\u00e0m INDEX v\u1edbi h\u00e0m MATCH trong Excel, r\u1ed3i \u0111\u1ec3 b\u1ea1n quy\u1ebft \u0111\u1ecbnh li\u1ec7u b\u1ea1n v\u1eabn d\u00f9ng h\u00e0m VLOOKUP hay d\u00f9ng h\u00e0m INDEX k\u1ebft h\u1ee3p v\u1edbi h\u00e0m MATCH.<\/p>\n<h4 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"4_LOI_ICH_CHINH_CUA_VIEC_KET_HOP_HAM_INDEX_VOI_HAM_MATCH_TRONG_EXCEL\"><\/span>4 L\u1ee2I \u00cdCH CH\u00cdNH C\u1ee6A VI\u1ec6C K\u1ebeT H\u1ee2P H\u00c0M INDEX V\u1edaI H\u00c0M MATCH TRONG EXCEL:<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<ul style=\"list-style-type: disc\">\n<li style=\"text-align: justify\">T\u00ecm ki\u1ebfm t\u1eeb ph\u1ea3i sang tr\u00e1i. B\u1ea5t c\u1ee9 ng\u01b0\u1eddi d\u00f9ng th\u00f4ng th\u1ea1o n\u00e0o c\u0169ng bi\u1ebft, h\u00e0m VLOOKUP trong Excel kh\u00f4ng th\u1ec3 t\u00ecm ki\u1ebfm ph\u00eda b\u00ean tr\u00e1i, c\u00f3 ngh\u0129a l\u00e0 gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ph\u1ea3i lu\u00f4n n\u1eb1m \u1edf c\u1ed9t c\u1eadn tr\u00e1i c\u1ee7a d\u1ea3i \u00f4 c\u1ea7n t\u00ecm. V\u1edbi h\u00e0m INDEX MATCH, c\u1ed9t c\u1ea7n t\u00ecm c\u1ee7a b\u1ea1n c\u00f3 th\u1ec3 n\u1eb1m \u1edf ph\u00eda b\u00ean ph\u1ea3i c\u1ee7a b\u1ea3ng.<\/li>\n<li style=\"text-align: justify\">Ch\u00e8n hay x\u00f3a c\u1ed9t m\u1ed9t c\u00e1ch an to\u00e0n. C\u00f4ng th\u1ee9c VLOOKUP b\u1ecb l\u1ed7i hay tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 kh\u00f4ng ch\u00ednh x\u00e1c khi x\u00f3a hay th\u00eam c\u1ed9t m\u1edbi v\u00e0o b\u1ea3ng c\u1ea7n t\u00ecm. V\u1edbi h\u00e0m VLOOKUP, b\u1ea5t c\u1ee9 c\u1ed9t n\u00e0o \u0111\u01b0\u1ee3c ch\u00e8n hay x\u00f3a c\u0169ng c\u00f3 th\u1ec3 thay \u0111\u1ed5i k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 b\u1edfi c\u00f4ng th\u1ee9c b\u1edfi v\u00ec c\u00fa ph\u00e1p h\u00e0m VLOOKUP y\u00eau c\u1ea7u \u0111\u1ecbnh r\u00f5 to\u00e0n b\u1ed9 m\u1ea3ng b\u1ea3ng v\u00e0 m\u1ed9t s\u1ed1 nh\u1ea5t \u0111\u1ecbnh \u0111\u1ec3 ch\u1ec9 b\u1ea1n mu\u1ed1n l\u1ea5y d\u1eef li\u1ec7u t\u1eeb c\u1ed9t n\u00e0o.<\/li>\n<\/ul>\n<p style=\"text-align: justify\"><strong>V\u00ed d\u1ee5:<\/strong>\u00a0n\u1ebfu b\u1ea1n c\u00f3 b\u1ea3ng A1:C10 v\u00e0 mu\u1ed1n tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u1eeb c\u1ed9t B, b\u1ea1n s\u1ebd \u0111\u1eb7t s\u1ed1 \u201c2\u201d \u1edf th\u00f4ng s\u1ed1 th\u1ee9 ba (col_index_num) c\u1ee7a c\u00f4ng th\u1ee9c VLOOKUP, v\u00ed d\u1ee5\u00a0=VLOOKUP(\u201clookup value\u201d, A1:C10, 2). N\u1ebfu sau \u0111\u00f3, b\u1ea1n ch\u00e8n m\u1ed9t c\u1ed9t m\u1edbi gi\u1eefa c\u1ed9t A v\u00e0 c\u1ed9t B, th\u00ec b\u1ea1n ph\u1ea3i thay \u0111\u1ed5i \u201c2\u201d th\u00e0nh \u201c3\u201d trong c\u00f4ng th\u1ee9c, n\u1ebfu kh\u00f4ng th\u00ec n\u00f3 s\u1ebd tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u1eeb c\u1ed9t m\u1edbi \u0111\u01b0\u1ee3c ch\u00e8n v\u00e0o.<\/p>\n<ul style=\"list-style-type: disc\">\n<li style=\"text-align: justify\">V\u1edbi h\u00e0m INDEX MATCH, b\u1ea1n c\u00f3 th\u1ec3 x\u00f3a hay ch\u00e8n c\u1ed9t m\u1edbi v\u00e0o b\u1ea3ng c\u1ea7n t\u00ecm m\u00e0 kh\u00f4ng thay \u0111\u1ed5i k\u1ebft qu\u1ea3 b\u1edfi v\u00ec b\u1ea1n \u0111\u1ecbnh r\u00f5 c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm m\u1ed9t c\u00e1ch tr\u1ef1c ti\u1ebfp. V\u00e0 \u0111\u00e2y l\u00e0 m\u1ed9t l\u1ee3i \u00edch r\u1ea5t l\u1edbn, nh\u1ea5t l\u00e0 khi x\u1eed l\u00fd chu\u1ed7i d\u1eef li\u1ec7u l\u1edbn, v\u00ec b\u1ea1n c\u00f3 kh\u1ea3 n\u0103ng ch\u00e8n hay x\u00f3a m\u00e0 kh\u00f4ng c\u1ea7n lo l\u1eafng v\u1ec1 vi\u1ec7c c\u1eadp nh\u1eadt m\u1ed7i c\u00f4ng th\u1ee9c t\u00ecm ki\u1ebfm li\u00ean quan.<\/li>\n<li style=\"text-align: justify\">Kh\u00f4ng \u0111\u1eb7t gi\u1edbi h\u1ea1n cho k\u00edch th\u01b0\u1edbc d\u1eef li\u1ec7u c\u1ea7n t\u00ecm. Khi s\u1eed d\u1ee5ng h\u00e0m VLOOKUP, h\u00e3y nh\u1edb r\u1eb1ng t\u1ed5ng chi\u1ec1u d\u00e0i c\u1ee7a ti\u00eau chu\u1ea9n t\u00ecm ki\u1ebfm kh\u00f4ng n\u00ean v\u01b0\u1ee3t qu\u00e1 255 k\u00fd t\u1ef1, n\u1ebfu kh\u00f4ng th\u00ec b\u1ea1n s\u1ebd nh\u1eadn \u0111\u01b0\u1ee3c l\u1ed7i #VALUE!. V\u00ec th\u1ebf, n\u1ebfu chu\u1ed7i d\u1eef li\u1ec7u c\u1ee7a b\u1ea1n ch\u1ee9a m\u1ed9t chu\u1ed7i d\u00e0i, th\u00ec h\u00e0m INDEX MATCH l\u00e0 gi\u1ea3i ph\u00e1p kh\u1ea3 thi duy nh\u1ea5t.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed, b\u1ea1n s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c VLOOKUP d\u01b0\u1edbi \u0111\u00e2y \u0111\u1ec3 t\u00ecm ki\u1ebfm \u00a0t\u1eeb \u00f4 B5 \u0111\u1ebfn \u00f4 D10 cho gi\u00e1 tr\u1ecb \u1edf \u00f4 A2:<\/p>\n<p style=\"text-align: justify\">=VLOOKUP(A2,B5:D10,3,FALSE)<\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c s\u1ebd kh\u00f4ng ch\u1ea1y n\u1ebfu gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm \u1edf \u00f4 A2 v\u01b0\u1ee3t qu\u00e1 255 k\u00fd t\u1ef1. Thay v\u00e0o \u0111\u00f3, b\u1ea1n n\u00ean s\u1eed d\u1ee5ng h\u00e0m INDEX\/MATCH t\u01b0\u01a1ng t\u1ef1:<\/p>\n<p style=\"text-align: justify\">=INDEX(D5:D10, MATCH(TRUE, INDEX(B5:B10=A2, 0), 0))<\/p>\n<ul style=\"list-style-type: disc\">\n<li style=\"text-align: justify\">T\u1ed1c \u0111\u1ed9 x\u1eed l\u00fd nhanh h\u01a1n. N\u1ebfu b\u1ea1n \u0111ang x\u1eed l\u00fd m\u1ed9t b\u1ea3ng t\u01b0\u01a1ng \u0111\u1ed1i nh\u1ecf, th\u00ec \u0111i\u1ec3m kh\u00e1c bi\u1ec7t trong vi\u1ec7c x\u1eed l\u00fd c\u1ee7a Excel s\u1ebd h\u1ea7u nh\u01b0 kh\u00f4ng th\u1ec3 nh\u1eadn th\u1ea5y, nh\u1ea5t l\u00e0 \u1edf c\u00e1c phi\u00ean b\u1ea3n g\u1ea7n \u0111\u00e2y. Nh\u01b0ng n\u1ebfu b\u1ea1n s\u1eed d\u1ee5ng b\u1ea3ng t\u00ednh l\u1edbn c\u00f3 h\u00e0ng ng\u00e0n h\u00e0ng v\u00e0 h\u00e0ng ng\u00e0n c\u00f4ng th\u1ee9c c\u1ea7n t\u00ecm, Excel s\u1ebd l\u00e0m vi\u1ec7c nhanh h\u01a1n nhi\u1ec1u n\u1ebfu b\u1ea1n s\u1eed d\u1ee5ng h\u00e0m INDEX\/MATCH thay v\u00ec h\u00e0m VLOOKUP. N\u00f3i chung, vi\u1ec7c s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c INDEX\/MATCH s\u1ebd t\u0103ng t\u1ed1c \u0111\u1ed9 x\u1eed l\u00fd c\u1ee7a Excel l\u00ean 13 % so v\u1edbi c\u00f4ng th\u1ee9c VLOOKUP t\u01b0\u01a1ng t\u1ef1.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">\u1ea2nh h\u01b0\u1edfng c\u1ee7a h\u00e0m VLOOKUP l\u00ean vi\u1ec7c x\u1eed l\u00fd c\u1ee7a Excel c\u00f3 th\u1ec3 s\u1ebd r\u00f5 r\u00e0ng h\u01a1n n\u1ebfu s\u1ed5 l\u00e0m vi\u1ec7c c\u1ee7a b\u1ea1n ch\u1ee9a h\u00e0ng tr\u0103m c\u00f4ng th\u1ee9c m\u1ea3ng ph\u1ee9c t\u1ea1p nh\u01b0 VLOOKUP v\u00e0 SUM. \u0110i\u1ec3m m\u1ea5u ch\u1ed1t \u0111\u00f3 l\u00e0 vi\u1ec7c ki\u1ec3m tra m\u1ed7i gi\u00e1 tr\u1ecb trong m\u1ea3ng y\u00eau c\u1ea7u m\u1ed9t h\u00e0m VLOOKUP kh\u00e1c. V\u00ec th\u1ebf, m\u1ea3ng c\u00e0ng c\u00f3 nhi\u1ec1u gi\u00e1 tr\u1ecb v\u00e0 s\u1ed5 l\u00e0m vi\u1ec7c c\u1ee7a b\u1ea1n c\u00e0ng c\u00f3 nhi\u1ec1u c\u00f4ng th\u1ee9c m\u1ea3ng, th\u00ec Excel x\u1eed l\u00fd c\u00e0ng ch\u1eadm.<\/p>\n<p style=\"text-align: justify\">M\u1eb7t kh\u00e1c, v\u1edbi h\u00e0m INDEX\/MATCH, Excel ch\u1ec9 ph\u1ea3i c\u00e2n nh\u1eafc c\u1ed9t t\u00ecm ki\u1ebfm v\u00e0 c\u1ed9t tr\u1ea3 v\u1ec1, n\u00ean n\u00f3 x\u1eed l\u00fd c\u00f4ng th\u1ee9c nhanh h\u01a1n nhi\u1ec1u.<\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"HAM_INDEX_VA_HAM_MATCH_TRONG_EXCEL_%E2%80%93_VI_DU_CONG_THUC\"><\/span>H\u00c0M INDEX V\u00c0 H\u00c0M MATCH TRONG EXCEL \u2013 V\u00cd D\u1ee4 C\u00d4NG TH\u1ee8C<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">B\u00e2y gi\u1edd, b\u1ea1n \u0111\u00e3 bi\u1ebft l\u00fd do n\u00ean h\u1ecdc h\u00e0m MATCH\/INDEX, n\u00ean h\u00e3y \u0111i v\u00e0o ph\u1ea7n th\u00fa v\u1ecb nh\u1ea5t v\u00e0 xem xem b\u1ea1n c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng nguy\u00ean l\u00fd hay kh\u00f4ng.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"CACH_TIM_KIEM_GIA_TRI_SANG_TRAI_VOI_HAM_INDEX_VA_HAM_MATCH\"><\/span>C\u00c1CH T\u00ccM KI\u1ebeM GI\u00c1 TR\u1eca SANG TR\u00c1I V\u1edaI H\u00c0M INDEX V\u00c0 H\u00c0M MATCH<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">Nh\u01b0 \u0111\u00e3 \u0111\u01b0\u1ee3c nh\u1eafc \u0111\u1ebfn trong b\u1ea5t k\u1ef3 b\u00e0i h\u01b0\u1edbng d\u1eabn VLOOKUP n\u00e0o, h\u00e0m Excel n\u00e0y kh\u00f4ng th\u1ec3 t\u00ecm ki\u1ebfm b\u00ean tr\u00e1i c\u1ee7a n\u00f3. V\u00ec v\u1eady, n\u1ebfu c\u1ed9t c\u1ea7n t\u00ecm c\u1ee7a b\u1ea1n kh\u00f4ng ph\u1ea3i l\u00e0 c\u1ed9t c\u1eadn tr\u00e1i trong d\u1ea3i \u00f4 c\u1ea7n t\u00ecm, th\u00ec kh\u00f4ng bao gi\u1edd c\u00f4ng th\u1ee9c VLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 m\u00e0 b\u1ea1n mong mu\u1ed1n.<\/p>\n<p style=\"text-align: justify\">H\u00e0m INDEX MATCH trong Excel th\u00ec linh ho\u1ea1t h\u01a1n v\u00e0 th\u1eadt s\u1ef1 kh\u00f4ng quan t\u00e2m vi\u1ec7c c\u1ed9t tr\u1ea3 v\u1ec1 n\u1eb1m \u1edf \u0111\u00e2u. V\u00ed d\u1ee5, ch\u00fang ta s\u1ebd d\u00f9ng m\u1ed9t b\u1ea3ng \u2013 b\u1ea3ng n\u00e0y li\u1ec7t k\u00ea th\u1ee7 \u0111\u00f4 v\u00e0 d\u00e2n s\u1ed1. L\u1ea7n n\u00e0y, h\u00e3y l\u1eadp m\u1ed9t c\u00f4ng th\u1ee9c INDEX MATCH t\u00ecm xem th\u1ee7 \u0111\u00f4 c\u1ee7a Nga, Matxc\u01a1va, x\u1ebfp th\u1ee9 m\u1ea5y d\u1ef1a theo d\u00e2n s\u1ed1.<\/p>\n<p style=\"text-align: justify\">Nh\u01b0 b\u1ea1n c\u00f3 th\u1ec3 th\u1ea5y t\u1eeb \u1ea3nh ch\u1ee5p m\u00e0n h\u00ecnh b\u00ean d\u01b0\u1edbi, c\u00f4ng th\u1ee9c sau \u0111\u00e2y ho\u1ea1t \u0111\u1ed9ng m\u1ed9t c\u00e1ch tr\u00f4i ch\u1ea3y:<\/p>\n<p style=\"text-align: justify\">=INDEX($A$2:$A$10,MATCH(\u201cRussia\u201d,$B$2:$B$10,0))<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-6268 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/103-300x129-1.png\" width=\"300\" height=\"129\" title=\"103-300x129-1\"><\/p>\n<p style=\"text-align: justify\">B\u00e2y gi\u1edd, c\u00f3 l\u1ebd b\u1ea1n ch\u1eb3ng g\u1eb7p ch\u00fat kh\u00f3 kh\u0103n n\u00e0o trong vi\u1ec7c hi\u1ec3u c\u00f4ng th\u1ee9c ho\u1ea1t \u0111\u1ed9ng nh\u01b0 th\u1ebf n\u00e0o:<\/p>\n<p style=\"text-align: justify\">\u0110\u1ea7u ti\u00ean, b\u1ea1n l\u1eadp c\u00f4ng th\u1ee9c MATCH \u0111\u01a1n gi\u1ea3n t\u00ecm v\u1ecb tr\u00ed c\u1ee7a Nga:<\/p>\n<p style=\"text-align: justify\">=MATCH(\u201cRussia\u201d,$B$2:$B$10,0))<\/p>\n<p style=\"text-align: justify\">R\u1ed3i, b\u1ea1n x\u00e1c \u0111\u1ecbnh th\u00f4ng s\u1ed1 m\u1ea3ng cho h\u00e0m INDEX, trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y l\u00e0 c\u1ed9t A (A2:A10).<\/p>\n<p style=\"text-align: justify\">Cu\u1ed1i c\u00f9ng, b\u1ea1n k\u1ebft h\u1ee3p hai ph\u1ea7n l\u1ea1i v\u00e0 c\u00f3 c\u00f4ng th\u1ee9c:<\/p>\n<p style=\"text-align: justify\">=INDEX($A$2:$A$10,MATCH(\u201cRussia\u201d,$B$2:$B$10,0))<\/p>\n<p style=\"text-align: justify\"><strong>M\u1eb9o<\/strong>. Vi\u1ec7c s\u1eed d\u1ee5ng tham chi\u1ebfu \u00f4 tuy\u1ec7t \u0111\u1ed1i lu\u00f4n l\u00e0 m\u1ed9t \u00fd t\u01b0\u1edfng hay trong c\u00f4ng th\u1ee9c INDEX v\u00e0 MATCH \u0111\u1ec3 d\u1ea3i \u00f4 c\u1ea7n t\u00ecm c\u1ee7a b\u1ea1n kh\u00f4ng b\u1ecb ph\u00e1 h\u1ecfng khi b\u1ea1n sao ch\u00e9p c\u00f4ng th\u1ee9c sang \u00f4 kh\u00e1c.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"TINH_TOAN_VOI_HAM_INDEX_MATCH_TRONG_EXCEL_AVERAGE_MAX_MIN\"><\/span>T\u00cdNH TO\u00c1N V\u1edaI H\u00c0M INDEX MATCH TRONG EXCEL (AVERAGE, MAX, MIN)<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">B\u1ea1n c\u00f3 th\u1ec3 l\u1ed3ng gh\u00e9p c\u00e1c h\u00e0m Excel kh\u00e1c v\u00e0o h\u00e0m INDEX MATCH, gi\u1ea3 s\u1eed, \u0111\u1ec3 t\u00ecm gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t hay l\u1edbn nh\u1ea5t, hay gi\u00e1 tr\u1ecb g\u1ea7n v\u1edbi gi\u00e1 tr\u1ecb trung b\u00ecnh nh\u1ea5t trong d\u1ea3i. Sau \u0111\u00e2y l\u00e0 v\u00e0i v\u00ed d\u1ee5 c\u00f4ng th\u1ee9c cho b\u1ea3ng \u0111\u01b0\u1ee3c d\u00f9ng trong v\u00ed d\u1ee5 tr\u01b0\u1edbc:<\/p>\n<table style=\"width: 780px\">\n<tbody>\n<tr>\n<td style=\"width: 87px\">Ch\u1ee9c n\u0103ng<\/td>\n<td style=\"width: 229px\">V\u00ed d\u1ee5 c\u00f4ng th\u1ee9c<\/td>\n<td style=\"width: 251px\">Mi\u00eau t\u1ea3<\/td>\n<td style=\"width: 245px\">K\u1ebft qu\u1ea3 \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 87px\">Min<\/td>\n<td style=\"width: 229px\">=INDEX($C$2:$C$10, MATCH(MIN($D$2:I$10), $D$2:D$10, 0))<\/td>\n<td style=\"width: 251px\">T\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t trong c\u1ed9t D r\u1ed3i l\u1ea5y m\u1ed9t gi\u00e1 tr\u1ecb t\u1eeb c\u1ed9t C tr\u00ean c\u00f9ng m\u1ed9t h\u00e0ng.<\/td>\n<td style=\"width: 245px\">B\u1eafc Kinh<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 87px\">Max<\/td>\n<td style=\"width: 229px\">=INDEX($C$2:$C$10, MATCH(MAX($D$2:I$10), $D$2:D$10, 0))<\/td>\n<td style=\"width: 251px\">T\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t \u1edf c\u1ed9t D r\u1ed3i l\u1ea5y m\u1ed9t gi\u00e1 tr\u1ecb t\u1eeb c\u1ed9t C tr\u00ean c\u00f9ng m\u1ed9t h\u00e0ng.<\/td>\n<td style=\"width: 245px\">Lima<\/td>\n<\/tr>\n<tr>\n<td style=\"width: 87px\">Average<\/td>\n<td style=\"width: 229px\">=INDEX($C$2:$C$10, MATCH(AVERAGE($D$2:D$10), $D$2:D$10, 1))<\/td>\n<td style=\"width: 251px\">T\u00ednh gi\u00e1 tr\u1ecb trung b\u00ecnh trong d\u1ea3i \u00f4 D2:D10, t\u00ecm gi\u00e1 tr\u1ecb g\u1ea7n v\u1edbi gi\u00e1 tr\u1ecb trung b\u00ecnh nh\u1ea5t, r\u1ed3i l\u1ea5y gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng t\u1eeb c\u1ed9t C.<\/td>\n<td style=\"width: 245px\">Matx-c\u01a1-va<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"NHUNG_DIEU_CAN_NHO_KHI_SU_DUNG_HAM_AVERAGE_VOI_HAM_INDEX_MATCH_TRONG_EXCEL\"><\/span>NH\u1eeeNG \u0110I\u1ec0U C\u1ea6N NH\u1eda KHI S\u1eec D\u1ee4NG H\u00c0M AVERAGE V\u1edaI H\u00c0M INDEX MATCH TRONG EXCEL<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">Khi k\u1ebft h\u1ee3p h\u00e0m AVERAGE v\u1edbi h\u00e0m INDEX\/MATCH, th\u00f4ng th\u01b0\u1eddng b\u1ea1n s\u1ebd nh\u1eadp \u201c1\u201d hay \u201c-1\u201d trong c\u00e2u l\u1ec7nh th\u1ee9 ba (match_type) c\u1ee7a h\u00e0m MATCH, n\u1ebfu b\u1ea1n kh\u00f4ng ch\u1eafc m\u1ea3ng c\u1ea7n t\u00ecm c\u00f3 ch\u1ee9a gi\u00e1 tr\u1ecb b\u1eb1ng gi\u00e1 tr\u1ecb trung b\u00ecnh hay kh\u00f4ng. N\u1ebfu b\u1ea1n kh\u00f4ng ch\u1eafc, th\u00ec b\u1ea1n c\u00f3 th\u1ec3 nh\u1eadp \u201c0\u201d cho s\u1ef1 ph\u00f9 h\u1ee3p tuy\u1ec7t \u0111\u1ed1i.<\/p>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n c\u00e0i \u0111\u1eb7t 1, th\u00ec c\u00e1c gi\u00e1 tr\u1ecb trong c\u1ed9t c\u1ea7n t\u00ecm ph\u1ea3i \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp theo th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n, v\u00e0 c\u00f4ng th\u1ee9c s\u1ebd tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t \u2013 gi\u00e1 tr\u1ecb n\u00e0y c\u00f3 th\u1ec3 nh\u1ecf h\u01a1n hay b\u1eb1ng gi\u00e1 tr\u1ecb trung b\u00ecnh.<\/p>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n c\u00e0i \u0111\u1eb7t -1, th\u00ec c\u00e1c gi\u00e1 tr\u1ecb trong c\u1ed9t c\u1ea7n t\u00ecm ph\u1ea3i \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp theo th\u1ee9 t\u1ef1 gi\u1ea3m d\u1ea7n, v\u00e0 c\u00f4ng th\u1ee9c s\u1ebd tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t \u2013 gi\u00e1 tr\u1ecb n\u00e0y c\u00f3 th\u1ec3 l\u1edbn h\u01a1n hay b\u1eb1ng gi\u00e1 tr\u1ecb trung b\u00ecnh.<\/p>\n<p style=\"text-align: justify\">Trong v\u00ed d\u1ee5 c\u1ee7a ch\u00fang ta, c\u00e1c gi\u00e1 tr\u1ecb \u1edf c\u1ed9t D \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp theo th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n, n\u00ean ta d\u00f9ng \u201c1\u201d l\u00e0m match type, v\u00e0 c\u00f4ng th\u1ee9c Match+Index Match s\u1ebd tr\u1ea3 v\u1ec1 \u201cMatxc\u01a1va\u201d v\u00ec d\u00e2n s\u1ed1 c\u1ee7a n\u00f3 (11,541,000) l\u00e0 gi\u00e1 tr\u1ecb nh\u1ecf h\u01a1n nh\u01b0ng g\u1ea7n v\u1edbi gi\u00e1 tr\u1ecb trung b\u00ecnh nh\u1ea5t (12,269,006).<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-6269 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/104-300x132-1.png\" width=\"300\" height=\"132\" title=\"104-300x132-1\"><\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"CACH_SU_DUNG_HAM_INDEX_MATCH_DE_KIEM_GIA_TRI_THEO_HANG_VA_THEO_COT\"><\/span>C\u00c1CH S\u1eec D\u1ee4NG H\u00c0M INDEX MATCH \u0110\u1ec2 KI\u1ebeM GI\u00c1 TR\u1eca THEO H\u00c0NG V\u00c0 THEO C\u1ed8T<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c n\u00e0y t\u01b0\u01a1ng \u0111\u01b0\u01a1ng c\u00f4ng th\u1ee9c VLOOKUP hai chi\u1ec1u \u2013 c\u00e1c c\u00f4ng th\u1ee9c n\u00e0y cho ph\u00e9p b\u1ea1n t\u00ecm gi\u00e1 tr\u1ecb \u1edf giao \u0111i\u1ec3m c\u1ee7a c\u1ed9t v\u00e0 h\u00e0ng.<\/p>\n<p style=\"text-align: justify\">Trong v\u00ed d\u1ee5 n\u00e0y, h\u00e0m INDEX MATCH trong Excel c\u1ee7a b\u1ea1n s\u1ebd r\u1ea5t gi\u1ed1ng v\u1edbi c\u00e1c c\u00f4ng th\u1ee9c kh\u00e1c m\u00e0 ch\u00fang ta \u0111\u00e3 b\u00e0n trong b\u00e0i h\u01b0\u1edbng d\u1eabn n\u00e0y, ngo\u1ea1i tr\u1eeb m\u1ed9t \u0111i\u1ec3m kh\u00e1c bi\u1ec7t. \u0110o\u00e1n xem n\u00e0o?<\/p>\n<p style=\"text-align: justify\">C\u00f3 l\u1ebd b\u1ea1n v\u1eabn c\u00f2n nh\u1edb, c\u00fa ph\u00e1p h\u00e0m INDEX cho ph\u00e9p b\u1ea1n th\u1ef1c hi\u1ec7n c\u1ea3 hai \u0111i\u1ec1u \u0111\u00f3:\u00a0INDEX(array, row_num, [column_num]). V\u00e0 t\u00f4i xin ch\u00fac m\u1eebng nh\u1eefng b\u1ea1n \u0111\u00e3 \u0111o\u00e1n \u0111\u00fang \ud83d\ude42<\/p>\n<p style=\"text-align: justify\">\u0110\u1ec3 b\u1eaft \u0111\u1ea7u, h\u00e3y l\u1eadp c\u00f4ng th\u1ee9c t\u1ed5ng qu\u00e1t \u0111\u1ec3 x\u1ee9 l\u00fd vi\u1ec7c t\u00ecm ki\u1ebfm ma tr\u1eadn n\u00e0y. Ch\u00fang ta ch\u1ec9 c\u1ea7n d\u00f9ng c\u00f4ng th\u1ee9c INDEX\/MATCH m\u00e0 b\u1ea1n \u0111\u00e3 bi\u1ebft r\u1ed3i b\u1ed5 sung th\u00eam m\u1ed9t h\u00e0m MATCH v\u00e0o n\u00f3, vi\u1ec7c n\u00e0y s\u1ebd tr\u1ea3 v\u1ec1 s\u1ed1 th\u1ee9 t\u1ef1 c\u1ee7a c\u1ed9t.<\/p>\n<p style=\"text-align: justify\">=INDEX (<em>lookup table<\/em>, MATCH (<em>vertical lookup value<\/em>, <em>column to lookup against<\/em>, 0), MATCH (<em>horizontal lookup value<\/em>, <em>row to lookup against<\/em>, 0))<\/p>\n<p style=\"text-align: justify\">Xin h\u00e3y ch\u00fa \u00fd r\u1eb1ng b\u1ea1n ph\u1ea3i \u0111\u1ecbnh r\u00f5 to\u00e0n b\u1ed9 b\u1ea3ng trong c\u00e2u l\u1ec7nh m\u1ea3ng c\u1ee7a h\u00e0m INDEX trong tr\u01b0\u1eddng h\u1ee3p t\u00ecm ki\u1ebfm theo hai c\u00e1ch.<\/p>\n<p style=\"text-align: justify\">V\u00e0 b\u00e2y gi\u1edd, h\u00e3y \u00e1p d\u1ee5ng m\u1eabu c\u00f4ng th\u1ee9c n\u00e0y. D\u01b0\u1edbi \u0111\u00e2y, b\u1ea1n th\u1ea5y danh s\u00e1ch c\u00e1c th\u00e0nh ph\u1ed1 \u0111\u00f4ng d\u00e2n nh\u1ea5t tr\u00ean th\u1ebf gi\u1edbi. Gi\u1ea3 s\u1eed, b\u1ea1n mu\u1ed1n bi\u1ebft s\u1ed1 d\u00e2n c\u1ee7a M\u1ef9 n\u0103m 2015:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-6270 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/105-300x114-1.png\" width=\"300\" height=\"114\" title=\"105-300x114-1\"><\/p>\n<p style=\"text-align: justify\">\u0110\u01b0\u1ee3c r\u1ed3i, h\u00e3y b\u1eaft \u0111\u1ea7u t\u1eeb c\u00f4ng th\u1ee9c n\u00e0o. B\u1ea5t c\u1ee9 khi n\u00e0o t\u00f4i c\u1ea7n t\u1ea1o ra m\u1ed9t c\u00f4ng th\u1ee9c Excel ph\u1ee9c t\u1ea1p c\u00f3 g\u1ed9p m\u1ed9t hai v\u00e0i h\u00e0m kh\u00e1c, th\u00ec t\u00f4i lu\u00f4n vi\u1ebft t\u1eebng h\u00e0m ra tr\u01b0\u1edbc.<\/p>\n<p style=\"text-align: justify\">V\u00ec v\u1eady, b\u1ea1n b\u1eaft \u0111\u1ea7u vi\u1ebft hai h\u00e0m MATCH \u2013 c\u00e1c h\u00e0m n\u00e0y s\u1ebd tr\u1ea3 v\u1ec1 s\u1ed1 th\u1ee9 t\u1ef1 h\u00e0ng v\u00e0 c\u1ed9t cho h\u00e0m INDEX c\u1ee7a b\u1ea1n.<\/p>\n<p style=\"text-align: justify\">Vertical match \u2013 b\u1ea1n d\u00f2 c\u1ed9t B, ch\u00ednh x\u00e1c l\u00e0 t\u1eeb \u00f4 B2 \u0111\u1ebfn \u00f4 B11, \u0111\u1ec3 ki\u1ebfm gi\u00e1 tr\u1ecb \u1edf \u00f4 H2 (\u201cM\u1ef9\u201d), v\u00e0 h\u00e0m MATCH t\u01b0\u01a1ng \u1ee9ng c\u00f3 d\u1ea1ng nh\u01b0 sau:\u00a0=MATCH($H$2,$B$1:$B$11,0). C\u00f4ng th\u1ee9c MATCH n\u00e0y tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb 4 v\u00ec \u201cM\u1ef9\u201d l\u00e0 gi\u00e1 tr\u1ecb th\u1ee9 t\u01b0 \u1edf c\u1ed9t B (bao g\u1ed3m c\u1ed9t ti\u00eau \u0111\u1ec1).<\/p>\n<p style=\"text-align: justify\">Horizontal match \u2013 b\u1ea1n t\u00ecm gi\u00e1 tr\u1ecb \u1edf c\u1ed9t H3 (\u201c2015\u201d) t\u1eeb h\u00e0ng 1, c\u1ee5 th\u1ec3 l\u00e0 t\u1eeb \u00f4 A1 \u0111\u1ebfn \u00f4 E1:\u00a0=MATCH($H$3,$A$1:$E$1,0). C\u00f4ng th\u1ee9c MATCH n\u00e0y tr\u1ea3 v\u1ec1 s\u1ed1 \u201c5\u201d b\u1edfi v\u00ec \u201c2015\u201d n\u1eb1m \u1edf c\u1ed9t th\u1ee9 n\u0103m.<\/p>\n<blockquote>\n<p style=\"text-align: justify\">Xem th\u00eam:\u00a0Kh\u00f3a h\u1ecdc excel n\u00e2ng cao H\u00e0 N\u1ed9i<\/p>\n<\/blockquote>\n<p style=\"text-align: justify\">B\u00e2y gi\u1edd, h\u00e3y \u0111\u1eb7t c\u00e1c c\u00f4ng th\u1ee9c tr\u00ean v\u00e0o h\u00e0m INDEX, v\u00e0 ch\u00fang ta c\u00f3:<\/p>\n<p style=\"text-align: justify\">=INDEX($A$1:$E$11, MATCH($H$2,$B$1:$B$11,0), MATCH($H$3,$A$1:$E$1,0))<\/p>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n thay th\u1ebf c\u00e1c h\u00e0m MATCH b\u1eb1ng c\u00e1c s\u1ed1 \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1, th\u00ec c\u00f4ng th\u1ee9c s\u1ebd d\u1ec5 hi\u1ec3u h\u01a1n r\u1ea5t nhi\u1ec1u: =INDEX($A$1:$E$11, 4, 5, 0))<\/p>\n<p style=\"text-align: justify\">C\u00f3 ngh\u0129a l\u00e0, n\u00f3 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb \u1edf giao \u0111i\u1ec3m c\u1ee7a h\u00e0ng th\u1ee9 t\u01b0 v\u00e0 c\u1ed9t th\u1ee9 n\u0103m trong d\u1ea3i \u00f4 A1:E11, gi\u00e1 tr\u1ecb \u0111\u00f3 n\u1eb1m \u1edf \u00f4 E4. D\u1ec5, \u0111\u00fang kh\u00f4ng n\u00e0o? \u0110\u00fang r\u1ed3i! \ud83d\ude42<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-6271 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/106-300x131-1.png\" width=\"300\" height=\"131\" title=\"106-300x131-1\"><\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"SU_DUNG_HAM_INDEX_MATCH_TIM_KIEM_KEM_THEO_NHIEU_DIEU_KIEN\"><\/span>S\u1eec D\u1ee4NG H\u00c0M INDEX MATCH T\u00ccM KI\u1ebeM K\u00c8M THEO NHI\u1ec0U \u0110I\u1ec0U KI\u1ec6N<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">Trong b\u00e0i h\u01b0\u1edbng d\u1eabn\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/39-%e2%97%8f-su-dung-ham-vlookup-trong-excel.html\" target=\"_blank\" rel=\"noopener noreferrer\">s\u1eed d\u1ee5ng\u00a0h\u00e0m VLOOKUP trong Excel<\/a>, t\u00f4i \u0111\u00e3 di\u1ec5n gi\u1ea3i m\u1ed9t v\u00ed d\u1ee5 c\u00f4ng th\u1ee9c t\u00ecm ki\u1ebfm k\u00e8m theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n. Tuy nhi\u00ean, h\u1ea1n ch\u1ebf \u0111\u00e1ng k\u1ec3 c\u1ee7a c\u00e1ch \u0111\u00f3 ch\u00ednh l\u00e0 c\u1ea7n ph\u1ea3i th\u00eam m\u1ed9t c\u1ed9t tr\u1ee3 gi\u00fap. Tin t\u1ed1t l\u00e0 h\u00e0m INDEX MATCH trong Excel c\u0169ng c\u00f3 th\u1ec3 t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb \u1edf hai c\u1ed9t, m\u00e0 kh\u00f4ng c\u1ea7n c\u1ed9t tr\u1ee3 gi\u00fap n\u00e0o c\u1ea3!<\/p>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed, b\u1ea1n c\u00f3 m\u1ed9t danh s\u00e1ch \u0111\u01a1n h\u00e0ng v\u00e0 b\u1ea1n mu\u1ed1n t\u00ednh t\u1ed5ng d\u1ef1a tr\u00ean hai ti\u00eau ch\u00ed \u201cT\u00ean kh\u00e1ch h\u00e0ng\u201d v\u00e0 \u201cS\u1ea3n ph\u1ea9m\u201d. M\u1ed9t t\u00e1c nh\u00e2n ph\u1ee9c t\u1ea1p \u0111\u00f3 l\u00e0 m\u1ed9t kh\u00e1ch h\u00e0ng c\u00f3 th\u1ec3 mua nhi\u1ec1u s\u1ea3n ph\u1ea9m v\u00e0 t\u00ean kh\u00e1ch h\u00e0ng \u0111\u01b0\u1ee3c li\u1ec7t k\u00ea m\u1ed9t c\u00e1ch ng\u1eabu nhi\u00ean trong b\u1ea3ng c\u1ea7n t\u00ecm:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-6272 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/107-300x169-1.png\" width=\"300\" height=\"169\" title=\"107-300x169-1\"><\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c m\u1ea3ng INDEX MATCH sau \u0111\u00e2y s\u1ebd gi\u00fap \u00edch r\u1ea5t nhi\u1ec1u:<\/p>\n<p style=\"text-align: justify\">=INDEX(\u2018Lookup table\u2019!$A$2:$C$13, MATCH(1, (A2=\u2019Lookup table\u2019!$A$2:$A$13) * (B2=\u2019Lookup table\u2019!$B$2:$B$13) ,0), 3)<\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c n\u00e0y th\u00ec ph\u1ee9c t\u1ea1p h\u01a1n nhi\u1ec1u so v\u1edbi b\u1ea5t k\u1ef3 c\u00f4ng th\u1ee9c n\u00e0o \u2013 c\u00e1c c\u00f4ng th\u1ee9c ch\u00fang ta \u0111\u00e3 th\u1ea3o lu\u1eadn ng\u00e0y h\u00f4m nay, nh\u01b0ng h\u00e3y t\u1eadn d\u1ee5ng ki\u1ebfn th\u1ee9c v\u1ec1 h\u00e0m INDEX MATCH trong Excel m\u00e0 b\u1ea1n c\u00f3, t\u00f4i tin r\u1eb1ng b\u1ea1n c\u00f3 th\u1ec3 x\u1eed l\u00fd n\u00f3.<\/p>\n<p style=\"text-align: justify\">Ph\u1ea7n kh\u00f3 nh\u1ea5t \u0111\u00f3 l\u00e0 h\u00e0m MATCH, v\u00ec th\u1ebf h\u00e3y ph\u00e2n t\u00edch n\u00f3 tr\u01b0\u1edbc:<\/p>\n<p style=\"text-align: justify\">MATCH(1, (A2=\u2019Lookup table\u2019!$A$2:$A$13),0) * (B2=\u2019Lookup table\u2019!$B$2:$B$13), 0)<\/p>\n<p style=\"text-align: justify\">Nh\u1eefng g\u00ec ch\u00fang ta c\u00f3 \u0111\u00f3 l\u00e0 3 c\u00e2u l\u1ec7nh d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\">Lookup_value: 1<\/p>\n<p style=\"text-align: justify\">Lookup_array:\u00a0(A2=\u2019Lookup table\u2019!$A$2:$A$13),0) * (B2=\u2019Lookup table\u2019!$B$2:$B$13)<\/p>\n<p style=\"text-align: justify\">Match_type: 0<\/p>\n<p style=\"text-align: justify\">Th\u00f4ng s\u1ed1 th\u1ee9 nh\u1ea5t v\u00e0 th\u1ee9 ba th\u00ec r\u00f5 r\u00e0ng r\u1ed3i \u2013 h\u00e0m t\u00ecm ki\u1ebfm \u201c1\u201d, r\u1ed3i tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u00ecm \u0111\u01b0\u1ee3c \u0111\u1ea7u ti\u00ean.<\/p>\n<p style=\"text-align: justify\">B\u00e2y gi\u1edd, c\u00e2u h\u1ecfi ch\u00ednh l\u00e0 \u2013 t\u1ea1i sao ch\u00fang ta l\u1ea1i t\u00ecm \u201c1\u201d? \u0110\u1ec3 c\u00f3 \u0111\u01b0\u1ee3c c\u00e2u tr\u1ea3 l\u1eddi, h\u00e3y xem x\u00e9t k\u1ef9 h\u01a1n m\u1ea3ng c\u1ea7n t\u00ecm c\u1ee7a ch\u00fang ta.<\/p>\n<p style=\"text-align: justify\">Vi\u1ec7c ch\u00fang ta l\u00e0m \u1edf \u0111\u00e2y \u0111\u00f3 l\u00e0 t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb \u0111\u1ea7u ti\u00ean (A2) trong c\u1ed9t \u201cT\u00ean kh\u00e1ch h\u00e0ng\u201d trong b\u1ea3ng ch\u00ednh v\u00e0 t\u00ecm t\u00ean \u0111\u00f3 trong t\u1ea5t c\u1ea3 t\u00ean kh\u00e1ch h\u00e0ng \u1edf b\u1ea3ng c\u1ea7n t\u00ecm (A2:A13). N\u1ebfu t\u00ecm th\u1ea5y s\u1ef1 tr\u00f9ng kh\u1edbp, th\u00ec ph\u01b0\u01a1ng tr\u00ecnh tr\u1ea3 v\u1ec1 TRUE, n\u1ebfu kh\u00f4ng th\u00ec s\u1ebd tr\u1ea3 v\u1ec1 FALSE. R\u1ed3i ch\u00fang ta l\u00e0m t\u01b0\u01a1ng t\u1ef1 v\u1edbi c\u00e1c gi\u00e1 tr\u1ecb \u1edf c\u1ed9t B (\u201cS\u1ea3n ph\u1ea9m\u201d).<\/p>\n<p style=\"text-align: justify\">\u0110\u1ec3 hi\u1ec3u r\u00f5 h\u01a1n nh\u1eefng g\u00ec t\u00f4i \u0111ang n\u00f3i, b\u1ea1n c\u00f3 th\u1ec3 ch\u1ecdn m\u1ea3ng trong thanh c\u00f4ng th\u1ee9c, r\u1ed3i nh\u1ea5n ph\u00edm F9 \u0111\u1ec3 xem m\u1ed7i ph\u1ea7n \u0111\u01b0\u1ee3c ch\u1ecdn \u0111\u01b0\u1ee3c \u0111\u00e1nh gi\u00e1 nh\u01b0 th\u1ebf n\u00e0o:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"imgl2\" title=\"Evaluating the parts of the INDEX \/ MATCH formula\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/evaluate-index-match-formula.png\" alt=\"Evaluating the parts of the INDEX \/ MATCH formula\"><\/p>\n<p style=\"text-align: justify\">C\u00f3 l\u1ebd b\u1ea1n \u0111\u00e3 bi\u1ebft, trong c\u00f4ng th\u1ee9c Excel, gi\u00e1 tr\u1ecb l\u00f4 gic TRUE t\u01b0\u01a1ng \u0111\u01b0\u01a1ng v\u1edbi 1, v\u00e0 FALSE t\u01b0\u01a1ng \u0111\u01b0\u01a1ng v\u1edbi 0. V\u00e0 v\u00ec d\u1ea5u hoa th\u1ecb (*) \u0111\u00f3ng vai tr\u00f2 l\u00e0m to\u00e1n t\u1eed AND trong c\u00f4ng th\u1ee9c m\u1ea3ng, n\u00ean ch\u00fang ta nh\u1eadn \u0111\u01b0\u1ee3c \u201c1\u201d ch\u1ec9 khi s\u1ef1 ph\u00f9 h\u1ee3p \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y \u1edf c\u1ea3 hai c\u1ed9t, n\u1ebfu kh\u00f4ng th\u00ec ch\u00fang ta nh\u1eadn \u0111\u01b0\u1ee3c \u201c0\u201d. V\u00ec th\u1ebf, k\u1ebft qu\u1ea3 c\u1ee7a thao t\u00e1c n\u00e0y l\u00e0 m\u1ed9t m\u1ea3ng g\u1ed3m 1 v\u00e0 0, trong \u0111\u00f3 \u201c1\u201d l\u00e0 gi\u00e1 tr\u1ecb \u0111\u00e1p \u1ee9ng c\u1ea3 hai \u0111i\u1ec1u ki\u1ec7n x\u00e1c \u0111\u1ecbnh. N\u1ebfu b\u1ea3ng c\u1ea7n t\u00ecm c\u1ee7a b\u1ea1n kh\u00f4ng c\u00f3 b\u1ea5t k\u1ef3 h\u00e0ng n\u00e0o tr\u00f9ng l\u1eb7p nhau, th\u00ec s\u1ebd ch\u1ec9 c\u00f3 m\u1ed9t gi\u00e1 tr\u1ecb \u201c1\u201d trong m\u1ea3ng. V\u00e0 v\u00ec \u201c1\u201d l\u00e0 gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm c\u1ee7a ch\u00fang ta, n\u00ean h\u00e0m MATCH tr\u1ea3 v\u1ec1 v\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a h\u00e0ng \u0111\u00f3 \u2013 vi\u1ec7c n\u00e0y \u0111\u01b0\u1ee3c di\u1ec5n gi\u1ea3i trong \u1ea3nh ch\u1ee5p m\u00e0n h\u00ecnh d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-6273 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/108-300x100-1.png\" width=\"300\" height=\"100\" title=\"108-300x100-1\"><\/p>\n<p style=\"text-align: justify\">\u0110\u1ed3ng th\u1eddi, xin h\u00e3y ch\u00fa \u00fd r\u1eb1ng b\u1ea1n ph\u1ea3i s\u1eed d\u1ee5ng th\u00f4ng s\u1ed1 t\u00f9y ch\u1ecdn th\u1ee9 ba (colum_num) c\u1ee7a h\u00e0m INDEX. \u0110\u00e2y l\u00e0 v\u00ec b\u1ea1n \u0111\u00e3 \u0111\u1ecbnh r\u00f5 to\u00e0n b\u1ed9 b\u1ea3ng \u1edf th\u00f4ng s\u1ed1 th\u1ee9 nh\u1ea5t (array), v\u00e0 b\u1ea1n c\u1ea7n ph\u1ea3i \u0111\u1ec3 h\u00e0m bi\u1ebft b\u1ea1n mu\u1ed1n l\u1ea5y gi\u00e1 tr\u1ecb c\u1ee7a c\u1ed9t n\u00e0o. trong tr\u01b0\u1eddng h\u1ee3p c\u1ee7a ch\u00fang ta, \u0111\u00f3 l\u00e0 c\u1ed9t C (\u201cT\u1ed5ng\u201d), n\u00ean ch\u00fang ta nh\u1eadp 3 trong c\u00e2u l\u1ec7nh th\u1ee9 ba c\u1ee7a h\u00e0m INDEX.<\/p>\n<p style=\"text-align: justify\">V\u00e0 cu\u1ed1i c\u00f9ng, v\u00ec ch\u00fang ta c\u1ea7n ki\u1ec3m tra m\u1ed7i \u00f4 c\u1ee7a m\u1ea3ng, n\u00ean c\u00f4ng th\u1ee9c INDEX MATCH n\u00ean l\u00e0 c\u00f4ng th\u1ee9c m\u1ea3ng. D\u1ea5u hi\u1ec7u nh\u1eadn bi\u1ebft r\u00f5 r\u00e0ng nh\u1ea5t c\u1ee7a c\u00f4ng th\u1ee9c m\u1ea3ng \u0111\u00f3 l\u00e0 {d\u1ea5u ngo\u1eb7c nh\u1ecdn} trong \u0111\u00f3 n\u00f3 \u0111\u01b0\u1ee3c \u0111\u00f3ng khung b\u1edfi thanh c\u00f4ng th\u1ee9c. H\u00e3y nh\u1edb nh\u1ea5n Ctrl+Shift+Enter \u0111\u1ec3 ho\u00e0n thi\u1ec7n c\u00f4ng th\u1ee9c sau khi b\u1ea1n \u0111\u00e3 nh\u1eadp xong.<\/p>\n<p style=\"text-align: justify\">N\u1ebfu m\u1ecdi th\u1ee9 \u0111\u1ec1u \u0111\u01b0\u1ee3c th\u1ef1c hi\u1ec7n ch\u00ednh x\u00e1c, th\u00ec b\u1ea1n s\u1ebd c\u00f3 k\u1ebft qu\u1ea3 t\u01b0\u01a1ng t\u1ef1 nh\u01b0 nh\u1eefng g\u00ec b\u1ea1n nh\u00ecn th\u1ea5y d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-6275 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/109-300x184-1.png\" width=\"300\" height=\"184\" title=\"109-300x184-1\"><\/p>\n<blockquote>\n<p style=\"text-align: justify\">Xem th\u00eam:\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-lap-trinh-vba-trong-excel\">H\u1ecdc l\u1eadp tr\u00ecnh VBA trong Excel \u1edf \u0111\u00e2u<\/a><\/p>\n<\/blockquote>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"SU_DUNG_HAM_INDEXMACTH_VOI_HAM_IFERROR_TRONG_EXCEL\"><\/span>S\u1eec D\u1ee4NG H\u00c0M INDEX\/MACTH V\u1edaI H\u00c0M IFERROR TRONG EXCEL<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">C\u00f3 l\u1ebd b\u1ea1n \u0111\u00e3 nh\u1eadn ra (h\u01a1n m\u1ed9t l\u1ea7n \ud83d\ude42 n\u1ebfu b\u1ea1n nh\u1eadp m\u1ed9t gi\u00e1 tr\u1ecb v\u00f4 hi\u1ec7u, c\u1ee5 th\u1ec3 l\u00e0 gi\u00e1 tr\u1ecb kh\u00f4ng t\u1ed3n t\u1ea1i trong m\u1ea3ng c\u1ea7n t\u00ecm, th\u00ec h\u00e0m INDEX\/MATCH s\u1ebd tr\u1ea3 v\u1ec1 l\u1ed7i #N\/A hay l\u1ed7i#VALUE. N\u1ebfu b\u1ea1n thay th\u1ebf n\u00f3 b\u1eb1ng m\u1ed9t c\u00e1i g\u00ec \u0111\u00f3 c\u00f3 ngh\u0129a, th\u00ec b\u1ea1n c\u00f3 th\u1ec3 l\u1ed3ng h\u00e0m INDEX\/MATCH trong h\u00e0m IFERROR.<\/p>\n<p style=\"text-align: justify\">C\u00fa ph\u00e1p c\u1ee7a h\u00e0m IFERROR r\u1ea5t \u0111\u01a1n gi\u1ea3n:<\/p>\n<p style=\"text-align: justify\">IFERROR(value, value_if_error)<\/p>\n<p style=\"text-align: justify\">Trong \u0111\u00f3 c\u00e2u l\u1ec7nh value ch\u00ednh l\u00e0 gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c ki\u1ec3m tra l\u1ed7i (k\u1ebft qu\u1ea3 c\u1ee7a c\u00f4ng th\u1ee9c INDEX MATCH trong tr\u01b0\u1eddng h\u1ee3p c\u1ee7a ch\u00fang ta); v\u00e0 value_if_error ch\u00ednh l\u00e0 gi\u00e1 tr\u1ecb c\u1ea7n tr\u1ea3 v\u1ec1 n\u1ebfu c\u00f4ng th\u1ee9c c\u00f3 l\u1ed7i.<\/p>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5, b\u1ea1n c\u00f3 th\u1ec3 l\u1ed3ng c\u00f4ng th\u1ee9c trong v\u00ed d\u1ee5 tr\u01b0\u1edbc v\u00e0o h\u00e0m IFERROR theo c\u00e1ch n\u00e0y:<\/p>\n<p style=\"text-align: justify\"><code>=IFERROR(INDEX($A$1:$E$11, MATCH($G$2,$B$1:$B$11,0), MATCH($G$3,$A$1:$E$1,0)),<\/code><\/p>\n<p style=\"text-align: justify\">\u201cKh\u00f4ng t\u00ecm th\u1ea5y. Xin h\u00e3y th\u1eed l\u1ea1i!\u201d)<\/p>\n<p style=\"text-align: justify\">V\u00e0 b\u00e2y gi\u1edd, n\u1ebfu ai \u0111\u00f3 nh\u1eadp m\u1ed9t m\u1ee5c v\u00f4 hi\u1ec7u, th\u00ec c\u00f4ng th\u1ee9c s\u1ebd cho k\u1ebft qu\u1ea3 nh\u01b0 trong \u1ea3nh ch\u1ee5p m\u00e0n h\u00ecnh d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"size-medium wp-image-6276 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/110-300x144-1.png\" width=\"300\" height=\"144\" title=\"110-300x144-1\"><\/p>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n mu\u1ed1n c\u00f3 m\u1ed9t \u00f4 tr\u1ed1ng khi l\u1ed7i \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1, th\u00ec b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng d\u1ea5u ngo\u1eb7c k\u00e9p (\u201c\u201d) trong th\u00f4ng s\u1ed1 th\u1ee9 hai c\u1ee7a h\u00e0m IFERROR, gi\u1ed1ng nh\u01b0 th\u1ebf n\u00e0y:<\/p>\n<p style=\"text-align: justify\">T\u00f4i hy v\u1ecdng \u00edt nh\u1ea5t c\u00f3 m\u1ed9t c\u00f4ng th\u1ee9c trong b\u00e0i h\u01b0\u1edbng d\u1eabn n\u00e0y s\u1ebd h\u1eefu \u00edch \u0111\u1ed1i v\u1edbi b\u1ea1n. N\u1ebfu b\u1ea1n g\u1eb7p ph\u1ea3i nhi\u1ec7m v\u1ee5 t\u00ecm ki\u1ebfm kh\u00e1c m\u00e0 b\u1ea1n kh\u00f4ng t\u00ecm th\u1ea5y gi\u1ea3i ph\u00e1p cho nhi\u1ec7m v\u1ee5 \u0111\u00f3 \u1edf \u0111\u00e2y, th\u00ec h\u00e3y c\u1ee9 b\u00ecnh lu\u1eadn \u1edf \u0111\u00e2y r\u1ed3i ch\u00fang ta s\u1ebd c\u00f9ng nhau t\u00ecm hi\u1ec3u n\u00f3.<\/p>\n<p style=\"text-align: justify\">\u2013 \u2013 \u2013 \u2013 \u2013<\/p>\n<p>Ngo\u00e0i ra \u0111\u1ec3 \u1ee9ng d\u1ee5ng Excel v\u00e0o c\u00f4ng vi\u1ec7c m\u1ed9t c\u00e1ch hi\u1ec7u qu\u1ea3 th\u00ec b\u1ea1n c\u00f2n ph\u1ea3i s\u1eed d\u1ee5ng t\u1ed1t c\u00e1c h\u00e0m, c\u00e1c c\u00f4ng c\u1ee5 kh\u00e1c c\u1ee7a Excel:<\/p>\n<p>M\u1ed9t s\u1ed1 h\u00e0m c\u01a1 b\u1ea3n th\u01b0\u1eddng g\u1eb7p nh\u01b0:<\/p>\n<ul>\n<li>SUMIF, SUMIFS \u0111\u1ec3 t\u00ednh t\u1ed5ng theo 1 \u0111i\u1ec1u ki\u1ec7n, nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/li>\n<li>COUNTIF, COUNTIFS \u0111\u1ec3 th\u1ed1ng k\u00ea, \u0111\u1ebfm theo m\u1ed9t \u0111i\u1ec1u ki\u1ec7n, nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/li>\n<li>C\u00e1c h\u00e0m x\u1eed l\u00fd d\u1eef li\u1ec7u d\u1ea1ng chu\u1ed7i, d\u1ea1ng ng\u00e0y th\u00e1ng, d\u1ea1ng s\u1ed1\u2026<\/li>\n<li>C\u00e1c h\u00e0m d\u00f2 t\u00ecm tham chi\u1ebfu Index+Match, h\u00e0m SUMPRODUCT\u2026<\/li>\n<\/ul>\n<p>M\u1ed9t s\u1ed1 c\u00f4ng c\u1ee5 hay s\u1eed d\u1ee5ng nh\u01b0:<\/p>\n<ul>\n<li>\u0110\u1ecbnh d\u1ea1ng theo \u0111i\u1ec1u ki\u1ec7n v\u1edbi Conditional formatting<\/li>\n<li>Thi\u1ebft l\u1eadp \u0111i\u1ec1u ki\u1ec7n nh\u1eadp d\u1eef li\u1ec7u v\u1edbi Data Validation<\/li>\n<li>C\u00e1ch \u0111\u1eb7t Name v\u00e0 s\u1eed d\u1ee5ng Name trong c\u00f4ng th\u1ee9c<\/li>\n<li>L\u1eadp b\u00e1o c\u00e1o v\u1edbi Pivot Table\u2026<\/li>\n<\/ul>\n<p>R\u1ea5t nhi\u1ec1u ki\u1ebfn th\u1ee9c ph\u1ea3i kh\u00f4ng n\u00e0o? 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<a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\/excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\" target=\"_blank\" rel=\"noopener\">EX101 \u2013 Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi chuy\u00ean gia<\/a> c\u1ee7a H\u1ecdc Excel Online. \u0110\u00e2y l\u00e0 kh\u00f3a h\u1ecdc gi\u00fap b\u1ea1n h\u1ec7 th\u1ed1ng ki\u1ebfn th\u1ee9c m\u1ed9t c\u00e1ch \u0111\u1ea7y \u0111\u1ee7, chi ti\u1ebft. H\u01a1n n\u1eefa kh\u00f4ng h\u1ec1 c\u00f3 gi\u1edbi h\u1ea1n v\u1ec1 th\u1eddi gian h\u1ecdc t\u1eadp n\u00ean b\u1ea1n c\u00f3 th\u1ec3 tho\u1ea3i m\u00e1i h\u1ecdc b\u1ea5t c\u1ee9 l\u00fac n\u00e0o, d\u1ec5 d\u00e0ng tra c\u1ee9u l\u1ea1i ki\u1ebfn th\u1ee9c khi c\u1ea7n. Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 \u01b0u \u0111\u00e3i r\u1ea5t l\u1edbn cho b\u1ea1n khi \u0111\u0103ng k\u00fd tham gia kh\u00f3a h\u1ecdc. Chi ti\u1ebft xem t\u1ea1i: <a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\"><strong>H\u1ecdc Excel Online<\/strong><\/a><\/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-135.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":1848,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1847","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\/1847","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=1847"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/1847\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/1848"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=1847"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=1847"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=1847"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}