Hướng dẫn cách dùng hàm XLOOKUP trong Excel

Bạn đã biết về hàm VLOOKUP, HLOOKUP để tìm kiếm dữ liệu cả hàng ngang và dọc, nhưng Excel còn có cả hàm XLOOKUP để tìm kiếm kết quả theo hàng và cột. Trong bài viết sau Phúc Anh sẽ hướng dẫn cho bạn cách dùng hàm XLOOKUP trong Excel để tra cứu dữ liệu một cách hiệu quả nhất.

Hàm XLOOKUP trong Excel là gì?

XLOOKUP thực chất là một sự thay thế cho các hàm cũ như HLOOKUP, VLOOKUP và LOOKUP. Hàm này cho phép bạn tra cứu dữ liệu theo cả hai chiều: ngang và dọc. Cụ thể, XLOOKUP trong Excel giúp bạn nhanh chóng tìm kiếm giá trị trong một bộ dữ liệu đã cho, cho phép tìm kiếm theo cả hàng và cột. Sau khi tìm thấy, nó sẽ trả về giá trị tương ứng từ hàng hoặc cột khác.

Hiện tại, hàm XLOOKUP mới chỉ dành riêng cho người dùng Microsoft 365. Đối với những người dùng Microsoft 365, tùy chọn này sẽ tự động được kích hoạt trong Excel, và XLOOKUP cũng có sẵn trên Office 365 Online.

Công thức hàm XLOOKUP như thế nào?

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

Trong đó: 

- Lookup_value – giá trị dùng để tìm kiếm (giá trị bắt buộc) 

- Lookup_array – vùng hoặc mảng dữ liệu dùng để tra cứu (giá trị bắt buộc) 

- Return_array – vùng hoặc mảng dữ liệu để lấy dữ liệu trả về (giá trị bắt buộc) 

- 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

Hướng dẫn cách sử dụng 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ỉ 3 tham số bắt buộc đầu tiên và thực hiện tra cứu chính xác.

Với công thức trên ta thấy, 

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

Công thức XLOOKUP này sẽ đơn giản hơn khi sử dụng hàm VLOOKUP.

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

Những ví dụ dưới đây sẽ minh hoạ cụ thể những trường hợp hữu dụng nhất của hàm XLOOKUP, ngoài ra Phúc Anh 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.

Hướng dẫn tra cứu dữ liệu theo chiều ngang và chiều dọc

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

Khi sử dụ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. 

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

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

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

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

Hướng dẫn 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, phương pháp duy nhất đáng tin cậy để trả về giá trị từ cột bên trái cột chứa giá trị cần tìm là sử dụng sự kết hợp của hàm Index và Match. Tuy nhiên, với hàm XLOOKUP, bạn có thể thực hiện điều này bằng một công thức đơn giản hơn rất nhiều:

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

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

Việc thiết lập cho phép hàm XLOOKUP thực hiện tìm kiếm chính xác hoặc gần đúng được xác định bởi tham số thứ năm: match_mode. Theo mặc định, hàm XLOOKUP sẽ thực hiện tìm kiếm chính xác.

Cần lưu ý rằng nếu bạn thiết lập tham số match_mode thành 1 hoặc -1, XLOOKUP vẫn sẽ ưu tiên tìm kiếm giá trị chính xác trước. Nếu không tìm thấy, hàm sẽ trả về giá trị gần đúng sau đó:

Dưới đây là các giá trị mà match_mode có thể nhận và ý nghĩa của chúng:

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

Tra cứu 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)

Trong trường hợp nếu không tìm thấy kết quả chính xác, hàm XLOOKUP sẽ trả về giá trị #N/A. 

Tra cứu gần đúng bằng 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 ví dụ dưới đâ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.

Note: 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 dữ liệ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 ví dụ, 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)

Note: 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

Khi giá trị bạn muốn tìm kiếm không phải là duy nhất trong bảng và bạn cần xác định giá trị xuất hiện cuối cùng, hàm XLOOKUP có thể hỗ trợ bạn thực hiện tìm kiếm từ phía cuối bảng.

Hướng tìm kiếm của hàm XLOOKUP được xác định bởi tham số thứ sáu:

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

Trong ví dụ dưới đây, bạn có thể tìm thấy số tiền bán hàng 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 sử dụng thêm hàm TRANSPOSE:

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

Sử dụng XLOOKUP tra cứu với nhiều điều kiện

Công thức để 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ần đáp ứng ba tiêu chí sau đây để thực hiện tra cứu trong trường hợp này:

• Salesperson: Laura (được biểu diễn dưới dạng B2:B10 = G1)
• Date: 02 tháng 9 (được biểu diễn dưới dạng A2:A10 = G2)
• Item: Apples (được biểu diễn dưới dạng C2:C10 = G3)

Sử dụng XLOOKUP tra cứu hai chiều ngang và dọc

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.

Trên đây Phúc Anh đã hướng dẫn bạn cách sử dụng hàm XLOOKUP trong Excel với các ví dụ vô cùng cụ thể. Hy vọng những thông tin trên sẽ hữu ích cho bạn nhé!