Bài viết này Học Excel Online sẽ chia sẻ với các bạn về hàm XLOOKUP trong Excel – một hàm mới trong Excel giúp chúng ta có thể tra cứu cả theo chiều dọc và theo chiều ngang. Một số vấn đề của hàm VLOOKUP như, cột trả về giá trị nằm phía bên trái cột tra cứu, tra cứu giá trị cuối cùng, tra cứu với nhiều tiêu chí điều kiện … trước đây cần tới những công thức cực kì phức tạp, thì giờ đây đã trở nên dễ dàng với XLOOKUP.

Mỗi khi bạn muốn tra cứu trong Excel, có thể bạn sẽ nghĩ ngay tới hàm tra cứu VLOOKUP, HLOOKUP hoặc nâng cao hơn là sự kết hợp giữa hàm INDEX và MATCH hoặc thậm chí, bạn có thể dùng tới Power Query hoặc lập trình VBA. Tin vui là có thể bạn sẽ không cần làm gì quá phức tạp, bởi vì, Microsoft đã mang tới cho chúng ta một hàm mới: hàm XLOOKUP trong Excel.

Hàm XLOOKUP tốt hơn như thế nào? Hàm này có thể tra cứu theo cả chiều dọc lần chiều ngang, cả bên trái của cột dữ liệu tra cứu, XLOOKUP còn có thể tra cứu theo nhiều tiêu chí, thậm chí trả về cả cột kết quả, không chỉ 1 kết quả duy nhất.

Tuy vậy, với tất cả sự ưu việt như vậy của hàm XLOOKUP, hàm này tại thời điểm bài viết, chỉ có cho phiên bản Excel 365.

Hàm XLOOKUP trong Excel – cú pháp và cách sử dụng

Hàm XLOOKUP trong Excel sẽ tìm kiếm trong một vùng dữ liệu một giá trị nhất định và trả về giá trị liên quan trong một cột khác. Hàm này có thể tra cứu cả theo chiều dọc và theo chiều ngang, hỗ trợ tra cứu chính xác (mặc định) và tra cứu với kết quả gần đúng, hoặc tra cứu sử dụng ký tự đại diện (*, ?, ~)

Cú pháp của hàm XLOOKUP như sau

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

3 tham số đầu tiên là bắt buộc, 3 tham số sau là không bắt buộc.

• Lookup_value – giá trị dùng để tìm kiếm
• Lookup_array – vùng hoặc mảng dữ liệu dùng để tra cứu
• Return_array – vùng hoặc mảng dữ liệu để lấy dữ liệu trả về
• If_not_found (không bắt buộc) – giá trị thay thế khi không tìm thấy. Nếu bỏ qua, lỗi #N/A sẽ là kết quả
• Match_mode (không bắt buộc) – kiểu tìm kiếm:

• 0 hoặc bỏ qua – tìm kiếm chính xác. Nếu giá trị tìm kiếm không tìm thấy, #N/A sẽ được trả về
• -1 – tìm kiếm chính xác hoặc giá trị nhỏ hơn gần nhất. Nếu giá trị tìm kiếm chính xác không được tìm thấy, giá trị nhỏ hơn gần nhất sẽ được trả về
• 1 – tìm kiếm chính xác hoặc giá trị lớn hơn gần nhất. Nếu giá trị tìm kiếm chính xác không được tìm thấy, giá trị lớn hơn gần nhất sẽ được trả về.
• 2 – tìm kiếm theo ký tự đại diện

• Search_mode (không bắt buộc) – hướng tìm kiếm trong dữ liệu

• 1 hoặc bỏ qua – tìm từ đầu tới cuối
• -1 – tìm từ cuối tới đầu
• 2 – tìm kiếm nhị phân trên dữ liệu được sắp xếp tăng dần
• -2 – tìm kiếm nhị phân trên dữ liệu được sắp xếp giảm dần

Theo Microsoft, tìm kiếm nhị phân là một thuật toán tìm kiếm đặc biệt giúp việc tìm kiếm nhanh hơn, tuy nhiên nó yêu cầu dữ liệu cần được sắp xếp trước khi thực hiện tìm kiếm.

Hàm XLOOKUP cơ bản

Để có thể hiểu hơn về hàm XLOOKUP, chúng ta hay đi sử dụng hàm này ở dạng đơn giản nhất với chỉ ba tham số bắt buộc đầu tiên và thực hiện tra cứu chính xác.

Trong công thức trên, F1 là ô chứa giá trị cần tìm kiếm, A2:A6 là vùng tìm kiếm, C2:C6 là vùng sẽ trả về kết quả nếu F1 được tìm thấy trong vùng A2:A6.

Bạn có thể thấy rằng, công thức XLOOKUP này, có vẻ đơn giản hơn VLOOKUP

XLOOKUP so với VLOOKUP

So sánh với hàm VLOOKUP nguyên bản trong Excel, hàm XLOOKUP có rất nhiều lợi thế. Sau đây là một danh sách 10 chức năng vượt trội của XLOOKUP so sánh với bất kì hàm tra cứu nào trong Excel:

1. Tra cứu theo cả chiều dọc lẫn chiều ngang. Chữ “X” trong hàm XLOOKUP thể hiện việc hỗ trợ tra cứu theo cả 2 hướng trên bảng tính.
2. Hỗ trợ tra cứu, tìm kiếm theo bất cứ hướng nào: từ phải qua, từ trái qua, từ trên xuống, từ dưới lên. Trong khi hàm VLOOKUP chỉ có thể tìm kiếm trong cột về phía trái còn HLOOKUP chỉ tìm kiếm trong hàng phía trên, XLOOKUP không bị giới hạn này.
3. XLOOKUP thực hiện tra cứu chính xác một cách mặc định. Trong hầu hết các trường hợp, bạn sẽ cần sử dụng tra cứu một cách chính xác, XLOOKUP mặc định sẽ tra cứu chính xác cho bạn (Không như VLOOKUP có mặc định cho việc tra cứu gần đúng). Tất nhiên XLOOKUP cũng có thể thực hiện tra cứu gần đúng nếu cần.
4. Tra cứu với ký tự đại diện. Khi bạn chỉ biết một vài phần của giá trị cần tra cứu, bạn có thể sử dụng tra cứu sử dụng ký tự đại diện với XLOOKUP.
5. Tìm kiếm ở thứ tự đảo ngược. Ngày trước, khi muốn lấy giá trị cuối cùng với VLOOKUP, chúng ta phải đảo ngược dữ liệu nguồn. Bây giờ, chúng ta chỉ cần điều chỉnh tham số search_mode thành -1 để XLOOKUP có thể thực hiện tìm kiếm từ dưới lên.
6. Trả về nhiều kết quả. Bằng cách thay đổi tham số return_array, bạn có thể lấy kết quả là cả một dòng hoặc một cột liên quan tới giá trị tìm kiếm
7. Tìm kiếm nhiều điều kiện. Hàm XLOOKUP có khả năng xử lý dữ liệu theo mảng, do vậy chúng ta có thể thực hiện tra cứu theo nhiều điều kiện cùng lúc.
8. Có khả năng bây lỗi. Thông thường, chúng ta sử dụng IFNA để có thể kiểm soát lỗi #N/A. XLOOKUP hỗ trợ sử dụng tham số if_not_found cho trường hợp tra cứu mà không tìm ra kết quả.
9. Không lo khi sửa đổi cấu trúc bảng. Đối với VLOOKUP, nếu bạn thay đổi cấu trúc bảng (xoá, hoặc thêm mới cột) thì nhiều khả năng công thức VLOOKUP sẽ không còn chạy được nữa do tham số thứ 3 của hàm VLOOKUP là cột lấy dữ liệu về. Sử dụng XLOOKUP, bạn không cần lo lắng về điều này.
10. XLOOKUP được cho là hoạt động nhanh hơn VLOOKUP vì chỉ cần xử lý dữ liệu của mảng tra cứu và mảng kết quả trả về.

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

Những ví dụ dưới đây sẽ minh hoạ những trường hợp hữu dụng nhất của hàm XLOOKUP, ngoài ra chúng ta cũng sẽ có một vài ví dụ phức tạp hơn để bạn có thể nâng cao khả năng sử dụng hàm tra cứu trong Excel

Tra cứu theo chiều ngang và chiều dọc

Microsoft sử dụng hai hàm cho hai kiểu tra cứu: VLOOKUP khi muốn tra cứu trong cột và hàm HLOOKUP nếu muốn tra cứu theo hàng.

Hàm XLOOKUP có thể tra cứu theo cả cột và hàng với cùng một cú pháp. Như vậy chúng ta sẽ có ít thông tin phải nhớ hơn khi sử dụng hàm XLOOKUP.

Công thức tra cứu theo cột sử dụng XLOOKUP như sau:

=XLOOKUP(E1, A2:A6, B2:B6)

Công thức tra cứu theo hàng sử dụng XLOOKUP như sau:

=XLOOKUP(I1, B1:F1, B2:F2)

Tra cứu và lấy giá trị ở cột bên trái cột tìm kiếm

Trong các phiên bản trước của Excel, cách đáng tin cậy duy nhất để bạn có thể trả về giá trị tìm kiếm trong cột năm ở bên trái cột chứa giá trị cần tra cứu là sử dụng kết hợp hàm Index và Match. Đối với hàm XLOOKUP, bạn có thể làm việc này với một công thức đơn giản hơn nhiều:

=XLOOKUP(F1, B2:B6, A2:A6)

Tra cứu chính xác và tra cứu gần đúng với hàm XLOOKUP

Thiết lập giúp cho hàm XLOOKUP có thể tra cứu chính xác hay tra cứu gần đúng được điều khiển bởi tham số thứ 5: match_mode. Mặc định, hàm XLOOKUP sẽ tra cứu chính xác.

Lưu ý, trong trường hợp bạn thiết lập tham số match_mode là 1 hoặc -1 thì XLOOKUP vẫn tìm kiếm giá trị chính xác trước, nếu giá trị này không được tìm thấy thì giá trị gần đúng mới được trả về:

Các giá trị mà match_mode nhận và ý nghĩa:

• 0 hoặc bỏ qua: tìm kiếm chính xác. Nếu không tìm thấy, trả về lỗi #N/A
• -1 – tìm kiếm chính xác trướ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 trước, nếu không tìm thấy, trả về giá trị lớn hơn gần nhất.

Tìm kiếm chính xác bằng XLOOKUP

Thông thường thì chúng ta sử dụng khả năng tìm kiếm chính xác của các hàm tìm kiếm nhiều hơn khả năng tìm kiếm gần đúng. Vì một cách mặc định, XLOOKUP sẽ tìm kiếm chính xác, vậy nên chúng ta có thể bỏ qua không điền tham số match_mode và chỉ điền đủ 3 tham số bắt buộc của hàm XLOOKUP mà thôi:

=XLOOKUP(F2, $B$2:$B$6, $C$2:$C$6)

Ví dụ trên cho thấy, XLOOKUP có hành vi mặc định là tra cứu chính xác, nếu không tìm thấy kết quả chính xác trong trường hợp này, hàm XLOOKUP trả về giá trị #N/A. Chúng ta sẽ cùng nhau giải quyết vấn đề này trong phần tiếp theo.

Tra cứu gần đúng với hàm XLOOKUP

Để thực hiện tra cứu gần đúng sử hàm XLOOKUP, bạn thiết lập match_mode với giá trị 1 hoặc -1. Việc sử dụng 1 hay -1 sẽ tuỳ thuộc vào dữ liệu của bạn được tổ chức như thế nào.

Trong trường hợp này, ô F2 có giá trị là 98, do không tìm thấy giá trị 98 trong vùng tìm kiếm là B2:B6, do vậy XLOOKUP tìm giá trị nhỏ hơn gần nhất đó là 90, và 90 sẽ ững với kết quả A ở cùng dòng.

Trong trường hợp bảng tìm kiếm của chúng ta chứa dữ liệu như trên, 98 không được tìm thấy trong vùng tìm kiếm, match_mode là 1, nên hàm XLOOKUP sẽ tìm tới giá trị lớn hơn gần nhất (100), và kết quả chúng ta có được là A.

Típ: Khi bạn copy công thức XLOOKUP để áp dụng cho nhiều dòng, bạn cần khoá tham chiếu $B$2:$B$6 để khi kéo công thức thì tham chiếu không thay đổi.

Tra cứu với ký tự đại diện

Để thiết lập hàm XLOOKUP cho việc tra cứu với ký tự đại diện, bạn cần viết match_mode bằng 2:

• Dấu sao / dấu hoa thị (*) – đại diện cho bất cứ số lượng ký tự nào
• Dấu hỏi chấm (?) – đại diện cho bất cứ ký tự đơn nào

Trong hình minh hoạ trên, bạn có thể tra cứu dung lượng pin mà không cần biết tên đầy đủ của phiên bản iPhone X sử dụng cách tra cứu gần đúng:

=XLOOKUP(“*iphone X*”, A2:A8, B2:B8, ,2)

hoặc

=XLOOKUP(“*”&E1&”*”, A2:A8, B2:B8, ,2)

Típ: Trong trường hợp giá trị cần tra cứu của bạn có dấu * hoặc dấu ? mà bạn muốn tìm kiếm cụm từ có 2 dấu này, bạn cần sử dụng thêm dấu ~ ở phía trước, ví dụ: ~*, ~?, hoặc ~~

Tra cứu từ phía cuối bảng lên

Trong trường hợp, giá trị tìm kiếm không phải là duy nhất trong bảng. Bạn cần tìm kiếm giá trị xuất hiện cuối cùng, hàm XLOOKUP có thể giúp bạn tìm kiếm từ phía cuối bảng.

Hướng tìm kiếm của hàm XLOOKUP được quy định trong tham số thứ 6:

• 1 hoặc bỏ qua (mặc định) – tìm từ trên xuống dưới hoặc từ trái qua phải
• -1 – tìm kiếm từ dưới lên hoặc từ phải qua

Trong ví dụ trên, chúng ta có thể tìm được Sales Amount mới nhất của Laura bằng cách sử dụng công thức XLOOKUP:

=XLOOKUP(G1, B2:B9, D2:D9, , ,-1)

XLOOKUP trả về nhiều cột hoặc nhiều hàng kết quả

Một tính năng cực kỳ mạnh mẽ của XLOOKUP là có thể trả về kết quả là nhiều cột hoặc nhiều hàng:

=XLOOKUP(F2, A2:A7, B2:D7)

Nếu bạn muốn đổi chiều kết quả, thì bạn chỉ cần bao hàm XLOOKUP bởi hàm TRANSPOSE:

=TRANSPOSE(XLOOKUP(G1, A2:A7, B2:D7))

XLOOKUP tra cứu dựa trên nhiều điều kiện

Cú pháp để tra cứu nhiều điều kiện dựa trên hàm XLOOKUP như sau:

XLOOKUP(1, (criteria_range1=criteria1) * (criteria_range2=criteria2) * (…), return_array)

Nếu bạn đã biết cách sử dụng hàm SUMPRODUCT thì cách sử dụng này của hàm XLOOKUP có lẽ sẽ rất quen thuộc với bạn. Hãy cùng nhau nghiên cứu ví dụ sau đây

=XLOOKUP(1, (B2:B10=G1) * (A2:A10=G2) * (C2:C10=G3), D2:D10)

Chúng ta có 3 tiêu chí cần thoả mãn đồng thời để tra cứu trong trường hợp này:

• Salesperson = Laura (được viết dưới dạng B2:B10 = G1)
• Date = 02 tháng 9 (được viết dưới dạng A2:A10 = G2)
• Item = Apples (được viết dưới dạng C2:C10 = G3)

XLOOKUP tra cứu hai chiều

Chúng ta hay cũng nhìn vào ví dụ sau đây:

Trong trường hợp bạn muốn tra cứu thông tin trong 1 bảng như trên, giá trị tra cứu nằm ở cả cột A và nằm ở hàng thứ nhất, thì công thức XLOOKUP có thể giúp bạn trong trường hợp này như sau:

=XLOOKUP(H1, A2:A6, XLOOKUP(H2, B1:E1, B2:E6))

hoặc

=XLOOKUP(H2, B1:E1, XLOOKUP(H1, A2:A6, B2:E6))

Với bài toán này, bạn cũng có thể giải quyết bằng cách sử dụng kết hợp hàm Index và Match trong Excel.

XLOOKUP thông báo lỗi khi không tìm thấy giá trị cần tìm kiếm

Trong trường hợp hàm XLOOKUP không tìm thấy giá trị cần tìm kiếm, hàm sẽ trả về lỗi #N/A. Để đưa ra một thông báo có đầy đủ thông tin hơn là một lỗi, chúng ta có thể sử dụng một tính năng có sẵn của hàm XLOOKUP đó là tham số if_not_found.

=XLOOKUP(E1, A2:A6, B2:B6, “No match is found”)

Chú ý:

• Tham số if_not_found của hàm XLOOKUP chỉ “bắt lỗi” #N/A, không phải tất cả các lỗi
• Lỗi #N/A có thể được xử lý bằng việc kết hợp hàm IFNA và VLOOKUP, nhưng bạn sẽ phải viết một công thức phức tạp và dài hơn.

XLOOKUP bị lỗi, không hoạt động

Trong trường hợp bạn thử sử dụng XLOOKUP mà bị lỗi, nhiều khả năng bạn đang gặp phải những vấn đề sau:

Lỗi #NAMES? khi dùng XLOOKUP

Hàm XLOOKUP không tương thích ngược. Hàm XLOOKUP chỉ có trong bộ Office 365 và sẽ không được cập nhật trong các bản Excel cũ hơn (Excel 2019 trở về trước)

XLOOKUP trả về kết quả sai

Khi bạn đã kiểm tra kĩ lại một lần cú pháp của hàm XLOOKUP rồi mà kết quả trả về vẫn sai, thì hãy kiểm tra lại vùng tham chiếu đề phòng trường hợp khi kéo công thức tham chiếu bị chệch đi.

XLOOKUP trả về kết quả #N/A

Khi XLOOKUP trả về lỗi #N/A, điều này có nghĩa là giá trị tìm kiếm không được tìm thấy. Hãy thử sử dụng cú pháp tìm kiếm gần đúng hoặc sử dụng thiết lập trả về giá trị khác khi dữ liệu cần tra cứu không được tìm thấy.

XLOOKUP trả về kết quả lỗi #VALUE

Lỗi #VALUE được trả về khi kích thước của vùng tìm kiếm và vùng kết quả không đồng nhất hoặc không đúng chiều. Ví dụ bạn không thể tìm ở trong một cột dọc và trả về giá trị trong một hàng ngang với XLOOKUP.

XLOOKUP trả về kết quả lỗi #REF

XLOOKUP sẽ trả về lỗi #REF khi bạn thực hiện tra cứu giữa 2 tài liệu Excel khác nhau, nhưng một trong hai tài liệu bị đóng. Hoặc tham chiếu sử dụng trong công thức XLOOKUP bị xoá và không được cập nhật lại.