{"id":3158,"date":"2024-12-03T01:16:53","date_gmt":"2024-12-03T01:16:53","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/03\/cach-su-dung-ham-vlookup-de-tham-chieu-nhieu-ket-qua-cung-luc-tren-excel\/"},"modified":"2024-12-03T01:16:53","modified_gmt":"2024-12-03T01:16:53","slug":"cach-su-dung-ham-vlookup-de-tham-chieu-nhieu-ket-qua-cung-luc-tren-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/03\/cach-su-dung-ham-vlookup-de-tham-chieu-nhieu-ket-qua-cung-luc-tren-excel\/","title":{"rendered":"C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m VLookup \u0111\u1ec3 tham chi\u1ebfu nhi\u1ec1u k\u1ebft qu\u1ea3 c\u00f9ng l\u00fac tr\u00ean Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p style=\"text-align: justify\"><em>H\u00f4m nay, <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong><\/a> xin gi\u1edbi thi\u1ec7u v\u1edbi b\u1ea1n c\u00e1ch s\u1eed d\u1ee5ng <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> \u0111\u1ec3 tham chi\u1ebfu nhi\u1ec1u k\u1ebft qu\u1ea3 c\u00f9ng l\u00fac tr\u00ean Excel m\u1ed9t c\u00e1ch d\u1ec5 d\u00e0ng nh\u1ea5t. D\u01b0\u1edbi \u0111\u00e2y s\u1ebd l\u00e0 h\u01b0\u1edbng d\u1eabn ph\u01b0\u01a1ng ph\u00e1p tham\u00a0chi\u1ebfu\u00a0ch\u1ec9 b\u1eb1ng 1 c\u00f4ng th\u1ee9c duy nh\u1ea5t.<\/em><\/p>\n<p style=\"text-align: justify\">Th\u00f4ng th\u01b0\u1eddng h\u00e0m Vlookup ch\u1ec9 cho ra 1 k\u1ebft qu\u1ea3 tra c\u1ee9u \u0111\u01a1n l\u1ebb. \u0110\u1ec3 c\u00f3 th\u1ec3 nh\u1eadn \u0111\u01b0\u1ee3c nhi\u1ec1u k\u1ebft qu\u1ea3 tham chi\u1ebfu h\u01a1n, b\u1ea1n c\u1ea7n ph\u1ea3i s\u1eed d\u1ee5ng chu\u1ed7i c\u00e1c \u0111i\u1ec1u ki\u1ec7n kh\u00e1c nhau \u0111\u1ec3 k\u1ebft h\u1ee3p chung v\u00e0o h\u00e0m Vlookup, c\u1ee5 th\u1ec3 ta s\u1ebd s\u1eed d\u1ee5ng c\u00e1c h\u00e0m sau:<\/p>\n<ul style=\"text-align: justify\">\n<li><a href=\"http:\/\/blog.ezworkapp.com\/ham-if-va-cach-su-dung-ham-if-trong-excel.html\">H\u00e0m IF<\/a>: L\u00e0 h\u00e0m \u0111i\u1ec1u ki\u1ec7n c\u00f3 ch\u1ee9c n\u0103ng tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 A n\u1ebfu nh\u01b0 \u0111\u00e1p \u1ee9ng \u0111\u01b0\u1ee3c m\u1ed9t \u0111i\u1ec1u ki\u1ec7n c\u1ee5 th\u1ec3 n\u00e0o \u0111\u00f3, c\u00f2n n\u1ebfu kh\u00f4ng \u0111\u00e1p \u1ee9ng \u0111\u01b0\u1ee3c \u0111i\u1ec1u ki\u1ec7n \u1ea5y th\u00ec thay v\u00e0o \u0111\u00f3 n\u00f3 s\u1ebd tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 B.<\/li>\n<li><a href=\"http:\/\/blog.ezworkapp.com\/ham-large-va-ham-small-trong-excel.html\">H\u00e0m SMALL<\/a>: Tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t n\u1eb1m \u1edf v\u1ecb tr\u00ed th\u1ee9 \u201ck\u201d trong chu\u1ed7i<\/li>\n<li>H\u00e0m INDEX: Tr\u1ea3 v\u1ec1 y\u1ebfu t\u1ed1 chu\u1ed7i d\u1ef1a tr\u00ean s\u1ed1 l\u01b0\u1ee3ng c\u1ed9t v\u00e0 h\u00e0ng b\u1ea1n s\u1eed d\u1ee5ng<\/li>\n<li>H\u00e0m ROW: Tr\u1ea3 v\u1ec1 s\u1ed1 th\u1ee9 t\u1ef1 theo h\u00e0ng<\/li>\n<li>H\u00e0m COLUMN: Tr\u1ea3 v\u1ec1 s\u1ed1 th\u1ee9 t\u1ef1 theo c\u1ed9t<\/li>\n<li>H\u00e0m IFERROR: C\u1ed1 \u0111\u1ecbnh v\u1ecb tr\u00ed sai s\u1ed1.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">D\u01b0\u1edbi \u0111\u00e2y l\u00e0 m\u1ed9t s\u1ed1 c\u00e1ch s\u1eed d\u1ee5ng kh\u00e1c nhau c\u1ee7a c\u00f4ng th\u1ee9c n\u00e0y:<\/p>\n<p style=\"text-align: justify\"><em><u>C\u00f4ng th\u1ee9c 1:<\/u> D\u00f9ng Vlookup \u0111\u1ec3 tham chi\u1ebfu nhi\u1ec1u k\u1ebft qu\u1ea3 trong c\u00f9ng m\u1ed9t c\u1ed9t<\/em><\/p>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5 minh h\u1ecda cho th\u1ea5y c\u1ed9t A bao g\u1ed3m t\u00ean c\u00e1c nh\u00e0 ph\u00e2n ph\u1ed1i v\u00e0 c\u1ed9t B th\u1ec3 hi\u1ec7n lo\u1ea1i m\u1eb7t h\u00e0ng m\u00e0 t\u1eebng nh\u00e0 ph\u00e2n ph\u1ed1i cung c\u1ea5p. L\u01b0u \u00fd l\u00e0 \u1edf c\u1ed9t A ta \u0111\u1ec3 \u00fd th\u1ea5y c\u00f3 m\u1ed9t s\u1ed1 t\u00ean nh\u00e0 ph\u00e2n ph\u1ed1i b\u1ecb l\u1eb7p \u0111i l\u1eb7p l\u1ea1i nhi\u1ec1u h\u01a1n 1 l\u1ea7n. Nhi\u1ec7m v\u1ee5 c\u1ee7a b\u1ea1n l\u00e0 ph\u1ea3i th\u1ed1ng k\u1ebf xem m\u1ed7i nh\u00e0 ph\u00e2n ph\u1ed1i t\u1ed5ng c\u1ed9ng cung c\u1ea5p nh\u1eefng m\u1eb7t h\u00e0ng n\u00e0o. D\u01b0\u1edbi \u0111\u00e2y l\u00e0 h\u01b0\u1edbng d\u1eabn chi ti\u1ebft c\u00e1ch l\u00e0m:<\/p>\n<p style=\"text-align: justify\">1. Ph\u00e2n lo\u1ea1i t\u00ean t\u1eebng nh\u00e0 ph\u00e2n ph\u1ed1i ri\u00eang ra m\u1ed9t h\u00e0ng kh\u00e1c, c\u00f3 th\u1ec3 c\u00f9ng n\u1eb1m chung trong trang t\u00ednh hi\u1ec7n t\u1ea1i. \u1ea2nh d\u01b0\u1edbi \u0111\u00e2y minh h\u1ecda t\u00ean c\u00e1c nh\u00e0 ph\u00e2n ph\u1ed1i \u0111\u01b0\u1ee3c ph\u00e2n lo\u1ea1i trong h\u00e0ng D2:G2<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-9316\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/source-data-2.png\" width=\"600\" height=\"257\" title=\"source-data-2\"><\/p>\n<p style=\"text-align: justify\">2. Ngay \u1edf \u00f4 d\u01b0\u1edbi c\u00e1i t\u00ean nh\u00e0 ph\u00e2n ph\u1ed1i \u0111\u1ea7u ti\u00ean, l\u1ef1a ch\u1ecdn s\u1ed1 \u00f4 tr\u1ed1ng v\u1eeba \u0111\u1ee7 sao cho n\u00f3 l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng s\u1ed1 l\u01b0\u1ee3ng m\u1eb7t h\u00e0ng t\u1ed1i \u0111a m\u00e0 m\u1ed9t nh\u00e0 ph\u00e2n ph\u1ed1i c\u00f3 th\u1ec3 cung c\u1ea5p, sau \u0111\u00f3 \u0111i\u1ec1n m\u1ed9t trong c\u00e1c c\u00f4ng th\u1ee9c d\u01b0\u1edbi \u0111\u00e2y v\u00e0o ch\u1ed7 tr\u1ed1ng. B\u1ea5m t\u1ed5 h\u1ee3p ph\u00edm Ctrl+Shift+Enter \u0111\u1ec3 ho\u00e0n t\u1ea5t (trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, b\u1ea1n ch\u1ec9 c\u00f3 th\u1ec3 ch\u1ec9nh s\u1eeda n\u1ed9i dung c\u00f4ng th\u1ee9c cho to\u00e0n b\u1ed9 c\u00e1c \u00f4 \u0111\u01b0\u1ee3c l\u1ef1a ch\u1ecdn m\u00e0 th\u00f4i). Ho\u1eb7c b\u1ea1n c\u00f3 th\u1ec3 \u0111i\u1ec1n c\u00f4ng th\u1ee9c v\u00e0o \u00f4 tr\u1ed1ng \u0111\u1ea7u ti\u00ean, b\u1ea5m t\u1ed5 h\u1ee3p ph\u00edm Ctrl+Shift+Enter v\u00e0 sao ch\u00e9p c\u00f4ng th\u1ee9c \u0111\u00f3 cho c\u00e1c \u00f4 tr\u1ed1ng ph\u00eda d\u01b0\u1edbi (khi \u0111\u00f3 b\u1ea1n v\u1eabn c\u00f3 th\u1ec3 ch\u1ec9nh s\u1eeda c\u00f4ng th\u1ee9c cho t\u1eebng \u00f4 m\u1ed9t)<\/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-240.gif\" title=\"ex101_92019-nho-240\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p style=\"text-align: justify\"><strong>=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,\u201d\u201d), ROW()-2)),\u201d\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\">Ho\u1eb7c<\/p>\n<p style=\"text-align: justify\"><strong>=IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1,\u201d\u201d), ROW()-2)),\u201d\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c \u0111\u1ea7u ti\u00ean c\u00f3 th\u1ec3 tr\u00f4ng g\u1ecdn nh\u1eb9 h\u01a1n nh\u01b0ng c\u00f4ng th\u1ee9c th\u1ee9 hai l\u1ea1i \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng ph\u1ed5 bi\u1ebfn h\u01a1n do \u00edt ph\u1ea3i \u0111i\u1ec1u ch\u1ec9nh (c\u1ea5u tr\u00fac c\u00fa ph\u00e1p c\u0169ng nh\u01b0 c\u00e1ch th\u1ee9c ho\u1ea1t \u0111\u1ed9ng c\u1ee7a n\u00f3 s\u1ebd \u0111\u01b0\u1ee3c gi\u1ea3i th\u00edch c\u1ee5 th\u1ec3 h\u01a1n \u1edf ph\u1ea7n sau)<\/p>\n<ol style=\"text-align: justify\" start=\"3\">\n<li>Sao ch\u00e9p c\u00f4ng th\u1ee9c n\u00e0y t\u01b0\u01a1ng t\u1ef1 cho c\u00e1c c\u1ed9t kh\u00e1c. \u0110\u1ec3 l\u00e0m \u0111\u01b0\u1ee3c, b\u1ea1n ch\u1ec9 c\u1ea7n b\u00f4i \u0111en ph\u1ea7n c\u1ed9t v\u1eeba \u0111i\u1ec1n c\u00f4ng th\u1ee9c v\u00e0o v\u00e0 click ph\u00edm \u0111i\u1ec1u h\u01b0\u1edbng (l\u00e0 \u00f4 h\u00ecnh vu\u00f4ng nh\u1ecf n\u1eb1m \u1edf g\u00f3c d\u01b0\u1edbi b\u00ean ph\u1ea3i c\u1ee7a ph\u1ea7n \u0111\u01b0\u1ee3c b\u00f4i \u0111en) r\u1ed3i k\u00e9o sang ph\u1ea3i l\u00e0 xong.<\/li>\n<\/ol>\n<p style=\"text-align: justify\">K\u1ebft qu\u1ea3 ta s\u1ebd c\u00f3 h\u00ecnh nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-9317 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-multiple-values-excel-1.png\" width=\"600\" height=\"314\" title=\"vlookup-multiple-values-excel-1\"><\/p>\n<blockquote>\n<p style=\"text-align: justify\">\n<\/blockquote>\n<p style=\"text-align: justify\"><em>C\u00e1ch th\u1ee9c ho\u1ea1t \u0111\u1ed9ng:<\/em><\/p>\n<ul style=\"text-align: justify\">\n<li>H\u00e0m IF<\/li>\n<\/ul>\n<p style=\"text-align: justify\">Tr\u1ecdng t\u00e2m c\u1ee7a c\u00f4ng th\u1ee9c ch\u00ednh l\u00e0 h\u00e0m IF, v\u1edbi ch\u1ee9c n\u0103ng t\u00ecm ki\u1ebfm v\u1ecb tr\u00ed c\u1ee7a t\u1eebng t\u1ed5 h\u1ee3p gi\u00e1 tr\u1ecb t\u01b0\u01a1ng t\u1ef1 c\u1ee7a \u00f4 c\u1ea7n t\u00ecm (D2) trong khu v\u1ef1c c\u1ea7n t\u00ecm l\u00e0 A3:A13: IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,\u201d\u201d)<\/p>\n<p style=\"text-align: justify\">N\u1ebfu t\u00ecm th\u1ea5y k\u1ebft qu\u1ea3 gi\u1ed1ng, h\u00e0m ROW s\u1ebd tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 theo c\u1ed9t c\u1ee7a \u00f4 tr\u1ed1ng \u0111\u1ea7u ti\u00ean trong ph\u1ea1m v\u1ecb tr\u1ea3 k\u1ebft qu\u1ea3 (B3:B13), sau \u0111\u00f3 b\u1ea1n ph\u1ea3i t\u00ednh to\u00e1n 1 con s\u1ed1 \u0111\u1ec3 tr\u1eeb b\u1edbt \u0111i sao cho kho\u1ea3ng c\u00e1ch gi\u1eefa khu v\u1ef1c c\u1ea7n t\u00ecm v\u00e0 khu v\u1ef1c tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u00e0 b\u1eb1ng 1. L\u00fd do l\u00e0 b\u1edfi ta \u0111ang \u0111i t\u00ecm v\u1ecb tr\u00ed t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a y\u1ebfu t\u1ed1 \u0111\u1ea7u ti\u00ean trong chu\u1ed7i. V\u1edbi v\u00ed d\u1ee5 minh h\u1ecda, ta c\u1ea7n tr\u1eeb b\u1edbt \u0111i 2 b\u1edfi v\u00ec ph\u1ea7n khu v\u1ef1c tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 n\u1eb1m b\u1eaft \u0111\u1ea7u \u1edf h\u00e0ng th\u1ee9 3. N\u1ebfu khu v\u1ef1c \u0111\u00f3 n\u1eb1m \u1edf h\u00e0ng th\u1ee9 2 th\u00ec b\u1ea1n ph\u1ea3i tr\u1eeb b\u1edbt \u0111i 1, v\u00e0 t\u01b0\u01a1ng t\u1ef1 nh\u01b0 v\u1eady.<\/p>\n<p style=\"text-align: justify\">Ho\u1eb7c c\u00e1ch kh\u00e1c b\u1ea1n c\u00f3 th\u1ec3 l\u00e0m l\u00e0 s\u1eed d\u1ee5ng c\u00e1ch di\u1ec5n \u0111\u1ea1t sau: ROW(lookup_column)-MIN(ROW(lookup_column))+1, khi \u0111\u00f3 k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 v\u1eabn gi\u1ed1ng t\u01b0\u01a1ng t\u1ef1 nh\u01b0ng kh\u00f4ng c\u1ea7n ph\u1ea3i \u0111i\u1ec1u ch\u1ec9nh g\u00ec th\u00eam m\u1eb7c cho v\u1ecb tr\u00ed c\u1ee7a khu v\u1ef1c tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u00e0 \u1edf \u0111\u00e2u. \u0110\u1ed1i v\u1edbi v\u00ed d\u1ee5 minh h\u1ecda, ta s\u1eed d\u1ee5ng c\u1ea5u tr\u00fac sau: ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1.<\/p>\n<p style=\"text-align: justify\">N\u1ebfu kh\u00f4ng t\u00ecm \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 gi\u1ed1ng, th\u00ec k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 s\u1ebd c\u00f3 d\u1ea1ng string r\u1ed7ng (\u201c\u201d)<\/p>\n<p style=\"text-align: justify\">\u0110\u1ebfn \u0111\u00e2y b\u1ea1n \u0111ang c\u00f3 m\u1ed9t t\u1eadp h\u1ee3p c\u00e1c s\u1ed1 (\u0111\u1ea1i di\u1ec7n cho s\u1ed1 l\u01b0\u1ee3ng k\u1ebft qu\u1ea3 gi\u1ed1ng) v\u00e0 c\u00e1c k\u00fd t\u1ef1 d\u1ea1ng string r\u1ed7ng (\u0111\u1ea1i di\u1ec7n cho s\u1ed1 l\u01b0\u1ee3ng k\u1ebft qu\u1ea3 kh\u00e1c). Gi\u1ea3 s\u1eed v\u00ed d\u1ee5 v\u1edbi \u00f4 D3, ta c\u00f3 t\u1eadp h\u1ee3p k\u1ebft qu\u1ea3 nh\u01b0 h\u00ecnh d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-9318\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/matching-values-array.png\" width=\"600\" height=\"31\" title=\"matching-values-array\"><\/p>\n<p style=\"text-align: justify\">Th\u1eed ki\u1ec3m tra \u0111\u1ed1i chi\u1ebfu v\u1edbi d\u1eef li\u1ec7u g\u1ed1c, b\u1ea1n c\u00f3 th\u1ec3 th\u1ea5y l\u00e0 gi\u00e1 tr\u1ecb \u201cAdam\u201d (gi\u00e1 tr\u1ecb tham chi\u1ebfu trong \u00f4 D2) xu\u1ea5t hi\u1ec7n \u1edf v\u1ecb tr\u00ed th\u1ee9 3, 8 v\u00e0 10 c\u1ee7a khu v\u1ef1c k\u1ebft qu\u1ea3 tham chi\u1ebfu (A3:A13)<\/p>\n<ul style=\"text-align: justify\">\n<li>H\u00e0m SMALL<\/li>\n<\/ul>\n<p style=\"text-align: justify\">Ti\u1ebfp theo, h\u00e0m SMALL(t\u1eadp h\u1ee3p, k) \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u1ec3 x\u00e1c \u0111\u1ecbnh v\u1ecb tr\u00ed c\u1ea7n tr\u1ea3 v\u1ec1 c\u1ee7a t\u1eebng k\u1ebft qu\u1ea3 tham chi\u1ebfu.<\/p>\n<p style=\"text-align: justify\">V\u1edbi th\u00f4ng s\u1ed1 \u201ct\u1eadp h\u1ee3p\u201d \u0111\u00e3 \u0111\u01b0\u1ee3c x\u00e1c \u0111\u1ecbnh t\u1eeb tr\u01b0\u1edbc, vi\u1ec7c c\u1ea7n quan t\u00e2m l\u00e0 x\u00e1c \u0111\u1ecbnh s\u1ed1 th\u1ee9 t\u1ef1 \u201ck\u201d , \u0111\u1ed3ng ngh\u0129a v\u1edbi gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t th\u1ee9 \u201ck\u201d \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1. \u0110\u1ec3 l\u00e0m \u0111\u01b0\u1ee3c \u0111i\u1ec1u \u0111\u00f3, b\u1ea1n c\u1ea7n ph\u1ea3i s\u1eed d\u1ee5ng m\u1ed9t b\u1ed9 \u0111\u1ebfm s\u1ed1 d\u01b0\u1edbi d\u1ea1ng h\u00e0m ROW()-n, trong \u0111\u00f3 n l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 h\u00e0ng c\u1ee7a \u00f4 t\u00ednh \u0111\u1ea7u ti\u00ean tr\u1eeb \u0111i 1. Trong v\u00ed d\u1ee5 n\u00e0y, ta \u0111i\u1ec1n c\u00f4ng th\u1ee9c v\u00e0o d\u1ea3i \u00f4 D3:D7, cho n\u00ean ROW()-2 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u00e0 1 \u0111\u1ed1i v\u1edbi \u00f4 D3 (h\u00e0ng th\u1ee9 3 tr\u1eeb \u0111i 2 \u0111\u01a1n v\u1ecb) v\u00e0 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u00e0 2 \u0111\u1ed1i v\u1edbi \u00f4 D4 (h\u00e0ng th\u1ee9 4 tr\u1eeb \u0111i 2 \u0111\u01a1n v\u1ecb), v\u00e0 l\u1eb7p l\u1ea1i t\u01b0\u01a1ng t\u1ef1 nh\u01b0 v\u1eady.<\/p>\n<p style=\"text-align: justify\">K\u1ebft qu\u1ea3 l\u00e0, h\u00e0m SMALL s\u1ebd \u0111\u1eb7t gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t c\u1ee7a t\u1eadp h\u1ee3p trong \u00f4 D3, gi\u00e1 tr\u1ecb nh\u1ecf th\u1ee9 nh\u00ec v\u00e0o \u00f4 D4, v\u00e0 c\u1ee9 ti\u1ebfp di\u1ec5n nh\u01b0 th\u1ebf. T\u1eeb \u0111\u00f3, c\u00f4ng th\u1ee9c d\u00e0i lo\u1eb1ng ngo\u1eb1ng ph\u1ee9c t\u1ea1p nh\u01b0 ban \u0111\u1ea7u \u0111\u01b0\u1ee3c gi\u1ea3n l\u01b0\u1ee3c \u0111i th\u00e0nh d\u1ea1ng \u0111\u01a1n gi\u1ea3n h\u01a1n nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-9319\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/value-to-return.png\" width=\"600\" height=\"64\" title=\"value-to-return\"><\/p>\n<p style=\"text-align: justify\">\u201cL\u1eddi khuy\u00ean: \u0110\u1ec3 nh\u00ecn th\u1ea5y gi\u00e1 tr\u1ecb t\u00ednh to\u00e1n \u1ea9n sau t\u1eebng ph\u1ea7n c\u1ee7a c\u00f4ng th\u1ee9c, ch\u1ec9 c\u1ea7n b\u00f4i \u0111en ph\u1ea7n \u0111\u00f3 l\u1ea1i v\u00e0 b\u1ea5m ph\u00edm F9\u201d<\/p>\n<ul style=\"text-align: justify\">\n<li>H\u00e0m INDEX<\/li>\n<\/ul>\n<p style=\"text-align: justify\">H\u00e0m n\u00e0y ho\u1ea1t \u0111\u1ed9ng kh\u00e1 \u0111\u01a1n gi\u1ea3n. H\u00e0m n\u00e0y \u0111\u01b0\u1ee3c d\u00f9ng \u0111\u1ec3 l\u1ea5y gi\u00e1 tr\u1ecb c\u1ee7a m\u1ed9t th\u00e0nh ph\u1ea7n trong t\u1eadp h\u1ee3p d\u1ef1a tr\u00ean s\u1ed1 th\u1ee9 t\u1ef1 theo h\u00e0ng c\u1ee7a n\u00f3<\/p>\n<ul style=\"text-align: justify\">\n<li>H\u00e0m IFERROR<\/li>\n<\/ul>\n<p style=\"text-align: justify\">Cu\u1ed1i c\u00f9ng, c\u00f4ng th\u1ee9c \u0111\u01b0\u1ee3c k\u1ebft th\u00fac b\u1eb1ng h\u00e0m IFERROR \u0111\u1ec3 x\u1eed l\u00fd m\u1ed9t s\u1ed1 l\u1ed7i c\u00f3 th\u1ec3 x\u1ea3y ra, \u0111i\u1ec1u n\u00e0y l\u00e0 kh\u00f4ng th\u1ec3 tr\u00e1nh kh\u1ecfi b\u1edfi v\u00ec b\u1ea1n kh\u00f4ng th\u1ec3 t\u00ednh \u0111\u01b0\u1ee3c c\u00f3 bao nhi\u1ec1u k\u1ebft qu\u1ea3 tham chi\u1ebfu gi\u1ed1ng nhau \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1 cho t\u1eebng gi\u00e1 tr\u1ecb kh\u00e1c nhau, t\u1eeb \u0111\u00f3 c\u00f3 th\u1ec3 trong qu\u00e1 tr\u00ecnh sao ch\u00e9p c\u00f4ng th\u1ee9c sang c\u00e1c \u00f4 t\u00ednh kh\u00e1c b\u1ea1n kh\u00f4ng th\u1ec3 bi\u1ebft ch\u00ednh x\u00e1c n\u00ean sao ch\u00e9p sang bao nhi\u00eau \u00f4 l\u00e0 \u0111\u1ee7. \u0110\u1ec3 tr\u00e1nh xu\u1ea5t hi\u1ec7n nh\u1eefng c\u1ea3nh b\u00e1o l\u1ed7i kh\u00f4ng c\u1ea7n thi\u1ebft, h\u00e0m n\u00e0y s\u1ebd tr\u1ea3 v\u1ec1 c\u00e1c chu\u1ed7i k\u00fd t\u1ef1 d\u1ea1ng string r\u1ed7ng (\u201c\u201d) \u0111\u1ec3 thay th\u1ebf.<\/p>\n<p style=\"text-align: justify\">L\u01b0u \u00fd: B\u1ea1n c\u1ea7n ph\u00e2n bi\u1ec7t c\u00e1ch s\u1eed d\u1ee5ng tham chi\u1ebfu tuy\u1ec7t \u0111\u1ed1i v\u00e0 tham chi\u1ebfu t\u01b0\u01a1ng \u0111\u1ed1i trong c\u00e1c c\u00f4ng th\u1ee9c Excel. T\u1ea5t c\u1ea3 tham chi\u1ebfu s\u1ebd kh\u00f4ng b\u1ecb thay \u0111\u1ed5i ngo\u1ea1i tr\u1eeb c\u1ed9t tham chi\u1ebfu t\u01b0\u01a1ng \u0111\u1ed1i c\u1ee7a gi\u00e1 tr\u1ecb c\u1ea7n tham chi\u1ebfu (D$2), \u0111i\u1ec1u n\u00e0y l\u00e0 y\u00eau c\u1ea7u b\u1eaft bu\u1ed9c trong qu\u00e1 tr\u00ecnh sao ch\u00e9p c\u00f4ng th\u1ee9c sang c\u00e1c c\u1ed9t b\u00ean c\u1ea1nh, \u0111\u1ec3 t\u1eeb \u0111\u00f3 cho ra c\u00e1c k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p v\u1edbi t\u1eebng c\u1ed9t c\u1ee5 th\u1ec3.<\/p>\n<p style=\"text-align: justify\">Sau khi t\u1ed5ng h\u1ee3p l\u1ea1i, ta c\u00f3 \u0111\u01b0\u1ee3c c\u00f4ng th\u1ee9c t\u1ed5ng qu\u00e1t d\u00e0nh cho h\u00e0m Vlookup tham chi\u1ebfu nhi\u1ec1u gi\u00e1 tr\u1ecb c\u00f9ng l\u00fac theo c\u1ed9t trong Excel:<\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c s\u1ed1 1: <strong>IFERROR(INDEX(\u00a0<\/strong><em><strong>return_range<\/strong><\/em><strong>, SMALL(IF(\u00a0<\/strong><em><strong>lookup_value<\/strong><\/em><strong>\u00a0=\u00a0<\/strong><em><strong>lookup_range<\/strong><\/em><strong>, ROW(\u00a0<\/strong><em><strong>return_range<\/strong><\/em><strong>\u00a0)-\u00a0<\/strong><em><strong>m<\/strong><\/em><strong>\u00a0,\u201d\u201d), ROW() \u2013\u00a0<\/strong><em><strong>n<\/strong><\/em><strong>\u00a0)),\u201d\u201d)<\/strong><\/p>\n<p style=\"text-align: left\">C\u00f4ng th\u1ee9c s\u1ed1 2:<strong> IFERROR(INDEX(\u00a0<\/strong><em><strong>return_range<\/strong><\/em><strong>, SMALL(IF(\u00a0<\/strong><em><strong>lookup_value<\/strong><\/em><strong>\u00a0=\u00a0<\/strong><em><strong>lookup_range<\/strong><\/em><strong>\u00a0, ROW(\u00a0<\/strong><em><strong>lookup_range<\/strong><\/em><strong>) -MIN(ROW(\u00a0<\/strong><em><strong>lookup_range<\/strong><\/em><strong>\u00a0))+1,\u201d\u201d), ROW() \u2013\u00a0<\/strong><em><strong>n<\/strong><\/em><strong>)),\u201d\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\">Trong \u0111\u00f3:<\/p>\n<ul>\n<li style=\"text-align: justify\">m l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 theo h\u00e0ng c\u1ee7a \u00f4 t\u00ednh \u0111\u1ea7u ti\u00ean c\u1ee7a khu v\u1ef1c tr\u1ea3 k\u1ebft qu\u1ea3, tr\u1eeb \u0111i 1 \u0111\u01a1n v\u1ecb<\/li>\n<li style=\"text-align: justify\">n l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 theo h\u00e0ng c\u1ee7a \u00f4 t\u00ednh ch\u1ee9a c\u00f4ng th\u1ee9c ban \u0111\u1ea7u, tr\u1eeb \u0111i 1 \u0111\u01a1n v\u1ecb<\/li>\n<\/ul>\n<p style=\"text-align: justify\">L\u01b0u \u00fd: Trong v\u00ed d\u1ee5 tr\u00ean, c\u1ea3 m v\u00e0 n \u0111\u1ec1u c\u00f3 gi\u00e1 tr\u1ecb b\u1eb1ng 2 b\u1edfi v\u00ec khu v\u1ef1c tr\u1ea3 k\u1ebft qu\u1ea3 v\u00e0 khu v\u1ef1c ch\u1ee9a c\u00f4ng th\u1ee9c \u0111\u1ec1u b\u1eaft \u0111\u1ea7u t\u1eeb h\u00e0ng th\u1ee9 3. T\u00f9y theo t\u1eebng b\u1ea3ng t\u00ednh c\u1ee5 th\u1ec3 m\u00e0 con s\u1ed1 n\u00e0y c\u00f3 th\u1ec3 kh\u00e1c nhau.<\/p>\n<blockquote>\n<p style=\"text-align: justify\">\n<\/blockquote>\n<p style=\"text-align: justify\"><em><u>C\u00f4ng th\u1ee9c 2:<\/u><\/em> <em>D\u00f9ng Vlookup \u0111\u1ec3<\/em><em> tham chi\u1ebf<\/em><em>u nhi\u1ec1<\/em><em>u k\u1ebf<\/em><em>t qu\u1ea3<\/em><em> trong c\u00f9ng m\u1ed9<\/em><em>t h\u00e0ng<\/em><\/p>\n<p style=\"text-align: justify\"><em>Trong tr<\/em><em>\u01b0\u1eddng h\u1ee3p b\u1ea1n v\u1eabn mu\u1ed1n tham chi\u1ebfu nhi\u1ec1u k\u1ebft qu\u1ea3 c\u00f9ng l\u00fac nh\u01b0ng c\u00e1c k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 ph\u1ea3i n\u1eb1m tr\u00ean c\u00f9ng 1 h\u00e0ng thay v\u00ec c\u1ed9t th\u00ec ta s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c nh\u01b0 sau:<\/em><\/p>\n<p style=\"text-align: justify\"><strong>=IFERROR(INDEX($B$3:$B$13, SMALL(IF($D3=$A$3:$A$13, ROW($B$3:$B$13)-2,\u201d\u201d), COLUMN()-4)),\u201d\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\">Ho\u1eb7c<\/p>\n<p style=\"text-align: justify\"><strong>=IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1,\u201d\u201d),COLUMN()-4)), \u201c\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\">T\u01b0\u01a1ng t\u1ef1 v\u00ed d\u1ee5 tr\u01b0\u1edbc \u0111\u00f3, \u0111\u00e2y \u0111\u1ec1u l\u00e0 c\u00f4ng th\u1ee9c d\u00e0nh cho t\u1eadp h\u1ee3p, n\u00ean l\u00e0 b\u1ea1n l\u01b0u \u00fd ph\u1ea3i b\u1ea5m t\u1ed5 h\u1ee3p ph\u00edm Ctrl+Shift+Enter \u0111\u1ec3 ho\u00e0n t\u1ea5t.<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-9322\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-multiple-matches-rows-1.png\" width=\"600\" height=\"298\" title=\"vlookup-multiple-matches-rows-1\"><\/p>\n<p style=\"text-align: justify\">C\u00e1ch th\u1ee9c ho\u1ea1t \u0111\u1ed9ng c\u1ee7a n\u00f3 t\u01b0\u01a1ng t\u1ef1 nh\u01b0 v\u00ed d\u1ee5 tr\u01b0\u1edbc, ngo\u1ea1i tr\u1eeb 1 \u0111i\u1ec1u l\u00e0 b\u1ea1n s\u1eed d\u1ee5ng h\u00e0m COLUMN thay v\u00ec h\u00e0m ROW nh\u1eb1m x\u00e1c \u0111\u1ecbnh v\u1ecb tr\u00ed tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 gi\u1ed1ng v\u1edbi gi\u00e1 tr\u1ecb tham chi\u1ebfu: COLUMN()-n. Trong \u0111\u00f3 n l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 theo c\u1ed9t c\u1ee7a \u00f4 t\u00ednh \u0111\u1ea7u ti\u00ean ch\u1ee9a c\u00f4ng th\u1ee9c, tr\u1eeb \u0111i 1 \u0111\u01a1n v\u1ecb. Trong v\u00ed d\u1ee5 n\u00e0y c\u00f4ng th\u1ee9c \u0111\u01b0\u1ee3c nh\u1eadp v\u00e0o d\u1ea3i \u00f4 E2:H2. V\u1edbi vi\u1ec7c c\u1ed9t E n\u1eb1m \u1edf th\u1ee9 t\u1ef1 s\u1ed1 5 th\u00ec khi \u0111\u00f3 n s\u1ebd b\u1eb1ng 4.<\/p>\n<p style=\"text-align: justify\">L\u01b0u \u00fd: \u0110\u1ec3 c\u00f3 th\u1ec3 sao ch\u00e9p ch\u00ednh x\u00e1c c\u00f4ng th\u1ee9c sang c\u00e1c h\u00e0ng kh\u00e1c, l\u01b0u \u00fd h\u00e3y \u0111\u1ec3 th\u00f4ng s\u1ed1 tham chi\u1ebfu d\u1ea1ng tuy\u1ec7t \u0111\u1ed1i theo c\u1ed9t v\u00e0 t\u01b0\u01a1ng \u0111\u1ed1i theo h\u00e0ng, ch\u1eb3ng h\u1ea1n: $D3<\/p>\n<p style=\"text-align: justify\">Sau khi t\u1ed5ng h\u1ee3p l\u1ea1i, ta c\u00f3 \u0111\u01b0\u1ee3c c\u00f4ng th\u1ee9c t\u1ed5ng qu\u00e1t d\u00e0nh cho h\u00e0m Vlookup tham chi\u1ebfu nhi\u1ec1u gi\u00e1 tr\u1ecb c\u00f9ng l\u00fac theo h\u00e0ng trong Excel:<\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c s\u1ed1 1: <strong>IFERROR(INDEX(\u00a0<\/strong><em><strong>return_range<\/strong><\/em><strong>, SMALL(IF(\u00a0<\/strong><em><strong>lookup_value<\/strong><\/em><strong>\u00a0=\u00a0<\/strong><em><strong>lookup_range<\/strong><\/em><strong>, ROW(\u00a0<\/strong><em><strong>return_range<\/strong><\/em><strong>) \u2013\u00a0<\/strong><em><strong>m<\/strong><\/em><strong>, \u201c\u201d), COLUMN() \u2013\u00a0<\/strong><em><strong>n<\/strong><\/em><strong>)), \u201c\u201d)<\/strong><\/p>\n<p style=\"text-align: left\">C\u00f4ng th\u1ee9c s\u1ed1 2: <strong>IFERROR(INDEX(\u00a0<\/strong><em><strong>return_range<\/strong><\/em><strong>, SMALL(IF(\u00a0<\/strong><em><strong>lookup_value<\/strong><\/em><strong>\u00a0=\u00a0<\/strong><em><strong>lookup_range<\/strong><\/em><strong>, ROW(<\/strong><em><strong>lookup_range<\/strong><\/em><strong>) \u2013 MIN(ROW(\u00a0<\/strong><em><strong>lookup_range<\/strong><\/em><strong>))+1,\u201d\u201d),COLUMN() \u2013\u00a0<\/strong><em><strong>n<\/strong><\/em><strong>)), \u201c\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\">Trong \u0111\u00f3:<\/p>\n<p style=\"text-align: justify\">m l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 theo h\u00e0ng c\u1ee7a \u00f4 t\u00ednh \u0111\u1ea7u ti\u00ean c\u1ee7a khu v\u1ef1c tr\u1ea3 k\u1ebft qu\u1ea3, tr\u1eeb \u0111i 1 \u0111\u01a1n v\u1ecb<\/p>\n<p style=\"text-align: justify\">n l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 theo c\u1ed9t c\u1ee7a \u00f4 t\u00ednh ch\u1ee9a c\u00f4ng th\u1ee9c ban \u0111\u1ea7u, tr\u1eeb \u0111i 1 \u0111\u01a1n v\u1ecb<\/p>\n<p style=\"text-align: justify\"><em><u>C\u00f4ng th\u1ee9c 3:<\/u><\/em><em> D\u00f9ng Vlookup \u0111\u1ec3 tham chi\u1ebfu nhi\u1ec1u k\u1ebft qu\u1ea3 c\u00f9ng l\u00fac v\u1edbi \u0111i\u1ec1u ki\u1ec7n r\u00e0ng bu\u1ed9c<\/em><\/p>\n<p style=\"text-align: justify\">B\u1ea1n c\u00f3 th\u1ec3 \u0111\u00e3 quen v\u1edbi vi\u1ec7c s\u1eed d\u1ee5ng Vlookup \u0111\u1ec3 tra c\u1ee9u k\u1ebft qu\u1ea3 trong Excel th\u00f4ng qua 1 \u0111i\u1ec1u ki\u1ec7n r\u00e0ng bu\u1ed9c. Nh\u01b0ng n\u1ebfu nh\u01b0 b\u1ea1n c\u1ea7n ph\u1ea3i tham chi\u1ebfu nhi\u1ec1u k\u1ebft qu\u1ea3 d\u1ef1a tr\u00ean nhi\u1ec1u h\u01a1n 1 ti\u00eau ch\u00ed? Ch\u1eb3ng h\u1ea1n v\u1edbi v\u00ed d\u1ee5 v\u1eeba r\u1ed3i, n\u1ebfu nh\u01b0 c\u00f3 th\u00eam 1 c\u1ed9t ti\u00eau ch\u00ed v\u1ec1 \u201cTh\u00e1ng\u201d \u0111\u01b0\u1ee3c b\u1ed5 sung v\u00e0o, l\u00e0m c\u00e1ch n\u00e0o \u0111\u1ec3 b\u1ea1n c\u00f3 th\u1ec3 ph\u00e2n lo\u1ea1i \u0111\u01b0\u1ee3c danh s\u00e1ch c\u00e1c m\u1eb7t h\u00e0ng m\u00e0 m\u1ed9t nh\u00e0 ph\u00e2n ph\u1ed1i cung c\u1ea5p theo t\u1eebng th\u00e1ng?<\/p>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n quen v\u1edbi c\u00e1c c\u00f4ng th\u1ee9c d\u00e0nh cho t\u1eadp h\u1ee3p, b\u1ea1n s\u1ebd th\u1ea5y l\u00e0 th\u01b0\u1eddng s\u1ebd c\u00f3 d\u1ea5u \u201c*\u201d v\u00e0 h\u00e0m AND \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng. V\u00ec v\u1eady b\u1ea1n c\u00f3 th\u1ec3 v\u1eabn t\u00e1i s\u1eed d\u1ee5ng c\u00e1c c\u00f4ng th\u1ee9c nh\u01b0 ph\u00eda tr\u00ean v\u00e0 th\u00eam v\u00e0o c\u00e1c \u0111i\u1ec1u ki\u1ec7n r\u00e0ng bu\u1ed9c d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<ul style=\"text-align: justify\">\n<li>Tr\u01b0\u1eddng h\u1ee3p tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u1ebft qu\u1ea3 theo c\u00f9ng 1 c\u1ed9t<\/li>\n<\/ul>\n<p style=\"text-align: left\"><strong>IFERROR(INDEX(return_range, SMALL(IF(1=((\u2013(lookup_value1=lookup_range1)) * ( \u2013(lookup_value2=lookup_range2))), ROW(return_range)-m,\u201d\u201d), ROW()-n)),\u201d\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\">Trong \u0111\u00f3<\/p>\n<ul>\n<li style=\"text-align: justify\">m l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 theo h\u00e0ng c\u1ee7a \u00f4 t\u00ednh \u0111\u1ea7u ti\u00ean c\u1ee7a khu v\u1ef1c tr\u1ea3 k\u1ebft qu\u1ea3, tr\u1eeb \u0111i 1 \u0111\u01a1n v\u1ecb<\/li>\n<li style=\"text-align: justify\">n l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 theo h\u00e0ng c\u1ee7a \u00f4 t\u00ednh ch\u1ee9a c\u00f4ng th\u1ee9c ban \u0111\u1ea7u, tr\u1eeb \u0111i 1 \u0111\u01a1n v\u1ecb<\/li>\n<\/ul>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed danh s\u00e1ch nh\u00e0 ph\u00e2n ph\u1ed1i (lookup_range1) n\u1eb1m \u1edf d\u1ea3i \u00f4 A1:A30, danh s\u00e1ch c\u00e1c th\u00e1ng (lookup_range2) n\u1eb1m \u1edf d\u1ea3i \u00f4 B1:B30, nh\u00e0 ph\u00e2n ph\u1ed1i c\u1ea7n quan t\u00e2m (lookup_value1) n\u1eb1m \u1edf \u00f4 t\u00ednh E3 v\u00e0 th\u00e1ng c\u1ea7n quan t\u00e2m (lookup_value2) n\u1eb1m \u1edf \u00f4 t\u00ednh F3, khi \u0111\u00f3 ta c\u00f3 c\u00f4ng th\u1ee9c sau:<\/p>\n<p style=\"text-align: justify\"><strong>=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((\u2013($E$3=$A$3:$A$30)) * (\u2013($F$3=$B$3:$B$30))), ROW($C$3:$C$30)-2,\u201d\u201d), ROW()-2)),\u201d\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\">V\u1edbi d\u1ea1ng tr\u00ean, ta c\u00f3 th\u1ec3 d\u1ec5 d\u00e0ng thi\u1ebft l\u1eadp n\u00f3 tr\u1edf th\u00e0nh m\u1ed9t b\u1ea3ng \u0111i\u1ec1u khi\u1ec3n, ch\u1eb3ng h\u1ea1n b\u1ea1n ch\u1ec9 c\u1ea7n \u0111i\u1ec1n t\u00ean nh\u00e0 ph\u00e2n ph\u1ed1i v\u00e0o \u00f4 E3 v\u00e0 t\u00ean th\u00e1ng c\u1ea7n t\u00ecm v\u00e0o \u00f4 F3, l\u1eadp t\u1ee9c k\u1ebft qu\u1ea3 cho ra m\u1ed9t danh s\u00e1ch c\u00e1c s\u1ea3n ph\u1ea9m \u1edf c\u1ed9t G.<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-9323\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-multiple-matches-column-1.png\" width=\"600\" height=\"727\" title=\"vlookup-multiple-matches-column-1\"><\/p>\n<ul style=\"text-align: justify\">\n<li>Tr\u01b0\u1eddng h\u1ee3p tr\u1ea3 v\u1ec1 nhi\u1ec1u k\u1ebft qu\u1ea3 theo c\u00f9ng 1 h\u00e0ng<\/li>\n<\/ul>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n mu\u1ed1n l\u1ea5y nhi\u1ec1u k\u1ebft qu\u1ea3 tham chi\u1ebfu c\u00f9ng l\u00fac nh\u01b0ng ch\u1ecbu ph\u1ee5 thu\u1ed9c v\u00e0o nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n r\u00e0ng bu\u1ed9c kh\u00e1c nhau, l\u1eddi khuy\u00ean l\u00e0 n\u00ean s\u1eed d\u1ee5ng b\u1ed1 c\u1ee5c h\u00e0ng ngang \u0111\u1ec3 k\u1ebft qu\u1ea3 tham chi\u1ebfu \u0111\u01b0\u1ee3c s\u1ebd \u0111\u01b0\u1ee3c b\u1ed1 tr\u00ed theo c\u00f9ng 1 h\u00e0ng. H\u00e3y s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c sau:<\/p>\n<p style=\"text-align: left\"><strong>IFERROR(INDEX(\u00a0<\/strong><em><strong>return_range<\/strong><\/em><strong>, SMALL(IF(1 = ((\u2013(<\/strong><em><strong>lookup_value1=lookup_range1<\/strong><\/em><strong>)) * (\u2013(<\/strong><em><strong>lookup_value2<\/strong><\/em><strong>\u00a0=\u00a0<\/strong><em><strong>lookup_range2<\/strong><\/em><strong>))), ROW(<\/strong><em><strong>return_range<\/strong><\/em><strong>) \u2013\u00a0<\/strong><em><strong>m<\/strong><\/em><strong>, \u201c\u201d), COLUMN() \u2013\u00a0<\/strong><em><strong>n<\/strong><\/em><strong>)),\u201d\u201d)<\/strong><\/p>\n<ul>\n<li>m l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 theo h\u00e0ng c\u1ee7a \u00f4 t\u00ednh \u0111\u1ea7u ti\u00ean c\u1ee7a khu v\u1ef1c tr\u1ea3 k\u1ebft qu\u1ea3, tr\u1eeb \u0111i 1 \u0111\u01a1n v\u1ecb<\/li>\n<li style=\"text-align: justify\">n l\u00e0 s\u1ed1 th\u1ee9 t\u1ef1 theo h\u00e0ng c\u1ee7a \u00f4 t\u00ednh ch\u1ee9a c\u00f4ng th\u1ee9c ban \u0111\u1ea7u, tr\u1eeb \u0111i 1 \u0111\u01a1n v\u1ecb<\/li>\n<\/ul>\n<p style=\"text-align: justify\">S\u1eed d\u1ee5ng d\u1eef li\u1ec7u c\u1ee7a v\u00ed d\u1ee5 tr\u00ean, c\u00f4ng th\u1ee9c s\u1ebd c\u00f3 d\u1ea1ng nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><strong>=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((\u2013($E3=$A$3:$A$30))*<\/strong><strong>(\u2013($F3=$B$3:$B$30))), ROW($C$3:$C$30)-2,\u201d\u201d), COLUMN()-6)),\u201d\u201d)<\/strong><\/p>\n<p style=\"text-align: justify\">V\u00e0 \u0111\u00e2y l\u00e0 k\u1ebft qu\u1ea3 thu \u0111\u01b0\u1ee3c:<\/p>\n<p style=\"text-align: justify\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-9324\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vlookup-multiple-matches-row-1.png\" width=\"600\" height=\"546\" title=\"vlookup-multiple-matches-row-1\"><\/p>\n<blockquote>\n<p style=\"text-align: justify\">\n<\/blockquote>\n<p style=\"text-align: justify\">T\u01b0\u01a1ng t\u1ef1, b\u1ea1n v\u1eabn c\u00f3 th\u1ec3 d\u00f9ng h\u00e0m Vlookup \u0111\u1ec3 tham chi\u1ebfu k\u1ebft qu\u1ea3 v\u1edbi 3, 4 hay nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n r\u00e0ng bu\u1ed9c h\u01a1n.<\/p>\n<p style=\"text-align: justify\"><em>C\u00e1ch th\u1ee9c ho\u1ea1t \u0111\u1ed9ng<\/em><\/p>\n<p style=\"text-align: justify\">V\u1ec1 c\u01a1 b\u1ea3n, c\u00f4ng th\u1ee9c tham chi\u1ebfu nhi\u1ec1u k\u1ebft qu\u1ea3 c\u00f9ng l\u00fac b\u1eb1ng h\u00e0m Vlookup r\u00e0ng bu\u1ed9c b\u1edfi nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n v\u1eabn c\u00f3 c\u00e1ch th\u1ee9c ho\u1ea1t \u0111\u1ed9ng gi\u1ed1ng nh\u01b0 c\u00e1ch gi\u1ea3i th\u00edch \u1edf v\u00ed d\u1ee5 \u0111\u1ea7u ti\u00ean. \u0110i\u1ec3m kh\u00e1c bi\u1ec7t duy nh\u1ea5t n\u1eb1m \u1edf ch\u1ed7 h\u00e0m IF b\u00e2y gi\u1edd c\u00f3 nhi\u1ec7m v\u1ee5 ph\u1ea3i x\u1eed l\u00fd nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n r\u00e0ng bu\u1ed9c h\u01a1n.<\/p>\n<p style=\"text-align: justify\">K\u1ebft qu\u1ea3 c\u1ee7a m\u1ed7i qu\u00e1 tr\u00ecnh so s\u00e1nh 2 bi\u1ebfn lookup_value=lookup_range l\u00e0 m\u1ed9t chu\u1ed7i c\u00e1c k\u1ebft qu\u1ea3 logic d\u1ea1ng TRUE (n\u1ebfu \u0111\u00fang) ho\u1eb7c FALSE (n\u1ebfu sai). D\u1ea5u g\u1ea1ch n\u1ed1i \u0111\u00f4i (\u2013) \u0111\u01b0\u1ee3c d\u00f9ng \u0111\u1ec3 bi\u1ebfn c\u00e1ch k\u1ebft qu\u1ea3 logic tr\u00ean th\u00e0nh d\u1ea1ng k\u00fd t\u1ef1 s\u1ed1 1 v\u00e0 0. B\u1edfi v\u00ec b\u1ea5t c\u1ee9 s\u1ed1 n\u00e0o nh\u00e2n v\u1edbi 0 c\u0169ng cho ra k\u1ebft qu\u1ea3 l\u00e0 0, b\u1ea1n s\u1ebd ch\u1ec9 th\u1ea5y c\u00e1c s\u1ed1 1 minh h\u1ecda c\u00e1c th\u00e0nh t\u1ed1 \u0111\u00e1p \u1ee9ng \u0111\u01b0\u1ee3c \u0111i\u1ec1u ki\u1ec7n r\u00e0ng bu\u1ed9c xu\u1ea5t hi\u1ec7n trong chu\u1ed7i k\u1ebft qu\u1ea3. Gi\u1edd vi\u1ec7c b\u1ea1n c\u1ea7n l\u00e0m \u0111\u01a1n gi\u1ea3n ch\u1ec9 l\u00e0 so s\u00e1nh chu\u1ed7i k\u1ebft qu\u1ea3 cu\u1ed1i c\u00f9ng v\u1edbi 1 sao cho h\u00e0m ROW tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u00e0 s\u1ed1 l\u01b0\u1ee3ng c\u00e1c h\u00e0ng \u0111\u00e1p \u1ee9ng \u0111\u01b0\u1ee3c t\u1ea5t c\u1ea3 \u0111i\u1ec1u ki\u1ec7n r\u00e0ng bu\u1ed9c, c\u00f2n kh\u00f4ng k\u1ebft qu\u1ea3 s\u1ebd l\u00e0 m\u1ed9t k\u00fd t\u1ef1 r\u1ed7ng.<\/p>\n<p style=\"text-align: justify\">Ch\u00fa \u00fd quan tr\u1ecdng: T\u1ea5t c\u1ea3 c\u00e1c c\u00f4ng th\u1ee9c Vlookup trong b\u00e0i vi\u1ebft n\u00e0y \u0111\u1ec1u \u1edf d\u1ea1ng c\u00f4ng th\u1ee9c d\u00e0nh cho t\u1eadp h\u1ee3p. V\u00ec th\u1ebf, c\u00f4ng th\u1ee9c s\u1ebd l\u1eb7p l\u1ea1i c\u00e1c th\u00e0nh t\u1ed1t c\u1ee7a chu\u1ed7i m\u1ed7i l\u1ea7n d\u1eef li\u1ec7u g\u1ed1c b\u1ecb thay \u0111\u1ed5i, ho\u1eb7c trang t\u00ednh \u0111\u01b0\u1ee3c t\u00ednh to\u00e1n l\u1ea1i. \u0110\u1ed1i v\u1edbi c\u00e1c b\u1ea3ng t\u00ednh l\u1edbn v\u1edbi s\u1ed1 l\u01b0\u1ee3ng h\u00e0ng v\u00e0 c\u1ed9t l\u00ean \u0111\u1ebfn h\u00e0ng tr\u0103m th\u00ec qu\u00e1 tr\u00ecnh n\u00e0y s\u1ebd l\u00e0m gi\u1ea3m t\u1ed1c \u0111\u1ed9 ph\u1ea3n h\u1ed3i c\u1ee7a Excel m\u1ed9t c\u00e1ch t\u01b0\u01a1ng \u0111\u1ed1i.<\/p>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 \u1ee9ng d\u1ee5ng t\u1ed1t Excel v\u00e0o trong c\u00f4ng vi\u1ec7c, ch\u00fang ta kh\u00f4ng ch\u1ec9 n\u1eafm v\u1eefng \u0111\u01b0\u1ee3c c\u00e1c h\u00e0m m\u00e0 c\u00f2n ph\u1ea3i s\u1eed d\u1ee5ng t\u1ed1t c\u1ea3 c\u00e1c c\u00f4ng c\u1ee5 c\u1ee7a Excel. Nh\u1eefng h\u00e0m n\u00e2ng cao gi\u00fap \u00e1p d\u1ee5ng t\u1ed1t v\u00e0o c\u00f4ng vi\u1ec7c nh\u01b0 SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH\u2026 Nh\u1eefng c\u00f4ng c\u1ee5 th\u01b0\u1eddng s\u1eed d\u1ee5ng l\u00e0 Data validation, Conditional formatting, Pivot table\u2026<\/p>\n<p>To\u00e0n b\u1ed9 nh\u1eefng ki\u1ebfn th\u1ee9c n\u00e0y c\u00e1c b\u1ea1n \u0111\u1ec1u c\u00f3 th\u1ec3 h\u1ecdc \u0111\u01b0\u1ee3c trong kh\u00f3a h\u1ecdc\u00a0<strong><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\/excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\" target=\"_blank\" rel=\"noopener\">EX101 \u2013 Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi chuy\u00ean gia<\/a><\/strong> c\u1ee7a H\u1ecdc Excel Online. Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 \u01b0u \u0111\u00e3i r\u1ea5t l\u1edbn cho b\u1ea1n khi \u0111\u0103ng k\u00fd tham gia kh\u00f3a h\u1ecdc.<\/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-240.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":3159,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3158","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\/3158","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=3158"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/3158\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/3159"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=3158"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=3158"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=3158"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}