Hàm XLOOKUP trong Excel đã từ lâu trở thành "ngôi sao mới" trong các hàm tìm kiếm và tham chiếu nhưng bạn đã thực sự nắm rõ cách sử dụng nó? Bài viết này HACOM sẽ đưa bạn đi sâu vào từ công thức đến những cách sử dụng nâng cao kèm theo các ví dụ minh họa trực quan để bạn có thể áp dụng ngay lập tức.
Hàm XLOOKUP trong Excel là gì?
Hàm XLOOKUP là một hàm tra cứu hiện đại được Microsoft ra mắt để thay thế các hàm truyền thống như VLOOKUP và HLOOKUP. Sở hữu khả năng tìm kiếm linh hoạt và hiện đại, XLOOKUP khắc phục hoàn toàn các hạn chế của các hàm cũ, xử lý dữ liệu đơn giản, chính xác và hiệu quả hơn.
Không giống VLOOKUP chỉ tra cứu từ trái sang phải, XLOOKUP cho phép tìm kiếm theo bất kỳ hướng nào – ngang, dọc hoặc thậm chí từ dưới lên, không phụ thuộc vào thứ tự cột, cho phép tùy chỉnh giá trị trả về khi không tìm thấy kết quả và cung cấp các chế độ tìm kiếm chính xác hoặc gần đúng. So với HLOOKUP, XLOOKUP vượt trội nhờ khả năng tra cứu sang trái và chỉ định phạm vi ô linh hoạt.
Công thức hàm XLOOKUP
Hàm XLOOKUP được sử dụng theo công thức sau: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).
Ý nghĩa chi tiết của từng tham số trong hàm:
- lookup_value (bắt buộc): Giá trị bạn muốn tìm kiếm trong dữ liệu. Ví dụ: Nếu bạn muốn tìm mã sản phẩm "SP001", thì "SP001" chính là lookup_value.
- lookup_array (bắt buộc): Mảng hoặc phạm vi ô chứa các giá trị để tìm kiếm, có thể là một hàng hoặc một cột tùy thuộc và cách sắp xếp dữ liệu.
- return_array (bắt buộc): Mảng hoặc phạm vi ô chứa các giá trị bạn muốn hàm trả về khi tìm thấy lookup_value.
- [if_not_found] (không bắt buộc): Giá trị trả về khi không tìm thấy lookup_value trong lookup_array.
- [match_mode] (không bắt buộc): Chỉ định cách hàm XLOOKUP đối sánh lookup_value với lookup_array. Các giá trị:
+ 0 (mặc định): Tìm kiếm chính xác, giá trị phải khớp hoàn toàn với lookup_array.
+ -1: Tìm kiếm chính xác; nếu không tìm thấy, trả về giá trị nhỏ hơn gần nhất.
+ 1: Tìm kiếm chính xác; nếu không tìm thấy, trả về giá trị lớn hơn gần nhất.
+ 2: Tìm kiếm từng phần, hỗ trợ ký tự đại diện như * (nhiều ký tự), ? (một ký tự), và ~ (ký tự đặc biệt).
- [search_mode] (không bắt buộc): Chỉ định thứ tự hoặc phương pháp tìm kiếm trong lookup_array. Các giá trị:
+ 1 (mặc định): Tìm kiếm từ đầu đến cuối (từ trên xuống hoặc trái sang phải).
+ - 1: Tìm kiếm ngược từ cuối đến đầu (từ dưới lên hoặc phải sang trái).
+ 2: Tìm kiếm nhị phân, yêu cầu lookup_array được sắp xếp tăng dần. Nếu không sắp xếp đúng, kết quả có thể sai.
+ - 2: Tìm kiếm nhị phân, yêu cầu lookup_array được sắp xếp giảm dần. Nếu không sắp xếp đúng, kết quả sẽ không chính xác.
Ví dụ minh họa sử dụng hàm XLOOKUP trong Excel
Để bạn dễ dàng nắm bắt cách áp dụng, dưới đây là các ví dụ cụ thể và minh họa chi tiết về cách sử dụng hàm XLOOKUP trong Excel:
Ví dụ 1: Tra cứu mã điện thoại quốc gia dựa trên tên quốc gia
Danh sách dưới đây là các quốc gia và mã số điện thoại quốc gia tương ứng.
Khi muốn tìm mã tương ứng tại ô F2 với với quốc gia ở ô E2, hàm XLOOKUP được sử dụng theo công thức sau: =XLOOKUP(E2,A2:A7,C2:C7).
Trong đó:
- lookup_value: Ô E2 chứa tên quốc gia cần tìm.
- lookup_array: Phạm vi A2:A7 chứa danh sách tên quốc gia.
- return_array: Phạm vi C2:C7 chứa danh sách tiền tố.
Không cần thiết lập tham số match_mode, bởi XLOOKUP tự động thực hiện tìm kiếm chính xác theo mặc định.
Ví dụ 2: Dựa vào ID nhân viên để tra cứu thông tin nhân viên và phòng ban
Bảng dữ liệu dưới đây có các cột ID nhân viên, tên nhân viên và phòng ban.
Ô A2 là ID nhân viên bạn đang muốn tra cứu thông tin nhân viên cũng như phòng ban tương ứng. Công thức hàm XLOOKUP trong Excel được áp dụng như sau: =XLOOKUP(A2,A6:A12,B6:C12).
Trong đó:
- lookup_value: Ô A2 chứa ID nhân viên cần tìm.
- lookup_array: Cột A6:B12 chứa danh sách ID nhân viên.
- return_array: Phạm vi B6:C12, có nghĩa là cả hai cột Tên nhân viên và Phòng ban.
Khác với VLOOKUP, vốn chỉ trả về dữ liệu từ một cột mỗi lần và nếu áp dụng với ví dụ này chúng ta cần thực hiện 2 công thức khác nhau. Nhưng với XLOOKUP, chỉ cần 1 công thức có thể trả về nhiều cột dữ liệu đồng thời.
Ví dụ 3: Thêm một if_not_found áp dụng xử lý khi không tìm thấy kết quả
Sử dụng tiếp ví dụ 2 tra cứu thông tin nhân viên ở phần trước nếu ID nhân viên nhập vào không có trong danh sách thay vì sẽ hiện lỗi #N/A, chúng ta có thể xử lý được thành “Không tìm thấy thông tin nhân viên”.
Tại ô B2, công thức hàm XLOOKUP trong Excel được áp dụng như sau: =XLOOKUP(A2,A6:A12,B6:C12,"Không tìm thấy thông tin nhân viên").
Tham số [if_not_found] được bổ sung với giá trị là “Không tìm thấy thông tin nhân viên”. Nếu ID nhân viên không tồn tại trong danh sách, công thức sẽ hiển thị thông báo “Không tìm thấy thông tin nhân viên” trông sẽ chuyên nghiệp hơn.
Ví dụ 4: Tìm kiếm thuế suất dựa trên mức thu nhập bằng chế độ khớp gần đúng
Dưới đây là một bảng thuế suất phải nộp tương ứng với thu nhập tối đa.
Ô D2 là mức thu nhập và bạn muốn tìm mức thuế suất tương ứng. Nếu không tìm thấy giá trị khớp chính xác tuyệt đối, bạn muốn hàm trả về mức thuế suất lớn hơn gần nhất. Công thức được áp dụng ở bảng như sau: =XLOOKUP(D2,B2:B8,A2:A8,0,1,1).
Trong đó:
- lookup_value: Ô D2 chứa thu nhập cần xác định thuế suất phải nộp.
- lookup_array: Phạm vi B2:B8 chứa mức thu nhập tối đa.
- return_array: Phạm vi A2:A8 chứa các mức thuế suất.
- if_not_found: Trả về 0 nếu không tìm thấy thu nhập phù hợp.
- match_mode = 1: Tìm kiếm chính xác; nếu không tìm thấy, trả về giá trị lớn hơn gần nhất.
- search_mode = 1 (mặc định): Tìm kiếm từ đầu đến cuối (từ trên xuống hoặc trái sang phải).
Trên đây là công thức, cách sử dụng cùng bốn ví dụ minh họa để bạn dễ dàng hình dung cách áp dụng hàm XLOOKUP trong Excel. Sự linh hoạt, dễ dùng và khả năng khắc phục hạn chế của các hàm tra cứu truyền thống, XLOOKUP sẽ là công cụ để xử lý dữ liệu hiệu quả.
