{"id":2577,"date":"2024-12-02T08:07:29","date_gmt":"2024-12-02T08:07:29","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao\/"},"modified":"2024-12-02T08:07:29","modified_gmt":"2024-12-02T08:07:29","slug":"cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao\/","title":{"rendered":"C\u00c1CH D\u00d9NG H\u00c0M VLOOKUP TRONG EXCEL: V\u00cd D\u1ee4 V\u1ec0 C\u00d4NG TH\u1ee8C N\u00c2NG CAO"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em><span style=\"font-weight: 400\">B\u00e0i vi\u1ebft sau \u0111\u00e2y, <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong> <\/a>s\u1ebd h\u01b0\u1edbng d\u1eabn b\u1ea1n <strong>c\u00e1ch s\u1eed d\u1ee5ng Vlookup<\/strong> \u0111\u1ec3 tra c\u1ee9u k\u1ebft h\u1ee3p nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n v\u00e0 tr\u1ea3 v\u1ec1 m\u1ed9t k\u1ebft qu\u1ea3 c\u1ee5 th\u1ec3 ho\u1eb7c t\u1ea5t c\u1ea3 c\u00e1c k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p. \u0110\u1ed3ng th\u1eddi, b\u1ea1n c\u0169ng s\u1ebd \u0111\u01b0\u1ee3c h\u01b0\u1edbng d\u1eabn c\u00e1ch d\u00f9ng <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> v\u1edbi nhi\u1ec1u trang t\u00ednh trong Excel v\u00e0 nhi\u1ec1u h\u01a1n th\u1ebf n\u1eefa.\u00a0<\/span><\/em><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 ph\u1ea7n th\u1ee9 2 trong lo\u1ea1t b\u00e0i gi\u00fap b\u1ea1n khai th\u00e1c s\u1ee9c m\u1ea1nh c\u1ee7a h\u00e0m Vlookup trong Excel. C\u00e1c v\u00ed d\u1ee5 s\u1ebd cho ch\u00fang ta bi\u1ebft ch\u1ee9c n\u0103ng Vlookup ho\u1ea1t \u0111\u1ed9ng nh\u01b0 th\u1ebf n\u00e0o. B\u00e2y gi\u1edd h\u00e3y b\u1eaft \u0111\u1ea7u xem x\u00e9t l\u1ea1i c\u00e1ch s\u1eed d\u1ee5ng c\u01a1 b\u1ea3n c\u1ee7a h\u00e0m Vlookup trong Excel.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Nh\u1eafc l\u1ea1i m\u1ed9t ch\u00fat v\u1ec1 c\u00fa ph\u00e1p c\u1ee7a h\u00e0m Vlookup:<\/span><\/p>\n<p><strong>VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])<\/strong><\/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-674d6ac00c924\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><\/span><\/span><\/label><\/p>\n<nav>\n<ul class=\"ez-toc-list ez-toc-list-level-1 \">\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao.html#Lam_the_nao_de_Vlookup_nhieu_tieu_chi\" title=\"L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 Vlookup nhi\u1ec1u ti\u00eau ch\u00ed\">L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 Vlookup nhi\u1ec1u ti\u00eau ch\u00ed<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao.html#Cong_thuc_1_VLOOKUP_dua_tren_2_tieu_chi\" title=\"C\u00f4ng th\u1ee9c 1. VLOOKUP d\u1ef1a tr\u00ean 2 ti\u00eau ch\u00ed\">C\u00f4ng th\u1ee9c 1. VLOOKUP d\u1ef1a tr\u00ean 2 ti\u00eau ch\u00ed<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao.html#Cong_thuc_2_Ham_VLOOKUP_trong_Excel_voi_nhieu_dieu_kien\" title=\"C\u00f4ng th\u1ee9c 2. H\u00e0m VLOOKUP trong Excel v\u1edbi nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n\">C\u00f4ng th\u1ee9c 2. H\u00e0m VLOOKUP trong Excel v\u1edbi nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao.html#Lam_the_nao_de_su_dung_VLOOKUP_va_tra_ve_hon_1_gia_tri\" title=\"L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 s\u1eed d\u1ee5ng VLOOKUP v\u00e0 tr\u1ea3 v\u1ec1 h\u01a1n 1 gi\u00e1 tr\u1ecb\">L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 s\u1eed d\u1ee5ng VLOOKUP v\u00e0 tr\u1ea3 v\u1ec1 h\u01a1n 1 gi\u00e1 tr\u1ecb<\/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\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao.html#Cong_thuc_1_Su_dung_Vlookup_lay_gia_tri_n_phu_hop\" title=\"C\u00f4ng th\u1ee9c 1: S\u1eed d\u1ee5ng Vlookup l\u1ea5y gi\u00e1 tr\u1ecb n ph\u00f9 h\u1ee3p\">C\u00f4ng th\u1ee9c 1: S\u1eed d\u1ee5ng Vlookup l\u1ea5y gi\u00e1 tr\u1ecb n ph\u00f9 h\u1ee3p<\/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\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao.html#Cong_thu_2_Vlookup_lay_gia_tri_xuat_hien_lan_thu_2\" title=\"C\u00f4ng th\u1ee9 2: Vlookup l\u1ea5y gi\u00e1 tr\u00ed xu\u1ea5t hi\u1ec7n l\u1ea7n th\u1ee9 2\">C\u00f4ng th\u1ee9 2: Vlookup l\u1ea5y gi\u00e1 tr\u00ed xu\u1ea5t hi\u1ec7n l\u1ea7n th\u1ee9 2<\/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-7\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao.html#Vlookup_tra_ve_nhieu_gia_tri_trong_Excel\" title=\"Vlookup tr\u1ea3 v\u1ec1 nhi\u1ec1u gi\u00e1 tr\u1ecb trong Excel\">Vlookup tr\u1ea3 v\u1ec1 nhi\u1ec1u gi\u00e1 tr\u1ecb trong Excel<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao.html#Cach_Vlookup_theo_hang_va_cot_tra_cuu_hai_chieu\" title=\"C\u00e1ch Vlookup theo h\u00e0ng v\u00e0 c\u1ed9t (tra c\u1ee9u hai chi\u1ec1u)\">C\u00e1ch Vlookup theo h\u00e0ng v\u00e0 c\u1ed9t (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-9\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao.html#Cach_thuc_hien_nhieu_Vlookup_trong_Excel_Vlookup_long_nhau\" title=\"C\u00e1ch th\u1ef1c hi\u1ec7n nhi\u1ec1u Vlookup trong Excel (Vlookup l\u1ed3ng nhau)\">C\u00e1ch th\u1ef1c hi\u1ec7n nhi\u1ec1u Vlookup trong Excel (Vlookup l\u1ed3ng nhau)<\/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\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao.html#Su_dung_Vlookup_tu_nhieu_trang_tinh_khac_nhau\" title=\"S\u1eed d\u1ee5ng Vlookup t\u1eeb nhi\u1ec1u trang t\u00ednh kh\u00e1c nhau\">S\u1eed d\u1ee5ng Vlookup t\u1eeb nhi\u1ec1u trang t\u00ednh kh\u00e1c nhau<\/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-11\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao.html#Cong_thuc_1_INDIRECT_VLOOKUP_de_keo_du_lieu_dong_tu_cac_trang_tinh_khac_nhau\" title=\"C\u00f4ng th\u1ee9c 1. INDIRECT VLOOKUP \u0111\u1ec3 k\u00e9o d\u1eef li\u1ec7u \u0111\u1ed9ng t\u1eeb c\u00e1c trang t\u00ednh kh\u00e1c nhau\">C\u00f4ng th\u1ee9c 1. INDIRECT VLOOKUP \u0111\u1ec3 k\u00e9o d\u1eef li\u1ec7u \u0111\u1ed9ng t\u1eeb c\u00e1c trang t\u00ednh kh\u00e1c nhau<\/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\/cach-dung-ham-vlookup-trong-excel-vi-du-ve-cong-thuc-nang-cao.html#Cong_thuc_2_VLOOKUP_va_IF_long_nhau_de_tra_cuu_nhieu_trang_tinh\" title=\"C\u00f4ng th\u1ee9c 2. VLOOKUP v\u00e0 IF l\u1ed3ng nhau \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u trang t\u00ednh\">C\u00f4ng th\u1ee9c 2. VLOOKUP v\u00e0 IF l\u1ed3ng nhau \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u trang t\u00ednh<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Lam_the_nao_de_Vlookup_nhieu_tieu_chi\"><\/span><b>L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 Vlookup nhi\u1ec1u ti\u00eau ch\u00ed<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">H\u00e0m Vlookup trong Excel th\u1ef1c s\u1ef1 h\u1eefu \u00edch khi t\u00ecm ki\u1ebfm m\u1ed9t gi\u00e1 tr\u1ecb nh\u1ea5t \u0111\u1ecbnh d\u1ef1a tr\u00ean c\u01a1 s\u1edf d\u1eef li\u1ec7u. Tuy nhi\u00ean, n\u00f3 thi\u1ebfu m\u1ed9t t\u00ednh n\u0103ng quan tr\u1ecdng \u2013 c\u00fa ph\u00e1p c\u1ee7a n\u00f3 ch\u1ec9 cho ph\u00e9p m\u1ed9t gi\u00e1 tr\u1ecb tra c\u1ee9u. Nh\u01b0ng n\u1ebfu b\u1ea1n mu\u1ed1n tra c\u1ee9u v\u1edbi m\u1ed9t s\u1ed1 \u0111i\u1ec1u ki\u1ec7n? C\u00f3 m\u1ed9t s\u1ed1 gi\u1ea3i ph\u00e1p kh\u00e1c nhau cho b\u1ea1n l\u1ef1a ch\u1ecdn.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Cong_thuc_1_VLOOKUP_dua_tren_2_tieu_chi\"><\/span><b>C\u00f4ng th\u1ee9c 1. VLOOKUP d\u1ef1a tr\u00ean 2 ti\u00eau ch\u00ed<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 danh s\u00e1ch c\u00e1c \u0111\u01a1n h\u00e0ng v\u00e0 mu\u1ed1n t\u00ecm s\u1ed1 l\u01b0\u1ee3ng d\u1ef1a tr\u00ean 2 ti\u00eau ch\u00ed Customer name v\u00e0 Product. M\u1ed9t y\u1ebfu t\u1ed1 ph\u1ee9c t\u1ea1p l\u00e0 m\u1ed7i kh\u00e1ch h\u00e0ng \u0111\u1eb7t h\u00e0ng nhi\u1ec1u s\u1ea3n ph\u1ea9m, nh\u01b0 trong b\u1ea3ng d\u01b0\u1edbi \u0111\u00e2y:<\/span><\/p>\n<figure style=\"width: 464px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"H\u00e0m VLOOKUP d\u1ef1a tr\u00ean 2 ti\u00eau ch\u00ed\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vookup-two-values.png\" alt=\"VLOOKUP based on two values \u2013 source data\" width=\"464\" height=\"240\"><figcaption class=\"wp-caption-text\">H\u00e0m VLOOKUP d\u1ef1a tr\u00ean 2 ti\u00eau ch\u00ed<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">M\u1ed9t c\u00f4ng th\u1ee9c VLOOKUP th\u00f4ng th\u01b0\u1eddng s\u1ebd kh\u00f4ng gi\u1ea3i quy\u1ebft \u0111\u01b0\u1ee3c trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, b\u1edfi n\u00f3 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u00ecm th\u1ea5y \u0111\u1ea7u ti\u00ean ph\u00f9 h\u1ee3p v\u1edbi gi\u00e1 tr\u1ecb tra c\u1ee9u m\u00e0 b\u1ea1n ch\u1ec9 \u0111\u1ecbnh<\/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-199.gif\" title=\"ex101_92019-nho-199\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><span style=\"font-weight: 400\">Gi\u1ea3i ph\u00e1p \u0111\u01a1n gi\u1ea3n l\u00e0 t\u1ea1o m\u1ed9t c\u1ed9t b\u1ed5 sung n\u1ed1i t\u1ea5t c\u1ea3 c\u00e1c ti\u00eau ch\u00ed m\u00e0 b\u1ea1n mu\u1ed1n, trong v\u00ed d\u1ee5 n\u00e0y l\u00e0 c\u00e1c c\u1ed9t <\/span><i><span style=\"font-weight: 400\">Customer<\/span><\/i><span style=\"font-weight: 400\"> v\u00e0 <\/span><i><span style=\"font-weight: 400\">Product<\/span><\/i><span style=\"font-weight: 400\">. \u0110i\u1ec1u quan tr\u1ecdng l\u00e0 c\u1ed9t tr\u1ee3 gi\u00fap ph\u1ea3i l\u00e0 c\u1ed9t ngo\u00e0i c\u00f9ng b\u00ean tr\u00e1i trong m\u1ea3ng b\u1ea3ng v\u00ec \u0111\u00f3 l\u00e0 n\u01a1i h\u00e0m VLOOKUP c\u1ee7a Excel lu\u00f4n t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb tra c\u1ee9u.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ec v\u1eady, h\u00e3y th\u00eam m\u1ed9t c\u1ed9t v\u00e0o b\u00ean tr\u00e1i b\u1ea3ng c\u1ee7a b\u1ea1n v\u00e0 sao ch\u00e9p c\u00f4ng th\u1ee9c d\u01b0\u1edbi \u0111\u00e2y qua c\u1ed9t \u0111\u00f3. \u0110i\u1ec1u n\u00e0y s\u1ebd \u0111i\u1ec1n v\u00e0o c\u1ed9t tr\u1ee3 gi\u00fap v\u1edbi c\u00e1c gi\u00e1 tr\u1ecb t\u1eeb c\u1ed9t B v\u00e0 C (k\u00fd t\u1ef1 kho\u1ea3ng tr\u1eafng \u0111\u01b0\u1ee3c n\u1ed1i gi\u1eefa \u0111\u1ec3 d\u1ec5 \u0111\u1ecdc h\u01a1n):<\/span><\/p>\n<p><strong>=B2&amp;\u201d \u201c&amp;C2<\/strong><\/p>\n<p><span style=\"font-weight: 400\">V\u00e0 sau \u0111\u00f3, s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c VLOOKUP chu\u1ea9n v\u00e0 \u0111\u1eb7t c\u1ea3 hai ti\u00eau ch\u00ed v\u00e0o \u0111\u1ed1i s\u1ed1 lookup_value:<\/span><\/p>\n<p><strong>=VLOOKUP(\u201cJeremy Sweets\u201d, A2:D11, 4, FALSE)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Ho\u1eb7c, nh\u1eadp ti\u00eau ch\u00ed v\u00e0o c\u00e1c \u00f4 ri\u00eang bi\u1ec7t (trong tr\u01b0\u1eddng h\u1ee3p tr\u00ean\u00a0 l\u00e0 G1 v\u00e0 G2) v\u00e0 n\u1ed1i c\u00e1c \u00f4 \u0111\u00f3:<\/span><\/p>\n<p><strong>=VLOOKUP(G1&amp;\u201d \u201c&amp;G2, A2:D11, 4, FALSE)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Khi ch\u00fang ta mu\u1ed1n tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb t\u1eeb c\u1ed9t D, l\u00e0 gi\u00e1 tr\u1ecb th\u1ee9 t\u01b0 trong m\u1ea3ng b\u1ea3ng, ch\u00fang ta s\u1eed d\u1ee5ng 4 cho <\/span><i><span style=\"font-weight: 400\">col_index_num<\/span><\/i><span style=\"font-weight: 400\"> . \u0110\u1ed1i s\u1ed1 <\/span><i><span style=\"font-weight: 400\">range_lookup<\/span><\/i><span style=\"font-weight: 400\"> \u0111\u01b0\u1ee3c \u0111\u1eb7t th\u00e0nh FALSE \u0111\u1ec3 Vlookup \u0111\u1ed1i s\u00e1nh ch\u00ednh x\u00e1c. \u1ea2nh ch\u1ee5p m\u00e0n h\u00ecnh b\u00ean d\u01b0\u1edbi cho th\u1ea5y k\u1ebft qu\u1ea3:<\/span><\/p>\n<figure style=\"width: 576px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"V\u00ed d\u1ee5 c\u00f4ng th\u1ee9c Vlookup\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-two-criteria.png\" alt=\"VLOOKUP with two criteria\" width=\"576\" height=\"278\"><figcaption class=\"wp-caption-text\">V\u00ed d\u1ee5 c\u00f4ng th\u1ee9c Vlookup<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">Trong tr\u01b0\u1eddng h\u1ee3p Lookup Table c\u1ee7a b\u1ea1n n\u1eb1m trong m\u1ed9t trang t\u00ednh kh\u00e1 , h\u00e3y \u0111\u01b0a t\u00ean c\u1ee7a trang t\u00ednh v\u00e0o c\u00f4ng th\u1ee9c VLOOKUP c\u1ee7a b\u1ea1n. V\u00ed d\u1ee5:<\/span><\/p>\n<p><strong>=VLOOKUP(G1&amp;\u201d \u201c&amp;G2, Orders!A2:D11, 4, FALSE)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Ngo\u00e0i ra, h\u00e3y t\u1ea1o m\u1ed9t ph\u1ea1m vi \u0111\u00e3 \u0111\u1eb7t t\u00ean cho Lookup Table (gi\u1ea3 s\u1eed, <\/span><i><span style=\"font-weight: 400\">Orders<\/span><\/i><span style=\"font-weight: 400\">) \u0111\u1ec3 l\u00e0m cho c\u00f4ng th\u1ee9c d\u1ec5 \u0111\u1ecdc h\u01a1n:<\/span><\/p>\n<p><strong><i>=VLOOKUP(G1&amp;\u201d \u201c&amp;G2, Orders, 4, FALSE)<\/i><\/strong><\/p>\n<p><span style=\"font-weight: 400\">Ghi ch\u00fa. \u0110\u1ec3 c\u00f4ng th\u1ee9c ho\u1ea1t \u0111\u1ed9ng ch\u00ednh x\u00e1c, c\u00e1c gi\u00e1 tr\u1ecb trong c\u1ed9t ph\u1ee5 ph\u1ea3i \u0111\u01b0\u1ee3c n\u1ed1i ch\u00ednh x\u00e1c theo gi\u1ed1ng nh\u01b0 trong \u0111\u1ed1i s\u1ed1 lookup_value . V\u00ed d\u1ee5: ch\u00fang ta s\u1eed d\u1ee5ng m\u1ed9t k\u00fd t\u1ef1 kho\u1ea3ng tr\u1eafng \u0111\u1ec3 ph\u00e2n t\u00e1ch c\u00e1c ti\u00eau ch\u00ed trong c\u1ea3 c\u1ed9t tr\u1ee3 gi\u00fap (B2 &amp; \u201c\u201d &amp; C2) v\u00e0 c\u00f4ng th\u1ee9c VLOOKUP (G1 &amp; \u201c\u201d &amp; G2).<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Cong_thuc_2_Ham_VLOOKUP_trong_Excel_voi_nhieu_dieu_kien\"><\/span><b>C\u00f4ng th\u1ee9c 2. H\u00e0m VLOOKUP trong Excel v\u1edbi nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">V\u1ec1 l\u00fd thuy\u1ebft Vlookup v\u1eabn s\u1eed d\u1ee5ng cho nh\u1eefng nhi\u1ec7m v\u1ee5 nhi\u1ec1u h\u01a1n hai ti\u00eau ch\u00ed. Tuy nhi\u00ean, c\u00f3 m\u1ed9t s\u1ed1 l\u01b0u \u00fd. Th\u1ee9 nh\u1ea5t, gi\u00e1 tr\u1ecb tra c\u1ee9u \u0111\u01b0\u1ee3c gi\u1edbi h\u1ea1n trong 255 k\u00fd t\u1ef1 v\u00e0 th\u1ee9 hai, thi\u1ebft k\u1ebf c\u1ee7a trang t\u00ednh c\u00f3 th\u1ec3 kh\u00f4ng cho ph\u00e9p th\u00eam c\u1ed9t tr\u1ee3 gi\u00fap.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Microsoft Excel th\u01b0\u1eddng cung c\u1ea5p nhi\u1ec1u c\u00e1ch \u0111\u1ec3 l\u00e0m \u0111i\u1ec1u t\u01b0\u01a1ng t\u1ef1. \u0110\u1ec3 Vlookup nhi\u1ec1u ti\u00eau ch\u00ed, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p INDEX MATCH ho\u1eb7c <a href=\"http:\/\/blog.ezworkapp.com\/mot-diem-thu-vi-ve-ham-xlookup.html\"><strong>h\u00e0m XLOOKUP<\/strong><\/a> \u0111\u01b0\u1ee3c gi\u1edbi thi\u1ec7u g\u1ea7n \u0111\u00e2y trong Office 365.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ed d\u1ee5: \u0111\u1ec3 tra c\u1ee9u d\u1ef1a tr\u00ean 3 gi\u00e1 tr\u1ecb kh\u00e1c nhau ( Date , Customer name v\u00e0 Product ), ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng m\u1ed9t trong c\u00e1c c\u00f4ng th\u1ee9c sau:<\/span><\/p>\n<p><strong><i>=INDEX(D2:D11, MATCH(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), 0))<\/i><\/strong><\/p>\n<p><strong><i>=XLOOKUP(1, (G1=A2:A11) * (G2=B2:B11) * (G3=C2:C11), D2:D11)<\/i><\/strong><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">G1 l\u00e0 ti\u00eau ch\u00ed 1 (Date)<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">G2 l\u00e0 ti\u00eau ch\u00ed 2 (Customer name)<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">G3 l\u00e0 ti\u00eau ch\u00ed 3 (Product)<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">A2: A11 l\u00e0 ph\u1ea1m vi tra c\u1ee9u 1 (Date)<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">B2: B11 l\u00e0 ph\u1ea1m vi tra c\u1ee9u 2 (product name)<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">C2: C11 l\u00e0 ph\u1ea1m vi tra c\u1ee9u 3 (product)<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">D2: D11 l\u00e0 ph\u1ea1m vi tr\u1ea3 v\u1ec1 (Qty)<\/span><\/li>\n<\/ul>\n<figure style=\"width: 534px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"H\u00e0m VLOOKUP trong Excel v\u1edbi nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-vlookup-multiple-criteria.png\" alt=\"VLOOKUP multiple criteria\" width=\"534\" height=\"277\"><figcaption class=\"wp-caption-text\">H\u00e0m VLOOKUP trong Excel v\u1edbi nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/figcaption><\/figure>\n<p><b>Ghi ch\u00fa<\/b><span style=\"font-weight: 400\">. Trong t\u1ea5t c\u1ea3 c\u00e1c phi\u00ean b\u1ea3n ngo\u1ea1i tr\u1eeb Excel 365, INDEX MATCH ph\u1ea3i \u0111\u01b0\u1ee3c nh\u1eadp d\u01b0\u1edbi d\u1ea1ng c\u00f4ng th\u1ee9c m\u1ea3ng CSE b\u1eb1ng c\u00e1ch nh\u1ea5n Ctrl + Shift + Enter. Trong Excel 365 h\u1ed7 tr\u1ee3 m\u1ea3ng \u0111\u1ed9ng, n\u00f3 c\u0169ng ho\u1ea1t \u0111\u1ed9ng nh\u01b0 m\u1ed9t c\u00f4ng th\u1ee9c th\u00f4ng th\u01b0\u1eddng.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Lam_the_nao_de_su_dung_VLOOKUP_va_tra_ve_hon_1_gia_tri\"><\/span><b>L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 s\u1eed d\u1ee5ng VLOOKUP v\u00e0 tr\u1ea3 v\u1ec1 h\u01a1n 1 gi\u00e1 tr\u1ecb<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">H\u00e0m Vlookup trong Excel ch\u1ec9 t\u00ecm n\u1ea1p m\u1ed9t gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p v\u00e0 n\u00f3 ch\u1ec9 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p \u0111\u1ea7u ti\u00ean. Nh\u01b0ng n\u1ebfu c\u00f3 nhi\u1ec1u k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p v\u1edbi m\u1ea3ng tra c\u1ee9u v\u00e0 b\u1ea1n mu\u1ed1n n\u00f3 tr\u1ea3 v\u1ec1 t\u1ea5t c\u1ea3 c\u00e1c gi\u00e1 tr\u1ecb th\u00ec ph\u1ea3i l\u00e0m th\u1ebf n\u00e0o? Nghe c\u00f3 ph\u1ee9c t\u1ea1p nh\u01b0ng d\u01b0\u1edbi \u0111\u00e2y s\u1ebd h\u01b0\u1edbng d\u1eabn b\u1ea1n m\u1ed9t s\u1ed1 c\u00e1ch x\u1eed l\u00fd.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Cong_thuc_1_Su_dung_Vlookup_lay_gia_tri_n_phu_hop\"><\/span><b>C\u00f4ng th\u1ee9c 1: S\u1eed d\u1ee5ng Vlookup l\u1ea5y gi\u00e1 tr\u1ecb n ph\u00f9 h\u1ee3p<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t c\u1ed9t l\u00e0 <\/span><i><span style=\"font-weight: 400\">Customer name<\/span><\/i><span style=\"font-weight: 400\">, m\u1ed9t c\u1ed9t l\u00e0 s\u1ea3n ph\u1ea9m h\u1ecd \u0111\u00e3 mua <\/span><i><span style=\"font-weight: 400\">Product <\/span><\/i><span style=\"font-weight: 400\">v\u00e0 b\u1ea1n \u0111ang t\u00ecm s\u1ea3n ph\u1ea9m th\u1ee9 2 ho\u1eb7c th\u1ee9 3 \u0111\u01b0\u1ee3c mua b\u1edfi m\u1ed9t kh\u00e1ch h\u00e0ng nh\u1ea5t \u0111\u1ecbnh.<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00e1ch \u0111\u01a1n gi\u1ea3n nh\u1ea5t l\u00e0 th\u00eam m\u1ed9t c\u1ed9t ph\u1ee5 v\u00e0o b\u00ean tr\u00e1i c\u1ee7a b\u1ea3ng nh\u01b0 \u0111\u00e3 l\u00e0m trong v\u00ed d\u1ee5 \u0111\u1ea7u ti\u00ean. Nh\u01b0ng l\u1ea7n n\u00e0y, ch\u00fang ta s\u1ebd \u0111i\u1ec1n t\u00ean kh\u00e1ch h\u00e0ng v\u00e0 s\u1ed1 l\u1ea7n xu\u1ea5t hi\u1ec7n nh\u01b0 \u201d John Doe1 \u201c, \u201d John Doe 2 \u201c, v.v.<\/span><\/p>\n<p><span style=\"font-weight: 400\">S\u1eed d\u1ee5ng h\u00e0m COUNTIF v\u1edbi tham chi\u1ebfu ph\u1ea1m vi h\u1ed7n h\u1ee3p (tham chi\u1ebfu \u0111\u1ea7u ti\u00ean l\u00e0 tuy\u1ec7t \u0111\u1ed1i v\u00e0 tham chi\u1ebfu th\u1ee9 hai l\u00e0 t\u01b0\u01a1ng \u0111\u1ed1i nh\u01b0 $ B $ 2: B2). V\u00ec tham chi\u1ebfu t\u01b0\u01a1ng \u0111\u1ed1i thay \u0111\u1ed5i d\u1ef1a tr\u00ean v\u1ecb tr\u00ed c\u1ee7a \u00f4 n\u01a1i c\u00f4ng th\u1ee9c \u0111\u01b0\u1ee3c sao ch\u00e9p, trong h\u00e0ng 3, n\u00f3 s\u1ebd tr\u1edf th\u00e0nh $ B $ 2: B3, \u1edf h\u00e0ng 4 \u2013 $ B $ 2: B4, v.v.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u01b0\u1ee3c n\u1ed1i v\u1edbi t\u00ean kh\u00e1ch h\u00e0ng (B2), c\u00f4ng th\u1ee9c c\u00f3 d\u1ea1ng sau:<\/span><\/p>\n<p><strong>=B2&amp;COUNTIF($B$2:B2, B2)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c tr\u00ean chuy\u1ec3n \u0111\u1ebfn A2, sau \u0111\u00f3 b\u1ea1n sao ch\u00e9p n\u00f3 xu\u1ed1ng bao nhi\u00eau \u00f4 n\u1ebfu c\u1ea7n.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Sau \u0111\u00f3, nh\u1eadp t\u00ean \u0111\u1ed1i t\u01b0\u1ee3ng v\u00e0 s\u1ed1 l\u1ea7n xu\u1ea5t hi\u1ec7n trong c\u00e1c \u00f4 ri\u00eang bi\u1ec7t (F1 v\u00e0 F2) v\u00e0 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c d\u01b0\u1edbi \u0111\u00e2y \u0111\u1ec3 Vlookup:<\/span><\/p>\n<p><strong>=VLOOKUP(F1&amp;F2, A2:C11, 3, FALSE)<\/strong><\/p>\n<figure style=\"width: 501px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"S\u1eed d\u1ee5ng Vlookup l\u1ea5y gi\u00e1 tr\u1ecb n ph\u00f9 h\u1ee3p\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-nth-instance.png\" alt=\"Vlookup Nth instance\" width=\"501\" height=\"277\"><figcaption class=\"wp-caption-text\">S\u1eed d\u1ee5ng Vlookup l\u1ea5y gi\u00e1 tr\u1ecb n ph\u00f9 h\u1ee3p<\/figcaption><\/figure>\n<h3><span class=\"ez-toc-section\" id=\"Cong_thu_2_Vlookup_lay_gia_tri_xuat_hien_lan_thu_2\"><\/span><b>C\u00f4ng th\u1ee9 2: Vlookup l\u1ea5y gi\u00e1 tr\u00ed xu\u1ea5t hi\u1ec7n l\u1ea7n th\u1ee9 2<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n \u0111ang t\u00ecm ki\u1ebfm l\u1ea7n xu\u1ea5t hi\u1ec7n th\u1ee9 2 , b\u1ea1n c\u00f3 th\u1ec3 ti\u1ebfn h\u00e0nh m\u00e0 kh\u00f4ng c\u1ea7n t\u1ea1o c\u1ed9t ph\u1ee5 b\u1eb1ng c\u00e1ch t\u1ea1o ra m\u1ed9t c\u00f4ng th\u1ee9c VLOOKUP ph\u1ee9c t\u1ea1p h\u01a1n v\u1edbi INDIRECT v\u00e0 MATCH:<\/span><\/p>\n<p><strong>=VLOOKUP(E1, INDIRECT(\u201cA\u201d&amp;(MATCH(E1, A2:A11, 0)+2)&amp;\u201d:B11\u2033), 2, FALSE)<\/strong><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">E1 l\u00e0 gi\u00e1 tr\u1ecb tra c\u1ee9u<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">A2: A11 l\u00e0 ph\u1ea1m vi tra c\u1ee9u<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">B11 l\u00e0 \u00f4 cu\u1ed1i c\u00f9ng (d\u01b0\u1edbi c\u00f9ng b\u00ean ph\u1ea3i) c\u1ee7a Lookup Table<\/span><\/li>\n<\/ul>\n<figure style=\"width: 483px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"H\u00e0m Vlookup l\u1ea5y gi\u00e1 tr\u00ed xu\u1ea5t hi\u1ec7n l\u1ea7n th\u1ee9 2\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-2nd-occurence.png\" alt=\"Vlookup 2nd occurrence\" width=\"483\" height=\"277\"><figcaption class=\"wp-caption-text\">H\u00e0m Vlookup l\u1ea5y gi\u00e1 tr\u00ed xu\u1ea5t hi\u1ec7n l\u1ea7n th\u1ee9 2<\/figcaption><\/figure>\n<p><b>Ch\u00fa \u00fd. <\/b><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c n\u00e0y ch\u1ec9 t\u00ecm th\u1ea5y gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p th\u1ee9 hai. N\u1ebfu b\u1ea1n c\u1ea7n c\u00f3 s\u1ef1 xu\u1ea5t hi\u1ec7n v\u1ecb tr\u00ed kh\u00e1c th\u00ec quay l\u1ea1i v\u1edbi gi\u1ea3i ph\u00e1p tr\u01b0\u1edbc. L\u01b0u \u00fd r\u1eb1ng c\u00f4ng th\u1ee9c tr\u00ean \u0111\u01b0\u1ee3c vi\u1ebft cho m\u1ed9t tr\u01b0\u1eddng h\u1ee3p c\u1ee5 th\u1ec3 trong \u0111\u00f3 c\u00e1c \u00f4 d\u1eef li\u1ec7u trong Lookup Table b\u1eaft \u0111\u1ea7u \u1edf h\u00e0ng 2. N\u1ebfu b\u1ea3ng c\u1ee7a b\u1ea1n \u1edf \u0111\u00e2u \u0111\u00f3 \u1edf gi\u1eefa trang t\u00ednh, h\u00e3y s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c chung d\u01b0\u1edbi \u0111\u00e2y, trong \u0111\u00f3 A1 l\u00e0 \u00f4 tr\u00ean c\u00f9ng b\u00ean tr\u00e1i c\u1ee7a Lookup Table c\u00f3 ch\u1ee9a ti\u00eau \u0111\u1ec1 c\u1ed9t:<\/span><\/p>\n<p><strong>=VLOOKUP(E1, INDIRECT(\u201cA\u201d&amp;(MATCH(E1, A2:A11, 0)+1+ROW(A1))&amp;\u201d:B11\u2033), 2, FALSE)<\/strong><\/p>\n<p><b><i>C\u00f4ng th\u1ee9c n\u00e0y ho\u1ea1t \u0111\u1ed9ng nh\u01b0 th\u1ebf n\u00e0o?<\/i><\/b><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u00e2y l\u00e0 ph\u1ea7n quan tr\u1ecdng c\u1ee7a c\u00f4ng th\u1ee9c t\u1ea1o ph\u1ea1m vi Vlookup \u0111\u1ed9ng :<\/span><\/p>\n<p><strong><i>INDIRECT(\u201cA\u201d&amp;(MATCH(E1, A2:A11, 0)+2)&amp;\u201d:B11\u2033)<\/i><\/strong><\/p>\n<p><span style=\"font-weight: 400\"><a href=\"http:\/\/blog.ezworkapp.com\/su-dung-ham-match.html\"><strong>H\u00e0m MATCH<\/strong> <\/a>\u0111\u01b0\u1ee3c c\u1ea5u h\u00ecnh cho \u0111\u1ed1i s\u00e1nh ch\u00ednh x\u00e1c (0 trong \u0111\u1ed1i s\u1ed1 cu\u1ed1i c\u00f9ng) so s\u00e1nh t\u00ean \u0111\u00edch (E1) v\u1edbi danh s\u00e1ch c\u00e1c t\u00ean (A2: A11) v\u00e0 tr\u1ea3 v\u1ec1 v\u1ecb tr\u00ed c\u1ee7a \u0111\u1ed1i s\u00e1nh \u0111\u01b0\u1ee3c t\u00ecm th\u1ea5y \u0111\u1ea7u ti\u00ean. S\u1ed1 n\u00e0y s\u1ebd \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng l\u00e0m t\u1ecda \u0111\u1ed9 h\u00e0ng b\u1eaft \u0111\u1ea7u cho ph\u1ea1m vi vlookup, v\u00ec v\u1eady ch\u00fang t\u00f4i th\u00eam 2 v\u00e0o n\u00f3 (+1 \u0111\u1ec3 lo\u1ea1i tr\u1eeb tr\u01b0\u1eddng h\u1ee3p \u0111\u1ea7u ti\u00ean v\u00e0 +1 \u0111\u1ec3 lo\u1ea1i tr\u1eeb h\u00e0ng 1 v\u1edbi ti\u00eau \u0111\u1ec1 c\u1ed9t). Ngo\u00e0i ra, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng 1 + ROW (A1) \u0111\u1ec3 t\u1ef1 \u0111\u1ed9ng t\u00ednh to\u00e1n \u0111i\u1ec1u ch\u1ec9nh c\u1ea7n thi\u1ebft d\u1ef1a tr\u00ean v\u1ecb tr\u00ed c\u1ee7a h\u00e0ng ti\u00eau \u0111\u1ec1 (A1 trong tr\u01b0\u1eddng h\u1ee3p c\u1ee7a ch\u00fang t\u00f4i).<\/span><\/p>\n<p><span style=\"font-weight: 400\">K\u1ebft qu\u1ea3 l\u00e0 nh\u1eadn \u0111\u01b0\u1ee3c chu\u1ed7i v\u0103n b\u1ea3n sau \u0111\u00e2y v\u00e0\u00a0 INDIRECT chuy\u1ec3n \u0111\u1ed5i th\u00e0nh tham chi\u1ebfu ph\u1ea1m vi:<\/span><\/p>\n<p><strong>INDIRECT(\u201cA\u201d&amp;5&amp;\u201d:B11\u2033) -&gt; A5:B11<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Ph\u1ea1m vi n\u00e0y \u0111i \u0111\u1ebfn \u0111\u1ed1i s\u1ed1 <\/span><i><span style=\"font-weight: 400\">table_array <\/span><\/i><span style=\"font-weight: 400\">c\u1ee7a VLOOKUP bu\u1ed9c n\u00f3 ph\u1ea3i b\u1eaft \u0111\u1ea7u t\u00ecm ki\u1ebfm \u1edf h\u00e0ng 5, b\u1ecf qua phi\u00ean b\u1ea3n \u0111\u1ea7u ti\u00ean c\u1ee7a gi\u00e1 tr\u1ecb tra c\u1ee9u:<\/span><\/p>\n<p><strong>VLOOKUP(E1, A5:B11, 2, FALSE)<\/strong><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Vlookup_tra_ve_nhieu_gia_tri_trong_Excel\"><\/span><b>Vlookup tr\u1ea3 v\u1ec1 nhi\u1ec1u gi\u00e1 tr\u1ecb trong Excel<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">H\u00e0m Vlookup trong Excel \u0111\u01b0\u1ee3c thi\u1ebft k\u1ebf v\u00e0 ch\u1ec9 tr\u1ea3 v\u1ec1 m\u1ed9t k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p. V\u1eady c\u00f3 c\u00e1ch n\u00e0o \u0111\u1ec3 h\u00e0m Vlookup tr\u1ea3 v\u1ec1 nhi\u1ec1u gi\u00e1 tr\u1ecb hay kh\u00f4ng? \u0110\u1ec3 l\u00e0m \u0111\u01b0\u1ee3c \u0111i\u1ec1u n\u00e0y ph\u1ea3i y\u00eau c\u1ea7u s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p m\u1ed9t s\u1ed1 h\u00e0m kh\u00e1c nh\u01b0 Index, Small v\u00e0 Row.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ed d\u1ee5 d\u01b0\u1edbi \u0111\u00e2y c\u00f3 th\u1ec3 t\u00ecm th\u1ea5y t\u1ea5t c\u1ea3 c\u00e1c l\u1ea7n xu\u1ea5t hi\u1ec7n c\u1ee7a gi\u00e1 tr\u1ecb tra c\u1ee9u F2 trong ph\u1ea1m vi tra c\u1ee9u B2: B16 v\u00e0 tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p t\u1eeb c\u1ed9t C:<\/span><\/p>\n<p><strong>{=IFERROR(INDEX($C$2:$C$16, SMALL(IF($F$2=B2:B16, ROW(C2:C16)-1,\u201d\u201d), ROW()-3)),\u201d\u201d)}<\/strong><\/p>\n<p><span style=\"font-weight: 400\">C\u00f3 2 c\u00e1ch \u0111\u1ec3 nh\u1eadp c\u00f4ng th\u1ee9c v\u00e0o trang t\u00ednh c\u1ee7a b\u1ea1n:<\/span><\/p>\n<p><span style=\"font-weight: 400\">Nh\u1eadp c\u00f4ng th\u1ee9c v\u00e0o \u00f4 \u0111\u1ea7u ti\u00ean, nh\u1ea5n Ctrl + Shift + Enter, r\u1ed3i k\u00e9o n\u00f3 xu\u1ed1ng m\u1ed9t v\u00e0i \u00f4 n\u1eefa.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ch\u1ecdn m\u1ed9t s\u1ed1 \u00f4 li\u1ec1n k\u1ec1 trong m\u1ed9t c\u1ed9t duy nh\u1ea5t (F1: F11 trong \u1ea3nh ch\u1ee5p m\u00e0n h\u00ecnh b\u00ean d\u01b0\u1edbi), nh\u1eadp c\u00f4ng th\u1ee9c v\u00e0 nh\u1ea5n Ctrl + Shift + Enter \u0111\u1ec3 ho\u00e0n th\u00e0nh n\u00f3.<\/span><\/p>\n<figure style=\"width: 561px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"Vlookup tr\u1ea3 v\u1ec1 nhi\u1ec1u gi\u00e1 tr\u1ecb trong Excel\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-multiple-values.png\" alt=\"Vlookup multiple values\" width=\"561\" height=\"279\"><figcaption class=\"wp-caption-text\">Vlookup tr\u1ea3 v\u1ec1 nhi\u1ec1u gi\u00e1 tr\u1ecb trong Excel<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">D\u00f9 b\u1eb1ng c\u00e1ch n\u00e0o, s\u1ed1 \u00f4 m\u00e0 b\u1ea1n nh\u1eadp c\u00f4ng th\u1ee9c ph\u1ea3i b\u1eb1ng ho\u1eb7c l\u1edbn h\u01a1n s\u1ed1 k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p t\u1ed1i \u0111a c\u00f3 th\u1ec3.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_Vlookup_theo_hang_va_cot_tra_cuu_hai_chieu\"><\/span><b>C\u00e1ch Vlookup theo h\u00e0ng v\u00e0 c\u1ed9t (tra c\u1ee9u hai chi\u1ec1u)<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">T\u00ecm ki\u1ebfm 2 chi\u1ec1u trong Excel (c\u00f2n g\u1ecdi l\u00e0 tra c\u1ee9u ma tr\u1eadn ho\u1eb7c tra c\u1ee9u hai chi\u1ec1u) t\u00ecm ki\u1ebfm tr\u00ean c\u1ea3 h\u00e0ng v\u00e0 c\u1ed9t . N\u00f3i c\u00e1ch kh\u00e1c, b\u1ea1n t\u00ecm ki\u1ebfm m\u1ed9t gi\u00e1 tr\u1ecb t\u1ea1i giao \u0111i\u1ec3m c\u1ee7a m\u1ed9t h\u00e0ng v\u00e0 c\u1ed9t nh\u1ea5t \u0111\u1ecbnh.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ed1i v\u1edbi v\u00ed d\u1ee5 n\u00e0y, ch\u00fang ta s\u1ebd l\u1ea5y b\u1ea3ng sau \u0111\u00e2y v\u1edbi doanh s\u1ed1 h\u00e0ng th\u00e1ng v\u00e0 t\u00ednh ra c\u00f4ng th\u1ee9c VLOOKUP \u0111\u1ec3 truy xu\u1ea5t s\u1ed1 li\u1ec7u b\u00e1n h\u00e0ng cho m\u1ed9t m\u1eb7t h\u00e0ng c\u1ee5 th\u1ec3 trong m\u1ed9t th\u00e1ng nh\u1ea5t \u0111\u1ecbnh.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u1edbi t\u00ean m\u1ee5c trong A2: A9, t\u00ean th\u00e1ng trong B1: F1, m\u1ee5c m\u1ee5c ti\u00eau trong I1 v\u00e0 th\u00e1ng m\u1ee5c ti\u00eau trong I2, c\u00f4ng th\u1ee9c nh\u01b0 sau:<\/span><\/p>\n<p><strong>=VLOOKUP(I1, A2:F9, MATCH(I2, A1:F1, 0), FALSE)<\/strong><\/p>\n<figure style=\"width: 628px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"C\u00e1ch Vlookup theo h\u00e0ng v\u00e0 c\u1ed9t (tra c\u1ee9u hai chi\u1ec1u)\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-rows-columns.png\" alt=\"Vlookup in rows and columns\" width=\"628\" height=\"237\"><figcaption class=\"wp-caption-text\">C\u00e1ch Vlookup theo h\u00e0ng v\u00e0 c\u1ed9t (tra c\u1ee9u hai chi\u1ec1u)<\/figcaption><\/figure>\n<p><b>C\u00f4ng th\u1ee9c n\u00e0y ho\u1ea1t \u0111\u1ed9ng nh\u01b0 th\u1ebf n\u00e0o<\/b><\/p>\n<p><span style=\"font-weight: 400\">C\u1ed1t l\u00f5i c\u1ee7a c\u00f4ng th\u1ee9c l\u00e0 h\u00e0m VLOOKUP ti\u00eau chu\u1ea9n t\u00ecm ki\u1ebfm k\u1ebft qu\u1ea3 kh\u1edbp ch\u00ednh x\u00e1c v\u1edbi gi\u00e1 tr\u1ecb tra c\u1ee9u trong I1. Nh\u01b0ng v\u00ec kh\u00f4ng bi\u1ebft ch\u00ednh x\u00e1c doanh s\u1ed1 b\u00e1n h\u00e0ng cho m\u1ed9t th\u00e1ng c\u1ee5 th\u1ec3 n\u1eb1m trong c\u1ed9t n\u00e0o, n\u00ean kh\u00f4ng th\u1ec3 cung c\u1ea5p s\u1ed1 c\u1ed9t tr\u1ef1c ti\u1ebfp cho \u0111\u1ed1i s\u1ed1 <\/span><i><span style=\"font-weight: 400\">col_index_num<\/span><\/i><span style=\"font-weight: 400\">. \u0110\u1ec3 t\u00ecm c\u1ed9t \u0111\u00f3 s\u1eed d\u1ee5ng h\u00e0m MATCH sau :<\/span><\/p>\n<p><strong>MATCH(I2, A1:F1, 0)<\/strong><\/p>\n<p><i><span style=\"font-weight: 400\">\u00dd ngh\u0129a c\u00f4ng th\u1ee9c:<\/span><\/i><span style=\"font-weight: 400\"> tra c\u1ee9u gi\u00e1 tr\u1ecb I2 trong A1: F1 v\u00e0 tr\u1ea3 v\u1ec1 v\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a n\u00f3 trong m\u1ea3ng. B\u1eb1ng c\u00e1ch cung c\u1ea5p 0 cho \u0111\u1ed1i s\u1ed1 th\u1ee9 3, b\u1ea1n h\u01b0\u1edbng d\u1eabn MATCH t\u00ecm gi\u00e1 tr\u1ecb ch\u00ednh x\u00e1c b\u1eb1ng gi\u00e1 tr\u1ecb tra c\u1ee9u (gi\u1ed1ng nh\u01b0 s\u1eed d\u1ee5ng FALSE cho \u0111\u1ed1i s\u1ed1 range_lookup c\u1ee7a h\u00e0m VLOOKUP).<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ec Mar \u1edf c\u1ed9t th\u1ee9 4 trong m\u1ea3ng tra c\u1ee9u, n\u00ean h\u00e0m MATCH tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb 4, \u0111i tr\u1ef1c ti\u1ebfp \u0111\u1ebfn \u0111\u1ed1i s\u1ed1 <\/span><i><span style=\"font-weight: 400\">col_index_num<\/span><\/i><span style=\"font-weight: 400\"> c\u1ee7a h\u00e0m VLOOKUP:<\/span><\/p>\n<p><strong>VLOOKUP(I1, A2:F9, 4, FALSE)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">M\u1eb7c d\u00f9 t\u00ean th\u00e1ng b\u1eaft \u0111\u1ea7u \u1edf c\u1ed9t B nh\u01b0ng v\u00ed d\u1ee5 tr\u00ean s\u1eed d\u1ee5ng A1: I1 cho m\u1ea3ng tra c\u1ee9u. \u0110i\u1ec1u n\u00e0y \u0111\u01b0\u1ee3c th\u1ef1c hi\u1ec7n \u0111\u1ec3 s\u1ed1 \u0111\u01b0\u1ee3c MATCH tr\u1ea3 v\u1ec1 t\u01b0\u01a1ng \u1ee9ng v\u1edbi v\u1ecb tr\u00ed c\u1ee7a c\u1ed9t trong table_array c\u1ee7a VLOOKUP.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_thuc_hien_nhieu_Vlookup_trong_Excel_Vlookup_long_nhau\"><\/span><b>C\u00e1ch th\u1ef1c hi\u1ec7n nhi\u1ec1u Vlookup trong Excel (Vlookup l\u1ed3ng nhau)<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">\u0110\u00f4i khi Main Table v\u00e0 Lookup Table c\u1ee7a b\u1ea1n kh\u00f4ng c\u00f3 m\u1ed9t c\u1ed9t chung n\u00e0o, \u0111i\u1ec1u n\u00e0y kh\u00f4ng cho b\u1ea1n th\u1ef1c hi\u1ec7n m\u1ed9t Vlookup b\u00ecnh th\u01b0\u1eddng. Tuy nhi\u00ean, c\u00f3 m\u1ed9t b\u1ea3ng kh\u00e1c, kh\u00f4ng ch\u1ee9a th\u00f4ng tin b\u1ea1n \u0111ang t\u00ecm ki\u1ebfm nh\u01b0ng c\u00f3 m\u1ed9t c\u1ed9t chung v\u1edbi Main Table c\u1ee7a b\u1ea1n v\u00e0 m\u1ed9t chung v\u1edbi Lookup Table c\u1ee7a b\u1ea1n.<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00ecnh \u1ea3nh minh h\u1ecda d\u01b0\u1edbi \u0111\u00e2y:<\/span><\/p>\n<figure style=\"width: 394px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"C\u00e1ch th\u1ef1c hi\u1ec7n nhi\u1ec1u Vlookup trong Excel (Vlookup l\u1ed3ng nhau)\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/nested-vlookup.png\" alt=\"Nested Vlookup in Excel\" width=\"394\" height=\"461\"><figcaption class=\"wp-caption-text\">C\u00e1ch th\u1ef1c hi\u1ec7n nhi\u1ec1u Vlookup trong Excel<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">M\u1ee5c \u0111\u00edch l\u00e0 sao ch\u00e9p gi\u00e1 v\u00e0o b\u1ea3ng ch\u00ednh d\u1ef1a tr\u00ean ID m\u1eb7t h\u00e0ng . V\u1ea5n \u0111\u1ec1 l\u00e0 b\u1ea3ng ch\u1ee9a gi\u00e1 kh\u00f4ng c\u00f3 ID m\u1eb7t h\u00e0ng , c\u00f3 ngh\u0129a l\u00e0 ch\u00fang ta s\u1ebd ph\u1ea3i th\u1ef1c hi\u1ec7n hai Vlookup trong m\u1ed9t c\u00f4ng th\u1ee9c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 thu\u1eadn ti\u1ec7n, tr\u01b0\u1edbc ti\u00ean h\u00e3y t\u1ea1o m\u1ed9t v\u00e0i ph\u1ea1m vi \u0111\u01b0\u1ee3c \u0111\u1eb7t t\u00ean:<\/span><\/p>\n<p><span style=\"font-weight: 400\">Lookup Table 1 c\u00f3 t\u00ean l\u00e0 S\u1ea3n ph\u1ea9m (D3: E3).<\/span><\/p>\n<p><span style=\"font-weight: 400\">Lookup Table 2 c\u00f3 t\u00ean l\u00e0 Gi\u00e1 ( G3: H3 ).<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00e1c b\u1ea3ng c\u00f3 th\u1ec3 n\u1eb1m trong c\u00e1c trang t\u00ednh gi\u1ed1ng nhau ho\u1eb7c kh\u00e1c nhau.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00e0 b\u00e2y gi\u1edd, ch\u00fang ta s\u1ebd th\u1ef1c hi\u1ec7n c\u00e1i g\u1ecdi l\u00e0 Vlookup k\u00e9p , hay c\u00f2n g\u1ecdi l\u00e0 Vlookup l\u1ed3ng nhau .<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ea7u ti\u00ean, t\u1ea1o c\u00f4ng th\u1ee9c VLOOKUP \u0111\u1ec3 t\u00ecm t\u00ean s\u1ea3n ph\u1ea9m trong Lookup Table 1 (c\u00f3 t\u00ean Product ) d\u1ef1a tr\u00ean ID ITEM (A3):<\/span><\/p>\n<p><strong>=VLOOKUP(A3, Products, 2, FALSE)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Ti\u1ebfp theo, \u0111\u1eb7t c\u00f4ng th\u1ee9c tr\u00ean v\u00e0o \u0111\u1ed1i s\u1ed1 lookup_value c\u1ee7a m\u1ed9t h\u00e0m VLOOKUP kh\u00e1c \u0111\u1ec3 k\u00e9o gi\u00e1 t\u1eeb Lookup Table 2 (c\u00f3 t\u00ean l\u00e0 Price ) d\u1ef1a tr\u00ean t\u00ean s\u1ea3n ph\u1ea9m \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1 b\u1edfi h\u00e0m VLOOKUP l\u1ed3ng nhau:<\/span><\/p>\n<p><strong>=VLOOKUP(VLOOKUP(A3, Products, 2, FALSE), Prices, 2, FALSE)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">\u1ea2nh ch\u1ee5p m\u00e0n h\u00ecnh b\u00ean d\u01b0\u1edbi cho th\u1ea5y c\u00f4ng th\u1ee9c Vlookup l\u1ed3ng nhau c\u1ee7a ch\u00fang t\u00f4i \u0111ang ho\u1ea1t \u0111\u1ed9ng:<\/span><\/p>\n<figure style=\"width: 562px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"C\u00e1ch d\u00f9ng Vlookup l\u1ed3ng nhau\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/multiple-vlookups.png\" alt=\"Multiple (nested) Vlookup in Excel\" width=\"562\" height=\"257\"><figcaption class=\"wp-caption-text\">C\u00e1ch d\u00f9ng Vlookup l\u1ed3ng nhau<\/figcaption><\/figure>\n<h2><span class=\"ez-toc-section\" id=\"Su_dung_Vlookup_tu_nhieu_trang_tinh_khac_nhau\"><\/span><b>S\u1eed d\u1ee5ng Vlookup t\u1eeb nhi\u1ec1u trang t\u00ednh kh\u00e1c nhau<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Trong m\u1ed9t s\u1ed1 tr\u01b0\u1eddng h\u1ee3p, b\u1ea1n c\u00f3 th\u1ec3 c\u00f3 d\u1eef li\u1ec7u \u1edf c\u00f9ng m\u1ed9t \u0111\u1ecbnh d\u1ea1ng \u0111\u01b0\u1ee3c chia tr\u00ean nhi\u1ec1u b\u1ea3ng t\u00ednh v\u00e0 m\u1ed7i l\u1ea7n b\u1ea1n mu\u1ed1n k\u00e9o d\u1eef li\u1ec7u ph\u00f9 h\u1ee3p t\u1eeb m\u1ed9t trang t\u00ednh c\u1ee5 th\u1ec3 t\u00f9y thu\u1ed9c v\u00e0o gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c nh\u1eadp v\u00e0o m\u1ed9t \u00f4 n\u00e0o. M\u1ed9t v\u00ed d\u1ee5 s\u1ebd gi\u00fap ta d\u1ec5 hi\u1ec3u h\u01a1n. Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t v\u00e0i b\u00e1o c\u00e1o b\u00e1n h\u00e0ng khu v\u1ef1c \u1edf c\u00f9ng m\u1ed9t \u0111\u1ecbnh d\u1ea1ng v\u00e0 b\u1ea1n \u0111ang t\u00ecm ki\u1ebfm s\u1ed1 li\u1ec7u b\u00e1n h\u00e0ng cho m\u1ed9t s\u1ea3n ph\u1ea9m c\u1ee5 th\u1ec3 \u1edf m\u1ed9t s\u1ed1 khu v\u1ef1c nh\u1ea5t \u0111\u1ecbnh:<\/span><\/p>\n<p><span style=\"font-weight: 400\">Gi\u1ed1ng nh\u01b0 trong v\u00ed d\u1ee5 tr\u01b0\u1edbc, b\u1eaft \u0111\u1ea7u v\u1edbi vi\u1ec7c x\u00e1c \u0111\u1ecbnh m\u1ed9t v\u00e0i t\u00ean:<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ph\u1ea1m vi A2: B5 trong trang t\u00ednh CA \u0111\u01b0\u1ee3c \u0111\u1eb7t t\u00ean l\u00e0 CA_Sales .<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ph\u1ea1m vi A2: B5 trong trang t\u00ednh FL \u0111\u01b0\u1ee3c \u0111\u1eb7t t\u00ean l\u00e0 FL_Sales .<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ph\u1ea1m vi A2: B5 trong trang t\u00ednh KS \u0111\u01b0\u1ee3c \u0111\u1eb7t t\u00ean l\u00e0 KS_Sales .<\/span><\/p>\n<p><span style=\"font-weight: 400\">Nh\u01b0 b\u1ea1n c\u00f3 th\u1ec3 th\u1ea5y, t\u1ea5t c\u1ea3 c\u00e1c ph\u1ea1m vi \u0111\u01b0\u1ee3c \u0111\u1eb7t t\u00ean \u0111\u1ec1u c\u00f3 ph\u1ea7n chung ( Sales ) v\u00e0 ph\u1ea7n duy nh\u1ea5t ( CA , FL , KS ). H\u00e3y \u0111\u1ea3m b\u1ea3o \u0111\u1eb7t t\u00ean cho c\u00e1c ph\u1ea1m vi theo c\u00e1ch t\u01b0\u01a1ng t\u1ef1 v\u00ec n\u00f3 r\u1ea5t c\u1ea7n thi\u1ebft cho c\u00f4ng th\u1ee9c m\u00e0 ch\u00fang ta s\u1eafp x\u00e2y d\u1ef1ng.<\/span><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Cong_thuc_1_INDIRECT_VLOOKUP_de_keo_du_lieu_dong_tu_cac_trang_tinh_khac_nhau\"><\/span><b>C\u00f4ng th\u1ee9c 1. INDIRECT VLOOKUP \u0111\u1ec3 k\u00e9o d\u1eef li\u1ec7u \u0111\u1ed9ng t\u1eeb c\u00e1c trang t\u00ednh kh\u00e1c nhau<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">N\u1ebfu nhi\u1ec7m v\u1ee5 c\u1ee7a b\u1ea1n l\u00e0 truy xu\u1ea5t d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u trang t\u00ednh, c\u00f4ng th\u1ee9c VLOOKUP INDIRECT l\u00e0 gi\u1ea3i ph\u00e1p t\u1ed1t nh\u1ea5t \u2013 nh\u1ecf g\u1ecdn v\u00e0 d\u1ec5 hi\u1ec3u.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u0110\u1ed1i v\u00ed d\u1ee5 n\u00e0y:<\/span><\/p>\n<p><span style=\"font-weight: 400\">Nh\u1eadp c\u00e1c s\u1ea3n ph\u1ea9m quan t\u00e2m trong A2 v\u00e0 A3. \u0110\u00f3 l\u00e0 nh\u1eefng gi\u00e1 tr\u1ecb tra c\u1ee9u c\u1ee7a ch\u00fang t\u00f4i.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Nh\u1eadp c\u00e1c ph\u1ea7n duy nh\u1ea5t c\u1ee7a c\u00e1c d\u1ea3i \u00f4 \u0111\u01b0\u1ee3c \u0111\u1eb7t t\u00ean trong B1, C1 v\u00e0 D1.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ti\u1ebfp theo n\u1ed1i \u00f4 ch\u1ee9a ph\u1ea7n duy nh\u1ea5t (B1) v\u1edbi ph\u1ea7n chung (\u201c_Sales\u201d) v\u00e0 c\u1ea5p chu\u1ed7i k\u1ebft qu\u1ea3 th\u00e0nh INDIRECT:<\/span><\/p>\n<p><strong>INDIRECT(B$1&amp;\u201d_Sales\u201d)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">H\u00e0m INDIRECT bi\u1ebfn \u0111\u1ed5i chu\u1ed7i th\u00e0nh t\u00ean m\u00e0 Excel c\u00f3 th\u1ec3 hi\u1ec3u \u0111\u01b0\u1ee3c v\u00e0 b\u1ea1n \u0111\u1eb7t n\u00f3 v\u00e0o \u0111\u1ed1i s\u1ed1 table_array c\u1ee7a h\u00e0m VLOOKUP:<\/span><\/p>\n<p><strong>=VLOOKUP($A2, INDIRECT(B$1&amp;\u201d_Sales\u201d), 2, FALSE)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c tr\u00ean chuy\u1ec3n \u0111\u1ebfn B2, sau \u0111\u00f3 b\u1ea1n sao ch\u00e9p n\u00f3 xu\u1ed1ng b\u00ean ph\u1ea3i.<\/span><\/p>\n<p><span style=\"font-weight: 400\">L\u01b0u \u00fd: trong gi\u00e1 tr\u1ecb tra c\u1ee9u ($ A2), ch\u00fang t\u00f4i \u0111\u00e3 kh\u00f3a t\u1ecda \u0111\u1ed9 c\u1ed9t v\u1edbi tham chi\u1ebfu \u00f4 tuy\u1ec7t \u0111\u1ed1i \u0111\u1ec3 c\u1ed9t v\u1eabn c\u1ed1 \u0111\u1ecbnh khi c\u00f4ng th\u1ee9c \u0111\u01b0\u1ee3c sao ch\u00e9p sang b\u00ean ph\u1ea3i n\u00ean \u0111\u00e3 kh\u00f3a h\u00e0ng. Trong tham chi\u1ebfu B$1, v\u00ec mu\u1ed1n t\u1ecda \u0111\u1ed9 c\u1ed9t thay \u0111\u1ed5i v\u00e0 cung c\u1ea5p m\u1ed9t ph\u1ea7n t\u00ean th\u00edch h\u1ee3p th\u00e0nh INDIRECT t\u00f9y thu\u1ed9c v\u00e0o c\u1ed9t m\u00e0 c\u00f4ng th\u1ee9c \u0111\u01b0\u1ee3c sao ch\u00e9p v\u00e0o \u0111\u00f3:<\/span><\/p>\n<figure style=\"width: 557px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"INDIRECT VLOOKUP \u0111\u1ec3 k\u00e9o d\u1eef li\u1ec7u \u0111\u1ed9ng t\u1eeb c\u00e1c trang t\u00ednh kh\u00e1c nhau\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-indirect.png\" alt=\"VLOOKUP and INDIRECT to dynamically pull data from multiple sheets\" width=\"557\" height=\"308\"><figcaption class=\"wp-caption-text\">INDIRECT VLOOKUP \u0111\u1ec3 k\u00e9o d\u1eef li\u1ec7u \u0111\u1ed9ng t\u1eeb c\u00e1c trang t\u00ednh kh\u00e1c nhau<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea3ng ch\u00ednh c\u1ee7a b\u1ea1n \u0111\u01b0\u1ee3c t\u1ed5 ch\u1ee9c kh\u00e1c nhau, c\u00e1c gi\u00e1 tr\u1ecb tra c\u1ee9u trong m\u1ed9t h\u00e0ng v\u00e0 c\u00e1c ph\u1ea7n duy nh\u1ea5t c\u1ee7a t\u00ean ph\u1ea1m vi trong m\u1ed9t c\u1ed9t, th\u00ec b\u1ea1n n\u00ean kh\u00f3a t\u1ecda \u0111\u1ed9 h\u00e0ng trong gi\u00e1 tr\u1ecb tra c\u1ee9u (B $ 1) v\u00e0 t\u1ecda \u0111\u1ed9 c\u1ed9t trong ph\u1ea7n t\u00ean ($ A2):<\/span><\/p>\n<p><strong>=VLOOKUP(B$1, INDIRECT($A2&amp;\u201d_Sales\u201d), 2, FALSE)<\/strong><\/p>\n<figure style=\"width: 335px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"K\u1ebft qu\u1ea3 INDIRECT VLOOKUP \u0111\u1ec3 k\u00e9o d\u1eef li\u1ec7u \u0111\u1ed9ng t\u1eeb c\u00e1c trang t\u00ednh kh\u00e1c nhau\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/indirect-vlookup.png\" alt=\"INDIRECT VLOOKUP in Excel\" width=\"335\" height=\"139\"><figcaption class=\"wp-caption-text\">K\u1ebft qu\u1ea3 INDIRECT VLOOKUP \u0111\u1ec3 k\u00e9o d\u1eef li\u1ec7u \u0111\u1ed9ng<\/figcaption><\/figure>\n<h3><span class=\"ez-toc-section\" id=\"Cong_thuc_2_VLOOKUP_va_IF_long_nhau_de_tra_cuu_nhieu_trang_tinh\"><\/span><b>C\u00f4ng th\u1ee9c 2. VLOOKUP v\u00e0 IF l\u1ed3ng nhau \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u trang t\u00ednh<\/b><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><span style=\"font-weight: 400\">Trong tr\u01b0\u1eddng h\u1ee3p b\u1ea1n ch\u1ec9 c\u00f3 hai ho\u1eb7c ba trang t\u00ednh tra c\u1ee9u, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c VLOOKUP kh\u00e1 \u0111\u01a1n gi\u1ea3n v\u1edbi c\u00e1c h\u00e0m IF l\u1ed3ng nhau \u0111\u1ec3 ch\u1ecdn trang t\u00ednh ch\u00ednh x\u00e1c d\u1ef1a tr\u00ean gi\u00e1 tr\u1ecb kh\u00f3a trong m\u1ed9t \u00f4 c\u1ee5 th\u1ec3:<\/span><\/p>\n<figure style=\"width: 631px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" title=\"VLOOKUP v\u00e0 IF l\u1ed3ng nhau \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u trang t\u00ednh\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-if-multiple-sheets.png\" alt=\"VLOOKUP and nested IFs to return data from multiple sheets\" width=\"631\" height=\"118\"><figcaption class=\"wp-caption-text\">VLOOKUP v\u00e0 IF l\u1ed3ng nhau \u0111\u1ec3 tra c\u1ee9u nhi\u1ec1u trang t\u00ednh<\/figcaption><\/figure>\n<p><strong>=VLOOKUP($A2, IF(B$1=\u201dCA\u201d, CA_Sales, IF(B$1=\u201dFL\u201d, FL_Sales, IF(B$1=\u201dKS\u201d, KS_Sales,\u201d\u201d))), 2, FALSE)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Trong \u0111\u00f3 $ A2 l\u00e0 gi\u00e1 tr\u1ecb tra c\u1ee9u (t\u00ean m\u1eb7t h\u00e0ng) v\u00e0 B $ 1 l\u00e0 gi\u00e1 tr\u1ecb kh\u00f3a (tr\u1ea1ng th\u00e1i):<\/span><\/p>\n<p><span style=\"font-weight: 400\">Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, b\u1ea1n kh\u00f4ng nh\u1ea5t thi\u1ebft ph\u1ea3i x\u00e1c \u0111\u1ecbnh t\u00ean v\u00e0 c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng tham chi\u1ebfu b\u00ean ngo\u00e0i \u0111\u1ec3 tham chi\u1ebfu \u0111\u1ebfn m\u1ed9t trang t\u00ednh ho\u1eb7c s\u1ed5 l\u00e0m vi\u1ec7c kh\u00e1c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Tr\u00ean \u0111\u00e2y l\u00e0<strong> c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m VLOOKUP<\/strong> trong Excel. Ngo\u00e0i ra c\u00f2n c\u00f3 r\u1ea5t nhi\u1ec1u <a href=\"http:\/\/blog.ezworkapp.com\/cac-ham-trong-excel-va-cac-vi-du-minh-hoa.html\"><strong>h\u00e0m trong Excel<\/strong><\/a> kh\u00e1c h\u1ed7 tr\u1ee3 t\u1ed1i \u01b0u cho c\u00f4ng vi\u1ec7c. Ch\u00fang t\u00f4i<\/span><span style=\"font-weight: 400\"> hy v\u1ecdng c\u00f3 th\u1ec3 h\u1ed7 tr\u1ee3 b\u1ea1n x\u1eed l\u00fd t\u1ed1t Excel v\u00e0 h\u1eb9n g\u1eb7p b\u1ea1n nh\u1eefng b\u00e0i h\u01b0\u1edbng d\u1eabn ti\u1ebfp theo.<\/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-199.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":2578,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-2577","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\/2577","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=2577"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/2577\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/2578"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=2577"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=2577"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=2577"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}