{"id":5173,"date":"2024-12-09T16:17:32","date_gmt":"2024-12-09T16:17:32","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/09\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup\/"},"modified":"2024-12-09T16:17:32","modified_gmt":"2024-12-09T16:17:32","slug":"ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/09\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup\/","title":{"rendered":"H\u00e0m XLOOKUP trong Excel thay th\u1ebf cho c\u1ea3 VLOOKUP v\u00e0 HLOOKUP"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p class=\"c4\"><em><span class=\"c2\">B\u00e0i vi\u1ebft n\u00e0y <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/a> s\u1ebd chia s\u1ebb v\u1edbi c\u00e1c b\u1ea1n v\u1ec1 <strong>h\u00e0m XLOOKUP<\/strong> trong Excel \u2013 m\u1ed9t h\u00e0m m\u1edbi trong Excel gi\u00fap ch\u00fang ta c\u00f3 th\u1ec3 tra c\u1ee9u c\u1ea3 theo chi\u1ec1u d\u1ecdc v\u00e0 theo chi\u1ec1u ngang. M\u1ed9t s\u1ed1 v\u1ea5n \u0111\u1ec1 c\u1ee7a <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> nh\u01b0, c\u1ed9t tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb n\u1eb1m ph\u00eda b\u00ean tr\u00e1i c\u1ed9t tra c\u1ee9u, tra c\u1ee9u gi\u00e1 tr\u1ecb cu\u1ed1i c\u00f9ng, tra c\u1ee9u v\u1edbi nhi\u1ec1u ti\u00eau ch\u00ed \u0111i\u1ec1u ki\u1ec7n \u2026 tr\u01b0\u1edbc \u0111\u00e2y c\u1ea7n t\u1edbi nh\u1eefng c\u00f4ng th\u1ee9c c\u1ef1c k\u00ec ph\u1ee9c t\u1ea1p, th\u00ec gi\u1edd \u0111\u00e2y \u0111\u00e3 tr\u1edf n\u00ean d\u1ec5 d\u00e0ng v\u1edbi XLOOKUP.<\/span><\/em><\/p>\n<p class=\"c4\"><span class=\"c2\">M\u1ed7i khi b\u1ea1n mu\u1ed1n tra c\u1ee9u trong Excel, c\u00f3 th\u1ec3 b\u1ea1n s\u1ebd ngh\u0129 ngay t\u1edbi h\u00e0m tra c\u1ee9u VLOOKUP, <strong><a href=\"http:\/\/blog.ezworkapp.com\/ham-hlookup-nen-su-dung-hlookup-hay-index-ket-hop-match-se-hay-hon.html\">HLOOKUP<\/a><\/strong> ho\u1eb7c n\u00e2ng cao h\u01a1n l\u00e0 s\u1ef1 k\u1ebft h\u1ee3p gi\u1eefa h\u00e0m INDEX v\u00e0 MATCH ho\u1eb7c th\u1eadm ch\u00ed, b\u1ea1n c\u00f3 th\u1ec3 d\u00f9ng t\u1edbi Power Query ho\u1eb7c l\u1eadp tr\u00ecnh VBA. Tin vui l\u00e0 c\u00f3 th\u1ec3 b\u1ea1n s\u1ebd kh\u00f4ng c\u1ea7n l\u00e0m g\u00ec qu\u00e1 ph\u1ee9c t\u1ea1p, b\u1edfi v\u00ec, Microsoft \u0111\u00e3 mang t\u1edbi cho ch\u00fang ta m\u1ed9t h\u00e0m m\u1edbi: h\u00e0m XLOOKUP trong Excel.<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">H\u00e0m XLOOKUP t\u1ed1t h\u01a1n nh\u01b0 th\u1ebf n\u00e0o? H\u00e0m n\u00e0y c\u00f3 th\u1ec3 tra c\u1ee9u theo c\u1ea3 chi\u1ec1u d\u1ecdc l\u1ea7n chi\u1ec1u ngang, c\u1ea3 b\u00ean tr\u00e1i c\u1ee7a c\u1ed9t d\u1eef li\u1ec7u tra c\u1ee9u, XLOOKUP c\u00f2n c\u00f3 th\u1ec3 tra c\u1ee9u theo nhi\u1ec1u ti\u00eau ch\u00ed, th\u1eadm ch\u00ed tr\u1ea3 v\u1ec1 c\u1ea3 c\u1ed9t k\u1ebft qu\u1ea3, kh\u00f4ng ch\u1ec9 1 k\u1ebft qu\u1ea3 duy nh\u1ea5t.<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">Tuy v\u1eady, v\u1edbi t\u1ea5t c\u1ea3 s\u1ef1 \u01b0u vi\u1ec7t nh\u01b0 v\u1eady c\u1ee7a h\u00e0m XLOOKUP, h\u00e0m n\u00e0y t\u1ea1i th\u1eddi \u0111i\u1ec3m b\u00e0i vi\u1ebft, ch\u1ec9 c\u00f3 cho phi\u00ean b\u1ea3n Excel 365.<\/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-6757181ae164c\" 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-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#Ham_XLOOKUP_trong_Excel_%E2%80%93_cu_phap_va_cach_su_dung\" title=\"H\u00e0m XLOOKUP trong Excel \u2013 c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng\">H\u00e0m XLOOKUP trong Excel \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-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#Ham_XLOOKUP_co_ban\" title=\"H\u00e0m XLOOKUP c\u01a1 b\u1ea3n\">H\u00e0m XLOOKUP c\u01a1 b\u1ea3n<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#Cach_su_dung_ham_XLOOKUP_trong_Excel_va_vi_du\" title=\"C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m XLOOKUP trong Excel v\u00e0 v\u00ed d\u1ee5\">C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m XLOOKUP trong Excel v\u00e0 v\u00ed d\u1ee5<\/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-4\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#Tra_cuu_theo_chieu_ngang_va_chieu_doc\" title=\"Tra c\u1ee9u theo chi\u1ec1u ngang v\u00e0 chi\u1ec1u d\u1ecdc\">Tra c\u1ee9u theo chi\u1ec1u ngang v\u00e0 chi\u1ec1u d\u1ecdc<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#Tra_cuu_va_lay_gia_tri_o_cot_ben_trai_cot_tim_kiem\" title=\"Tra c\u1ee9u v\u00e0 l\u1ea5y gi\u00e1 tr\u1ecb \u1edf c\u1ed9t b\u00ean tr\u00e1i c\u1ed9t t\u00ecm ki\u1ebfm\">Tra c\u1ee9u v\u00e0 l\u1ea5y gi\u00e1 tr\u1ecb \u1edf c\u1ed9t b\u00ean tr\u00e1i c\u1ed9t t\u00ecm ki\u1ebfm<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#Tra_cuu_chinh_xac_va_tra_cuu_gan_dung_voi_ham_XLOOKUP\" title=\"Tra c\u1ee9u ch\u00ednh x\u00e1c v\u00e0 tra c\u1ee9u g\u1ea7n \u0111\u00fang v\u1edbi h\u00e0m XLOOKUP\">Tra c\u1ee9u ch\u00ednh x\u00e1c v\u00e0 tra c\u1ee9u g\u1ea7n \u0111\u00fang v\u1edbi h\u00e0m XLOOKUP<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#Tim_kiem_chinh_xac_bang_XLOOKUP\" title=\"T\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c b\u1eb1ng XLOOKUP\">T\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c b\u1eb1ng XLOOKUP<\/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\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#Tra_cuu_gan_dung_voi_ham_XLOOKUP\" title=\"Tra c\u1ee9u g\u1ea7n \u0111\u00fang v\u1edbi h\u00e0m XLOOKUP\">Tra c\u1ee9u g\u1ea7n \u0111\u00fang v\u1edbi h\u00e0m XLOOKUP<\/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-9\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#Tra_cuu_voi_ky_tu_dai_dien\" title=\"Tra c\u1ee9u v\u1edbi k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n\">Tra c\u1ee9u v\u1edbi k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#Tra_cuu_tu_phia_cuoi_bang_len\" title=\"Tra c\u1ee9u t\u1eeb ph\u00eda cu\u1ed1i b\u1ea3ng l\u00ean\">Tra c\u1ee9u t\u1eeb ph\u00eda cu\u1ed1i b\u1ea3ng l\u00ean<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#XLOOKUP_tra_ve_nhieu_cot_hoac_nhieu_hang_ket_qua\" title=\"XLOOKUP tr\u1ea3 v\u1ec1 nhi\u1ec1u c\u1ed9t ho\u1eb7c nhi\u1ec1u h\u00e0ng k\u1ebft qu\u1ea3\">XLOOKUP tr\u1ea3 v\u1ec1 nhi\u1ec1u c\u1ed9t ho\u1eb7c nhi\u1ec1u h\u00e0ng k\u1ebft qu\u1ea3<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#XLOOKUP_tra_cuu_dua_tren_nhieu_dieu_kien\" title=\"XLOOKUP tra c\u1ee9u d\u1ef1a tr\u00ean nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n\">XLOOKUP tra c\u1ee9u d\u1ef1a tr\u00ean nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#XLOOKUP_tra_cuu_hai_chieu\" title=\"XLOOKUP tra c\u1ee9u hai chi\u1ec1u\">XLOOKUP tra c\u1ee9u hai chi\u1ec1u<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#XLOOKUP_thong_bao_loi_khi_khong_tim_thay_gia_tri_can_tim_kiem\" title=\"XLOOKUP th\u00f4ng b\u00e1o l\u1ed7i khi kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm\">XLOOKUP th\u00f4ng b\u00e1o l\u1ed7i khi kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#XLOOKUP_bi_loi_khong_hoat_dong\" title=\"XLOOKUP b\u1ecb l\u1ed7i, kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng\">XLOOKUP b\u1ecb l\u1ed7i, kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng<\/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-16\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#Loi_NAMES_khi_dung_XLOOKUP\" title=\"L\u1ed7i #NAMES? khi d\u00f9ng XLOOKUP\">L\u1ed7i #NAMES? khi d\u00f9ng XLOOKUP<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-17\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#XLOOKUP_tra_ve_ket_qua_sai\" title=\"XLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 sai\">XLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 sai<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-18\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#XLOOKUP_tra_ve_ket_qua_NA\" title=\"XLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 #N\/A\">XLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 #N\/A<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-19\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#XLOOKUP_tra_ve_ket_qua_loi_VALUE\" title=\"XLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u1ed7i #VALUE\">XLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u1ed7i #VALUE<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-20\" href=\"https:\/\/blog.hocexcel.online\/ham-xlookup-trong-excel-thay-the-cho-ca-vlookup-va-hlookup.html#XLOOKUP_tra_ve_ket_qua_loi_REF\" title=\"XLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u1ed7i #REF\">XLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u1ed7i #REF<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2 id=\"h.jznlt3hva9mr\" class=\"c16\"><span class=\"ez-toc-section\" id=\"Ham_XLOOKUP_trong_Excel_%E2%80%93_cu_phap_va_cach_su_dung\"><\/span><span class=\"c5\">H\u00e0m XLOOKUP trong Excel \u2013 c\u00fa ph\u00e1p v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p class=\"c4\"><span class=\"c2\">H\u00e0m XLOOKUP trong Excel s\u1ebd t\u00ecm ki\u1ebfm trong m\u1ed9t v\u00f9ng d\u1eef li\u1ec7u m\u1ed9t gi\u00e1 tr\u1ecb nh\u1ea5t \u0111\u1ecbnh v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb li\u00ean quan trong m\u1ed9t c\u1ed9t kh\u00e1c. H\u00e0m n\u00e0y c\u00f3 th\u1ec3 tra c\u1ee9u c\u1ea3 theo chi\u1ec1u d\u1ecdc v\u00e0 theo chi\u1ec1u ngang, h\u1ed7 tr\u1ee3 tra c\u1ee9u ch\u00ednh x\u00e1c (m\u1eb7c \u0111\u1ecbnh) v\u00e0 tra c\u1ee9u v\u1edbi k\u1ebft qu\u1ea3 g\u1ea7n \u0111\u00fang, ho\u1eb7c tra c\u1ee9u s\u1eed d\u1ee5ng k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n (*, ?, ~)<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">C\u00fa ph\u00e1p c\u1ee7a h\u00e0m XLOOKUP nh\u01b0 sau<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])<\/span><\/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-440.gif\" title=\"ex101_92019-nho-440\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p class=\"c4\"><span class=\"c2\">3 tham s\u1ed1 \u0111\u1ea7u ti\u00ean l\u00e0 b\u1eaft bu\u1ed9c, 3 tham s\u1ed1 sau l\u00e0 kh\u00f4ng b\u1eaft bu\u1ed9c.<\/span><\/p>\n<ul class=\"c10 lst-kix_ynq48sspq8tf-0 start\">\n<li class=\"c3\"><span class=\"c2\">Lookup_value \u2013 gi\u00e1 tr\u1ecb d\u00f9ng \u0111\u1ec3 t\u00ecm ki\u1ebfm<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">Lookup_array \u2013 v\u00f9ng ho\u1eb7c m\u1ea3ng d\u1eef li\u1ec7u d\u00f9ng \u0111\u1ec3 tra c\u1ee9u<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">Return_array \u2013 v\u00f9ng ho\u1eb7c m\u1ea3ng d\u1eef li\u1ec7u \u0111\u1ec3 l\u1ea5y d\u1eef li\u1ec7u tr\u1ea3 v\u1ec1<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">If_not_found (kh\u00f4ng b\u1eaft bu\u1ed9c) \u2013 gi\u00e1 tr\u1ecb thay th\u1ebf khi kh\u00f4ng t\u00ecm th\u1ea5y. N\u1ebfu b\u1ecf qua, l\u1ed7i #N\/A s\u1ebd l\u00e0 k\u1ebft qu\u1ea3<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">Match_mode (kh\u00f4ng b\u1eaft bu\u1ed9c) \u2013 ki\u1ec3u t\u00ecm ki\u1ebfm:<\/span><\/li>\n<\/ul>\n<ul class=\"c10 lst-kix_ynq48sspq8tf-1 start\">\n<li class=\"c4 c11\"><span class=\"c2\">0 ho\u1eb7c b\u1ecf qua \u2013 t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c. N\u1ebfu gi\u00e1 tr\u1ecb t\u00ecm ki\u1ebfm kh\u00f4ng t\u00ecm th\u1ea5y, #N\/A s\u1ebd \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1<\/span><\/li>\n<li class=\"c4 c11\"><span class=\"c2\">-1 \u2013 t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c ho\u1eb7c gi\u00e1 tr\u1ecb nh\u1ecf h\u01a1n g\u1ea7n nh\u1ea5t. N\u1ebfu gi\u00e1 tr\u1ecb t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c kh\u00f4ng \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y, gi\u00e1 tr\u1ecb nh\u1ecf h\u01a1n g\u1ea7n nh\u1ea5t s\u1ebd \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1<\/span><\/li>\n<li class=\"c4 c11\"><span class=\"c2\">1 \u2013 t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c ho\u1eb7c gi\u00e1 tr\u1ecb l\u1edbn h\u01a1n g\u1ea7n nh\u1ea5t. N\u1ebfu gi\u00e1 tr\u1ecb t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c kh\u00f4ng \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y, gi\u00e1 tr\u1ecb l\u1edbn h\u01a1n g\u1ea7n nh\u1ea5t s\u1ebd \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1.<\/span><\/li>\n<li class=\"c4 c11\"><span class=\"c2\">2 \u2013 t\u00ecm ki\u1ebfm theo k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n<\/span><\/li>\n<\/ul>\n<ul class=\"c10 lst-kix_ynq48sspq8tf-0\">\n<li class=\"c3\"><span class=\"c2\">Search_mode (kh\u00f4ng b\u1eaft bu\u1ed9c) \u2013 h\u01b0\u1edbng t\u00ecm ki\u1ebfm trong d\u1eef li\u1ec7u<\/span><\/li>\n<\/ul>\n<ul class=\"c10 lst-kix_ynq48sspq8tf-1 start\">\n<li class=\"c4 c11\"><span class=\"c2\">1 ho\u1eb7c b\u1ecf qua \u2013 t\u00ecm t\u1eeb \u0111\u1ea7u t\u1edbi cu\u1ed1i<\/span><\/li>\n<li class=\"c4 c11\"><span class=\"c2\">-1 \u2013 t\u00ecm t\u1eeb cu\u1ed1i t\u1edbi \u0111\u1ea7u<\/span><\/li>\n<li class=\"c4 c11\"><span class=\"c2\">2 \u2013 t\u00ecm ki\u1ebfm nh\u1ecb ph\u00e2n tr\u00ean d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp t\u0103ng d\u1ea7n<\/span><\/li>\n<li class=\"c4 c11\"><span class=\"c2\">-2 \u2013 t\u00ecm ki\u1ebfm nh\u1ecb ph\u00e2n tr\u00ean d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp gi\u1ea3m d\u1ea7n<\/span><\/li>\n<\/ul>\n<p class=\"c4\"><span class=\"c2\">Theo Microsoft, t\u00ecm ki\u1ebfm nh\u1ecb ph\u00e2n l\u00e0 m\u1ed9t thu\u1eadt to\u00e1n t\u00ecm ki\u1ebfm \u0111\u1eb7c bi\u1ec7t gi\u00fap vi\u1ec7c t\u00ecm ki\u1ebfm nhanh h\u01a1n, tuy nhi\u00ean n\u00f3 y\u00eau c\u1ea7u d\u1eef li\u1ec7u c\u1ea7n \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp tr\u01b0\u1edbc khi th\u1ef1c hi\u1ec7n t\u00ecm ki\u1ebfm.<\/span><\/p>\n<h2 id=\"h.vbdvz3923qkx\" class=\"c16\"><span class=\"ez-toc-section\" id=\"Ham_XLOOKUP_co_ban\"><\/span><span class=\"c5\">H\u00e0m XLOOKUP c\u01a1 b\u1ea3n<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p class=\"c4\"><span class=\"c2\">\u0110\u1ec3 c\u00f3 th\u1ec3 hi\u1ec3u h\u01a1n v\u1ec1 h\u00e0m XLOOKUP, ch\u00fang ta hay \u0111i s\u1eed d\u1ee5ng h\u00e0m n\u00e0y \u1edf d\u1ea1ng \u0111\u01a1n gi\u1ea3n nh\u1ea5t v\u1edbi ch\u1ec9 ba tham s\u1ed1 b\u1eaft bu\u1ed9c \u0111\u1ea7u ti\u00ean v\u00e0 th\u1ef1c hi\u1ec7n tra c\u1ee9u ch\u00ednh x\u00e1c.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32885\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-xlookup-function.png\" alt=\"H\u00e0m XLOOKUP trong Excel\" width=\"436\" height=\"58\" title=\"excel-xlookup-function\"><\/p>\n<p class=\"c4\"><span class=\"c2\">Trong c\u00f4ng th\u1ee9c tr\u00ean, F1 l\u00e0 \u00f4 ch\u1ee9a gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm, A2:A6 l\u00e0 v\u00f9ng t\u00ecm ki\u1ebfm, C2:C6 l\u00e0 v\u00f9ng s\u1ebd tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 n\u1ebfu F1 \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y trong v\u00f9ng A2:A6.<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">B\u1ea1n c\u00f3 th\u1ec3 th\u1ea5y r\u1eb1ng, c\u00f4ng th\u1ee9c XLOOKUP n\u00e0y, c\u00f3 v\u1ebb \u0111\u01a1n gi\u1ea3n h\u01a1n VLOOKUP<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32886\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ham-xlookup-trong-excel-tot-hon-vlookup-01.png\" alt=\"H\u00e0m XLOOKUP trong Excel t\u1ed1t h\u01a1n h\u00e0m VLOOKUP\" width=\"638\" height=\"215\" title=\"ham-xlookup-trong-excel-tot-hon-vlookup-01\"><\/p>\n<p class=\"c4\"><span class=\"c2\">XLOOKUP so v\u1edbi VLOOKUP<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">So s\u00e1nh v\u1edbi h\u00e0m VLOOKUP nguy\u00ean b\u1ea3n trong Excel, h\u00e0m XLOOKUP c\u00f3 r\u1ea5t nhi\u1ec1u l\u1ee3i th\u1ebf. Sau \u0111\u00e2y l\u00e0 m\u1ed9t danh s\u00e1ch 10 ch\u1ee9c n\u0103ng v\u01b0\u1ee3t tr\u1ed9i c\u1ee7a XLOOKUP so s\u00e1nh v\u1edbi b\u1ea5t k\u00ec h\u00e0m tra c\u1ee9u n\u00e0o trong Excel:<\/span><\/p>\n<ol class=\"c10 lst-kix_fmzhxsms0fk1-0 start\" start=\"1\">\n<li class=\"c3\"><span class=\"c2\">Tra c\u1ee9u theo c\u1ea3 chi\u1ec1u d\u1ecdc l\u1eabn chi\u1ec1u ngang. Ch\u1eef \u201cX\u201d trong h\u00e0m XLOOKUP th\u1ec3 hi\u1ec7n vi\u1ec7c h\u1ed7 tr\u1ee3 tra c\u1ee9u theo c\u1ea3 2 h\u01b0\u1edbng tr\u00ean b\u1ea3ng t\u00ednh.<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">H\u1ed7 tr\u1ee3 tra c\u1ee9u, t\u00ecm ki\u1ebfm theo b\u1ea5t c\u1ee9 h\u01b0\u1edbng n\u00e0o: t\u1eeb ph\u1ea3i qua, t\u1eeb tr\u00e1i qua, t\u1eeb tr\u00ean xu\u1ed1ng, t\u1eeb d\u01b0\u1edbi l\u00ean. Trong khi <a href=\"http:\/\/blog.ezworkapp.com\/ham-vlookup-trong-excel-huong-dan-su-dung-chi-tiet-va-co-vi-du-cu-the.html\"><em><strong>h\u00e0m VLOOKUP<\/strong><\/em><\/a> ch\u1ec9 c\u00f3 th\u1ec3 t\u00ecm ki\u1ebfm trong c\u1ed9t v\u1ec1 ph\u00eda tr\u00e1i c\u00f2n HLOOKUP ch\u1ec9 t\u00ecm ki\u1ebfm trong h\u00e0ng ph\u00eda tr\u00ean, XLOOKUP kh\u00f4ng b\u1ecb gi\u1edbi h\u1ea1n n\u00e0y.<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">XLOOKUP th\u1ef1c hi\u1ec7n tra c\u1ee9u ch\u00ednh x\u00e1c m\u1ed9t c\u00e1ch m\u1eb7c \u0111\u1ecbnh. Trong h\u1ea7u h\u1ebft c\u00e1c tr\u01b0\u1eddng h\u1ee3p, b\u1ea1n s\u1ebd c\u1ea7n s\u1eed d\u1ee5ng tra c\u1ee9u m\u1ed9t c\u00e1ch ch\u00ednh x\u00e1c, XLOOKUP m\u1eb7c \u0111\u1ecbnh s\u1ebd tra c\u1ee9u ch\u00ednh x\u00e1c cho b\u1ea1n (Kh\u00f4ng nh\u01b0 VLOOKUP c\u00f3 m\u1eb7c \u0111\u1ecbnh cho vi\u1ec7c tra c\u1ee9u g\u1ea7n \u0111\u00fang). T\u1ea5t nhi\u00ean XLOOKUP c\u0169ng c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n tra c\u1ee9u g\u1ea7n \u0111\u00fang n\u1ebfu c\u1ea7n.<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">Tra c\u1ee9u v\u1edbi k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n. Khi b\u1ea1n ch\u1ec9 bi\u1ebft m\u1ed9t v\u00e0i ph\u1ea7n c\u1ee7a gi\u00e1 tr\u1ecb c\u1ea7n tra c\u1ee9u, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng tra c\u1ee9u s\u1eed d\u1ee5ng k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n v\u1edbi XLOOKUP.<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">T\u00ecm ki\u1ebfm \u1edf th\u1ee9 t\u1ef1 \u0111\u1ea3o ng\u01b0\u1ee3c. Ng\u00e0y tr\u01b0\u1edbc, khi mu\u1ed1n l\u1ea5y gi\u00e1 tr\u1ecb cu\u1ed1i c\u00f9ng v\u1edbi VLOOKUP, ch\u00fang ta ph\u1ea3i \u0111\u1ea3o ng\u01b0\u1ee3c d\u1eef li\u1ec7u ngu\u1ed3n. B\u00e2y gi\u1edd, ch\u00fang ta ch\u1ec9 c\u1ea7n \u0111i\u1ec1u ch\u1ec9nh tham s\u1ed1 search_mode th\u00e0nh -1 \u0111\u1ec3 XLOOKUP c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n t\u00ecm ki\u1ebfm t\u1eeb d\u01b0\u1edbi l\u00ean.<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">Tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u1ebft qu\u1ea3. B\u1eb1ng c\u00e1ch thay \u0111\u1ed5i tham s\u1ed1 return_array, b\u1ea1n c\u00f3 th\u1ec3 l\u1ea5y k\u1ebft qu\u1ea3 l\u00e0 c\u1ea3 m\u1ed9t d\u00f2ng ho\u1eb7c m\u1ed9t c\u1ed9t li\u00ean quan t\u1edbi gi\u00e1 tr\u1ecb t\u00ecm ki\u1ebfm<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">T\u00ecm ki\u1ebfm nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n. H\u00e0m XLOOKUP c\u00f3 kh\u1ea3 n\u0103ng x\u1eed l\u00fd d\u1eef li\u1ec7u theo m\u1ea3ng, do v\u1eady ch\u00fang ta c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n tra c\u1ee9u theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n c\u00f9ng l\u00fac.<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">C\u00f3 kh\u1ea3 n\u0103ng b\u00e2y l\u1ed7i. Th\u00f4ng th\u01b0\u1eddng, ch\u00fang ta s\u1eed d\u1ee5ng IFNA \u0111\u1ec3 c\u00f3 th\u1ec3 ki\u1ec3m so\u00e1t l\u1ed7i #N\/A. XLOOKUP h\u1ed7 tr\u1ee3 s\u1eed d\u1ee5ng tham s\u1ed1 if_not_found cho tr\u01b0\u1eddng h\u1ee3p tra c\u1ee9u m\u00e0 kh\u00f4ng t\u00ecm ra k\u1ebft qu\u1ea3.<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">Kh\u00f4ng lo khi s\u1eeda \u0111\u1ed5i c\u1ea5u tr\u00fac b\u1ea3ng. \u0110\u1ed1i v\u1edbi VLOOKUP, n\u1ebfu b\u1ea1n thay \u0111\u1ed5i c\u1ea5u tr\u00fac b\u1ea3ng (xo\u00e1, ho\u1eb7c th\u00eam m\u1edbi c\u1ed9t) th\u00ec nhi\u1ec1u kh\u1ea3 n\u0103ng c\u00f4ng th\u1ee9c VLOOKUP s\u1ebd kh\u00f4ng c\u00f2n ch\u1ea1y \u0111\u01b0\u1ee3c n\u1eefa do tham s\u1ed1 th\u1ee9 3 c\u1ee7a h\u00e0m VLOOKUP l\u00e0 c\u1ed9t l\u1ea5y d\u1eef li\u1ec7u v\u1ec1. S\u1eed d\u1ee5ng XLOOKUP, b\u1ea1n kh\u00f4ng c\u1ea7n lo l\u1eafng v\u1ec1 \u0111i\u1ec1u n\u00e0y.<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">XLOOKUP \u0111\u01b0\u1ee3c cho l\u00e0 ho\u1ea1t \u0111\u1ed9ng nhanh h\u01a1n VLOOKUP v\u00ec ch\u1ec9 c\u1ea7n x\u1eed l\u00fd d\u1eef li\u1ec7u c\u1ee7a m\u1ea3ng tra c\u1ee9u v\u00e0 m\u1ea3ng k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1.<\/span><\/li>\n<\/ol>\n<h2 id=\"h.72ctaszm4pj\" class=\"c16\"><span class=\"ez-toc-section\" id=\"Cach_su_dung_ham_XLOOKUP_trong_Excel_va_vi_du\"><\/span><span class=\"c5\">C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m XLOOKUP trong Excel v\u00e0 v\u00ed d\u1ee5<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p class=\"c4\"><span class=\"c2\">Nh\u1eefng v\u00ed d\u1ee5 d\u01b0\u1edbi \u0111\u00e2y s\u1ebd minh ho\u1ea1 nh\u1eefng tr\u01b0\u1eddng h\u1ee3p h\u1eefu d\u1ee5ng nh\u1ea5t c\u1ee7a h\u00e0m XLOOKUP, ngo\u00e0i ra ch\u00fang ta c\u0169ng s\u1ebd c\u00f3 m\u1ed9t v\u00e0i v\u00ed d\u1ee5 ph\u1ee9c t\u1ea1p h\u01a1n \u0111\u1ec3 b\u1ea1n c\u00f3 th\u1ec3 n\u00e2ng cao kh\u1ea3 n\u0103ng s\u1eed d\u1ee5ng h\u00e0m tra c\u1ee9u trong Excel<\/span><\/p>\n<h3 id=\"h.mj3mot437fw1\" class=\"c6\"><span class=\"ez-toc-section\" id=\"Tra_cuu_theo_chieu_ngang_va_chieu_doc\"><\/span><span class=\"c13 c8\">Tra c\u1ee9u theo chi\u1ec1u ngang v\u00e0 chi\u1ec1u d\u1ecdc<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p class=\"c4\"><span class=\"c2\">Microsoft s\u1eed d\u1ee5ng hai h\u00e0m cho hai ki\u1ec3u tra c\u1ee9u: VLOOKUP khi mu\u1ed1n tra c\u1ee9u trong c\u1ed9t v\u00e0 <a href=\"http:\/\/blog.ezworkapp.com\/ham-hlookup-nen-su-dung-hlookup-hay-index-ket-hop-match-se-hay-hon.html\"><em><strong>h\u00e0m HLOOKUP<\/strong><\/em><\/a> n\u1ebfu mu\u1ed1n tra c\u1ee9u theo h\u00e0ng.<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">H\u00e0m XLOOKUP c\u00f3 th\u1ec3 tra c\u1ee9u theo c\u1ea3 c\u1ed9t v\u00e0 h\u00e0ng v\u1edbi c\u00f9ng m\u1ed9t c\u00fa ph\u00e1p. Nh\u01b0 v\u1eady ch\u00fang ta s\u1ebd c\u00f3 \u00edt th\u00f4ng tin ph\u1ea3i nh\u1edb h\u01a1n khi s\u1eed d\u1ee5ng h\u00e0m XLOOKUP.<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">C\u00f4ng th\u1ee9c tra c\u1ee9u theo c\u1ed9t s\u1eed d\u1ee5ng XLOOKUP nh\u01b0 sau:<\/span><\/p>\n<p class=\"c4\"><span class=\"c7\">=XLOOKUP(E1, A2:A6, B2:B6)<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32887\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vertical-xlookup-excel.png\" alt=\"h\u00e0m xlookup tra c\u1ee9u theo chi\u1ec1u d\u1ecdc trong excel\" width=\"370\" height=\"206\" title=\"vertical-xlookup-excel\"><\/p>\n<p class=\"c4\"><span class=\"c2\">C\u00f4ng th\u1ee9c tra c\u1ee9u theo h\u00e0ng s\u1eed d\u1ee5ng XLOOKUP nh\u01b0 sau:<\/span><\/p>\n<p class=\"c4\"><span class=\"c7 c8\">=XLOOKUP(I1, B1:F1, B2:F2)<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32888\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/horizontal-xlookup-excel.png\" alt=\"H\u00e0m xlookup tra c\u1ee9u theo chi\u1ec1u ngang trong excel\" width=\"626\" height=\"133\" title=\"horizontal-xlookup-excel\"><\/p>\n<h3 id=\"h.2jxqul9cj1jg\" class=\"c6\"><span class=\"ez-toc-section\" id=\"Tra_cuu_va_lay_gia_tri_o_cot_ben_trai_cot_tim_kiem\"><\/span><span class=\"c13 c8\">Tra c\u1ee9u v\u00e0 l\u1ea5y gi\u00e1 tr\u1ecb \u1edf c\u1ed9t b\u00ean tr\u00e1i c\u1ed9t t\u00ecm ki\u1ebfm<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p class=\"c4\"><span class=\"c2\">Trong c\u00e1c phi\u00ean b\u1ea3n tr\u01b0\u1edbc c\u1ee7a Excel, c\u00e1ch \u0111\u00e1ng tin c\u1eady duy nh\u1ea5t \u0111\u1ec3 b\u1ea1n c\u00f3 th\u1ec3 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u00ecm ki\u1ebfm trong c\u1ed9t n\u0103m \u1edf b\u00ean tr\u00e1i c\u1ed9t ch\u1ee9a gi\u00e1 tr\u1ecb c\u1ea7n tra c\u1ee9u l\u00e0 s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p h\u00e0m Index v\u00e0 Match. \u0110\u1ed1i v\u1edbi h\u00e0m XLOOKUP, b\u1ea1n c\u00f3 th\u1ec3 l\u00e0m vi\u1ec7c n\u00e0y v\u1edbi m\u1ed9t c\u00f4ng th\u1ee9c \u0111\u01a1n gi\u1ea3n h\u01a1n nhi\u1ec1u:<\/span><\/p>\n<p class=\"c4\"><span class=\"c7 c8\">=XLOOKUP(F1, B2:B6, A2:A6)<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32889\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/left-lookup-excel.png\" alt=\"Xlookup t\u00ecm ki\u1ebfm sang ph\u00eda tr\u00e1i\" width=\"436\" height=\"180\" title=\"left-lookup-excel\"><\/p>\n<h3 id=\"h.3pygdyczicug\" class=\"c6\"><span class=\"ez-toc-section\" id=\"Tra_cuu_chinh_xac_va_tra_cuu_gan_dung_voi_ham_XLOOKUP\"><\/span><span class=\"c13 c8\">Tra c\u1ee9u ch\u00ednh x\u00e1c v\u00e0 tra c\u1ee9u g\u1ea7n \u0111\u00fang v\u1edbi h\u00e0m XLOOKUP<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p class=\"c4\"><span class=\"c2\">Thi\u1ebft l\u1eadp gi\u00fap cho h\u00e0m XLOOKUP c\u00f3 th\u1ec3 tra c\u1ee9u ch\u00ednh x\u00e1c hay tra c\u1ee9u g\u1ea7n \u0111\u00fang \u0111\u01b0\u1ee3c \u0111i\u1ec1u khi\u1ec3n b\u1edfi tham s\u1ed1 th\u1ee9 5: match_mode. M\u1eb7c \u0111\u1ecbnh, h\u00e0m XLOOKUP s\u1ebd tra c\u1ee9u ch\u00ednh x\u00e1c.<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">L\u01b0u \u00fd, trong tr\u01b0\u1eddng h\u1ee3p b\u1ea1n thi\u1ebft l\u1eadp tham s\u1ed1 match_mode l\u00e0 1 ho\u1eb7c -1 th\u00ec XLOOKUP v\u1eabn t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c tr\u01b0\u1edbc, n\u1ebfu gi\u00e1 tr\u1ecb n\u00e0y kh\u00f4ng \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y th\u00ec gi\u00e1 tr\u1ecb g\u1ea7n \u0111\u00fang m\u1edbi \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1:<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">C\u00e1c gi\u00e1 tr\u1ecb m\u00e0 match_mode nh\u1eadn v\u00e0 \u00fd ngh\u0129a:<\/span><\/p>\n<ul class=\"c10 lst-kix_9g73wcemy18n-0 start\">\n<li class=\"c3\"><span class=\"c2\">0 ho\u1eb7c b\u1ecf qua: t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c. N\u1ebfu kh\u00f4ng t\u00ecm th\u1ea5y, tr\u1ea3 v\u1ec1 l\u1ed7i #N\/A<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">-1 \u2013 t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c tr\u01b0\u1edbc, n\u1ebfu kh\u00f4ng t\u00ecm th\u1ea5y, tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb nh\u1ecf h\u01a1n g\u1ea7n nh\u1ea5t<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">1 \u2013 t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c tr\u01b0\u1edbc, n\u1ebfu kh\u00f4ng t\u00ecm th\u1ea5y, tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb l\u1edbn h\u01a1n g\u1ea7n nh\u1ea5t.<\/span><\/li>\n<\/ul>\n<h3 id=\"h.5xoot1pb0bkp\" class=\"c0\"><span class=\"ez-toc-section\" id=\"Tim_kiem_chinh_xac_bang_XLOOKUP\"><\/span><span class=\"c12 c8\">T\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c b\u1eb1ng XLOOKUP<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p class=\"c4\"><span class=\"c2\">Th\u00f4ng th\u01b0\u1eddng th\u00ec ch\u00fang ta s\u1eed d\u1ee5ng kh\u1ea3 n\u0103ng t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c c\u1ee7a c\u00e1c h\u00e0m t\u00ecm ki\u1ebfm nhi\u1ec1u h\u01a1n kh\u1ea3 n\u0103ng t\u00ecm ki\u1ebfm g\u1ea7n \u0111\u00fang. V\u00ec m\u1ed9t c\u00e1ch m\u1eb7c \u0111\u1ecbnh, XLOOKUP s\u1ebd t\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c, v\u1eady n\u00ean ch\u00fang ta c\u00f3 th\u1ec3 b\u1ecf qua kh\u00f4ng \u0111i\u1ec1n tham s\u1ed1 match_mode v\u00e0 ch\u1ec9 \u0111i\u1ec1n \u0111\u1ee7 3 tham s\u1ed1 b\u1eaft bu\u1ed9c c\u1ee7a h\u00e0m XLOOKUP m\u00e0 th\u00f4i:<\/span><\/p>\n<p class=\"c4\"><span class=\"c7 c8\">=XLOOKUP(F2, $B$2:$B$6, $C$2:$C$6)<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32890\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-xlookup-exact-match.png\" alt=\"T\u00ecm ki\u1ebfm ch\u00ednh x\u00e1c b\u1eb1ng xlookup trong Excel\" width=\"512\" height=\"177\" title=\"excel-xlookup-exact-match\"><\/p>\n<p class=\"c4\"><span class=\"c2\">V\u00ed d\u1ee5 tr\u00ean cho th\u1ea5y, XLOOKUP c\u00f3 h\u00e0nh vi m\u1eb7c \u0111\u1ecbnh l\u00e0 tra c\u1ee9u ch\u00ednh x\u00e1c, n\u1ebfu kh\u00f4ng t\u00ecm th\u1ea5y k\u1ebft qu\u1ea3 ch\u00ednh x\u00e1c trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, h\u00e0m XLOOKUP tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb #N\/A. Ch\u00fang ta s\u1ebd c\u00f9ng nhau gi\u1ea3i quy\u1ebft v\u1ea5n \u0111\u1ec1 n\u00e0y trong ph\u1ea7n ti\u1ebfp theo.<\/span><\/p>\n<h3 id=\"h.17qqgieay4jx\" class=\"c0\"><span class=\"ez-toc-section\" id=\"Tra_cuu_gan_dung_voi_ham_XLOOKUP\"><\/span><span class=\"c12 c8\">Tra c\u1ee9u g\u1ea7n \u0111\u00fang v\u1edbi h\u00e0m XLOOKUP<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p class=\"c4\"><span class=\"c2\">\u0110\u1ec3 th\u1ef1c hi\u1ec7n tra c\u1ee9u g\u1ea7n \u0111\u00fang s\u1eed h\u00e0m XLOOKUP, b\u1ea1n thi\u1ebft l\u1eadp match_mode v\u1edbi gi\u00e1 tr\u1ecb 1 ho\u1eb7c -1. Vi\u1ec7c s\u1eed d\u1ee5ng 1 hay -1 s\u1ebd tu\u1ef3 thu\u1ed9c v\u00e0o d\u1eef li\u1ec7u c\u1ee7a b\u1ea1n \u0111\u01b0\u1ee3c t\u1ed5 ch\u1ee9c nh\u01b0 th\u1ebf n\u00e0o.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32891\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-approximate-xlookup-smaller.png\" alt=\"xlookup tra c\u1ee9u g\u1ea7n \u0111\u00fang nh\u1ecf h\u01a1n g\u1ea7n nh\u1ea5t\" width=\"512\" height=\"177\" title=\"excel-approximate-xlookup-smaller\"><\/p>\n<p class=\"c4\"><span class=\"c2\">Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, \u00f4 F2 c\u00f3 gi\u00e1 tr\u1ecb l\u00e0 98, do kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb 98 trong v\u00f9ng t\u00ecm ki\u1ebfm l\u00e0 B2:B6, do v\u1eady XLOOKUP t\u00ecm gi\u00e1 tr\u1ecb nh\u1ecf h\u01a1n g\u1ea7n nh\u1ea5t \u0111\u00f3 l\u00e0 90, v\u00e0 90 s\u1ebd \u1eefng v\u1edbi k\u1ebft qu\u1ea3 A \u1edf c\u00f9ng d\u00f2ng.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32892\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-approximate-xlookup-larger.png\" alt=\"xlookup tra c\u1ee9u g\u1ea7n nh\u1ea5t l\u1edbn h\u01a1n\" width=\"514\" height=\"177\" title=\"excel-approximate-xlookup-larger\"><\/p>\n<p class=\"c4\"><span class=\"c2\">Trong tr\u01b0\u1eddng h\u1ee3p b\u1ea3ng t\u00ecm ki\u1ebfm c\u1ee7a ch\u00fang ta ch\u1ee9a d\u1eef li\u1ec7u nh\u01b0 tr\u00ean, 98 kh\u00f4ng \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y trong v\u00f9ng t\u00ecm ki\u1ebfm, match_mode l\u00e0 1, n\u00ean h\u00e0m XLOOKUP s\u1ebd t\u00ecm t\u1edbi gi\u00e1 tr\u1ecb l\u1edbn h\u01a1n g\u1ea7n nh\u1ea5t (100), v\u00e0 k\u1ebft qu\u1ea3 ch\u00fang ta c\u00f3 \u0111\u01b0\u1ee3c l\u00e0 A.<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">T\u00edp: Khi b\u1ea1n copy c\u00f4ng th\u1ee9c XLOOKUP \u0111\u1ec3 \u00e1p d\u1ee5ng cho nhi\u1ec1u d\u00f2ng, b\u1ea1n c\u1ea7n kho\u00e1 tham chi\u1ebfu $B$2:$B$6 \u0111\u1ec3 khi k\u00e9o c\u00f4ng th\u1ee9c th\u00ec tham chi\u1ebfu kh\u00f4ng thay \u0111\u1ed5i.<\/span><\/p>\n<h2 id=\"h.t278fqcyzw81\" class=\"c6\"><span class=\"ez-toc-section\" id=\"Tra_cuu_voi_ky_tu_dai_dien\"><\/span><span class=\"c13 c8\">Tra c\u1ee9u v\u1edbi k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p class=\"c4\"><span class=\"c2\">\u0110\u1ec3 thi\u1ebft l\u1eadp h\u00e0m XLOOKUP cho vi\u1ec7c tra c\u1ee9u v\u1edbi k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n, b\u1ea1n c\u1ea7n vi\u1ebft match_mode b\u1eb1ng 2:<\/span><\/p>\n<ul class=\"c10 lst-kix_9x4owawq8gog-0 start\">\n<li class=\"c3\"><span class=\"c2\">D\u1ea5u sao \/ d\u1ea5u hoa th\u1ecb (*) \u2013 \u0111\u1ea1i di\u1ec7n cho b\u1ea5t c\u1ee9 s\u1ed1 l\u01b0\u1ee3ng k\u00fd t\u1ef1 n\u00e0o<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">D\u1ea5u h\u1ecfi ch\u1ea5m (?) \u2013 \u0111\u1ea1i di\u1ec7n cho b\u1ea5t c\u1ee9 k\u00fd t\u1ef1 \u0111\u01a1n n\u00e0o<\/span><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32893\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-xlookup-wildcard-match.png\" alt=\"xlookup kh\u1edbp v\u1edbi k\u00fd t\u1ef1 \u0111\u1ea1i di\u1ec7n\" width=\"462\" height=\"218\" title=\"excel-xlookup-wildcard-match\"><\/p>\n<p class=\"c4\"><span class=\"c2\">Trong h\u00ecnh minh ho\u1ea1 tr\u00ean, b\u1ea1n c\u00f3 th\u1ec3 tra c\u1ee9u dung l\u01b0\u1ee3ng pin m\u00e0 kh\u00f4ng c\u1ea7n bi\u1ebft t\u00ean \u0111\u1ea7y \u0111\u1ee7 c\u1ee7a phi\u00ean b\u1ea3n iPhone X s\u1eed d\u1ee5ng c\u00e1ch tra c\u1ee9u g\u1ea7n \u0111\u00fang:<\/span><\/p>\n<p class=\"c4\"><span class=\"c7 c8\">=XLOOKUP(\u201c*iphone X*\u201d, A2:A8, B2:B8, ,2)<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">ho\u1eb7c<\/span><\/p>\n<p class=\"c4\"><span class=\"c7 c8\">=XLOOKUP(\u201c*\u201d&amp;E1&amp;\u201d*\u201d, A2:A8, B2:B8, ,2)<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">T\u00edp: Trong tr\u01b0\u1eddng h\u1ee3p gi\u00e1 tr\u1ecb c\u1ea7n tra c\u1ee9u c\u1ee7a b\u1ea1n c\u00f3 d\u1ea5u * ho\u1eb7c d\u1ea5u ? m\u00e0 b\u1ea1n mu\u1ed1n t\u00ecm ki\u1ebfm c\u1ee5m t\u1eeb c\u00f3 2 d\u1ea5u n\u00e0y, b\u1ea1n c\u1ea7n s\u1eed d\u1ee5ng th\u00eam d\u1ea5u ~ \u1edf ph\u00eda tr\u01b0\u1edbc, v\u00ed d\u1ee5: ~*, ~?, ho\u1eb7c ~~<\/span><\/p>\n<h2 id=\"h.zbeic4738ktr\" class=\"c6\"><span class=\"ez-toc-section\" id=\"Tra_cuu_tu_phia_cuoi_bang_len\"><\/span><span class=\"c13 c8\">Tra c\u1ee9u t\u1eeb ph\u00eda cu\u1ed1i b\u1ea3ng l\u00ean<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p class=\"c4\"><span class=\"c2\">Trong tr\u01b0\u1eddng h\u1ee3p, gi\u00e1 tr\u1ecb t\u00ecm ki\u1ebfm kh\u00f4ng ph\u1ea3i l\u00e0 duy nh\u1ea5t trong b\u1ea3ng. B\u1ea1n c\u1ea7n t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb xu\u1ea5t hi\u1ec7n cu\u1ed1i c\u00f9ng, h\u00e0m XLOOKUP c\u00f3 th\u1ec3 gi\u00fap b\u1ea1n t\u00ecm ki\u1ebfm t\u1eeb ph\u00eda cu\u1ed1i b\u1ea3ng.<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">H\u01b0\u1edbng t\u00ecm ki\u1ebfm c\u1ee7a h\u00e0m XLOOKUP \u0111\u01b0\u1ee3c quy \u0111\u1ecbnh trong tham s\u1ed1 th\u1ee9 6:<\/span><\/p>\n<ul class=\"c10 lst-kix_fuhzyhkng44f-0 start\">\n<li class=\"c3\"><span class=\"c2\">1 ho\u1eb7c b\u1ecf qua (m\u1eb7c \u0111\u1ecbnh) \u2013 t\u00ecm t\u1eeb tr\u00ean xu\u1ed1ng d\u01b0\u1edbi ho\u1eb7c t\u1eeb tr\u00e1i qua ph\u1ea3i<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">-1 \u2013 t\u00ecm ki\u1ebfm t\u1eeb d\u01b0\u1edbi l\u00ean ho\u1eb7c t\u1eeb ph\u1ea3i qua<\/span><\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32894\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/xlookup-last-match.png\" alt=\"xlookup t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb cu\u1ed1i c\u00f9ng\" width=\"511\" height=\"238\" title=\"xlookup-last-match\"><\/p>\n<p class=\"c4\"><span class=\"c2\">Trong v\u00ed d\u1ee5 tr\u00ean, ch\u00fang ta c\u00f3 th\u1ec3 t\u00ecm \u0111\u01b0\u1ee3c Sales Amount m\u1edbi nh\u1ea5t c\u1ee7a Laura b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c XLOOKUP:<\/span><\/p>\n<p class=\"c4\"><span class=\"c7 c8\">=XLOOKUP(G1, B2:B9, D2:D9, , ,-1)<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"XLOOKUP_tra_ve_nhieu_cot_hoac_nhieu_hang_ket_qua\"><\/span><span class=\"c13 c8\">XLOOKUP tr\u1ea3 v\u1ec1 nhi\u1ec1u c\u1ed9t ho\u1eb7c nhi\u1ec1u h\u00e0ng k\u1ebft qu\u1ea3<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p class=\"c4\"><span class=\"c2\">M\u1ed9t t\u00ednh n\u0103ng c\u1ef1c k\u1ef3 m\u1ea1nh m\u1ebd c\u1ee7a XLOOKUP l\u00e0 c\u00f3 th\u1ec3 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u00e0 nhi\u1ec1u c\u1ed9t ho\u1eb7c nhi\u1ec1u h\u00e0ng:<\/span><\/p>\n<p class=\"c4\"><span class=\"c7\">=XLOOKUP(F2, A2:A7, B2:D7)<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32895\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/xlookup-multiple-values.png\" alt=\"xlookup tr\u1ea3 v\u1ec1 nhi\u1ec1u d\u1eef li\u1ec7u\" width=\"627\" height=\"197\" title=\"xlookup-multiple-values\"><\/p>\n<p class=\"c4\"><span class=\"c2\">N\u1ebfu b\u1ea1n mu\u1ed1n \u0111\u1ed5i chi\u1ec1u k\u1ebft qu\u1ea3, th\u00ec b\u1ea1n ch\u1ec9 c\u1ea7n bao h\u00e0m XLOOKUP b\u1edfi h\u00e0m TRANSPOSE:<\/span><\/p>\n<p class=\"c4\"><span class=\"c7 c8\">=TRANSPOSE(XLOOKUP(G1, A2:A7, B2:D7))<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32896\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/xlookup-multiple-values-transpose.png\" alt=\"xlookup tr\u1ea3 v\u1ec1 nhi\u1ec1u d\u1eef li\u1ec7u v\u1edbi h\u00e0m transpose\" width=\"504\" height=\"197\" title=\"xlookup-multiple-values-transpose\"><\/p>\n<h2><span class=\"ez-toc-section\" id=\"XLOOKUP_tra_cuu_dua_tren_nhieu_dieu_kien\"><\/span><span class=\"c13 c8\">XLOOKUP tra c\u1ee9u d\u1ef1a tr\u00ean nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p class=\"c4\"><span class=\"c2\">C\u00fa ph\u00e1p \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n d\u1ef1a tr\u00ean h\u00e0m XLOOKUP nh\u01b0 sau:<\/span><\/p>\n<p class=\"c4\"><span class=\"c17\">XLOOKUP(1, (<\/span><span class=\"c15\">criteria_range1<\/span><span class=\"c17\">=<\/span><span class=\"c15\">criteria1<\/span><span class=\"c17\">) * (<\/span><span class=\"c15\">criteria_range2<\/span><span class=\"c17\">=<\/span><span class=\"c15\">criteria2<\/span><span class=\"c17\">) * (\u2026),\u00a0<\/span><span class=\"c15\">return_array<\/span><span class=\"c17 c8\">)<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">N\u1ebfu b\u1ea1n \u0111\u00e3 bi\u1ebft c\u00e1ch s\u1eed d\u1ee5ng <a href=\"http:\/\/blog.ezworkapp.com\/ham-subtotal-cac-ung-dung-cua-subtotal-trong-excel.html\"><em><strong>h\u00e0m SUMPRODUCT<\/strong><\/em><\/a> th\u00ec c\u00e1ch s\u1eed d\u1ee5ng n\u00e0y c\u1ee7a h\u00e0m XLOOKUP c\u00f3 l\u1ebd s\u1ebd r\u1ea5t quen thu\u1ed9c v\u1edbi b\u1ea1n. H\u00e3y c\u00f9ng nhau nghi\u00ean c\u1ee9u v\u00ed d\u1ee5 sau \u0111\u00e2y<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32897\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/xlookup-multiple-conditions.png\" alt=\"xlookup tra c\u1ee9u theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n\" width=\"515\" height=\"259\" title=\"xlookup-multiple-conditions\"><\/p>\n<p class=\"c4\"><span class=\"c7 c8\">=XLOOKUP(1, (B2:B10=G1) * (A2:A10=G2) * (C2:C10=G3), D2:D10)<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">Ch\u00fang ta c\u00f3 3 ti\u00eau ch\u00ed c\u1ea7n tho\u1ea3 m\u00e3n \u0111\u1ed3ng th\u1eddi \u0111\u1ec3 tra c\u1ee9u trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y:<\/span><\/p>\n<ul class=\"c10 lst-kix_grrezlso2bft-0 start\">\n<li class=\"c3\"><span class=\"c2\">Salesperson = Laura (\u0111\u01b0\u1ee3c vi\u1ebft d\u01b0\u1edbi d\u1ea1ng B2:B10 = G1)<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">Date = 02 th\u00e1ng 9 (\u0111\u01b0\u1ee3c vi\u1ebft d\u01b0\u1edbi d\u1ea1ng A2:A10 = G2)<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">Item = Apples (\u0111\u01b0\u1ee3c vi\u1ebft d\u01b0\u1edbi d\u1ea1ng C2:C10 = G3)<\/span><\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"XLOOKUP_tra_cuu_hai_chieu\"><\/span><span class=\"c8 c13\">XLOOKUP tra c\u1ee9u hai chi\u1ec1u<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p class=\"c4\"><span class=\"c2\">Ch\u00fang ta hay c\u0169ng nh\u00ecn v\u00e0o v\u00ed d\u1ee5 sau \u0111\u00e2y:<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32898\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/two-way-xlookup.png\" alt=\"xlookup tra c\u1ee9u hai chi\u1ec1u\" width=\"583\" height=\"177\" title=\"two-way-xlookup\"><\/p>\n<p class=\"c4\"><span class=\"c2\">Trong tr\u01b0\u1eddng h\u1ee3p b\u1ea1n mu\u1ed1n tra c\u1ee9u th\u00f4ng tin trong 1 b\u1ea3ng nh\u01b0 tr\u00ean, gi\u00e1 tr\u1ecb tra c\u1ee9u n\u1eb1m \u1edf c\u1ea3 c\u1ed9t A v\u00e0 n\u1eb1m \u1edf h\u00e0ng th\u1ee9 nh\u1ea5t, th\u00ec c\u00f4ng th\u1ee9c XLOOKUP c\u00f3 th\u1ec3 gi\u00fap b\u1ea1n trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y nh\u01b0 sau:<\/span><\/p>\n<p class=\"c4\"><span class=\"c7 c8\">=XLOOKUP(H1, A2:A6, XLOOKUP(H2, B1:E1, B2:E6))<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">ho\u1eb7c<\/span><\/p>\n<p class=\"c4\"><span class=\"c7 c8\">=XLOOKUP(H2, B1:E1, XLOOKUP(H1, A2:A6, B2:E6))<\/span><\/p>\n<p class=\"c4\"><span class=\"c2\">V\u1edbi b\u00e0i to\u00e1n n\u00e0y, b\u1ea1n c\u0169ng c\u00f3 th\u1ec3 gi\u1ea3i quy\u1ebft b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p h\u00e0m Index v\u00e0 Match trong Excel.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"XLOOKUP_thong_bao_loi_khi_khong_tim_thay_gia_tri_can_tim_kiem\"><\/span><span class=\"c13 c8\">XLOOKUP th\u00f4ng b\u00e1o l\u1ed7i khi kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p class=\"c4\"><span class=\"c2\">Trong tr\u01b0\u1eddng h\u1ee3p h\u00e0m XLOOKUP kh\u00f4ng t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb c\u1ea7n t\u00ecm ki\u1ebfm, h\u00e0m s\u1ebd tr\u1ea3 v\u1ec1 l\u1ed7i #N\/A. \u0110\u1ec3 \u0111\u01b0a ra m\u1ed9t th\u00f4ng b\u00e1o c\u00f3 \u0111\u1ea7y \u0111\u1ee7 th\u00f4ng tin h\u01a1n l\u00e0 m\u1ed9t l\u1ed7i, ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng m\u1ed9t t\u00ednh n\u0103ng c\u00f3 s\u1eb5n c\u1ee7a h\u00e0m XLOOKUP \u0111\u00f3 l\u00e0 tham s\u1ed1 if_not_found.<\/span><\/p>\n<p class=\"c4\"><span class=\"c7 c8\">=XLOOKUP(E1, A2:A6, B2:B6, \u201cNo match is found\u201d)<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-32899\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/if-error-xlookup.png\" alt=\"xlookup v\u00e0 kh\u1ea3 n\u0103ng s\u1eeda l\u1ed7i\" width=\"422\" height=\"179\" title=\"if-error-xlookup\"><\/p>\n<p class=\"c4\"><span class=\"c2\">Ch\u00fa \u00fd:<\/span><\/p>\n<ul class=\"c10 lst-kix_oi6f53d6bpxa-0 start\">\n<li class=\"c3\"><span class=\"c2\">Tham s\u1ed1 if_not_found c\u1ee7a h\u00e0m XLOOKUP ch\u1ec9 \u201cb\u1eaft l\u1ed7i\u201d #N\/A, kh\u00f4ng ph\u1ea3i t\u1ea5t c\u1ea3 c\u00e1c l\u1ed7i<\/span><\/li>\n<li class=\"c3\"><span class=\"c2\">L\u1ed7i #N\/A c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c x\u1eed l\u00fd b\u1eb1ng vi\u1ec7c k\u1ebft h\u1ee3p h\u00e0m IFNA v\u00e0 VLOOKUP, nh\u01b0ng b\u1ea1n s\u1ebd ph\u1ea3i vi\u1ebft m\u1ed9t c\u00f4ng th\u1ee9c ph\u1ee9c t\u1ea1p v\u00e0 d\u00e0i h\u01a1n.<\/span><\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"XLOOKUP_bi_loi_khong_hoat_dong\"><\/span><span class=\"c13 c8\">XLOOKUP b\u1ecb l\u1ed7i, kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng<\/span><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p class=\"c4\"><span class=\"c2\">Trong tr\u01b0\u1eddng h\u1ee3p b\u1ea1n th\u1eed s\u1eed d\u1ee5ng XLOOKUP m\u00e0 b\u1ecb l\u1ed7i, nhi\u1ec1u kh\u1ea3 n\u0103ng b\u1ea1n \u0111ang g\u1eb7p ph\u1ea3i nh\u1eefng v\u1ea5n \u0111\u1ec1 sau:<\/span><\/p>\n<h3 id=\"h.uij5l53og89m\" class=\"c0\"><span class=\"ez-toc-section\" id=\"Loi_NAMES_khi_dung_XLOOKUP\"><\/span><span class=\"c12 c8\">L\u1ed7i #NAMES? khi d\u00f9ng XLOOKUP<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p class=\"c4\"><span class=\"c2\">H\u00e0m XLOOKUP kh\u00f4ng t\u01b0\u01a1ng th\u00edch ng\u01b0\u1ee3c. H\u00e0m XLOOKUP ch\u1ec9 c\u00f3 trong b\u1ed9 Office 365 v\u00e0 s\u1ebd kh\u00f4ng \u0111\u01b0\u1ee3c c\u1eadp nh\u1eadt trong c\u00e1c b\u1ea3n Excel c\u0169 h\u01a1n (Excel 2019 tr\u1edf v\u1ec1 tr\u01b0\u1edbc)<\/span><\/p>\n<h3 id=\"h.fx1jjhwfwy7w\" class=\"c0\"><span class=\"ez-toc-section\" id=\"XLOOKUP_tra_ve_ket_qua_sai\"><\/span><span class=\"c12 c8\">XLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 sai<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p class=\"c4\"><span class=\"c2\">Khi b\u1ea1n \u0111\u00e3 ki\u1ec3m tra k\u0129 l\u1ea1i m\u1ed9t l\u1ea7n c\u00fa ph\u00e1p c\u1ee7a h\u00e0m XLOOKUP r\u1ed3i m\u00e0 k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 v\u1eabn sai, th\u00ec h\u00e3y ki\u1ec3m tra l\u1ea1i v\u00f9ng tham chi\u1ebfu \u0111\u1ec1 ph\u00f2ng tr\u01b0\u1eddng h\u1ee3p khi k\u00e9o c\u00f4ng th\u1ee9c tham chi\u1ebfu b\u1ecb ch\u1ec7ch \u0111i.<\/span><\/p>\n<h3 id=\"h.4s3mkcua8w5t\" class=\"c0\"><span class=\"ez-toc-section\" id=\"XLOOKUP_tra_ve_ket_qua_NA\"><\/span><span class=\"c12 c8\">XLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 #N\/A<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p class=\"c4\"><span class=\"c2\">Khi XLOOKUP tr\u1ea3 v\u1ec1 l\u1ed7i #N\/A, \u0111i\u1ec1u n\u00e0y c\u00f3 ngh\u0129a l\u00e0 gi\u00e1 tr\u1ecb t\u00ecm ki\u1ebfm kh\u00f4ng \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y. H\u00e3y th\u1eed s\u1eed d\u1ee5ng c\u00fa ph\u00e1p t\u00ecm ki\u1ebfm g\u1ea7n \u0111\u00fang ho\u1eb7c s\u1eed d\u1ee5ng thi\u1ebft l\u1eadp tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb kh\u00e1c khi d\u1eef li\u1ec7u c\u1ea7n tra c\u1ee9u kh\u00f4ng \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y.<\/span><\/p>\n<h3 id=\"h.jzqjxd5pmc2\" class=\"c0\"><span class=\"ez-toc-section\" id=\"XLOOKUP_tra_ve_ket_qua_loi_VALUE\"><\/span><span class=\"c8 c12\">XLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u1ed7i #VALUE<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p class=\"c4\"><span class=\"c2\">L\u1ed7i #VALUE \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1 khi k\u00edch th\u01b0\u1edbc c\u1ee7a v\u00f9ng t\u00ecm ki\u1ebfm v\u00e0 v\u00f9ng k\u1ebft qu\u1ea3 kh\u00f4ng \u0111\u1ed3ng nh\u1ea5t ho\u1eb7c kh\u00f4ng \u0111\u00fang chi\u1ec1u. V\u00ed d\u1ee5 b\u1ea1n kh\u00f4ng th\u1ec3 t\u00ecm \u1edf trong m\u1ed9t c\u1ed9t d\u1ecdc v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb trong m\u1ed9t h\u00e0ng ngang v\u1edbi XLOOKUP.<\/span><\/p>\n<h3 id=\"h.6zze6okzv10b\" class=\"c0\"><span class=\"ez-toc-section\" id=\"XLOOKUP_tra_ve_ket_qua_loi_REF\"><\/span><span class=\"c12 c8\">XLOOKUP tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u1ed7i #REF<\/span><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p class=\"c4\"><span class=\"c2\">XLOOKUP s\u1ebd tr\u1ea3 v\u1ec1 l\u1ed7i #REF khi b\u1ea1n th\u1ef1c hi\u1ec7n tra c\u1ee9u gi\u1eefa 2 t\u00e0i li\u1ec7u Excel kh\u00e1c nhau, nh\u01b0ng m\u1ed9t trong hai t\u00e0i li\u1ec7u b\u1ecb \u0111\u00f3ng. Ho\u1eb7c tham chi\u1ebfu s\u1eed d\u1ee5ng trong c\u00f4ng th\u1ee9c XLOOKUP b\u1ecb xo\u00e1 v\u00e0 kh\u00f4ng \u0111\u01b0\u1ee3c c\u1eadp nh\u1eadt l\u1ea1i.<\/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-440.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5174,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-5173","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\/5173","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=5173"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5173\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5174"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5173"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5173"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5173"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}