Ví dụ trong bài được thực hiện trên phiên bản Excel đã được đanh ký chương trình Insider. Rất tiếc, hàm XLOOKUP chưa khả dụng cho người dùng chạy các phiên bản Excel 2010, 2013, 2016 và 2019.

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

Hàm XLOOKUP hỗ trợ tối đa 6 đối số và đây là giá trị của chúng.

1. lookup_value (bắt buộc): Giá trị mà bạn muốn tìm.

2. lookup_array (bắt buộc): Mảng mà bạn muốn tìm kiếm giá trị tra cứu.

3. return_array (bắt buộc): Mảng từ nơi bạn muốn tìm nạp và trả về các giá trị khi giá trị tra cứu được tìm thấy.

4. [if_not_found] (tùy chọn): Khi không tìm thấy kết quả phù hợp, đây là giá trị được trả về.

5. [match_mode] (tùy chọn): Đối số này cho phép bạn chỉ định loại đối sánh mà mình đang tìm kiếm. Có các giá trị khác nhau để chỉ định nó:

• 0: Tìm kiếm một kết quả khớp chính xác và giá trị phải khớp chính xác với giá trị trong lookup_array. Giá trị này cũng được đặt làm mặc định khi không được đề cập.
• -1: Tìm kiếm kết quả phù hợp chính xác và sau khi được tìm thấy, sẽ trở về giá trị nhỏ hơn tiếp theo.
• 1: Tìm kiếm kết quả phù hợp chính xác và khi được tìm thấy sẽ trả về giá trị lớn hơn tiếp theo.
• 2: Thực hiện đối sánh từng phần bằng cách sử dụng các ký tự đại diện trong đó *, ? và ~ có ý nghĩa đặc biệt.

6. [search_mode] (tùy chọn): Được sử dụng để chỉ định chế độ tìm kiếm của XLOOKUP trong lookup_array. Có các giá trị khác nhau để chỉ định giống nhau:

• 1: Thực hiện tìm kiếm bắt đầu từ mục đầu tiên. Giá trị này được đặt làm mặc định khi không có gì được chỉ định.
• -1: Thực hiện tìm kiếm ngược lại bắt đầu từ mục cuối cùng.
• 2: Thực hiện tìm kiếm nhị phân trong lookup_array nơi dữ liệu cần sắp xếp theo thứ tự tăng dần. Nếu dữ liệu không được sắp xếp, nó có thể tạo ra lỗi hoặc kết quả sai.
• -2: Thực hiện tìm kiếm nhị phân trong lookup_array nơi dữ liệu cần sắp xếp theo thứ tự giảm dần. Nếu dữ liệu không được sắp xếp, nó có thể tạo ra lỗi hoặc kết quả sai.

4. Ưu điểm và nhược điểm của hàm XLOOKUP trong Excel

XLOOKUP vẫn là một hàm có lợi thế hơn so với VLOOKUP và INDEX/ ATCH. Nhưng nó cũng có nhược điểm riêng.

4.1. Ưu điểm của hàm XLOOKUP

• Hàm hoạt động theo cả chiều dọc và chiều ngang.
• Chỉ cần 3 đối số, thay vì 4 đối số như trong hàm VLOOKUP và INDEX MATCH.
• Luôn luôn mặc định là kết quả khớp hoàn toàn.
• Có thể thực hiện tra cứu từng phần bằng cách sử dụng ký tự đại diện.
• Có thể thực hiện tra cứu theo thứ tự giảm dần.
• Sử dụng một hàm thay vì hai trong INDEX MATCH.

4.2. Nhược điểm của hàm XLOOKUP

• Các đối số tùy chọn có thể trông phức tạp đối với người mới bắt đầu.
• 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.
• Trả về lỗi khi tra cứu và trả về mảng không có cùng độ dài.
• Cần nhớ cả phạm vi tra cứu và trả về.

5. Cách sử dụng hàm XLOOKUP

Hãy xem ví dụ dưới đây để hiểu cách XLOOKUP hoạt động. Trong ví dụ này, chúng ta cần trả lại bộ phận từ cột F cho mỗi ID ở cột A.

Đây là ví dụ tìm kiếm kết quả chính xác, nhưng hàm XLOOKUP chỉ yêu cầu ba loại thông tin.

Hình ảnh bên dưới hiển thị XLOOKUP với 5 đối số, nhưng ba đối số đầu tiên cần cho kết quả chính xác. Vì vậy hãy tập trung vào chúng:

• Lookup_value: Giá trị tìm kiếm
• Lookup_array: Phạm vi tìm kiếm
• Return_array: Phạm vi chứa giá trị cần trả về

Trong ví dụ này, chúng ta sẽ sử dụng công thức:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

Tuy nhiên, đã xảy ra lỗi trong ô C7, đáng lẽ phải trả lại chiết khấu 0% vì chỉ chi 64, không đạt được tiêu chí để nhận chiết khấu.

Một ưu điểm khác của hàm XLOOKUP là nó không yêu cầu phạm vi tìm kiếm theo thứ tự tăng dần như VLOOKUP.

Nhập một hàng mới ở cuối bảng tra cứu và sau đó mở công thức. Mở rộng phạm vi sử dụng bằng cách click và kéo các góc.

Công thức ngay lập tức sửa lỗi. Nó không có vấn đề với việc có 0 ở cuối bảng phạm vi.

5.5. XLOOKUP thay thế hàm HLOOKUP

Như đã đề cập, hàm XLOOKUP cũng có thể thay thế hàm HLOOKUP. Một hàm có thể thay thế hai hàm, còn gì tuyệt vời hơn.

Hàm HLOOKUP tra cứu theo chiều ngang, được sử dụng để tìm kiếm theo hàng.

Hàm này không nổi tiếng như VLOOKUP nhưng hữu ích trong trường hợp khi tiêu đề nằm ở cột A và dữ liệu ở hàng 4, 5 như ví dụ bên dưới.

XLOOKUP có thể tìm kiếm ở cả hai chiều, dưới cột và dọc theo hàng.

Trong ví dụ này, công thức được sử dụng để trả về giá trị bán hàng liên quan đến tên trong cột A2. Nó nhìn theo hàng 4 để tìm tên và trả lại giá trị từ hàng 5:

5.6. XLOOKUP có thể nhìn từ dưới lên

Thông thường bạn cần nhìn từ trên xuống dưới trong một danh sách để tìm sự hiện diện đầu tiên của một giá trị. XLOOKUP có một đối số thứ năm có tên search_mode. Đối số này cho phép chuyển hướng tìm kiếm để bắt đầu từ dưới lên trong một danh sách để tìm sự hiện diện cuối cùng của một giá trị.

Trong ví dụ bên dưới, chúng ta cần tìm mức tồn kho của từng sản phẩm trong cột A.

Bảng tra cứu được sắp xếp theo thứ tự ngày tháng. Chúng ta muốn trả lại mức tồn kho từ lần cuối cùng kiểm tra (lần xuất hiện cuối cùng của Product ID).

Đối số thứ năm của hàm XLOOKUP cung cấp bốn tùy chọn. Ở đây chúng ta sẽ sử dụng tùy chọn Search last-to-first.

Công thức sử dụng trong ví dụ này:

Trong công thức này, đối số thứ tư bị bỏ qua. Nó là tùy chọn và chúng ta muốn mặc định khớp tuyệt đối.

Hàm XLOOKUP là sự kế thừa được mong đọi cho cả hai hàm VLOOKUP và HLOOKUP. Một loạt các ví dụ đã được sử dụng trong bài viết này để chứng minh những ưu điểm của hàm XLOOKUP.

XLOOKUP là một cải tiến tuyệt vời khi nói đến các chức năng tra cứu. Hạn chế duy nhất của hàm XLOOKUP là nó không tương thích ngược. Nếu chia sẻ bảng tính với các phiên bản Microsoft Office Suite cũ hơn, bạn có thể gặp lỗi.

Tuy nhiên, bạn có thể truy cập XLOOKUP từ Office 365 Online. XLOOKUP vẫn đang trong quá trình phát triển, vì vậy người dùng Excel sẽ mất một thời gian để áp dụng nó.

Cùng tìm hiểu thêm về hàm XLOOKUP tại video dưới đây nhé:

Trên đây là bài viết chia sẻ cho bạn cách dùng hàm XLOOKUP trong Excel. Mong rằng những thông tin này sẽ giúp ích cho bạn trong việc hiểu và sử dụng hàm XLOOKUP cho công việc của mình.