Hôm nay, Học Excel Online xin giới thiệu với các bạn cách sử dụng hàm Vlookup nhằm tham chiếu nhiều công dụng cùng thời gian trên Excel một cách thuận lợi nhất. Sau đây sẽ là phía dẫn cách thức tham chiếu chỉ bằng 1 phương pháp duy nhất.

Bạn đang xem: Cách dùng hàm vlookup trả về nhiều giá trị

Thông thường hàm Vlookup chỉ tạo ra 1 hiệu quả tra cứu solo lẻ. Để hoàn toàn có thể nhận được nhiều kết quả tham chiếu hơn, bạn cần phải sử dụng chuỗi các điều kiện khác nhau để kết hợp chung vào hàm Vlookup, ví dụ ta vẫn sử dụng các hàm sau:

Hàm INDEX: Trả về nguyên tố chuỗi dựa trên số lượng cột với hàng chúng ta sử dụngHàm ROW: Trả về số máy tự theo hàngHàm COLUMN: Trả về số vật dụng tự theo cộtHàm IFERROR: cố định vị trí sai số.

Dưới đó là một số giải pháp sử dụng không giống nhau của cách làm này:

Công thức 1: sử dụng Vlookup nhằm tham chiếu nhiều công dụng trong cùng một cột

Ví dụ minh họa cho thấy cột A bao hàm tên các đơn vị phân phối và cột B bộc lộ loại sản phẩm mà từng đơn vị sản xuất cung cấp. để ý là sinh sống cột A ta xem xét thấy có một trong những tên đơn vị sản xuất bị lặp đi lặp lại nhiều hơn nữa 1 lần. Nhiệm vụ của người tiêu dùng là bắt buộc thống kế coi mỗi nhà phân phối tổng cộng cung ứng những món đồ nào. Dưới đây là hướng dẫn cụ thể cách làm:

1. Phân loại tên từng đơn vị sản xuất riêng ra một sản phẩm khác, rất có thể cùng nằm bình thường trong trang tính hiện tại. Ảnh tiếp sau đây minh họa tên các đơn vị sản xuất được phân một số loại trong hàng D2:G2

*

=IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,””), ROW()-2)),””)

Hoặc

=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,””), ROW()-2)),””)

Công thức đầu tiên rất có thể trông gọn khối lượng nhẹ hơn nhưng công thức thứ nhị lại được sử dụng thông dụng hơn bởi vì ít phải kiểm soát và điều chỉnh (cấu trúc cú pháp tương tự như cách thức hoạt động vui chơi của nó sẽ được giải thích cụ thể hơn tại phần sau)

Sao chép cách làm này tựa như cho các cột khác. Để có tác dụng được, bạn chỉ việc bôi đen phần cột vừa điền bí quyết vào cùng click phím điều hướng (là ô hình vuông bé dại nằm ở góc cạnh dưới bên phải của phần được quẹt đen) rồi kéo sang bắt buộc là xong.

Kết trái ta đã có ngoài ra sau:

*

Cách thức hoạt động:

Hàm IF

Trọng trung khu của công thức đó là hàm IF, với chức năng tìm kiếm vị trí của từng tổng hợp giá trị tương tự như của ô đề xuất tìm (D2) trong khoanh vùng cần tìm kiếm là A3:A13: IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,””)

Nếu tra cứu thấy tác dụng giống, hàm ROW vẫn trả về công dụng là số vật dụng tự theo cột của ô trống đầu tiên trong phạm vị trả hiệu quả (B3:B13), sau đó bạn phải giám sát 1 con số để trừ ít hơn sao cho khoảng cách giữa khu vực cần tra cứu và khu vực trả về hiệu quả là bởi 1. Lý do là do ta đang đi kiếm vị trí tương đối của yếu tố đầu tiên trong chuỗi. Với lấy ví dụ minh họa, ta nên trừ tiết kiệm hơn 2 cũng chính vì phần khoanh vùng trả về kết quả nằm ban đầu ở hàng sản phẩm công nghệ 3. Nếu khu vực đó nằm ở hàng thứ 2 thì các bạn phải trừ bớt đi 1, và tựa như như vậy.

Hoặc biện pháp khác bạn có thể làm là thực hiện cách miêu tả sau: ROW(lookup_column)-MIN(ROW(lookup_column))+1, lúc đó kết quả trả về vẫn giống tương tự như nhưng không cần phải điều chỉnh gì thêm mặc mang đến vị trí của khoanh vùng trả về hiệu quả là sinh hoạt đâu. Đối với lấy ví dụ minh họa, ta sử dụng cấu trúc sau: ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1.

Nếu không tìm kiếm được công dụng giống, thì hiệu quả trả về sẽ sở hữu được dạng string rỗng (“”)

Đến đây bạn đang xuất hiện một tập hợp những số (đại diện mang đến số lượng công dụng giống) và các ký trường đoản cú dạng string trống rỗng (đại diện đến số lượng hiệu quả khác). Trả sử ví dụ như với ô D3, ta tất cả tập hợp công dụng như hình dưới đây:

*

Thử kiểm tra đối chiếu với dữ liệu gốc, bạn cũng có thể thấy là giá trị “Adam” (giá trị tham chiếu trong ô D2) xuất hiện thêm ở vị trí thứ 3, 8 với 10 của quần thể vực kết quả tham chiếu (A3:A13)

Hàm SMALL

Tiếp theo, hàm SMALL(tập hợp, k) được sử dụng để xác định vị trí phải trả về của từng công dụng tham chiếu.

Với thông số “tập hợp” sẽ được xác minh từ trước, câu hỏi cần ân cần là xác định số đồ vật tự “k” , đồng nghĩa tương quan với giá trị nhỏ dại nhất đồ vật “k” được trả về. Để có tác dụng được điều đó, bạn phải sử dụng một cỗ đếm số bên dưới dạng hàm ROW()-n, trong số ấy n là số máy tự sản phẩm của ô tính đầu tiên trừ đi 1. Trong lấy ví dụ như này, ta điền công thức vào dải ô D3:D7, vì vậy ROW()-2 trả về hiệu quả là 1 đối với ô D3 (hàng lắp thêm 3 trừ đi 2 đơn vị) cùng trả về hiệu quả là 2 đối với ô D4 (hàng vật dụng 4 trừ đi 2 solo vị), cùng lặp lại tương tự như vậy.

Kết quả là, hàm SMALL vẫn đặt giá chỉ trị nhỏ dại nhất của tập vừa lòng trong ô D3, giá bán trị nhỏ thứ nhì vào ô D4, và cứ tiếp tục như thế. Từ đó, bí quyết dài loằng ngoằng tinh vi như ban sơ được giản lược đi thành dạng đơn giản và dễ dàng hơn như sau:

*

“Lời khuyên: Để bắt gặp giá trị tính toán ẩn phía sau từng phần của công thức, chỉ việc bôi black phần đó lại và bấm phím F9”

Hàm INDEX

Hàm này hoạt động khá đối chọi giản. Hàm này được dùng để lấy giá trị của một thành bên trong tập hợp dựa trên số vật dụng tự theo sản phẩm của nó

Hàm IFERROR

Cuối cùng, cách làm được dứt bằng hàm IFERROR để xử lý một trong những lỗi rất có thể xảy ra, vấn đề này là cấp thiết tránh khỏi bởi vì bạn không thể tính được gồm bao nhiều công dụng tham chiếu tương tự nhau được trả về đến từng giá trị khác nhau, trường đoản cú đó hoàn toàn có thể trong thừa trình coppy công thức sang các ô tính khác chúng ta không thể biết đúng mực nên coppy sang từng nào ô là đủ. Để tránh lộ diện những chú ý lỗi không đề nghị thiết, hàm này vẫn trả về các chuỗi ký kết tự dạng string trống rỗng (“”) để thế thế.

Lưu ý: bạn phải phân biệt cách áp dụng tham chiếu tuyệt vời và tham chiếu tương đối trong những công thức Excel. Tất cả tham chiếu sẽ không bị thay đổi ngoại trừ cột tham chiếu tương đối của giá chỉ trị đề xuất tham chiếu (D$2), điều đó là yêu cầu bắt buộc trong thừa trình xào nấu công thức sang các cột bên cạnh, nhằm từ đó đến ra những kết quả phù hợp với từng cột vắt thể.

Xem thêm: 13 Cách Làm Chân Giò Hun Khói Vinmart, Chân Giò Hun Khói

Sau lúc tổng thích hợp lại, ta có được công thức tổng quát dành riêng cho hàm Vlookup tham chiếu nhiều giá trị cùng lúc theo cột vào Excel:

Công thức số 1: IFERROR(INDEX( return_range, SMALL(IF( lookup_value = lookup_range, ROW( return_range )- m ,””), ROW() – n )),””)

Công thức số 2: IFERROR(INDEX( return_range, SMALL(IF( lookup_value = lookup_range , ROW( lookup_range) -MIN(ROW( lookup_range ))+1,””), ROW() – n)),””)

Trong đó:

m là số máy tự theo mặt hàng của ô tính thứ nhất của quanh vùng trả kết quả, trừ đi 1 solo vịn là số đồ vật tự theo hàng của ô tính chứa cách làm ban đầu, trừ đi 1 solo vị

Lưu ý: Trong lấy ví dụ như trên, cả m và n đều sở hữu giá trị bằng 2 bởi vì khu vực trả công dụng và khu vực chứa bí quyết đều bắt đầu từ hàng đồ vật 3. Tùy thuộc vào từng bảng tính ví dụ mà con số này có thể khác nhau.

Công thức 2: Dùng Vlookup để tham chiếu nhiều kết quả trong cùng một hàng

Trong trường hợp bạn vẫn ao ước tham chiếu nhiều hiệu quả cùng lúc cơ mà các kết quả trả về yêu cầu nằm trên thuộc 1 hàng thay vì chưng cột thì ta áp dụng công thức như sau:

=IFERROR(INDEX($B$3:$B$13, SMALL(IF($D3=$A$3:$A$13, ROW($B$3:$B$13)-2,””), COLUMN()-4)),””)

Hoặc

=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,””),COLUMN()-4)), “”)

Tương tự ví dụ như trước đó, đây hầu như là công thức giành cho tập hợp, yêu cầu là bạn xem xét phải bấm tổng hợp phím Ctrl+Shift+Enter nhằm hoàn tất.

*

Cách thức buổi giao lưu của nó giống như như lấy ví dụ như trước, ngoại trừ 1 điểm là bạn áp dụng hàm COLUMN thay do hàm ROW nhằm mục đích xác xác định trí trả về công dụng giống với cái giá trị tham chiếu: COLUMN()-n. Trong đó n là số trang bị tự theo cột của ô tính trước tiên chứa công thức, trừ đi 1 solo vị. Trong lấy một ví dụ này công thức được nhập vào dải ô E2:H2. Với câu hỏi cột E nằm tại vị trí thứ trường đoản cú số 5 thì lúc ấy n sẽ bằng 4.

Lưu ý: Để có thể sao chép đúng chuẩn công thức sang các hàng khác, chú ý hãy để thông số tham chiếu dạng hoàn hảo nhất theo cột và kha khá theo hàng, chẳng hạn: $D3

Sau lúc tổng hòa hợp lại, ta có được công thức tổng quát giành riêng cho hàm Vlookup tham chiếu các giá trị cùng lúc theo hàng trong Excel:

Công thức số 1: IFERROR(INDEX( return_range, SMALL(IF( lookup_value = lookup_range, ROW( return_range) – m, “”), COLUMN() – n)), “”)

Công thức số 2: IFERROR(INDEX( return_range, SMALL(IF( lookup_value = lookup_range, ROW(lookup_range) – MIN(ROW( lookup_range))+1,””),COLUMN() – n)), “”)

Trong đó:

m là số trang bị tự theo sản phẩm của ô tính đầu tiên của khu vực trả kết quả, trừ đi 1 đối kháng vị

n là số thiết bị tự theo cột của ô tính chứa bí quyết ban đầu, trừ đi 1 1-1 vị

Công thức 3: sử dụng Vlookup nhằm tham chiếu nhiều hiệu quả cùng lúc với đk ràng buộc

Bạn rất có thể đã quen thuộc với việc thực hiện Vlookup để tra cứu kết quả trong Excel thông sang 1 điều kiện ràng buộc. Nhưng nếu như bạn cần phải tham chiếu nhiều tác dụng dựa trên nhiều hơn thế nữa 1 tiêu chí? ví dụ điển hình với lấy ví dụ như vừa rồi, nếu như có thêm một cột tiêu chuẩn về “Tháng” được bổ sung cập nhật vào, làm giải pháp nào để chúng ta cũng có thể phân một số loại được danh sách các mặt hàng mà một nhà phân phối cung ứng theo từng tháng?

Nếu các bạn quen với các công thức dành cho tập hợp, bạn sẽ thấy là thường sẽ có được dấu “*” và hàm và được sử dụng. Bởi vậy chúng ta cũng có thể vẫn tái sử dụng những công thức như phía bên trên và thêm vào những điều kiện ràng buộc bên dưới đây:

Trường phù hợp trả về nhiều tác dụng theo thuộc 1 cột

IFERROR(INDEX(return_range, SMALL(IF(1=((–(lookup_value1=lookup_range1)) * ( –(lookup_value2=lookup_range2))), ROW(return_range)-m,””), ROW()-n)),””)

Trong đó

m là số máy tự theo hàng của ô tính thứ nhất của khoanh vùng trả kết quả, trừ đi 1 1-1 vịn là số máy tự theo mặt hàng của ô tính chứa công thức ban đầu, trừ đi 1 solo vị

Giả sử danh sách đơn vị sản xuất (lookup_range1) nằm ở dải ô A1:A30, danh sách những tháng (lookup_range2) nằm ở vị trí dải ô B1:B30, nhà phân phối cần thân thiện (lookup_value1) nằm ở ô tính E3 với tháng cần niềm nở (lookup_value2) nằm ở ô tính F3, lúc ấy ta gồm công thức sau:

=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((–($E$3=$A$3:$A$30)) * (–($F$3=$B$3:$B$30))), ROW($C$3:$C$30)-2,””), ROW()-2)),””)

Với dạng trên, ta hoàn toàn có thể dễ dàng tùy chỉnh cấu hình nó thay đổi một bảng điều khiển, chẳng hạn bạn chỉ cần điền tên đơn vị sản xuất vào ô E3 cùng tên tháng đề xuất tìm vào ô F3, lập tức tác dụng cho ra một danh sách các sản phẩm ở cột G.

*

Trường đúng theo trả về nhiều tác dụng theo thuộc 1 hàng

Nếu bạn có nhu cầu lấy nhiều kết quả tham chiếu cùng lúc cơ mà chịu nhờ vào vào nhiều đk ràng buộc không giống nhau, lời răn dạy là cần sử dụng bố cục hàng ngang để hiệu quả tham chiếu được đã được sắp xếp theo cùng 1 hàng. Hãy thực hiện công thức sau:

IFERROR(INDEX( return_range, SMALL(IF(1 = ((–(lookup_value1=lookup_range1)) * (–(lookup_value2 = lookup_range2))), ROW(return_range) – m, “”), COLUMN() – n)),””)

m là số vật dụng tự theo mặt hàng của ô tính trước tiên của khu vực trả kết quả, trừ đi 1 đối kháng vịn là số thứ tự theo hàng của ô tính chứa phương pháp ban đầu, trừ đi 1 1-1 vị

Sử dụng tài liệu của ví dụ trên, công thức sẽ sở hữu dạng như sau:

=IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((–($E3=$A$3:$A$30))*(–($F3=$B$3:$B$30))), ROW($C$3:$C$30)-2,””), COLUMN()-6)),””)

Và trên đây là tác dụng thu được:

*

Tương tự, chúng ta vẫn rất có thể dùng hàm Vlookup để tham chiếu tác dụng với 3, 4 tốt nhiều điều kiện ràng buộc hơn.

Cách thức hoạt động

Về cơ bản, công thức tham chiếu nhiều kết quả cùng lúc bằng hàm Vlookup ràng buộc vị nhiều điều kiện vẫn có cách thức hoạt động giống như cách phân tích và lý giải ở lấy một ví dụ đầu tiên. Điểm biệt lập duy duy nhất nằm tại đoạn hàm IF bây giờ có trách nhiệm phải cách xử trí nhiều điều kiện ràng buộc hơn.

Kết quả của mỗi quá trình so sánh 2 vươn lên là lookup_value=lookup_range là một trong những chuỗi các tác dụng logic dạng TRUE (nếu đúng) hoặc FALSE (nếu sai). Vết gạch nối đôi (–) được dùng làm biến cách công dụng logic trên thành dạng ký tự tiên phong hàng đầu và 0. Vày vì bất cứ số như thế nào nhân cùng với 0 cũng đến ra công dụng là 0, bạn sẽ chỉ thấy những số 1 minh họa các thành tố thỏa mãn nhu cầu được đk ràng buộc xuất hiện trong chuỗi kết quả. Giờ việc bạn cần làm đơn giản dễ dàng chỉ là đối chiếu chuỗi kết quả cuối với 1 sao cho hàm ROW trả về công dụng là số lượng các hàng thỏa mãn nhu cầu được tất cả điều kiện ràng buộc, còn không công dụng sẽ là một trong ký trường đoản cú rỗng.

Chú ý quan lại trọng: tất cả các phương pháp Vlookup trong nội dung bài viết này phần lớn ở dạng công thức dành riêng cho tập hợp. Do thế, bí quyết sẽ lặp lại các thành giỏi của chuỗi mỗi lần dữ liệu nơi bắt đầu bị nạm đổi, hoặc trang tính được đo lường và tính toán lại. Đối với các bảng tính lớn với con số hàng cùng cột lên tới mức hàng trăm thì quy trình này sẽ làm giảm vận tốc phản hồi của Excel một giải pháp tương đối.

Để rất có thể ứng dụng xuất sắc Excel vào trong công việc, bọn họ không chỉ nắm rõ được những hàm mà lại còn đề xuất sử dụng xuất sắc cả các công ráng của Excel. đều hàm cải thiện giúp áp dụng giỏi vào các bước như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những qui định thường thực hiện là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kỹ năng này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ phiên bản tới chuyên gia của học Excel Online. Hiện giờ hệ thống đang xuất hiện ưu đãi khôn cùng lớn cho bạn khi đăng ký tham gia khóa học.