Bất cứ khi nào chúng ta cần lấy một giá trị từ một trang tính khác hoặc trong cùng một trang tính, chúng ta không thể không nghĩ đến hàm VLOOKUP trong Excel. Nhưng vấn đề chính với VLOOKUP chức năng là nó chỉ được thiết kế để trả về một giá trị duy nhất một lần. Vì vậy, chúng tôi phải suy nghĩ một chút để vlookup nhiều giá trị với chức năng này mà chúng tôi sẽ mô tả trong bài viết này.
Chúng tôi cũng có thể sử dụng các hàm Excel khác, chẳng hạn như INDEX , NHỎ , TRẬN ĐẤU , ROW , CỘT , v.v., kết hợp chúng một cách thích hợp để vlookup và trả về nhiều giá trị. Chúng tôi cũng có thể sử dụng một số tính năng của Excel như Bộ lọc nâng cao , Bộ lọc tự động và Định dạng dưới dạng bảng dụng cụ. Vì vậy, trong hướng dẫn này, chúng ta sẽ thảo luận về tổng số 1 bằng cách sử dụng hàm Vlookup , 4 các phương pháp sử dụng các hàm Excel khác và 3 các phương pháp sử dụng các công cụ và tùy chọn Excel khác nhau để vlookup và trả về nhiều giá trị trong Excel một cách dễ dàng.
Bạn nên tải xuống tệp Excel và thực hành cùng với nó.
8 phương pháp để VLOOKUP và trả về nhiều giá trị trong Excel
Trong hướng dẫn này, chúng tôi sẽ sử dụng Cơ sở dữ liệu nhân viên để chứng minh tất cả 8 phương pháp trong suốt bài viết.
Vì vậy, không cần thảo luận thêm, hãy cùng tìm hiểu tất cả 8 phương pháp một.
1. Trả về nhiều giá trị bằng cách sử dụng hàm VLOOKUP trong Excel
Chúng tôi biết rằng VLOOKUP hàm chỉ có thể trả về một giá trị tại một thời điểm. Nhưng chúng ta cần trả về nhiều giá trị. Có, có các tùy chọn khác có sẵn để làm như vậy. Nhưng nếu bạn muốn đặc biệt sử dụng VLOOKUP chức năng, đừng mất hy vọng. Có một lối thoát. Trong phần này, bạn sẽ tìm hiểu cách sử dụng VLOOKUP chức năng trả về nhiều giá trị bằng cách điều chỉnh tập dữ liệu của bạn một chút. Các hàm mà chúng tôi sẽ sử dụng trong phương pháp này là COUNTIF và VLOOKUP . Bây giờ hãy làm theo các bước bên dưới:
🔗 Các bước:
Trước hết, chúng ta cần làm cho tất cả các tên bộ phận là duy nhất. Để làm như vậy,
❶ Chọn ô E5 .
❷ Bây giờ nhập công thức
=B5&COUNTIF(B5:B$13,B5) trong ô.
❸ Sau đó, nhấn ENTER nút.
❹ Kéo Fill Handle xuống biểu tượng Dept._Unique cột.
❺ Bây giờ hãy chọn ô C16 và loại công thức
=VLOOKUP(B16,E5:F13,2,0) với nó.
❻ Sau đó, nhấn ENTER nút.
❼ Cuối cùng, kéo Fill Handle biểu tượng ở cuối Nhân viên cột.
Thế là xong.
2. Vlookup và kéo nhiều giá trị theo chiều dọc trong Excel
Giả sử, bạn muốn tìm hiểu số lượng nhân viên làm việc theo Kỹ thuật bộ phận và tổ chức các tên đó theo chiều dọc trong các cột. Nếu vậy, hãy làm theo các bước dưới đây để tìm hiểu cách thực hiện. Các hàm mà chúng tôi sẽ sử dụng trong phương pháp này là INDEX , NHỎ , TRẬN ĐẤU và ROW .
🔗 Các bước:
❶ Trước hết, hãy chọn ô F5 ▶ để lưu trữ kết quả công thức.
❷ Sau đó, nhập công thức
=INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), ""), ROWS($A$1:A1))) trong ô.
❸ Sau đó nhấn ENTER nút.
❹ Bây giờ hãy kéo Tay cầm điền vào biểu tượng hướng xuống trong Nhân viên cột.
␥ Phân tích công thức
- ROW ($ B $ 5:$ B $ 13) ▶ trả về số hàng ở dạng mảng: {5; 6; 7; 8; 9; 10; 11; 12; 13}
- TRẬN ĐẤU (ROW ($ B $ 5:$ B $ 13), ROW ($ B $ 5:$ B $ 13)) ▶ chuyển đổi mảng trước đó thành :{1; 2; 3; 4; 5; 6; 7; 8; 9}
- IF (($ C $ 15 =$ B $ 5:$ B $ 13), MATCH (ROW ($ B $ 5:$ B $ 13), ROW ($ B $ 5:$ B $ 13)), “”) ▶ trả về số hàng phù hợp với điều kiện, nếu không trả về null: {“”; 2; ””; 4; ””; ””; ””; ””; 9}
- NHỎ (NẾU (($ E $ 5 =$ B $ 5:$ B $ 13), TRẬN ĐẤU (ROW ($ B $ 5:$ B $ 13), ROW ($ B $ 5:$ B $ 13)), “ ”) ▶ trả về số nhỏ đầu tiên trong mảng: {“”; 2; ””; 4; ””; ””; ””; ””; 9}
- CHỈ SỐ ($ C $ 5:$ C $ 13, NHỎ (NẾU (($ E $ 5 =$ B $ 5:$ B $ 13), TRẬN ĐẤU (ROW ($ B $ 5:$ B $ 13)), ROW ($ B $ 5:$ B $ 13)), “”) ▶ trả về tên nhân viên dựa trên số chỉ mục hàng được trả về bởi SMALL.
3. Vlookup và trả về nhiều giá trị theo chiều ngang trong Excel
Giả sử, bây giờ bạn muốn tìm hiểu số lượng nhân viên làm việc theo Kỹ thuật và sắp xếp các tên đó theo chiều ngang theo hàng. Các hàm mà chúng tôi sẽ sử dụng trong phương pháp này là INDEX , NHỎ , PHÚT và ROW .
🔗 Các bước:
❶ Trước hết, hãy chọn ô C16 ▶ để lưu trữ kết quả công thức.
❷ Sau đó, nhập công thức
=INDEX($C$5:$C$13, SMALL(IF($C$15=$B$5:$B$13, ROW($B$5:$B$13)-MIN(ROW($B$5:$B$13))+1, ""), COLUMNS($A$1:A1))) trong ô.
❸ Sau đó nhấn ENTER nút.
❹ Bây giờ hãy kéo Tay cầm điền vào ở bên phải của Nhân viên hàng.
␥ Phân tích công thức
- ROW ($ B $ 5:$ B $ 13) ▶ trả về số hàng ở dạng mảng: {5; 6; 7; 8; 9; 10; 11; 12; 13}
- NHỎ (NẾU ($ C $ 15 =$ B $ 5:$ B $ 13, ROW ($ B $ 5:$ B $ 13) -MIN (ROW ($ B $ 5:$ B $ 13)) + 1, “ ”) ▶ trả về số nhỏ đầu tiên trong mảng.
- CHỈ SỐ ($ C $ 5:$ C $ 13, NHỎ (NẾU ($ C $ 15 =$ B $ 5:$ B $ 13, ROW ($ B $ 5:$ B $ 13) -MIN (ROW ($ B $ 5:$ B $ 5) :$ B $ 13)) + 1, “”) ▶ trả về tên nhân viên dựa trên số chỉ mục hàng được trả về bởi SMALL.
4. Vlookup và Nhận Nhiều Giá trị trong Excel với Tiêu chí
Trong phần này, chúng tôi sẽ trả lại tất cả tên nhân viên trong Nhân viên những người làm việc theo Kỹ thuật và cũng làm việc vào Buổi sáng sự thay đổi. Các hàm mà chúng tôi sẽ sử dụng trong phương pháp này là INDEX , NHỎ , IFERROR và ROW .
🔗 Các bước:
❶ Trước hết, hãy chọn ô H5 ▶ để lưu trữ kết quả công thức.
❷ Sau đó, nhập công thức
=IFERROR(INDEX($C$5:$C$13,SMALL(IF(1=((--($F$5=$B$5:$B$13)) *(--($G$5=$D$5:$D$13))), ROW($C$5:$C$13)-4,""), ROW()-4)),"") trong ô.
❸ Sau đó nhấn ENTER nút.
❹ Bây giờ hãy kéo Tay cầm điền vào biểu tượng hướng xuống trong Nhân viên cột.
␥ Phân tích công thức
Công thức này khá giống với hai công thức trước. Dưới đây là các trường hợp ngoại lệ:
- ROW ($ C $ 5:$ C $ 13) -4 ▶ trả về các số hàng trong mảng: {5; 6; 7; 8; 9; 10; 11; 12; 13}. Ở đây, số 4 đề cập đến số hàng mà trên đó tiêu đề cột Nhân viên cư trú .
- ROW () - 4 ▶ ở đây số 4 đề cập đến số hàng trước đó trước khi hàng đầu ra bắt đầu.
- IFERROR ▶ đây là IFERROR được sử dụng để tổng hợp kết quả đầu ra tùy chỉnh nếu có bất kỳ lỗi nào xảy ra.
5. Vlookup và trích xuất nhiều giá trị trong Excel (Tất cả trong một ô)
Bây giờ chúng ta sẽ tập hợp tất cả các tên nhân viên trong cùng một ô làm việc dưới bộ phận Kỹ thuật. Các chức năng mà chúng tôi sẽ sử dụng trong phương pháp này là TEXTJOIN và IF .
🔗 Các bước:
❶ Trước hết, hãy chọn ô H5 ▶ để lưu trữ kết quả công thức.
❷ Sau đó, nhập công thức
=TEXTJOIN(",",TRUE,IF($B$5:$B$13=$C$15,$C$5:$C$13,"")) trong ô.
❸ Sau đó nhấn ENTER nút.
Thế là xong.
␥ Phân tích công thức
Đây, IF hàm trả về tất cả các tên nhân viên phù hợp với điều kiện và TEXTJOIN hàm kết hợp tất cả các tên nhân viên được trả về bởi IF chức năng.
Đọc thêm: Cách VLOOKUP Nhiều giá trị trong một ô trong Excel (2 Phương pháp Dễ dàng)
6. Vlookup để trả về nhiều giá trị trong Excel bằng AutoFilter
Nếu bạn muốn tránh sử dụng công thức Excel, thì bạn có thể vlookup qua các bảng dữ liệu và lấy ra nhiều giá trị bằng cách sử dụng tính năng AutoFilter. Ở đây, chúng tôi sẽ cố gắng sắp xếp tất cả các tên nhân viên làm việc dưới bộ phận Kỹ thuật.
🔗 Các bước:
❶ Trước hết, hãy chọn toàn bộ bảng dữ liệu.
❷ Sau đó, chuyển đến Dữ liệu dải băng.
❸ Sau đó, nhấp vào Bộ lọc tùy chọn.
❹ Bây giờ hãy nhấp vào biểu tượng bộ lọc ngay bên dưới Bộ phận tiêu đề cột.
❺ Bây giờ đánh dấu đánh dấu trên Kỹ thuật từ menu bật lên.
❻ Sau đó nhấn OK nút.
Hoan hô! Bạn đã hoàn thành với nó. Bây giờ bạn sẽ có kết quả như sau:
7. Vlookup và trích xuất nhiều giá trị trong Excel bằng bộ lọc nâng cao
Có một tính năng được gọi là Bộ lọc nâng cao trong Excel mà bạn có thể sử dụng để tra cứu bảng dữ liệu theo chiều dọc và trích xuất nhiều giá trị cùng một lúc. Trong ví dụ sau, chúng tôi chọn bộ phận Kỹ thuật trong tiêu đề cột Bộ phận. Chúng tôi sẽ sử dụng cái mà chúng tôi sẽ sử dụng để tìm kiếm trong toàn bộ bảng dữ liệu để tìm ra những nhân viên nào làm việc dưới bộ phận Kỹ thuật. Bây giờ, đây là các bước để làm theo:
🔗 Các bước:
❶ Trước hết, hãy chọn toàn bộ bảng dữ liệu.
❷ Sau đó, chuyển đến Dữ liệu dải băng.
❸ Sau đó, nhấp vào Nâng cao tùy chọn.
❹ Khi bạn đã hoàn tất bước cuối cùng, một hộp thoại có tên Bộ lọc nâng cao sẽ xuất hiện trên màn hình.
❺ Bây giờ hãy nhập
$B$4:$C$13 trong Phạm vi danh sách: thanh và
'Advanced Filter'!$E$4:$E$5 trong Phạm vi tiêu chí: quán ba.
❻ Sau đó, bạn có thể nhấn OK nút.
Khi bạn hoàn thành tất cả các bước trước đó, bạn sẽ nhận được kết quả như sau:
8. Vlookup để lấy nhiều giá trị trong Excel bằng cách sử dụng định dạng dưới dạng bảng
Bạn có thể sử dụng phương pháp này thay thế cho Bộ lọc tự động và Bộ lọc nâng cao quyền mua. Trong phương pháp này, chúng tôi sẽ sử dụng Định dạng dưới dạng bảng tùy chọn để sắp xếp tất cả các tên nhân viên trong Kỹ thuật thể loại. Dưới đây là các bước để làm theo:
🔗 Các bước:
❶ Trước hết, hãy chọn toàn bộ bảng dữ liệu.
❷ Sau đó, chuyển đến Trang chủ dải băng.
❸ Sau đó, chọn Định dạng dưới dạng bảng tùy chọn.
❹ Sau đó, chọn bất kỳ kiểu bảng nào bạn muốn.
Bây giờ bảng dữ liệu của bạn sẽ giống như hình sau.
❺ Ở giai đoạn này, hãy nhấp vào biểu tượng Bộ lọc thuộc Cục tiêu đề cột.
❻ Từ trình đơn bật lên, hãy đánh dấu chỉ đánh dấu trên Kỹ thuật tùy chọn.
❼ Sau đó nhấp vào nút OK nút.
Khi bạn hoàn thành tất cả các bước trước đó, kết quả của bạn sẽ giống như sau:
Những điều cần nhớ
📌 VLOOKUP chức năng không thể mang lại nhiều hơn một giá trị.
📌 Hãy cẩn thận về cú pháp của các hàm.
📌 Chèn cẩn thận các phạm vi dữ liệu vào công thức.
Kết luận
Tóm lại, chúng ta đã thảo luận về 8 phương pháp riêng biệt để vlookup và trả về nhiều giá trị trong Excel. Bạn nên tải xuống sách bài tập thực hành đính kèm với bài viết này và thực hành tất cả các phương pháp với tài liệu đó. Và đừng ngần ngại đặt bất kỳ câu hỏi nào trong phần bình luận bên dưới. Chúng tôi sẽ cố gắng trả lời tất cả các câu hỏi có liên quan càng sớm càng tốt.
Các bài viết liên quan
- Cách thực hiện hàm VLOOKUP với nhiều hàng trong Excel (5 phương pháp)