Khi nào cần lấy giá trị từ sheet khác hoặc cùng sheet, chúng ta không khỏi nghĩ đến hàm VLOOKUP trong Excel. Nhưng vấn đề chính của VLOOKUP là nó chỉ thiết kế để trả về một giá trị duy nhất. Vì vậy, chúng ta phải tư duy linh hoạt hơn để VLOOKUP nhiều giá trị bằng hàm này, điều mà bài viết này sẽ mô tả.
Chúng ta cũng có thể dùng các hàm Excel khác như INDEX, SMALL, MATCH, ROW, COLUMNS, v.v., kết hợp phù hợp để VLOOKUP và trả về nhiều giá trị. Ngoài ra, có thể dùng các tính năng Excel như Advanced Filter (Bộ lọc nâng cao), AutoFilter (Bộ lọc tự động), và công cụ Format as Table (Định dạng thành bảng). Vì vậy, trong hướng dẫn này chúng ta sẽ thảo luận tổng cộng 8 phương pháp: 1 phương pháp dùng hàm VLOOKUP, 4 phương pháp dùng các hàm Excel khác, và 3 phương pháp dùng công cụ/tùy chọn Excel khác để VLOOKUP và trả về nhiều giá trị dễ dàng.
Bạn nên tải file Excel và thực hành cùng bài viết.
8 phương pháp VLOOKUP và trả về nhiều giá trị trong Excel
Trong hướng dẫn này, chúng ta sẽ dùng cơ sở dữ liệu Nhân viên (Employee Database) để minh họa tất cả 8 phương pháp.
Hãy đi thẳng vào 8 phương pháp một bằng một.
1. Trả về nhiều giá trị bằng hàm VLOOKUP trong Excel
Chúng ta biết VLOOKUP chỉ trả về một giá trị mỗi lần. Nhưng chúng ta cần nhiều giá trị. Có các lựa chọn khác, nhưng nếu bạn nhất định dùng VLOOKUP, đừng nản lòng. Có giải pháp. Phần này sẽ chỉ cách dùng VLOOKUP trả về nhiều giá trị bằng cách điều chỉnh tập dữ liệu một chút. Các hàm sử dụng: COUNTIF và VLOOKUP. Làm theo các bước:
🔗 Các bước:
Trước hết, cần làm cho tên phòng ban trở nên duy nhất. Để làm vậy:
❶ Chọn ô E5.
❷ Nhập công thức:
=B5&COUNTIF(B5:B$13,B5)❸ Nhấn ENTER.
❹ Kéo Fill Handle xuống cột Dept._Unique.
❺ Chọn ô C16 và nhập công thức:
=VLOOKUP(B16,E5:F13,2,0)❻ Nhấn ENTER.
❼ Kéo Fill Handle đến cuối cột Employee (Nhân viên).
Đã 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 số nhân viên làm việc dưới phòng ban Engineering (Kỹ thuật) và sắp xếp tên theo chiều dọc. Nếu vậy, làm theo các bước dưới đây. Các hàm sử dụng: INDEX, SMALL, MATCH, và ROW.
🔗 Các bước:
❶ Chọn ô F5 để lưu kết quả công thức.
❷ 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)))❸ Nhấn ENTER.
❹ Kéo Fill Handle xuống cột Employee.
␥ 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}
- MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) ▶ chuyển 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 khớp điều kiện, ngược lại rỗng: {"";2;"";4;"";"";"";"";9}
- SMALL(IF(($E$5=$B$5:$B$13), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), "") ▶ trả về số nhỏ đầu tiên trong mảng: {"";2;"";4;"";"";"";"";9}
- INDEX($C$5:$C$13, SMALL(IF(($E$5=$B$5:$B$13),MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), "")) ▶ trả về tên nhân viên dựa trên chỉ số hàng từ 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 nhân viên phòng Engineering và sắp xếp tên theo chiều ngang (hàng). Các hàm dùng: INDEX, SMALL, MIN, và ROW.
🔗 Các bước:
❶ Chọn ô C16 để lưu kết quả.
❷ 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)))❸ Nhấn ENTER.
❹ Kéo Fill Handle sang phải hàng Employee.
␥ 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}
- SMALL(IF($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.
- 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, "")) ▶ trả về tên nhân viên dựa trên chỉ số hàng từ SMALL.
4. VLOOKUP và lấy nhiều giá trị trong Excel với điều kiện
Phần này sẽ trả về tất cả tên nhân viên trong cột Employee làm việc dưới phòng Engineering VÀ ca Morning (Sáng). Các hàm: INDEX, SMALL, IFERROR, và ROW.
🔗 Các bước:
❶ Chọn ô H5 để lưu kết quả.
❷ 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)),"")❸ Nhấn ENTER.
❹ Kéo Fill Handle xuống cột Employee.
␥ Phân tích công thức
Công thức này khá tương tự hai công thức trước. Các ngoại lệ:
- ROW($C$5:$C$13)-4 ▶ trả về số hàng trong mảng: {5;6;7;8;9;10;11;12;13}. Số 4 là số hàng tiêu đề cột Employee.
- ROW()-4 ▶ số 4 là hàng trước hàng bắt đầu đầu ra.
- IFERROR ▶ dùng để tùy chỉnh đầu ra nếu có lỗi.
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ẽ gom tất cả tên nhân viên vào cùng một ô cho phòng Engineering. Các hàm: TEXTJOIN và IF.
🔗 Các bước:
❶ Chọn ô H5 để lưu kết quả.
❷ Nhập công thức:
=TEXTJOIN(",",TRUE,IF($B$5:$B$13=$C$15,$C$5:$C$13,""))❸ Nhấn ENTER.
Đã xong.
␥ Phân tích công thức
Hàm IF trả về tất cả tên nhân viên khớp điều kiện, hàm TEXTJOIN nối tất cả tên đó lại.
Đọ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 muốn tránh công thức, bạn có thể VLOOKUP qua bảng dữ liệu và lấy nhiều giá trị bằng tính năng AutoFilter. Ở đây, chúng ta sẽ lọc tất cả nhân viên phòng Engineering.
🔗 Các bước:
❶ Chọn toàn bộ bảng dữ liệu.
❷ Vào tab Data (Dữ liệu).
❸ Nhấp Filter (Lọc).
❹ Nhấp biểu tượng lọc dưới tiêu đề cột Department (Phòng ban).
❺ Tích vào tùy chọn Engineering từ menu bật lên.
❻ Nhấp OK.
Tuyệt vời! Bạn đã xong. Kết quả như sau:
7. VLOOKUP và trích xuất nhiều giá trị trong Excel bằng Advanced Filter
Có tính năng Advanced Filter (Bộ lọc nâng cao) trong Excel để tra cứu bảng dữ liệu theo chiều dọc và trích xuất nhiều giá trị cùng lúc. Ví dụ sau, chúng ta chọn phòng Engineering dưới tiêu đề cột Department. Sẽ dùng để tìm nhân viên phòng Engineering. Các bước:
🔗 Các bước:
❶ Chọn toàn bộ bảng dữ liệu.
❷ Vào tab Data.
❸ Nhấp Advanced (Nâng cao).
❹ Hộp thoại Advanced Filter xuất hiện.
❺ Nhập:
$B$4:$C$13vào thanh List range: và
'Advanced Filter'!$E$4:$E$5vào thanh Criteria range: (Phạm vi điều kiện).
❻ Nhấp OK.
Khi hoàn tất, đầu ra như sau:
8. VLOOKUP lấy nhiều giá trị trong Excel bằng Format as Table
Phương pháp này là phương án thay thế cho AutoFilter và Advanced Filter. Ở đây dùng Format as Table (Định dạng thành bảng) để lọc tất cả nhân viên dưới thể loại Engineering. Các bước:
🔗 Các bước:
❶ Chọn toàn bộ bảng dữ liệu.
❷ Vào tab Home (Trang chủ).
❸ Chọn Format as a Table (Định dạng thành bảng).
❹ Chọn bất kỳ kiểu bảng nào bạn thích.
Bảng dữ liệu sẽ trông như sau:
❺ Nhấp biểu tượng Filter dưới tiêu đề cột Department.
❻ Từ menu bật lên, chỉ tích vào Engineering.
❼ Nhấp OK.
Kết quả sẽ như sau:
Lưu ý quan trọng
📌 Hàm VLOOKUP không thể lấy hơn một giá trị đơn lẻ.
📌 Chú ý cú pháp các hàm.
📌 Nhập phạm vi dữ liệu cẩn thận vào công thức.
Kết luận
Tóm lại, chúng ta đã thảo luận 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 file thực hành đính kèm và thử tất cả. Đừng ngần ngại hỏi đáp trong bình luận. Chúng tôi sẽ phản hồi sớm nhất.
Các bài viết liên quan
- Cách thực hiện VLOOKUP với nhiều hàng trong Excel (5 phương pháp)