Bạn đã bao giờ muốn VLOOKUP nhưng hiển thị nhiều kết quả phù hợp? Công thức VLOOKUP tiêu chuẩn trong Excel có một giới hạn và nó sẽ chỉ hiển thị kết quả khớp đầu tiên. Nhưng nếu bạn cần xem tất cả các trận đấu ở một nơi thì sao? Trong bài viết này, hãy để tôi trình bày cho bạn một công thức đơn giản để lấy tất cả các kết quả phù hợp ở định dạng danh sách.
Ví dụ:
Chúng ta muốn dò tìm dữ liệu trong cột Geography dựa vào dữ liệu Husein Augar đã cho trong cột Sales Person.
Để nhanh chóng có được tất cả các tên khác nhau trong một danh sách, bạn có thể sử dụng một công thức để trích xuất các giá trị riêng biệt trong Excel .
Dưới tên đầu tiên, chọn một số ô trống bằng hoặc lớn hơn số lượng kết quả phù hợp tối đa, nhập một trong các công thức mảng sau vào thanh công thức và nhấn Ctrl + Shift + Enter để hoàn thành [trong trường hợp, bạn sẽ chỉ có thể chỉnh sửa công thức trong toàn bộ phạm vi mà nó được nhập]. Hoặc, bạn có thể nhập công thức trong ô đầu tiên, nhấn Ctrl + Shift + Enter , rồi sao chép công thức vào một vài ô khác xuống cột [trong trường hợp này, bạn sẽ có thể chỉnh sửa công thức trong từng ô riêng lẻ] .
=IFERROR[INDEX[$B$3:$B$13, SMALL[IF[D$2=$A$3:$A$13, ROW[$B$3:$B$13]-2,""], ROW[]-2]],""]
hoặc là
=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]],""]
Như bạn thấy, 1 st thức là nhỏ gọn hơn một chút, nhưng 2 thứ một là phổ quát hơn và đòi hỏi phải sửa đổi ít [chúng tôi sẽ xây dựng thêm về cú pháp và logic một chút nữa].
Sao chép công thức sang các cột khác. Đối với điều này, chọn phạm vi ô mà bạn vừa nhập công thức và kéo thanh điều khiển điền [một hình vuông nhỏ ở góc dưới bên phải của phạm vi đã chọn] sang bên phải.
Kết quả sẽ trông giống như sau:
Đây là một ví dụ về sử dụng Excel từ trung cấp đến nâng cao hàm ý kiến thức cơ bản về công thức mảng và hàm Excel. Làm việc từ trong ra ngoài, đây là những gì bạn làm:
Hàm IF
Tại cốt lõi của công thức, bạn sử dụng hàm IF để nhận vị trí của tất cả các lần xuất hiện của giá trị tra cứu trong phạm vi tìm kiếm:
IF [D $ 2 = $ A $ 3: $ A $ 13, ROW [$ B $ 3: $ B $ 13 ] -2, ""]
IF so sánh giá trị tra cứu [D2] với mỗi giá trị trong phạm vi tìm kiếm [A3: A13] và nếu kết quả nếu tìm thấy, trả về vị trí tương đối của hàng; một chuỗi rỗng [""] nếu không.
Vị trí tương đối của các hàng được tính bằng cách trừ 2 từ ROW [$ B $ 3: $ B $ 13] sao cho hàng đầu tiên có vị trí 1. Nếu phạm vi trả về của bạn bắt đầu bằng hàng 2, sau đó trừ 1, v.v. Kết quả của phép toán này là mảng {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11}, đi tới đối số value_if_true của hàm IF.
Thay vì tính toán ở trên, bạn có thể sử dụng biểu thức này: ROW [lookup_column] - MIN [ROW [lookup_column]] + 1, trả về cùng một kết quả nhưng không yêu cầu bất kỳ thay đổi nào bất kể vị trí cột trả về. Trong ví dụ này, nó sẽ là ROW [$ A $ 3: $ A $ 13] - MIN [ROW [$ A $ 3: $ A $ 13]] + 1.
Vì vậy, tại thời điểm này bạn có một mảng bao gồm các số [vị trí của các trận đấu] và các chuỗi rỗng [không phù hợp]. Đối với ô D3 trong ví dụ này, chúng ta có mảng sau:
Một mảng có vị trí của các kết quả phù hợp và các chuỗi rỗng cho các kết quả không phù hợp
Nếu bạn kiểm tra với nguồn dữ liệu, bạn sẽ thấy rằng "Adam" [tra cứu giá trị trong D2] xuất hiện trên 3 thứ , 8 tháng và 10 ngày các vị trí trong dãy dò tìm [A3: A13].
Hàm SMALL
Tiếp theo, hàm SMALL [mảng, k] bước vào để xác định kết quả khớp nào sẽ được trả về trong một ô cụ thể.
Với mảng đã được thiết lập, hãy làm việc ra k đối số , tức là giá trị nhỏ nhất k-thứ được trả về. Đối với điều này, bạn tạo một loại "số đếm gia tăng" ROW [] - n, trong đó "n" là số thứ tự của ô công thức đầu tiên trừ 1. Trong ví dụ này, chúng ta đã nhập công thức vào ô D3: D7, vì vậy ROW [] - 2 trả về "1" cho ô D3 [hàng 3 trừ 2], "2" cho ô D4 [hàng 4 trừ 2], v.v.
Kết quả là, chức năng NHỎ kéo 1 st phần tử nhỏ nhất của mảng trong phòng giam D3, phần tử nhỏ nhất 2 trong ô D4, và vân vân. Và điều này biến đổi công thức ban đầu và phức tạp thành một công thức rất đơn giản, như sau:
Vị trí của giá trị khớp được trả về trong một ô đã cho
Tiền boa. Để xem giá trị được tính toán đằng sau một phần nhất định của công thức, hãy chọn phần đó trong thanh công thức và nhấn F9 .
Hàm INDEX
Phần này rất dễ. Bạn sử dụng hàm INDEX để trả về giá trị của phần tử mảng dựa trên số hàng của nó.
Và cuối cùng, bạn bọc công thức trong hàm IFERROR để xử lý các lỗi có thể xảy ra, không thể tránh khỏi vì bạn không thể biết số lượng kết quả phù hợp sẽ được trả về cho giá trị tra cứu này và do đó bạn sao chép công thức vào một số ô bằng hoặc lớn hơn số lượng các kết quả phù hợp có thể. Không phải để dọa người dùng của bạn với một loạt các lỗi, chỉ cần thay thế chúng bằng một chuỗi rỗng [ô trống].
Chú thích. Vui lòng lưu ý việc sử dụng đúng các tham chiếu ô tuyệt đối và tương đối trong công thức. Tất cả các tham chiếu được cố định ngoại trừ tham chiếu cột tương đối trong giá trị tra cứu [D $ 2], sẽ thay đổi dựa trên vị trí tương đối của [các] cột trong đó công thức được sao chép để trả về kết quả phù hợp cho các giá trị tra cứu khác.
Đặt tất cả những thứ này lại với nhau, chúng tôi nhận các công thức chung sau đây để Vlookup nhiều giá trị trong Excel:
Công thức 1 :
Hàm IFERROR [INDEX [ return_range , SMALL [IF [ lookup_value = lookup_range , ROW [ return_range ] - m , ""], COLUMN [] - n ]], ""]
Công thức 2 :
Hàm IFERROR [INDEX [ return_range , SMALL [IF [ lookup_value = lookup_range , ROW [ lookup_range ] - MIN [ROW [ lookup_range ]] + 1, ""], COLUMN [] - n ]], ""]
Ở đâu:
m là số hàng của ô đầu tiên trong phạm vi trả về trừ đi 1.
n là số cột của ô công thức đầu tiên trừ 1.
Công thức 3. Vlookup nhiều kết quả phù hợp dựa trên nhiều điều kiện
Bạn đã biết cách Vlookup cho nhiều giá trị trong Excel dựa trên một điều kiện. Nhưng nếu bạn muốn trả lại nhiều kết quả phù hợp dựa trên hai hoặc nhiều tiêu chí thì sao? Lấy ví dụ trước hơn nữa, nếu bạn có cột Tháng bổ sung và bạn đang tìm kiếm danh sách tất cả các sản phẩm được bán bởi một người bán nhất định trong một tháng cụ thể?
Nếu bạn đã quen thuộc với các công thức mảng, bạn có thể nhớ rằng chúng cho phép sử dụng dấu hoa thị [*] làm toán tử AND . Vì vậy, bạn chỉ có thể thực hiện các công thức được thảo luận trong hai ví dụ trước và yêu cầu chúng kiểm tra nhiều điều kiện như được minh họa bên dưới.
Hàm IFERROR [INDEX [ return_range , SMALL [IF [1 = [[- [ lookup_value1 = lookup_range1 ]] * [- [ lookup_value2 = lookup_range2 ]]], ROW [ return_range ] - m , ""], ROW [] - n ]], ""]
Ở đâu:
m là số hàng của ô đầu tiên trong phạm vi trả về trừ đi 1.
n là số hàng của ô công thức đầu tiên trừ 1.
Giả sử danh sách người bán [ lookup_range1 ] nằm trong A3: A30, danh sách Month [ lookup_range2 ] nằm trong B3: B30, người bán quan tâm [ lookup_value1 ] nằm trong ô E3 và tháng quan tâm [ lookup_value2 ] nằm trong ô F3, công thức có hình dạng 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]],""]
Bố cục này có thể hữu ích khi tạo trang tổng quan, ví dụ: người dùng của bạn có thể nhập tên trong E3, tháng trong F3 và nhận danh sách sản phẩm trong cột G:
Nếu bạn muốn kéo nhiều giá trị dựa trên nhiều bộ tiêu chí, bạn có thể thích bố cục nằm ngang nơi kết quả được trả về trong hàng. Trong trường hợp này, sử dụng công thức chung sau:
Hàm IFERROR [INDEX [ return_range , SMALL [IF [1 = [[- [ lookup_value1 = lookup_range1 ]] * [- [ lookup_value2 = lookup_range2 ]]], ROW [ return_range ] - m , ""], COLUMN [] - n ]], ""]
Ở đâu:
m là số hàng của ô đầu tiên trong phạm vi trả về trừ đi 1.
n là số hàng của ô công thức đầu tiên trừ 1.
Đối với tập dữ liệu mẫu của chúng tôi, công thức sẽ 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à kết quả có thể giống như sau:
Theo cách tương tự, bạn có thể thực hiện nhiều Vlookup với ba, bốn hoặc nhiều điều kiện.
Cách thức hoạt động của các công thức này
Về cơ bản, các công thức để Vlookup nhiều giá trị với nhiều điều kiện làm việc với logic đã quen thuộc, được giải thích trong ví dụ đầu tiên . Điểm khác biệt duy nhất là hàm IF hiện kiểm tra nhiều điều kiện:
1 = [[- [ lookup_value1 = lookup_range1 ]] * [- [ lookup_value2 = lookup_range2 ]] *…]
Kết quả của mỗi lookup_value = lookup_range so sánh là một mảng các giá trị lô-gic TRUE [điều kiện được đáp ứng] và FALSE [điều kiện không được đáp ứng]. Toán tử đơn nguyên kép [-] coerces các giá trị logic thành 1 và 0. Và bởi vì nhân với số không luôn luôn cho không, trong mảng kết quả, bạn chỉ có 1 cho những phần tử đáp ứng tất cả các điều kiện được chỉ định. Bây giờ, bạn chỉ cần so sánh mảng cuối cùng với số 1 để hàm ROW trả về số hàng đáp ứng tất cả các điều kiện, một chuỗi rỗng khác.
Một lời cảnh cáo . Tất cả các công thức Vlookup được thảo luận trong hướng dẫn này là các công thức mảng . Như vậy, mỗi công thức lặp lại thông qua tất cả các phần tử của mảng mỗi khi dữ liệu nguồn được thay đổi hoặc bảng tính được tính toán lại. Trên các trang tính lớn chứa hàng trăm hoặc hàng nghìn hàng, điều này có thể làm chậm đáng kể Excel của bạn.
Đây là cách bạn Vlookup nhiều kết quả phù hợp trong Excel bằng cách sử dụng công thức. Để xem xét kỹ hơn các ví dụ và có thể đảo ngược các công thức để hiểu rõ hơn, bạn có thể tải xuống bảng tính mẫu Excel Vlookup nhiều giá trị .
Tôi sẽ trả trước - Tôi không biết một công thức để các bản sao Vlookup có thể xuất nhiều kết quả khớp trong một lần bán duy nhất. Tuy nhiên, tôi biết một cách miễn phí công thức [đọc "căng thẳng-miễn phí" :] để làm điều này, bằng cách sử dụng hai add-in đi kèm với Ultimate Suite cho Excel của chúng tôi . Nếu bạn chưa có nó trong Excel của mình, bạn có thể tải xuống bản dùng thử miễn phí 14 ngày tại đây và sau đó làm theo các bước được nêu bên dưới.
Như được hiển thị trong ảnh chụp màn hình, chúng tôi tiếp tục làm việc với tập dữ liệu chúng tôi đã sử dụng trong ví dụ trước. Nhưng lần này chúng tôi muốn đạt được điều gì đó khác - thay vì trích xuất nhiều kết quả phù hợp trong các ô riêng biệt, chúng tôi muốn chúng xuất hiện trong một lần bán duy nhất, được phân tách bằng dấu phẩy, dấu cách hoặc một số dấu phân cách khác mà bạn chọn.
Trong bảng chính của bạn, hãy nhập danh sách các tên duy nhất trong cột đầu tiên, các tháng trong cột thứ hai và sắp xếp chúng như được hiển thị trong ảnh chụp màn hình bên dưới. Sau đó, thực hiện các bước sau:
Chọn bảng chính của bạn hoặc nhấp vào bất kỳ ô nào trong đó, sau đó nhấp vào nút Hợp nhất hai bảng trên ruy-băng:
Trình bổ sung đủ thông minh để xác định và chọn toàn bộ bảng, vì vậy bạn chỉ cần nhấp vào Tiếp theo :
Khi sử dụng công cụ lần đầu tiên, nó là lý do để chọn Tạo bản sao lưu của hộp trang tính trong trường hợp có sự cố.
Chọn bảng tra cứu và nhấp vào Tiếp theo .
Chọn một hoặc nhiều cặp cột phù hợp sẽ được so sánh trong bảng chính và bảng tra cứu [trong ví dụ này, đó là cột Người bán và Tháng ], sau đó bấm Tiếp theo .
Chọn [các] cột mà từ đó bạn muốn kéo các giá trị phù hợp [ Sản phẩm trong ví dụ này] và nhấp vào Tiếp theo .
Cho trình bổ sung biết chính xác bạn muốn sắp xếp nhiều đối sánh trong bảng chính như thế nào. Đối với ví dụ này, chúng tôi cần tùy chọn sau: Chèn các hàng có giá trị khớp trùng lặp sau hàng có cùng giá trị . Đảm bảo rằng không có tùy chọn nào khác được chọn và nhấp vào Hoàn tất .
Tại thời điểm này, bạn sẽ có kết quả sau đây - tất cả các hàng phù hợp được kéo đến bảng chính và được nhóm theo các giá trị trong cột tra cứu - đầu tiên bởi Người bán và sau đó theo Tháng:
Bảng kết quả đã có vẻ đẹp, nhưng nó không chính xác những gì chúng tôi muốn, phải không? Như bạn đã nhớ, chúng tôi đang tìm kiếm Vlookup nhiều kết quả phù hợp và đưa chúng trở lại trong một lần bán, dấu phẩy hoặc cách khác.
Chúc bạn thành công !
Bạn có thắc mắc gì muốn được giải đáp hãy để lại ý kiến [ Họ tên, Số điện thoại hoặc Email, câu hỏi] của mình dưới dưới phần bình luận chúng tôi sẽ giải đáp cho bạn !