Hàm XLOOKUP trong Excel là một công cụ tìm kiếm mạnh mẽ và linh hoạt, được ra đời vào năm 2019, như một giải pháp thay thế cho các hàm tìm kiếm trước đây như VLOOKUP và HLOOKUP.

Với XLOOKUP, bạn có thể tìm kiếm dữ liệu theo cả chiều ngang và chiều dọc, không giống như VLOOKUP chỉ tìm kiếm theo chiều dọc và HLOOKUP chỉ tìm kiếm theo chiều ngang. Hơn nữa, XLOOKUP cung cấp nhiều tùy chọn khớp và tìm kiếm, cho phép bạn tùy chỉnh cách thức tìm kiếm để phù hợp với nhu cầu cụ thể của bạn.

Tuy nhiên, hàm XLOOKUP cũng có nhược điểm. Các đối số tùy chọn có thể trở nên phức tạp đối với người mới bắt đầu. Nó có thể tốn nhiều thời gian hơn khi chọn hai phạm vi và có quá nhiều ô trong bảng tính. Ngoài ra, nó trả về lỗi khi tra cứu và trả về mảng không có cùng độ dài.

Dù vậy, với những ưu điểm nổi bật, hàm XLOOKUP chắc chắn sẽ trở thành một công cụ không thể thiếu trong bộ công cụ của bất kỳ người dùng Excel nào. Hãy cùng tìm hiểu tổng quan về hàm XLOOKUP.

Cú nháp cơ bản của hàm XLOOKUP:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Trong đó:

lookup_value: Giá trị cần tìm kiếm.
lookup_array: Mảng hoặc phạm vi cần tìm kiếm.
return_array: Mảng hoặc phạm vi cần trả về.
if_not_found (tùy chọn): Trong trường hợp không tìm thấy kết quả khớp hợp lệ, trả về văn bản [if_not_found] mà bạn cung cấp. Nếu không tìm thấy kết quả khớp hợp lệ và [if_not_found] bị thiếu, thì kết quả #N/A được trả về.
match_mode (tùy chọn): Xác định kiểu khớp:
- [0] - Kết quả khớp chính xác. Nếu không tìm thấy, hãy trả #N/A. Đây là tùy chọn mặc định.
- [-1] – Kết quả chính xác. Nếu không tìm thấy, hãy trả về mục nhỏ tiếp theo.
- [1] - Trận đấu chính xác. Nếu không tìm thấy, hãy trả về mục lớn hơn tiếp theo.
- [2] - Một kết quả khớp ký tự đại diện trong đó *, ?, và ~ có ý nghĩa đặc biệt.
search_mode (tùy chọn): Chỉ định chế độ tìm kiếm cần sử dụng:
- [1] - Thực hiện tìm kiếm bắt đầu từ mục đầu tiên. Đây là tùy chọn mặc định.
- [-1] - Thực hiện tìm kiếm đảo ngược bắt đầu từ mục cuối cùng.
- [2] - Thực hiện tìm kiếm nhị phân dựa trên lookup_array sắp xếp theo thứ tự tăng dần . Nếu không được sắp xếp, kết quả không hợp lệ sẽ được trả về.
- [-2] - Thực hiện một tìm kiếm nhị phân dựa trên lookup_array được sắp xếp theo thứ tự giảm dần . Nếu không được sắp xếp, kết quả không hợp lệ sẽ được trả về.

1. Tìm kiếm Cơ Bản

Hàm XLOOKUP cơ bản nhất có dạng cú pháp:

=XLOOKUP (lookup_value, lookup_array, return_array)

Trong đó:

lookup_value: là giá trị cần tìm.
lookup_array: là mảng chứa các giá trị để tìm kiếm.
return_array: là mảng chứa các giá trị sẽ được trả về.

Ví dụ: tìm kiếm mã nhân viên “NV005” là tên gì?

Công thức tại ô I4:

=XLOOKUP("NV005",B2:B11,C2:C11)

Kết quả trả về: TRƯƠNG LÊ T

2. Tìm kiếm với giá trị không tìm thấy

Nếu ‘lookup_value’ không có trong ‘lookup_array’, XLOOKUP sẽ trả về ‘#N/A’. Tuy nhiên, bạn có thể chỉ định một giá trị mặc định để trả về khi không tìm thấy bằng cách thêm ‘if_not_found’.

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Ví dụ: tìm kiếm mã nhân viên “NV100” tên gì?

Công thức tại ô I5:

=XLOOKUP("NV100", B2:B11, C2:C11, "Không tìm thấy")

Kết quả trả về: “Không tìm thấy”

3. Tìm kiếm Với Hướng Ngược Lại

Giả sử trong trong Data có nhiều dữ liệu bị trùng lắp, như là một mã nhân viên có 2 người tồn tại, để lấy được giá trị từ dưới lên, ta sử dụng ‘search_mode’= -1, để có thể duyệt danh sách từ dưới lên.

Ví dụ: tìm mã ‘NV005’ từ cuối danh sách:

Công thức tại ô I6:

=XLOOKUP("NV005", B2:B12, C2:C12,,,-1)

Kết quả trả về: PHẠM HỮU T

4. Tìm kiếm Với Mảng Trả Về Nhiều Cột

Hàm XLOOKUP không chỉ có thể trả về một giá trị, mà còn có thể trả về một hàng hoặc cột giá trị. Điều này rất hữu ích khi bạn muốn trích xuất nhiều thông tin liên quan từ một bảng dữ liệu:

Ví dụ: trích xuất toàn thông tin về họ tên, ngày sinh, đơn vị, thu nhập của mã nhân viên “NV005”

Công thức ô I6:

=XLOOKUP("NV005", B3:B13, C3:F13, "Không tìm thấy")

Kết quả trả về: ‘TRƯƠNG LÊ T’ ‘36851’ ‘Phòng hành chính’ ‘48340035’

5. Tìm kiếm Dựa trên Điều Kiện

Hàm XLOOKUP có thể kết hợp với các hàm khác để tạo ra các điều kiện tìm kiếm phức tạp. Ví dụ, bạn có thể sử dụng hàm IF để tạo ra một ‘lookup_array’ dựa trên điều kiện.

Ví dụ: Tìm nhân viên đầu tiên trong danh sách có mức lương trên 35.000.000

Công thức ô I8:

=XLOOKUP(TRUE, IF((F2:F11)>35000000, TRUE, FALSE), C2:E11, "Không tìm thấy")

Kết quả trả về: ‘TRƯƠNG LÊ T’ ‘36851’ ‘Phòng hành chính’

6. Tìm kiếm và xuất ra cột cần thiết

Ví dụ: Tìm tên và phòng ban nhân viên đầu tiên có trong danh sách có mức lương trên 35.000.000. Ở đây, ta dùng hàm choose để tạo mảng, cụ thể là 2 mảng cho họ tên và đơn vị.

Công thức ô I9:

=XLOOKUP(TRUE,IF((F2:F11)>35000000, TRUE, FALSE), CHOOSE({1,2},C2:C11,E2:E11),"Không tìm thấy")

Kết quả trả về: ‘TRƯƠNG LÊ T’ ‘Phòng hành chính’

Trên đây là một số ví dụ ứng dụng của hàm XLOOKUP. Với những khả năng linh hoạt và mạnh mẽ của mình, hàm XLOOKUP đã trở thành một công cụ không thể thiếu trong việc xử lý dữ liệu trong Excel. Hãy khám phá và tận dụng tối đa những khả năng của hàm XLOOKUP trong công việc của bạn!