Thời gian đọc ước tính: 15 phút
Những điểm chính
- XLOOKUP là hàm tìm kiếm mạnh mẽ thay thế cho VLOOKUP, HLOOKUP và INDEX/MATCH trong Excel.
- Hỗ trợ tìm kiếm từ phải sang trái, tìm kiếm hai chiều và trả về mảng dữ liệu động.
- Cung cấp các tùy chọn mặc định cho giá trị không tìm thấy (xử lý lỗi #N/A).
- Cho phép tìm kiếm từ dưới lên trên và sử dụng ký tự đại diện linh hoạt.
- Chỉ khả dụng trên Microsoft 365 và các phiên bản Excel mới nhất (Excel 2021 trở lên).
Nếu bạn đã quen thuộc với việc sử dụng VLOOKUP hoặc tổ hợp INDEX/MATCH để xử lý dữ liệu, sự xuất hiện của hàm XLOOKUP sẽ mang lại hiệu suất vượt trội cho quy trình làm việc của bạn. Microsoft đã phát triển XLOOKUP như một giải pháp hiện đại, khắc phục hầu hết các hạn chế của các hàm tìm kiếm cũ.
Trong bài viết chuyên sâu này, chúng ta sẽ phân tích toàn bộ cú pháp, cách hoạt động và đi qua 11 ví dụ thực tế để bạn làm chủ công cụ này.
XLOOKUP Là Gì?
XLOOKUP là một hàm mới được giới thiệu trong Office 365, được xem là phiên bản nâng cấp toàn diện và thay thế cho hàm VLOOKUP và HLOOKUP truyền thống.
Chức năng cốt lõi của XLOOKUP là cho phép người dùng tìm kiếm một giá trị trong một mảng (dọc hoặc ngang) và trả về giá trị tương ứng từ một hàng hoặc cột khác. Ví dụ, nếu bạn có danh sách điểm thi, bạn có thể dùng XLOOKUP để truy xuất điểm số dựa trên tên học sinh một cách chính xác.
Điểm khác biệt lớn nhất là XLOOKUP tách biệt cột tìm kiếm và cột kết quả, giúp công thức linh hoạt và ít bị lỗi hơn khi cấu trúc bảng tính thay đổi.
Làm Thế Nào Để Sử Dụng XLOOKUP?
Tại thời điểm hiện tại, XLOOKUP chỉ khả dụng cho người dùng đăng ký Microsoft 365 (trước đây là Office 365) và Excel 2021. Nếu bạn đang sử dụng Excel 2010, 2013, 2016 hoặc 2019 bản vĩnh viễn cũ, hàm này sẽ không hoạt động.
Để kiểm tra xem bạn có quyền truy cập hay không:
- Mở Excel, chọn thẻ File > Account.
- Nếu bạn đang dùng Microsoft 365 nhưng chưa thấy hàm này, hãy cập nhật lên phiên bản mới nhất hoặc tham gia chương trình Office Insider.
Tùy chọn Office Insider trong phần Tài khoản Excel
Hàm này cũng hoạt động tốt trên Excel for Mac (phiên bản 365) và Excel phiên bản Web.
Cú Pháp Hàm XLOOKUP
Cú pháp của XLOOKUP bao gồm 6 đối số, trong đó 3 đối số đầu là bắt buộc và 3 đối số sau là tùy chọn.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Cú pháp chi tiết của hàm XLOOKUP trong Excel
Giải thích chi tiết các đối số:
- lookup_value (Bắt buộc): Giá trị bạn muốn tìm kiếm (ví dụ: tên nhân viên, mã sản phẩm).
- lookup_array (Bắt buộc): Mảng hoặc dải ô chứa giá trị cần tìm.
- return_array (Bắt buộc): Mảng hoặc dải ô chứa giá trị kết quả bạn muốn lấy về.
- [if_not_found] (Tùy chọn): Giá trị trả về nếu không tìm thấy kết quả. Nếu bỏ trống, Excel sẽ trả về lỗi #N/A theo mặc định.
- [match_mode] (Tùy chọn): Xác định kiểu khớp dữ liệu:
0: Khớp chính xác (Mặc định).-1: Khớp chính xác; nếu không thấy, trả về mục nhỏ hơn tiếp theo.1: Khớp chính xác; nếu không thấy, trả về mục lớn hơn tiếp theo.2: Khớp ký tự đại diện (sử dụng*,?,~).
- [search_mode] (Tùy chọn): Xác định hướng tìm kiếm:
1: Tìm từ mục đầu tiên đến cuối cùng (Mặc định).-1: Tìm từ mục cuối cùng lên đầu tiên.2: Tìm kiếm nhị phân (dữ liệu phải sắp xếp tăng dần).-2: Tìm kiếm nhị phân (dữ liệu phải sắp xếp giảm dần).
11 Ví Dụ Thực Tế Về Hàm XLOOKUP
Để hiểu rõ sức mạnh của XLOOKUP, chúng ta sẽ đi qua các kịch bản sử dụng từ cơ bản đến nâng cao. Bạn có thể áp dụng ngay các công thức này vào công việc hàng ngày.
Ví dụ 1: Tra Cứu Giá Trị Cơ Bản
Giả sử bạn có một danh sách học sinh và điểm số. Bạn muốn tìm điểm môn Toán của học sinh tên “Greg”.
Dữ liệu bao gồm: Cột A là Tên, Cột B là điểm Toán.
Công thức sử dụng:
=XLOOKUP(F2, A2:A15, B2:B15)
Sử dụng hàm XLOOKUP để tra cứu điểm số học sinh theo tên
Trong công thức trên:
F2: Chứa tên “Greg” (giá trị tìm kiếm).A2:A15: Cột chứa danh sách tên.B2:B15: Cột chứa điểm số tương ứng.
Sự khác biệt rõ rệt so với VLOOKUP là bạn chọn hai dải ô riêng biệt (lookup_array và return_array) thay vì chọn toàn bộ bảng rồi đếm số cột. Điều này giúp XLOOKUP không bị lỗi khi bạn chèn thêm cột vào giữa bảng dữ liệu.
Ví dụ 2: Tra Cứu Về Phía Bên Trái (Left Lookup)
VLOOKUP có một điểm yếu “chí mạng”: nó chỉ có thể tìm kiếm từ trái sang phải. Nếu cột kết quả nằm bên trái cột tìm kiếm, bạn buộc phải dùng INDEX/MATCH. XLOOKUP đã loại bỏ hoàn toàn hạn chế này.
Xét ví dụ: Tên học sinh nằm ở Cột D, nhưng điểm Toán lại nằm ở Cột A.
Công thức để lấy điểm của Greg:
=XLOOKUP(F2, D2:D15, A2:A15)
Hàm XLOOKUP lấy giá trị từ cột bên trái vùng tìm kiếm
Cơ chế hoạt động vẫn giữ nguyên: Tìm trong cột D và trả về giá trị tương ứng ở cột A. Thứ tự cột không còn quan trọng với XLOOKUP.
Ví dụ 3: Trả Về Toàn Bộ Bản Ghi (Mảng Động)
Bạn không cần viết nhiều công thức để lấy thông tin từ nhiều cột. XLOOKUP có thể trả về một mảng giá trị (Spill Array).
Ví dụ: Bạn muốn lấy điểm số của Greg cho cả 3 môn: Toán (Cột B), Lý (Cột C), Hóa (Cột D).
Công thức:
=XLOOKUP(F2, A2:A15, B2:D15)
Công thức XLOOKUP trả về toàn bộ hàng dữ liệu cùng lúc
Chỉ cần nhập công thức vào một ô (ví dụ G2), kết quả sẽ tự động “tràn” sang các ô H2 và I2. Lưu ý quan trọng: Bạn phải đảm bảo các ô đích (H2, I2) đang trống, nếu không Excel sẽ báo lỗi #SPILL!.
Ví dụ 4: Tra Cứu Hai Chiều (Kết Hợp Dọc Và Ngang)
Trước đây, để tìm một giá trị dựa trên giao điểm của hàng và cột (ví dụ: điểm của học sinh X môn Y), chúng ta thường dùng INDEX(MATCH, MATCH). Với XLOOKUP, bạn có thể lồng hai hàm vào nhau trực quan hơn.
Bài toán: Tìm điểm của “Greg” (hàng dọc) môn “Math” (hàng ngang).
Công thức:
=XLOOKUP(G1, B1:D1, XLOOKUP(F2, A2:A15, B2:D15))
Tra cứu hai chiều kết hợp hàng và cột bằng XLOOKUP
Phân tích:
- Hàm bên trong
XLOOKUP(F2, A2:A15, B2:D15)sẽ trả về một mảng điểm số của Greg:{21, 94, 81}. - Hàm bên ngoài dùng mảng này làm
return_array. Nó tìm môn “Math” trongB1:D1và lấy giá trị tương ứng từ mảng điểm của Greg.
Ví dụ 5: Xử Lý Lỗi Khi Không Tìm Thấy Giá Trị
Thay vì phải bọc công thức trong hàm IFERROR như trước đây, XLOOKUP tích hợp sẵn đối số thứ 4 [if_not_found].
Nếu bạn tìm tên một học sinh không có trong danh sách và muốn hiển thị “Không dự thi” thay vì lỗi #N/A:
=XLOOKUP(F2, A2:A15, B2:B15, "Không dự thi")
Xử lý lỗi trực tiếp trong XLOOKUP với tham số if_not_found
Tính năng này giúp công thức gọn gàng hơn và giảm tải việc tính toán lặp lại so với việc dùng IFERROR(VLOOKUP(...), "...").
Ví dụ 6: XLOOKUP Lồng Nhau (Tìm Kiếm Trên Nhiều Bảng)
Giả sử bạn có hai danh sách dữ liệu riêng biệt. Bạn muốn tìm kiếm tên nhân viên trong Bảng 1, nếu không thấy thì tìm tiếp ở Bảng 2.
Chúng ta tận dụng đối số [if_not_found] để lồng thêm một hàm XLOOKUP khác:
=XLOOKUP(A12, A2:A8, B2:B8, XLOOKUP(A12, F2:F8, G2:G8))
Công thức XLOOKUP lồng nhau để tìm kiếm trên nhiều bảng
Công thức này sẽ quét Bảng 1 (A2:B8) trước. Nếu không tìm thấy kết quả, nó sẽ tự động thực thi hàm XLOOKUP thứ hai để quét Bảng 2 (F2:G8).
Ví dụ 7: Tìm Giá Trị Xuất Hiện Cuối Cùng
Đây là một tính năng “đắt giá”. VLOOKUP luôn dừng lại ở giá trị đầu tiên nó tìm thấy. Để tìm giá trị cuối cùng, trước đây người dùng phải dùng các mẹo phức tạp. XLOOKUP giải quyết việc này bằng đối số [search_mode].
Bài toán: Tìm ngày tuyển dụng của nhân viên được tuyển gần đây nhất trong bộ phận Bán hàng.
Công thức:
=XLOOKUP(F1, $B$2:$B$15, $C$2:$C$15, , , -1)
XLOOKUP tìm giá trị cuối cùng trong danh sách từ dưới lên
Tham số -1 ở cuối cùng chỉ định Excel tìm kiếm từ dưới lên trên (Bottom-to-Top). Nhờ đó, nó sẽ bắt gặp nhân viên cuối cùng trong danh sách trước.
Ví dụ 8: Tìm Kiếm Gần Đúng (Tính Thuế/Hoa Hồng)
Khi tính thuế suất hoặc hoa hồng dựa trên doanh số, chúng ta thường cần tìm một giá trị gần đúng (nhỏ hơn hoặc bằng giá trị tìm kiếm).
Ví dụ: Doanh số là 2500. Các mốc hoa hồng là 1000 (10%), 2000 (20%), 3000 (30%).
Công thức:
=XLOOKUP(B2, $E$2:$E$6, $F$2:$F$6, 0, -1) * B2
XLOOKUP tìm kiếm gần đúng để tính hoa hồng
Ở đây, đối số [match_mode] là -1.
- Nó tìm kiếm chính xác giá trị 2500.
- Nếu không thấy, nó lùi về giá trị nhỏ hơn gần nhất (2000) và trả về mức hoa hồng tương ứng.
- Lợi thế: Dữ liệu không cần phải sắp xếp như VLOOKUP yêu cầu.
Ví dụ 9: Tra Cứu Theo Hàng Ngang (Thay Thế HLOOKUP)
XLOOKUP hoạt động linh hoạt theo cả hai chiều. Nếu dữ liệu của bạn được bố trí theo hàng ngang (Tiêu đề ở cột A, dữ liệu trải dài sang phải), công thức vẫn tương tự.
=XLOOKUP(B7, B1:O1, B2:O2)
Công thức XLOOKUP thay thế cho HLOOKUP để tra cứu ngang
Bạn không cần học thêm hàm mới, chỉ cần thay đổi vùng chọn lookup_array và return_array thành hàng ngang.
Ví dụ 10: Tra Cứu Có Điều Kiện Phức Tạp
Bạn có thể kết hợp XLOOKUP với các hàm khác như MAX, COUNTIF để giải quyết các bài toán khó.
Bài toán:
- Tìm tên học sinh có điểm cao nhất môn Toán.
- Đếm số lượng học sinh có điểm trên 80.
Tìm người điểm cao nhất:
=XLOOKUP(MAX(XLOOKUP(G1, $B$1:$D$1, $B$2:$D$15)), XLOOKUP(G1, $B$1:$D$1, $B$2:$D$15), $A$2:$A$15)Giải thích: Hàm XLOOKUP bên trong lấy toàn bộ cột điểm môn Toán. Hàm MAX tìm điểm cao nhất. Sau đó XLOOKUP ngoài cùng tìm điểm cao nhất đó thuộc về ai.
Đếm số người điểm > 80:
=COUNTIF(XLOOKUP(G1, $B$1:$D$1, $B$2:$D$15), ">80")
Kết hợp XLOOKUP với các hàm thống kê nâng cao
Ví dụ 11: Sử Dụng Ký Tự Đại Diện (Wildcard)
XLOOKUP hỗ trợ các ký tự đại diện như dấu sao * (chuỗi ký tự bất kỳ) và dấu hỏi ? (một ký tự đơn). Tuy nhiên, bạn phải kích hoạt chế độ này bằng cách đặt match_mode là 2.
Bài toán: Tìm vốn hóa thị trường của công ty có tên chứa chữ “Apple” (ví dụ trong bảng là “Apple Inc.”).
Công thức:
=XLOOKUP("*"&D2&"*", $A$2:$A$11, $B$2:$B$11, , 2)
Sử dụng ký tự đại diện trong XLOOKUP để tìm kiếm gần đúng chuỗi
Nếu không đặt đối số thứ 5 là 2, Excel sẽ coi các ký tự * là văn bản thông thường và trả về lỗi.
Lưu Ý Về Tính Tương Thích Ngược
Một điều cực kỳ quan trọng cần ghi nhớ: XLOOKUP không có tính tương thích ngược.
Nếu bạn tạo một bảng tính sử dụng XLOOKUP và gửi cho đồng nghiệp đang dùng Excel 2016 hoặc 2019 (không phải 365), họ sẽ thấy lỗi #NAME? thay vì kết quả.
Vì vậy, nếu bạn làm việc trong môi trường hỗn hợp nhiều phiên bản Excel, giải pháp an toàn vẫn là sử dụng INDEX/MATCH hoặc VLOOKUP để đảm bảo mọi người đều đọc được dữ liệu.
Câu Hỏi Thường Gặp (FAQ)
1. XLOOKUP có miễn phí không?
Không hoàn toàn. Nó đi kèm với gói đăng ký Microsoft 365 hoặc phiên bản Excel 2021 trở lên.
2. Tôi có thể dùng XLOOKUP để tìm kiếm phân biệt chữ hoa/thường không?
Mặc định thì không. Tuy nhiên, bạn có thể kết hợp XLOOKUP với hàm EXACT để thực hiện việc này.
3. Tại sao tôi nhận được lỗi #SPILL?
Lỗi này xuất hiện khi bạn dùng XLOOKUP trả về mảng dữ liệu (nhiều ô), nhưng không gian xung quanh ô chứa công thức không đủ chỗ trống (có dữ liệu khác chặn đường). Hãy xóa các ô bị chắn để công thức hiển thị đủ.
4. XLOOKUP có nhanh hơn VLOOKUP không?
Trong các tập dữ liệu lớn, XLOOKUP thường hoạt động nhanh hơn vì nó chỉ xử lý hai mảng dữ liệu được chỉ định (Lookup và Return) thay vì tải toàn bộ bảng như VLOOKUP. Hơn nữa, chế độ tìm kiếm nhị phân (Binary Search) của XLOOKUP cực kỳ tốc độ trên dữ liệu đã sắp xếp.
