Đầu tiên, chúng ta cần hiểu rằng hàm LOOKUP trong Excel là một hàm tìm kiếm giá trị trong một danh sách và trả về giá trị tương ứng. Tuy nhiên, hàm này đã bị lỗi trong một số trường hợp và không phải là một công cụ tìm kiếm linh hoạt nhất. Đó là lý do tại sao Microsoft đã giới thiệu hàm XLOOKUP, một công cụ tìm kiếm mới và hiệu quả hơn.
Hàm XLOOKUP cho phép bạn tìm kiếm giá trị trong bất kỳ vị trí nào của danh sách, không chỉ là vị trí đầu tiên. Nó cũng có thể tìm kiếm giá trị tương đối hoặc chính xác và có thể trả về giá trị gần giống nhất trong trường hợp không tìm thấy giá trị chính xác.
Để sử dụng hàm XLOOKUP, bạn cần biết các tham số sau:
Lookup_value: Giá trị bạn đang tìm kiếm.
Lookup_array: Danh sách hoặc phạm vi bạn muốn tìm kiếm.
Return_array: Danh sách giá trị mà bạn muốn trả về.
Match_mode: Chế độ tìm kiếm.
If_not_found: Giá trị trả về nếu không tìm thấy giá trị.
Hướng dẫn sử dụng hàm xlookup với ví dụ cụ thể
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))