Cách sử dụng hàm XLOOKUP trong Excel

hoc tin hoc cap toc o thanh hoa

Hàm xlookup sẽ khắc phục những hạn chế của hàm vlookup và có một số các chức năng khác. Vậy bạn đã biết cách sử dụng hàm này chưa?

Hôm nay hãy cùng kế toán ATC tìm hiểu về hàm xlookup này nhé!

học tin học ở thanh hóa

XLOOKUP là gì?

Hàm XLOOKUP mới là giải pháp cho một số hạn chế lớn của VLOOKUP. Ngoài ra nó còn thay thế hàm HLOOKUP. Ví dụ, XLOOKUP có thể nhìn sang trái, mặc định tìm kết quả chính xác và cho phép chỉ định phạm vi ô thay vì số cột. VLOOKUP không dễ sử dụng và không linh hoạt.

Nói cách khác, hàm XLOOKUP cho phép bạn nhanh chóng tìm một giá trị trong một tập dữ liệu nhất định, theo cả chiều ngang và chiều dọc, sau đó trả về giá trị tương ứng của nó trong một hàng hoặc cột khác.

Có nhiều cách để sử dụng hàm XLOOKUP một cách tối đa.

hoc tin hoc cap toc o thanh hoa

Cách truy cập hàm XLOOKUP

Hiện nay, XLOOKUP chỉ có cho người dùng chương trình Insiders. Bất cứ ai tham gia chương trình Insider đều có thể truy cập tính năng Excel mới nhất khi nó có sẵn. Microsoft sẽ sớm tung hàm này cho tất cả người dùng Office 365. Rất tiếc, XLOOKUP không khả dụng cho người dùng chạy các phiên bản Office 2010, 2013, 2016 và 2019.

Nếu bạn không sử dụng bộ Microsoft 365 trên máy tính của mình, bạn có thể cần phải nâng cấp lên Microsoft 365 để có quyền truy cập vào chức năng XLOOKUP. Và nếu đang sử dụng Microsoft 365, thì bạn sẽ thấy tùy chọn đã được bật trong Excel. XLOOKUP cũng có sẵn trên Office 365 Online.

Cú pháp hàm XLOOKUP

Cú pháp của hàm XLOOKUP tương tự như cú pháp của hàm VLOOKUP và HLOOKUP. Nếu đã từng sử dụng chúng, bạn sẽ thấy việc sử dụng XLOOKUP tiện lợi hơn. Đây là cú pháp cho hàm XLOOKUP trong Excel:

=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.

lookup_value(bắt buộc): Giá trị mà bạn muốn tìm.
lookup_array(bắt buộc): Mảng mà bạn muốn tìm kiếm giá trị tra cứu.
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.
[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ề.
[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ó:

học tin học ở thanh hóa

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 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.

[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.

hoc tin hoc cap toc o thanh hoa

Ư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.

Ư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.

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ề.

Bây giờ chúng ta cùng khám phá một số ưu điểm của XLOOKUP so với VLOOKUP.

Không có thêm số chỉ mục cột

Đối số thứ ba của VLOOKUP là chỉ định số cột của thông tin sẽ trả về từ phạm vi bảng. Đây không còn là vấn đề với XLOOKUP vì hàm này cho phép người dùng chọn phạm vi trả về

Và đừng quên, XLOOKUP có thể xem dữ liệu còn lại của ô đã chọn, không giống như VLOOKUP.

Bạn cũng không gặp phải vấn đề lỗi công thức khi thêm cột mới. Nếu điều này xảy ra trong bảng tính, phạm vi trả về sẽ được điều chỉnh tự động.

học tin học ở thanh hóa

Khớp tuyệt đối theo mặc định

Khi sử dụng VLOOKUP, người dùng phải chỉ định khớp tuyệt đối nếu muốn. Nhưng với XLOOKUP mặc định là khớp tuyệt đối. Điều này giúp giảm đối số thứ tư và đảm bảo người mới dùng ít mắc lỗi hơn. Tóm lại, XLOOKUP yêu cầu ít câu hỏi hơn VLOOKUP và thân thiện với người dùng.

XLOOKUP có thể tìm đến bên trái

Khả năng chọn phạm vi tra cứu giúp XLOOKUP linh hoạt hơn so với VLOOKUP. Với XLOOKUP, thứ tự của các cột trong bảng không quan trọng.

VLOOKUP bị hạn chế bằng cách tìm kiếm cột ngoài cùng bên trái của bảng và sau đó trả về từ một số cột được chỉ định ở bên phải.

Sử dụng XLOOKUP để tìm kiếm phạm vi

Mặc dù không phổ biến như khớp tuyệt đối, nhưng bạn có thể sử dụng công thức tìm kiếm để tìm một giá trị trong phạm vi.

Bạn có thể thấy rằng XLOOKUP có thể tìm các kết quả gần đúng tốt hơn so với VLOOKUP.

Có tùy chọn để tìm kết quả khớp gần nhất nhỏ hơn (-1) hoặc gần nhất lớn hơn (1) giá trị tìm kiếm. Ngoài ra còn có một tùy chọn để sử dụng các ký tự đại diện (2), chẳng hạn như ? hoặc *. Cài đặt này không được bật theo mặc định như với VLOOKUP.

hoc tin hoc cap toc o thanh hoa

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.

học tin học tại thanh hóa

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ị.

Đố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.

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. 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ó.

Bạn muốn học thêm cách sử dụng nhiều hàm khác,mời bạn tham khảo khóa học của chúng tôi nhé!