Hôm nay, Học Excel Online xin reviews với bạn cách thực hiện hàm Vlookup để tham chiếu nhiều kết quả cùng dịp trên Excel một cách dễ dãi nhất. Sau đây sẽ là hướng dẫn phương pháp tham chiếu chỉ bằng 1 công thức duy nhất. Bạn đang xem: Hàm vlookup tìm giá trị trùng nhau
Thông thường xuyên hàm Vlookup chỉ tạo ra 1 kết quả tra cứu solo lẻ. Để hoàn toàn có thể nhận được nhiều hiệu quả tham chiếu hơn, bạn phải sử dụng chuỗi những điều kiện khác nhau để phối kết hợp chung vào hàm Vlookup, cụ thể ta sẽ sử dụng những hàm sau:
Hàm INDEX: Trả về nhân tố chuỗi dựa trên con số cột với hàng bạn sử dụng Hàm ROW: Trả về số đồ vật tự theo mặt hàng Hàm COLUMN: Trả về số đồ vật tự theo cột Hàm IFERROR: cố định và thắt chặt vị trí sai số.Dưới đó là một số phương pháp sử dụng không giống nhau của bí quyết này:
Công thức 1: sử dụng Vlookup nhằm tham chiếu nhiều kết quả trong và 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 biểu hiện loại món đồ mà từng nhà sản xuất cung cấp. Chú ý là sinh hoạt cột A ta xem xét thấy có một trong những tên hãng sản xuất bị lặp đi lặp lại nhiều hơn 1 lần. Nhiệm vụ của khách hàng là buộc phải thống kế xem mỗi nhà phân phối tổng cộng hỗ trợ những món đồ nào. Dưới đấy là hướng dẫn cụ thể cách làm:
1. Phân nhiều loại tên từng nhà sản xuất riêng ra một mặt hàng khác, rất có thể cùng nằm phổ biến trong trang tính hiện nay tại. Ảnh tiếp sau đây minh họa tên các đơn vị sản xuất được phân nhiều loại trong mặt 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 nhẹ nhàng hơn nhưng công thức thứ nhì lại được sử dụng phổ biến hơn vì ít phải kiểm soát và điều chỉnh (cấu trúc cú pháp cũng giống như cách thức buổi giao lưu của nó sẽ được giải thích ví dụ hơn ở chỗ sau)
coppy công thức này tương tự như cho các cột khác. Để làm cho được, bạn chỉ cần bôi black phần cột vừa điền phương pháp vào và click phím điều hướng (là ô hình vuông nhỏ 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 nên là xong.Kết quả ta sẽ có hình như sau:
Cách thức hoạt động:
Hàm IFTrọng vai trung phong 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ổ hợp giá trị tương tự của ô đề xuất tìm (D2) trong khu vực cần kiếm tìm là A3:A13: IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,””)
Nếu search thấy công dụng giống, hàm ROW vẫn trả về công dụng là số đồ vật tự theo cột của ô trống trước tiên trong phạm vị trả hiệu quả (B3:B13), kế tiếp bạn phải đo lường và tính toán 1 số lượng để trừ ít hơn sao cho khoảng cách giữa khoanh vùng cần tìm và khoanh vùng trả về kết quả là bằng 1. Nguyên nhân là do ta đang đi tìm kiếm vị trí tương đối của yếu hèn tố đầu tiên trong chuỗi. Với ví dụ minh họa, ta yêu cầu trừ bớt đi 2 chính vì phần khu vực trả về hiệu quả nằm ban đầu ở hàng thiết bị 3. Nếu khoanh vùng đó nằm tại vị trí hàng thứ hai thì chúng ta phải trừ ít hơn 1, và giống như như vậy.
Hoặc biện pháp khác bạn có thể làm là sử dụng 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 lại vị trí của khu vực trả về kết quả là nghỉ ngơi đâu. Đối với lấy ví dụ như 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 được công dụng giống, thì tác dụng trả về sẽ sở hữu được dạng string trống rỗng (“”)
Đến trên đây bạn đang có một tập hợp những số (đại diện cho số lượng công dụng giống) và những ký từ dạng string trống rỗng (đại diện mang lại số lượng tác dụng khác). Mang sử lấy ví dụ với ô D3, ta tất cả tập hợp kết quả như hình bên dưới đây:
Thử kiểm tra đối chiếu với tài liệu gốc, chúng ta có thể thấy là cực hiếm “Adam” (giá trị tham chiếu vào ô D2) xuất hiện ở địa chỉ thứ 3, 8 với 10 của khu vực vực hiệu quả tham chiếu (A3:A13)
Hàm SMALLTiếp theo, hàm SMALL(tập hợp, k) được thực hiện để xác định vị trí đề nghị trả về của từng công dụng tham chiếu.
Với thông số kỹ thuật “tập hợp” đang được xác minh từ trước, bài toán cần thân thương là khẳng định số máy tự “k” , đồng nghĩa tương quan với giá chỉ trị bé dại nhất thiết bị “k” được trả về. Để làm cho được điều đó, bạn phải sử dụng một bộ đếm số bên dưới dạng hàm ROW()-n, trong các số đó n là số đồ vật tự hàng của ô tính thứ nhất trừ đi 1. Trong ví dụ này, ta điền công thức vào dải ô D3:D7, do đó ROW()-2 trả về hiệu quả là 1 so với ô D3 (hàng máy 3 trừ đi 2 đơn vị) với trả về hiệu quả là 2 đối với ô D4 (hàng trang bị 4 trừ đi 2 1-1 vị), và lặp lại giống như như vậy.
Kết quả là, hàm SMALL sẽ đặt giá chỉ trị nhỏ tuổi nhất của tập thích hợp trong ô D3, giá trị nhỏ thứ hai vào ô D4, và cứ tiếp tục như thế. Từ bỏ đó, bí quyết dài loằng ngoằng phức tạp như ban đầu được giản lược đi thành dạng dễ dàng hơn như sau:
“Lời khuyên: Để thấy được giá trị tính toán khuất phía sau từng phần của công thức, chỉ việc bôi đen phần đó lại và bấm phím F9”
Hàm INDEXHà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 vào số thiết bị tự theo sản phẩm của nó
Hàm IFERRORCuối cùng, công thức được kết thúc bằng hàm IFERROR để xử lý một vài lỗi rất có thể xảy ra, điều này là bắt buộc tránh khỏi bởi vì bạn cần thiết tính được gồm bao nhiều kết quả tham chiếu tương tự nhau được trả về cho từng cực hiếm khác nhau, trường đoản cú đó có thể trong quá trình xào luộc công thức sang các ô tính khác bạn không thể biết đúng mực nên sao chép sang từng nào ô là đủ. Để tránh xuất hiện thêm những chú ý lỗi không bắt buộc thiết, hàm này sẽ trả về các chuỗi ký kết tự dạng string trống rỗng (“”) để cố gắng thế.
Lưu ý: bạn phải phân biệt cách áp dụng tham chiếu hoàn hảo nhất 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 kha khá của giá bán trị buộc phải tham chiếu (D$2), điều đó là yêu cầu nên trong quá trình coppy công thức sang những cột mặt cạnh, nhằm từ đó cho ra những kết quả cân xứng với từng cột gắng thể.
Xem thêm: Khai Mạc Liên Hoan Phim Bộ Ấn Độ 2015, Những Bộ Phim Gây Sốt Màn Ảnh Việt Năm 2015
Sau khi tổng đúng theo lại, ta đạt được công thức tổng quát dành riêng cho hàm Vlookup tham chiếu nhiều giá trị đồng thời theo cột trong 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ố thứ tự theo mặt hàng của ô tính thứ nhất của khu vực trả kết quả, trừ đi 1 đơn vị chức năng n là số thiết bị tự theo sản phẩm của ô tính chứa phương pháp ban đầu, trừ đi 1 đơn vịLưu ý: Trong lấy một ví dụ trên, cả m với n đều có giá trị bởi 2 chính vì khu vực trả hiệu quả và khu vực chứa công thức đều bước đầu từ hàng đồ vật 3. Tùy thuộc vào từng bảng tính rõ ràng mà con số này rất 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 mong tham chiếu nhiều hiệu quả cùng lúc nhưng mà các công dụng trả về bắt buộc nằm trên thuộc 1 hàng thay bởi cột thì ta thực hiện 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 số đông là công thức dành cho tập hợp, nên là bạn xem xét phải bấm tổng hợp phím Ctrl+Shift+Enter để hoàn tất.
Cách thức hoạt động của nó giống như như ví dụ như trước, nước ngoài trừ 1 điều là bạn sử dụng hàm COLUMN thay vì hàm ROW nhằm mục đích xác xác định trí trả về công dụng giống với giá trị tham chiếu: COLUMN()-n. Trong các số đó n là số sản phẩm công nghệ tự theo cột của ô tính đầu tiên chứa công thức, trừ đi 1 đối chọi vị. Trong lấy ví dụ này bí quyết được nhập vào dải ô E2:H2. Với bài toán cột E nằm ở thứ tự số 5 thì khi đó n sẽ bởi 4.
Lưu ý: Để rất có thể sao chép đúng mực công thức sang những hàng khác, chú ý hãy để thông số kỹ thuật tham chiếu dạng tuyệt vời theo cột và tương đối theo hàng, chẳng hạn: $D3
Sau lúc tổng đúng theo lại, ta đã có được công thức tổng quát dành riêng cho hàm Vlookup tham chiếu các giá trị cùng lúc theo mặt 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ố sản phẩm công nghệ tự theo mặt hàng của ô tính đầu tiên của khu vực trả kết quả, trừ đi 1 đơn vị
n là số sản phẩm công nghệ tự theo cột của ô tính chứa bí quyết ban đầu, trừ đi 1 solo vị
Công thức 3: dùng Vlookup để tham chiếu nhiều kết quả cùng lúc với điều kiện ràng buộc
Bạn hoàn toàn có thể đã quen với việc thực hiện Vlookup để tra cứu công dụng trong Excel thông qua 1 điều khiếu nại ràng buộc. Dẫu vậy nếu như bạn cần phải tham chiếu nhiều hiệu quả dựa trên nhiều hơn nữa 1 tiêu chí? chẳng hạn với lấy ví dụ như vừa rồi, nếu như có thêm 1 cột tiêu chí về “Tháng” được bổ sung cập nhật vào, làm bí quyết nào để chúng ta cũng có thể phân các loại được list các sản phẩm mà một đơn vị phân phối hỗ trợ 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ẽ sở hữu dấu “*” với hàm & được sử dụng. Do vậy chúng ta cũng có thể vẫn tái sử dụng các công thức như bên trên và thêm vào các điều khiếu nại ràng buộc dưới đây:
Trường hòa hợp trả về nhiều kết quả theo thuộc 1 cộtIFERROR(INDEX(return_range, SMALL(IF(1=((–(lookup_value1=lookup_range1)) * ( –(lookup_value2=lookup_range2))), ROW(return_range)-m,””), ROW()-n)),””)
Trong đó
m là số đồ vật 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 đơn vị n là số máy tự theo hàng của ô tính chứa phương pháp ban đầu, trừ đi 1 đối chọi vịGiả sử danh sách đơn vị sản xuất (lookup_range1) nằm tại vị trí dải ô A1:A30, danh sách các tháng (lookup_range2) nằm tại vị trí dải ô B1:B30, hãng sản xuất cần niềm nở (lookup_value1) nằm tại vị trí ô tính E3 cùng tháng cần thân yêu (lookup_value2) nằm tại ô 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 thiết lập cấu hình nó đổi thay một bảng điều khiển, chẳng hạn bạn chỉ việc điền tên đơn vị phân phối vào ô E3 với tên tháng đề nghị tìm vào ô F3, lập tức hiệu quả cho ra một danh sách các thành phầm ở cột G.
Nếu bạn muốn lấy nhiều hiệu quả tham chiếu thuộc lúc nhưng lại chịu nhờ vào vào nhiều đk ràng buộc khác nhau, lời răn dạy là nên sử dụng bố cục tổng quan hàng ngang để kết quả tham chiếu được vẫn được bố trí theo thuộc 1 hàng. Hãy áp dụng 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ố sản phẩm tự theo sản phẩm của ô tính trước tiên của khu vực trả kết quả, trừ đi 1 đơn vị chức năng n là số sản phẩm công nghệ tự theo mặt hàng của ô tính chứa phương pháp ban đầu, trừ đi 1 đơn vịSử dụng tài liệu của lấy ví dụ trên, công thức sẽ có được 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à đây là công dụng thu được:
Tương tự, các bạn vẫn hoàn toàn có thể dùng hàm Vlookup nhằm tham chiếu công dụng với 3, 4 tuyệt nhiều điều kiện ràng buộc hơn.
Cách thức hoạt động
Về cơ bản, phương pháp tham chiếu nhiều tác dụng cùng lúc bởi hàm Vlookup ràng buộc vày nhiều điều kiện vẫn có phương pháp hoạt động giống như cách giải thích ở lấy một ví dụ đầu tiên. Điểm khác hoàn toàn duy độc nhất vô nhị nằm ở đoạn hàm IF bây chừ có nhiệm vụ phải xử lý nhiều đk ràng buộc hơn.
Kết quả của mỗi quy trình so sánh 2 biến chuyển lookup_value=lookup_range là 1 chuỗi các công dụng logic dạng TRUE (nếu đúng) hoặc FALSE (nếu sai). Vết gạch nối song (–) được dùng để biến cách kết quả logic trên thành dạng ký kết tự tiên phong hàng đầu và 0. Vị vì bất cứ số như thế nào nhân cùng với 0 cũng cho ra hiệu quả là 0, bạn sẽ chỉ thấy những số 1 minh họa những thành tố đáp ứng nhu cầu được đk ràng buộc xuất hiện thêm trong chuỗi kết quả. Giờ đồng hồ việc bạn phải làm đơn giản chỉ là so sánh chuỗi hiệu quả cuối cùng rất 1 làm thế nào cho hàm ROW trả về kết quả là số lượng các hàng thỏa mãn nhu cầu được toàn bộ điều kiện ràng buộc, còn không hiệu quả sẽ là một trong những ký trường đoản cú rỗng.
Chú ý quan liêu trọng: toàn bộ các công thức Vlookup trong nội dung bài viết này phần lớn ở dạng công thức giành riêng cho tập hợp. Vì thế, bí quyết sẽ lặp lại những thành tốt của chuỗi các lần dữ liệu gốc bị cụ đổi, hoặc trang tính được tính toán lại. Đối với các bảng tính béo với số lượng hàng và cột lên tới mức hàng trăm thì quy trình này sẽ làm cho giảm tốc độ phản hồi của Excel một phương pháp tương đối.
Để có thể ứng dụng xuất sắc Excel vào vào công việc, chúng ta không chỉ nắm rõ được những hàm nhưng còn đề nghị sử dụng tốt cả các công thay của Excel. đa số hàm nâng cấp giúp áp dụng giỏi vào các bước như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những biện pháp thường thực hiện là Data validation, Conditional formatting, Pivot table…Toàn cỗ những kiến thức này các bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel tự cơ bạn dạng tới chăm gia của học Excel Online. Hiện nay hệ thống đang sẵn có ưu đãi siêu lớn cho mình khi đk tham gia khóa học.
