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

Hàm Xlookup trong Excel là gì? Cách dùng Xlookup trong Excel như thế nào? Hãy cùng Quantrimang.com tìm hiểu nhé!

Nếu thường xuyên sử dụng Excel, có thể bạn đã khá quen thuộc với các hàm tra cứu. Nếu chưa biết, bạn chỉ cần hiểu đơn giản nó là hàm Excel được dùng để tìm kiếm dữ liệu hay giá trị cụ thể trên hàng hoặc cột trong bảng tính.

Hàm này đặc biệt hữu ích khi bạn phải xử lý rất nhiều dữ liệu trong bảng tính Excel. Ở Excel, bạn có một số kiểu hàm LOOKUP. XLOOKUP là một trong số đó. Vậy hàm XLOOKUP là gì, công thức XLOOKUP thế nào và cách sử dụng ra sao? Hãy cùng Quantrimang.com tìm hiểu nhé!

Hàm XLOOKUP là gì trong Excel?

Về bản chất, XLOOKUP thay thế các hàm cũ như HLOOKUP, VLOOKUP và LOOKUP. Nó hỗ trợ tra cứu dữ liệu theo cả chiều dọc và ngang.

Nói cách khác, hàm XLOOKUP trong Excel giúp bạn tìm nhanh giá trị trong bộ dữ liệu được cung cấp, cả ngang và dọc. Sau đó, nó trả về giá trị tương ứng theo hàng và cột khác nhau.

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

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:

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.

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

Lợi ích khi dùng hàm XLOOKUP Excel

• XLOOKUP khiến công thức được dùng nhiều nhất trong Excel trở nên đơn giản và ít lỗi hơn. Bạn chỉ cần viết =XLOOKUP (dữ liệu muốn tìm, danh sách, danh sách kết quả ) và bạn sẽ nhận được câu trả lời hoặc #N/A nếu không tìm thấy giá trị.
• Mặc định tra cứu kết quả chính xác. Một trong số những bất tiện của VLOOKUP là bạn phải nhắc tới FALSE là tham số cuối cùng để lấy kết quả chính xác. XLOOKUP khắc phục điều đó bằng cách mặc định thực hiện đối sánh chính xác. Bạn có thể dùng tham số match mode để thay đổi hành vi tra cứu nếu muốn.
• Tham số thứ tư hỗ trợ trường hợp không tìm thấy giá trị. Trong hầu hết tình huống kinh doanh, bạn buộc phải “gói” công thức tra cứu với IFERROR hoặc IFNA để loại bỏ lỗi. XLOOKUP cung cấp tham số thứ 4 để bạn có thể biết kết quả mặc định mong muốn nếu không tìm thấy giá trị.
• XLOOKUP cung cấp các tham số lựa chọn để tìm tình huống đặc biệt. Bạn có thể tìm từ trên hoặc phía dưới, theo ký tự đặc biệt và các lựa chọn nhanh hơn để tìm dữ liệu được lọc.

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,$F$2:$F$8)

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ề. (cột F trong ví dụ này).

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.

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.

Trong ví dụ dưới đây, chúng ta cần tìm kiếm ID (cột E) và trả về tên người (cột D).

Sử dụng công thức dưới đây:

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

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. Ví dụ, chúng ta muốn trả lại chiết khấu phụ thuộc vào số tiền chi.

Lần này chúng ta không tìm một giá trị cụ thể và cần biết các giá trị trong cột B nằm trong phạm vi nào trong cột E để xác định mức chiết khấu.

XLOOKUP có một đối số thứ tư tùy chọn (hãy nhớ, nó mặc định là khớp tuyệt đối) được gọi là match_mode.

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.

Công thức trong ví dụ này trả về giá trị gần nhất nhỏ hơn giá trị tìm kiếm nếu không tìm thấy kết quả khớp tuyệt đối:

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

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:

=XLOOKUP(A2,B4:E4,B5:E5)

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:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

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

Chúc các bạn thực hiện thành công!

Sự khác biệt giữa XLOOKUP và VLOOKUP

Tại sao XLOOKUP tốt hơn VLOOKUP?

Dù VLOOKUP có cách dùng riêng nhưng nó hạn chế tính năng hơn XLOOKUP. Điều này khiến XLOOKUP trở thành lựa chọn được yêu thích hơn cả mỗi khi cần xử lý số lượng dữ liệu lớn. Kết quả nó chạy nhanh và ít lỗi hơn.

• XLOOKUP không hạn chế phạm vi dữ liệu: XLOOKUP linh hoạt hơn VLOOKUP. VLOOKUP có thể tra cứu giá trị chỉ nằm ở cột ngoài cùng bên trái của bảng và trả về giá trị từ cột tương ứng ở bên phải. Trong khi đó, XLOOKUP chỉ cần những bước đơn giản hơn và có thể trả về giá trị cho bất kỳ cột theo một trong hai hướng.
• Kết quả mặc định của XLOOKUP là giá trị chính xác nhất.
• Mặc định, hàm VLOOKUP trong Excel luôn tìm kết quả gần đúng. Tuy nhiên, nếu cần kết quả chính xác, bạn nên nhập FALSE trong đối số range_lookup.
• Hàm XLOOKUP tìm kiếm giá trị tra cứu trong phạm vi cụ thể luôn mặc định là giá trị chính xác. Nếu không thể tìm thấy, bạn có thể chỉnh sửa đối số để tìm một kết quả gần đúng, nhỏ hoặc lớn hơn giá trị tra cứu.

Lí do khác:

• XLOOKUP có thể xử lý chèn hoặc xóa cột.
• XLOOKUP triển khai tra cứu theo chiều dọc và ngang.
• XLOOKUP cung cấp các đối sánh gần đúng thông minh hơn.
• XLOOKUP cần ít tham chiếu ô hơn.