Cách Dùng Hàm Vlookup Nhiều Giá Trị Với Một Hoặc Nhiều Tiêu Chí

Bài viết sau đây, Học Excel Online sẽ hướng dẫn một số cách đề hàm Vlookup nhiều kết quả phù hợp trong Excel dựa trên một hoặc nhiều điều kiện và trả về nhiều kết quả trong một cột, một hàng hoặc một ô.

Khi sử dụng Microsoft Excel để phân tích dữ liệu tình huống thường gặp là bạn muốn lấy tất cả các giá trị cho một ID cụ thể như tên, địa chỉ email hoặc một số định danh duy nhất khác. Một giải pháp nghĩ đến đó là sử dụng hàm Vlookup trong Excel nhưng vấn đề nó chỉ trả về một kết quả khớp duy nhất.

Vlookup cho nhiều giá trị với nhiều điều kiện có thể được thực hiện thông qua việc sử dụng kết hợp một số chức năng. Nội dung dưới đây sẽ giải thích những kiến thức cơ bản và một số cách điều chỉnh để giải quyết các nhiệm vụ tương tự. Ngoài ra bài viết cũng sẽ cung cấp cho bạn một số mẹo nhỏ chỉ bằng cú nhấp chuột mà không yêu cầu bất kỳ kiến thức nào phức tạp nào.

Vlookup nhiều giá trị bằng công thức

Như đã đề cập ở phần đầu hướng dẫn thì hàm Vlookup không thể trả về nhiều giá trị. Tác vụ này được thực hiện chỉ khi kết hợp các hàm sau trong công thức mảng:

• IF – đánh giá điều kiện và trả về một giá trị nếu điều kiện được đáp ứng và một giá trị khác nếu điều kiện không được đáp ứng.
• SMALL – nhận giá trị nhỏ nhất thứ k trong chuỗi.
• INDEX – trả về một phần tử dựa trên số hàng và cột mà bạn chỉ định.
• ROW – trả về số thứ tự theo hàng.
• COLUMN – trả về số thứ tự theo cột.
• IFERROR – Cố định vị trí sai số.

Dưới đây bạn sẽ tìm thấy một vài ví dụ về các công thức như vậy.

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

Ví dụ minh họa: Chúng ta có tên người bán cột A, và sản phẩm họ đã bán trong cột B. Lưu ý ở cột A ta thấy có một số tên người bán lặp đi lặp lại hơn 1 lần. Nhiệm vụ của chúng ta là thống kê người bán tổng cộng bán những mặt hàng nào. Dưới đây là hướng dẫn chi tiết cách thực hiện.

1. Nhập tên người bán riêng ra một hàng khác, có thể nằm trong cùng trang tính hiện tại. Trong ví dụ này, tên được nhập vào các ô D2: G2:

       2. Ngay cái tên đầu tiên, hãy chọn một ô trống bằng hoặc lớn hơn số lượng mặt hàng mà người bán có thể cung cấp, sau đó nhập một trong các công thức dưới đây vào chỗ trống và bấm tổ hợp Ctrl+Shift+Enter để hoàn tất (trong trường hợp này bạn chỉ có thể chỉnh sửa công thức trong phạm vi mà bạn đã chọn). Hoặc, bạn có thể nhập công thức vào ô đầu tiên, nhấn Ctrl + Shift + Enter, và sau đó sao chép công thức vào một vài ô nữa trong 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ẻ).

Dù đã có phần mềm, nhưng kỹ năng Excel vẫn cực kỳ quan trọng với kế toán, bạn đã vững Excel chưa? Hãy để tôi giúp bạn, đăng ký khoá học Excel:

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

Công thức đầu tiên có thể trông gọn nhẹ hơn những công thức thứ hai lại được sử dụng phổ biến hơn do ít phải điều chỉnh (cấu trúc cú pháp cũng như cách thức hoạt động của nó sẽ được giải thích cụ thể hơn ở phần sau).

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

Kết quả sẽ giống như sau:

Công thức này hoạt động như sau:

• Hàm IF:

Cốt lõi của công thức chính là hàm IF với chức năng là xác định vị trí của từng tổ hợp giá trị tương tự của ô cần tìm (D2) trong khu vực cần tìm là A3:A13: 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 vùng dữ liệu (A3:A13) và nếu tìm thấy khớp sẽ trả về vị trí tương đối của hàng, nếu không khớp một chuỗi rỗng (“”) xuất hiện.

Nếu tìm thấy kết quả giống thì hàm ROW sẽ trả về kết quả là số thứ tự theo cột của ô trống đầu tiên trong phạm vi trả kết quả (B3:B13). Sau đó chúng ta sẽ tính toán 1 con số để trừ bớt đi sao cho khoảng cách khu vực cần tìm và khu vực trả kết quả bằng 1. Với ví dụ trên ta cần trừ bớt đi 2 vì phần khu vực trả về kết quả bắt đầu ở hàng thứ 3. Nếu khu vực nằm ở hàng thứ 2 thì phải trừ đi 1 tương tự như vậy. 

Hoặc một cách khác là bạn có thể sử dụng biểu thức: ROW(lookup_column)-MIN(ROW(lookup_column))+1

 Khi đó kết quả vẫn trả về giống như vậy và không cần phải điều chỉnh gì mặc cho vị trí của khu vực trả kết quả ở đâu.  Trong ví dụ này sẽ là ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1.

Nếu không tìm được kết quả giống, thì kết quả trả về sẽ có dạng  rỗng (“”).

Đến đây bạn đang có một tập hợp các số (đại diện cho số lượng kết quả giống) và các ký tự dạng rỗng (đại diện cho số lượng kết quả khác). Giả sử ví dụ với ô D3, ta có tập hợp kết quả như hình dưới đây:

Thử kiểm tra đối chiếu với dữ liệu gốc, bạn có thể thấy là giá trị “Adam” (giá trị tham chiếu trong ô D2) xuất hiện ở vị trí thứ 3, 8 và 10 của khu 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í cần trả về của từng kết quả tham chiếu.

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

Kết quả là, hàm SMALL sẽ đặt giá trị nhỏ nhất của tập hợp trong ô D3, giá trị nhỏ thứ nhì vào ô D4, và cứ tiếp diễn như thế. Từ đó, công thức dài loằng ngoằng phức tạp như ban đầu được giản lược đi thành dạng đơn giản hơn như sau:

Lời khuyên: Để nhìn thấy giá trị tính toán ẩn sau từng phần của công thức, chỉ cần bôi đen phần đó lại và bấm phím F9”

Hàm INDEX

Hàm index này hoạt động khá đơn giản. Hàm này được dùng để lấy giá trị của một thành phần trong tập hợp dựa trên số thứ tự theo hàng của nó. 

Hàm IFERROR

Cuối cùng, bạn lông hàm IFERROR vào các công thức để xử lý tất cả các lỗi có thể xảy ra. Điều này không thể tránh khỏi vì bạn sẽ không biết có bao nhiêu kết quả tham chiếu trả giống nau được trả về cho từng giá trị, và từ đó trong quá trình sao chép công thức sang những ô tính khác bạn không thể biết chính xác nên sao chép bao nhiêu ô là đủ. Để tránh xuất hiện những cảnh báo lỗi không cần thiết, hàm này sẽ trả về các chuỗi ký tự dạng string rỗng (“”) để thay thế.

Lưu ý: Bạn cần phân biệt cách sử dụng tham chiếu tuyệt đối và tham chiếu tương đối trong các 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á trị cần tham chiếu (D$2), điều này là yêu cầu bắt buộc trong quá trình sao chép công thức sang các cột bên cạnh, để từ đó cho ra các kết quả phù hợp với từng cột cụ thể.

Kết hợp tất cả những điều này lại với nhau, chúng ta có được được các công thức chung sau để Vlookup nhiều giá trị 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 hàng của ô tính đầu tiên của của khu vực trả kết quả, trừ 1 đơn vị.
• n là số thứ tự theo cột của ô tính chứa công thức ban đầu, trừ đi 1 đơn vị.

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

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

Trong trường hợp bạn vẫn muốn tham chiếu nhiều kết quả cùng lúc nhưng các kết quả trả về sẽ nằm trên cùng 1 hàng thay vì cột thì chúng ta sử dụng công thức sau đây:

=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ụ trước đó, đây đều là công thức dành cho tập hợp, nên là bạn lưu ý phải bấm tổ hợp phím Ctrl+Shift+Enter để hoàn tất.

Cách thức hoạt động của nó tương tự như ví dụ trước, ngoại trừ 1 điều là bạn sử dụng hàm COLUMN thay vì hàm ROW nhằm xác định vị trí trả về kết quả: COLUMN()-n. Trong đó n là số thứ tự theo cột của ô tính đầu tiên chứa công thức, trừ đi 1 đơn vị. Trong ví dụ này công thức được nhập vào dải ô E2:H2. Với việc cột E nằm ở thứ tự số 5 thì khi đó n sẽ bằng 4.

Lưu ý: Để có thể sao chép chính xác công thức sang các hàng khác, lưu ý hãy để thông số tham chiếu dạng tuyệt đối theo cột và tương đối theo hàng, chẳng hạn: $D3.

Sau khi tổng hợp lại, ta có được công thức tổng quát dành hàm Vlookup tham chiếu nhiều giá trị cùng lúc theo hàng trong Excel như sau:

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ố thứ tự theo 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ố thứ tự theo cột của ô tính chứa công thức ban đầu, trừ đi 1 đơn 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

Vlookup thường được biết đến tra cứu trả kết quả trong Excel thông qua 1 điều kiện ràng buộc. Tuy nhiên nếu bạn muốn tham chiếu nhiều kết quả dựa trên nhiều hơn 1 tiêu chí? Chẳng hạn với ví dụ vừa rồi nếu như thêm một tiêu chí là cột “tháng” và yêu cầu đặt ra làm sao phân loại được danh sách mặt hàng người bán cung cấp theo từng tháng.

Nếu bạn quá quen với các công thức mảng, bạn sẽ thấy là thường có dấu “*” và hàm AND được sử dụng.  Vì vậy mà bạn vẫn có thể sử dụng công thức đã thảo luận trong 2 ví dụ trên và yêu cầu kiểm tra nhiều điều kiện như ví dụ được minh họa dưới đây.

Trường hợp trả nhiều kết quả theo một 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ố thứ tự theo 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ố thứ tự theo hàng của ô tính chứa công thức ban đầu, trừ đi 1 đơn vị.

Giả sử danh sách Seller (người bán)  (lookup_range1) nằm ở dải ô A1:A30, danh sách Months các tháng (lookup_range2) nằm ở dải ô B1:B30, nhà phân phối cần quan tâm (lookup_value1) nằm ở ô tính E3 và tháng cần quan tâm (lookup_value2) nằm ở ô tính F3, khi đó ta có 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 có thể dễ dàng thiết lập nó trở thành một bảng điều khiển, chẳng hạn bạn chỉ cần điền tên Seller (người bán) vào ô E3 và tên Month (tháng) cần tìm vào ô F3, lập tức kết quả cho ra một danh sách các sản phẩm ở cột G.

Trường hợp trả về nhiều kết quả theo cùng 1 hàng

Nếu bạn muốn lấy nhiều kết quả tham chiếu cùng lúc nhưng chịu phụ thuộc vào nhiều điều kiện ràng buộc khác nhau, bạn nên sử dụng bố cục hàng ngang để kết quả tham chiếu sẽ được bố trí theo cùng 1 hàng. Hãy sử dụng công thức sau:

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

• m là số thứ tự theo 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ố thứ tự theo hàng của ô tính chứa công thức ban đầu, trừ đi 1 đơn vị.

Sử dụng dữ liệu của ví dụ trên, công thức sẽ 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)),””)

Tương tự, bạn vẫn có thể dùng hàm Vlookup để tham chiếu kết quả với 3, 4 hay 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 bởi nhiều điều kiện vẫn có cách thức hoạt động giống như cách giải thích ở ví dụ đầu tiên. Sự khác biệt duy nhất nằm ở chỗ hàm IF bây giờ sẽ phải xử lý nhiều điều kiện ràng buộc hơn.

1=((–(lookup_value1=lookup_range1))*(–(lookup_value2=lookup_range2))*…)

Kết quả của mỗi quá trình so sánh 2 biến lookup_value=lookup_range là một chuỗi các kết quả logic dạng TRUE (nếu đúng) hoặc FALSE (nếu sai). Dấu gạch nối đôi (-) để biến kết quả logic thành ký tự số 1 và 0. Bởi vì bất kỳ số nào nhân với 0 cũng bằng 0 nên hầu như bạn sẽ chỉ thấy số 1 minh họa thành tố đáp ứng điều kiện ràng buộc. Bây giờ, bạn cần làm đơn giản chỉ là so sánh chuỗi kết quả với 1 sao cho hàm ROW trả về kết quả là số lượng các hàng đáp ứng được tất cả điều kiện ràng buộc, còn không kết quả sẽ là một ký tự rỗng.

Chú ý: Tất cả các công thức Vlookup trong bài viết này đều ở dạng công thức dành cho tập hợp. Vì thế, công thức sẽ lặp lại các thành tố của chuỗi mỗi lần dữ liệu gốc bị thay đổi, hoặc trang tính được tính toán lại. Đối với các bảng tính lớn với số lượng hàng và cột lên đến hàng trăm thì quá trình này sẽ làm giảm tốc độ xử lý của Excel một cách tương đối.

Cách Vlookup trả về nhiều giá trị trong một ô

Để thực hiện công việc này bạn có thể sử dụng phần bổ trợ đi kèm là Ultimate Suite và Combine Rows Wizard dành cho Excel. Nếu bạn chưa có nó trong Excel thì bạn có thể tải xuống phiên bản dùng thử 14 ngày miễn phí và tiến hành các bước như bên dưới.

Dữ liệu gốc và kết quả mong đợi với Ultimate Suite

Chúng ta sẽ tiếp tục làm việc với tập dữ liệu đã sử dụng trong các ví dụ trước. Nhưng lần nay thay vì trích xuất nhiều kết quả tham chiếu trong từng ô riêng biệt thì sẽ xuất hiện trong một ô duy nhất và phân tách bởi dấu phẩy, dấu cách hoặc phân cách theo cách bạn chọn.

Di chuyển các hàng có nhiều kết quả vào bảng chính

Trong bảng chính bạn 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ứ 2 và sắp xếp chúng như dưới đây và thực hiện các bước sau:

1. Chọn bảng chính của bạn hoặc bấm vào bất kỳ ô nào trong đó, sau đó bấm vào nút Hợp nhất Hai bảng trên thanh công cụ:

1. Phần tính năng bổ trợ để 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 :

Lời khuyên: Khi bạn sử dụng công cụ lần đầu tiên thì nên chọn “create a backup copy of the worksheet” để tránh tình trạng xảy ra sự cố.

      3. Chọn Lookup table và nhấn next:

      4. Bạn chọn những cặp giá trị phù hợp được so sánh trong bảng chính và bảng tra cứu (trong ví dụ này là cột Seller và Month), sau đó nhấn Next.

     5. Chọn cột mà bạn muốn lấy các giá trị phù hợp (trong ví dụ này là cột Product) và nhấn Next.

     6. Bạn lựa chọn hình thức sắp xếp nhiều kết quả trong bảng chính. Với ví dụ này sẽ là “Insert rows with duplicate matching values after the row with the same value” . Nếu không có tùy chọn nhất Finish. 

Kết quả: Tất cả các hàng phù hợp sẽ được di chuyển đến bảng chính và được phân theo các giá trị trong các cột tra cứu: Seller, Month, Product.

Tuy nhiên đây chưa phải là kết quả cuối cùng mà chúng ta mong muốn. Chúng ta đang sử dụng Vlookup để tìm kiếm nhiều kết quả cùng lúc và được cách nhau dấu phẩy, dấu cách hoặc dấu khác. Để làm như vậy chúng ta tiếp tục các bước sau.

Kết hợp các hàng trùng lặp thành một hàng 

Để hợp nhất “các hàng trùng lặp” trong một hàng, chúng ta sẽ sử dụng một công cụ khác – Combine Rows Wizard

1. Chọn bảng được tạo bởi công cụ Merge Tables (xem ảnh chụp màn hình ở trên) hoặc bất kỳ ô nào trong bảng và nhấp vào nút Combine Rows trên thanh công cụ: Bạn kiểm tra đúng bảng và nhấn Next

     2. Kiểm tra nếu dữ liệu được thêm vào bảng đúng, bạn nhấn Next để tiếp tục:

      3. Chọn cột chính làm việc ( Seller và Month trong ví dụ này) và nhấp vào Next :

      4. Chọn (các) cột chứa nhiều kết quả ( Product trong ví dụ này), chọn dấu phân cách mong muốn (dấu chấm phẩy, dấu phẩy, dấu cách hoặc ngắt dòng) và nhấp vào Finish:

Theo tùy chọn, bạn có thể bật một trong các tính năng bổ sung hoặc cả hai:

• Xóa các giá trị trùng lặp – nếu cột được hợp nhất chứa một vài giá trị giống nhau, lần xuất hiện đầu tiên sẽ được giữ lại, các kết quả trùng lặp sẽ bị xóa.
• Bỏ qua các ô trống

5. Đợi vài giây để xử lý, bạn sẽ nhận được kết quả như dưới hình:

Trong ví dụ này, không sử dụng tùy chọn này do đó công cụ này trả về tất cả các kết quả phù hợp được tìm thấy. Ví dụ, trong ô C2, chúng ta có chuỗi này: Lemon, Banana, Apple, Lemon, Banana (vui lòng xem kết quả ở bước 5 bên dưới). Nếu bạn chọn xóa các mục trùng lặp, kết quả sẽ là: Lemon, Banana, Apple .

Kết quả 

Trên đây là cách tra cứu và trả về nhiều giá trị trong Excel bằng các 2 công cụ trên. Nếu bạn muốn dùng thử chúng, bạn có thể tải xuống phiên bản Ultimate Suite bên dưới để trải nghiệm.