XLOOKUP(): Hàm tra cứu linh hoạt nhất của Excel

Nếu bạn từng dùng VLOOKUP() và bị bí vì cần tra cứu sang bên trái, XLOOKUP() sẽ giải quyết vấn đề đó. Đây là hàm tra cứu mới hơn của Excel. Nó có thể thay thế VLOOKUP(), HLOOKUP(), và thậm chí phần lớn công thức INDEX() và MATCH() chỉ với một công thức đơn giản.

XLOOKUP() có thể tìm theo mọi hướng, trả về nhiều hơn một cột cùng lúc, và hiển thị thông báo tùy chỉnh khi thiếu giá trị. 

Trong hướng dẫn này, trước tiên chúng ta sẽ đi qua cú pháp. Sau đó sẽ xây dựng công thức thực tế từng bước, dùng một bộ dữ liệu đơn giản xuyên suốt. Chúng ta cũng sẽ đề cập đến khớp chính xác và xấp xỉ, cách trả về nhiều hơn một cột, cách xử lý lỗi, và cách XLOOKUP() so với VLOOKUP() và INDEX()/MATCH(). 

Cách sử dụng XLOOKUP() trong Excel

Hãy xây dựng một công thức XLOOKUP() thực tế từ đầu. 

Bộ dữ liệu của chúng ta có năm sản phẩm, mỗi sản phẩm có ID, tên, danh mục và giá, nằm ở các cột A đến D, tiêu đề ở hàng 1.

Bộ dữ liệu cho XLOOKUP(). Ảnh: Tác giả.

Giả sử chúng ta muốn tìm giá của "P104." Hãy thực hiện từng bước.

Tra cứu một giá trị đơn

Nhấp vào một ô trống nơi bạn muốn hiển thị kết quả. Tôi dùng F2. Và gõ công thức sau:

=XLOOKUP("P104", A2:A6, D2:D6)

Trong công thức này: 

• "P104" là lookup_value, giá trị chúng ta cần tìm

• A2:A6 là lookup_array, phạm vi Excel tìm kiếm

• D2:D6 là return_array, phạm vi Excel lấy câu trả lời

Excel tìm trong cột A, “Product ID,” giá trị "P104," thấy ở hàng 5 và trả về giá trị tương ứng ở cột D: $45.

Thay vì gõ trực tiếp "P104", bạn có thể tham chiếu đến một ô để có thể thay đổi giá trị tra cứu mà không cần sửa công thức. Trong ví dụ của chúng ta, ô A5 chứa "P104," nên công thức trở thành:

=XLOOKUP(A5, A2:A6, D2:D6)

Sử dụng XLOOKUP() trong Excel. Ảnh: Tác giả.

Giờ bạn có thể gõ bất kỳ Product ID nào vào A5 và giá sẽ tự động cập nhật.

Trả về giá trị từ cột khác

Bước này cho thấy sự khác biệt thực sự giữa XLOOKUP() và VLOOKUP(). Bạn không bị bó buộc phải trả về từ một cột nằm bên phải cột tìm kiếm.

Giả sử thay vì giá, bạn muốn lấy danh mục của "P102." Thiết lập tương tự, chỉ cần trỏ return_array sang cột C, “Category”:

=XLOOKUP(A3, A2:A6, C2:C6)

Ở đây, A3 trả về Home. Nếu đổi thành A5, nó cập nhật thành Electronics. Tương tự, đổi return_array để lấy tên sản phẩm, giá, hoặc cột bất kỳ khác, tất cả vẫn dùng cùng lookup_value và lookup_array.

XLOOKUP() trả về giá trị từ cột khác. Ảnh: Tác giả.

Với VLOOKUP(), bạn sẽ phải đếm số cột tính từ cột tra cứu và điều chỉnh chỉ số cột mỗi lần muốn kết quả khác. Với XLOOKUP(), bạn chỉ cần trỏ trực tiếp vào cột mong muốn. 

Sao chép công thức xuống nhiều hàng

Từ đầu đến giờ, chúng ta tra cứu từng giá trị một. Nhưng hầu hết bảng tính thực tế cần tra cứu nhiều giá trị cùng lúc, ví dụ một danh sách đầy đủ các ID đơn hàng, mỗi ID cần một giá khớp tương ứng.

Thiết lập như sau: Cột A, bắt đầu từ A2, đã có danh sách Product ID. Chúng ta muốn giá tương ứng xuất hiện cạnh mỗi ID, ở cột F.

Để làm vậy, bấm vào F2 và gõ công thức, tham chiếu A2 làm giá trị tra cứu như sau:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Giờ bấm lại F2, rồi kéo tay nắm điền (ô vuông nhỏ ở góc dưới bên phải của ô) xuống các hàng còn lại.

Excel sao chép công thức xuống, và mỗi hàng trả về đúng giá cho Product ID của chính nó. ID ở A2 nhận giá ở F2, ID ở A3 nhận giá ở F3, v.v.

Sao chép công thức xuống các ô khác. Ảnh: Tác giả.

Lưu ý các dấu đô la quanh A2:A6 và D2:D6. Đây gọi là tham chiếu tuyệt đối. Nó cố định phạm vi để không bị dịch chuyển khi bạn sao chép công thức xuống. 

Điều này quan trọng vì mỗi hàng cần tìm trong cùng một danh sách sản phẩm, chỉ thay đổi giá trị tra cứu mỗi lần.

Cách dùng khớp chính xác và khớp xấp xỉ trong XLOOKUP()

Theo mặc định, XLOOKUP() tìm khớp chính xác. Nếu không tìm thấy, nó trả về lỗi, trừ khi bạn đã thiết lập đối số if_not_found như đã đề cập trước đó. 

Nhưng XLOOKUP() cũng có thể tìm giá trị gần nhất bằng đối số match_mode. 

Khớp chính xác (Mặc định)

Bạn không cần nhập gì thêm cho tùy chọn này. Mọi công thức ta đã xây dựng đều dùng khớp chính xác vì đó là hành vi mặc định của XLOOKUP().

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel tìm giá trị đúng bằng A2 và chỉ trả về kết quả nếu tìm thấy khớp chính xác ở đâu đó trong phạm vi tra cứu.

Nếu muốn ghi rõ ràng, hãy thêm số 0 làm đối số thứ tư:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() trả về khớp chính xác. Ảnh: Tác giả.

Để ý khoảng trống trước số 0? Điều đó bỏ qua if_not_found (vì chúng ta để trống) và chuyển thẳng đến match_mode. Hầu hết thời gian, bạn có thể bỏ hẳn đối số này và để Excel tự mặc định khớp chính xác.

Khớp xấp xỉ

Giờ giả sử dữ liệu của bạn không có giá trị cho mọi khả năng tra cứu. Hãy nghĩ đến cấu trúc hoa hồng nơi nhân viên bán hàng nhận các mức tỷ lệ khác nhau tùy khoảng doanh số của họ. Bạn sẽ không có hàng cho mọi giá trị doanh số, chỉ có điểm bắt đầu của mỗi khoảng.

Đây là lúc khớp xấp xỉ hữu ích. 

Đặt match_mode bằng -1, và XLOOKUP() sẽ tìm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tra cứu.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Giả sử phạm vi tra cứu của bạn có các ngưỡng doanh số $0, $5.000 và $10.000, mỗi ngưỡng gắn với một tỷ lệ hoa hồng khác. 

Một nhân viên có $7.500 doanh số không khớp chính xác hàng nào. Với match_mode đặt -1, XLOOKUP() sẽ tìm $5.000, ngưỡng lớn nhất không vượt quá $7.500, và trả về tỷ lệ hoa hồng của hàng đó.

Đặt match_mode bằng 1, và XLOOKUP() đảo logic: tìm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị tra cứu. Điều này hữu ích cho các bậc trọng lượng vận chuyển, nơi bạn làm tròn lên bậc tiếp theo thay vì làm tròn xuống.

XLOOKUP() trả về khớp xấp xỉ. Ảnh: Tác giả.

Lưu ý: khớp xấp xỉ giả định phạm vi tra cứu được sắp xếp, tăng dần cho -1, giảm dần cho 1. Nếu không được sắp đúng, XLOOKUP() có thể trả về kết quả sai mà không báo lỗi, còn tệ hơn. Vì vậy, luôn kiểm tra lại thứ tự sắp xếp trước khi dựa vào tính năng này.

Cách tra cứu các giá trị ở bên trái với XLOOKUP()

VLOOKUP() chỉ tìm trong cột ngoài cùng bên trái của phạm vi và trả về giá trị từ một cột nằm bên phải; nó không thể làm ngược lại. Đối số thứ ba của nó không phải là một phạm vi; đó là chỉ số cột tính từ mép trái. 

Không có cách nào để bảo nó “đi sang một cột bên trái,” nên nếu giá trị bạn cần nằm bên trái cột tra cứu, bạn sẽ phải sắp xếp lại dữ liệu hoặc thêm cột phụ chỉ để tra cứu hoạt động.

XLOOKUP() không có hạn chế này, vì bạn trỏ nó đến các phạm vi riêng cho phần tìm và phần trả về.

Xét danh sách sản phẩm của chúng ta: Product ID ở cột A, Product Name ở cột B. 

Giả sử bạn có tên sản phẩm và muốn tìm ID của nó; điều ngược lại so với khả năng của VLOOKUP().

Tại C9, nhập Bluetooth Speaker (một tên có trong danh sách). Tại E2, nhập:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() tìm trong cột B "Bluetooth Speaker," thấy ở hàng 5, và trả về "P104" từ cột A, mặc dù cột A nằm bên trái cột B. 

XLOOKUP() có thể tra cứu sang trái, khác với VLOOKUP(). Ảnh: Tác giả.

Cách trả về nhiều cột với XLOOKUP()

XLOOKUP() có thể trả về nhiều cột cùng lúc trong một công thức, dùng tính năng mảng động (dynamic arrays). Để làm vậy, trỏ return_array đến một phạm vi thay vì một cột đơn, và XLOOKUP() sẽ “tràn” toàn bộ hàng khớp qua nhiều ô theo nhu cầu. 

Hãy thử với danh sách sản phẩm của chúng ta. 

Tại C9, tôi nhập một Product ID, ví dụ "P104." Ở E2 tôi nhập:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Lưu ý return_array ở đây là B2:D6, rộng ba cột, bao phủ Product Name, Category, và Price. Sau khi nhấn Enter, XLOOKUP() tìm "P104" ở cột A, rồi trả về Bluetooth Speaker, Electronics, và $45 cùng lúc, tràn vào E9, F9, và G9.

XLOOKUP() trả về nhiều cột chỉ với một công thức. Ảnh: Tác giả.

Đây chính là “phạm vi tràn.” E9 chứa công thức thực tế, còn F9 và G9 tự điền, không có công thức riêng. 

Bấm vào F9, thanh công thức hiển thị phiên bản bị làm mờ của công thức gốc, không phải thứ được gõ trực tiếp ở đó. Thử gõ vào F9 khi vùng tràn của E9 đang hoạt động, Excel sẽ báo lỗi spill. 

Chúng ta sẽ đề cập cách khắc phục trong phần Lỗi thường gặp.

Cách xử lý giá trị thiếu trong XLOOKUP()

Theo mặc định, XLOOKUP() trả lỗi #N/A khi không tìm được khớp. Đối số if_not_found có thể khắc phục bằng cách hiển thị nội dung bạn muốn thay vì lỗi mặc định.  

Giả sử trong C9, tôi nhập một Product ID không tồn tại trong dữ liệu, như "P999."

Trả về thông điệp tùy chỉnh

Trong G2, tôi nhập:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Vì "P999" không tồn tại ở cột A, XLOOKUP() bỏ qua lỗi và trả về "Not Found". Bạn có thể đổi cụm này thành "Check Product ID" hoặc "No Match".

Hiển thị thông điệp tùy chỉnh thay cho lỗi mặc định. Ảnh: Tác giả.

Trả về ô trống

Đôi khi bạn muốn một ô trống không có chữ. Với trường hợp này, dùng hai dấu ngoặc kép trống như sau:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Bạn có thể dùng cách này khi công thức nằm trong báo cáo, nơi một "Not Found" lạc lõng sẽ trông lộn xộn bên cạnh một cột giá trị thực.

Hiển thị ô trống thay cho lỗi mặc định. Ảnh: Tác giả.

Giữ nguyên dưới dạng lỗi thực

Nếu bạn bỏ qua hoàn toàn if_not_found, XLOOKUP() sẽ quay về #N/A. Điều này không phải lúc nào cũng xấu. Nếu bạn xây công thức để tự dùng và muốn phát hiện ngay lỗi gõ hoặc mục thiếu, việc thấy #N/A xuất hiện hữu ích hơn một ô trống im lặng che giấu vấn đề.

Lưu ý: if_not_found chỉ bắt các trường hợp thiếu khớp. Nếu lookup_array và return_array khác kích thước, ví dụ vậy, XLOOKUP() vẫn ném lỗi #VALUE!, và if_not_found sẽ không chặn được. Chúng ta sẽ nói cụ thể trong phần Lỗi thường gặp phía dưới.

Các tính năng nâng cao của XLOOKUP()

Hãy xem các tính năng nâng cao của XLOOKUP()

Tìm từ dưới lên trên

Đặt search_mode là -1, và XLOOKUP() sẽ quét từ cuối phạm vi lên trên, thay vì từ trên xuống dưới. Dùng khi dữ liệu có giá trị trùng, và bạn muốn bản ghi mới nhất, không phải bản đầu tiên.

Giả sử trong bộ dữ liệu của tôi có ba Customer ID trùng nhau. Ở ô E2, tôi nhập công thức mặc định sẽ dùng cách tìm từ trên xuống: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

Công thức này tính từ đầu bảng và hiển thị kết quả của lần khớp đầu tiên. 

Nhưng nếu tôi muốn tìm từ dưới lên, tôi sẽ dùng công thức này: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

Trong công thức này, -1 bảo Excel bắt đầu tìm từ cuối bộ dữ liệu. Và điều này sẽ trả về 340, vì đó là giá trị khớp đầu tiên tính từ dưới lên.  

XLOOKUP() quét dữ liệu từ dưới lên. Ảnh: Tác giả.

Chế độ tìm kiếm nhị phân

Đặt search_mode bằng 2, và XLOOKUP() sẽ chuyển từ việc kiểm tra từng hàng sang tìm kiếm nhị phân. 

Cách hoạt động: 

• Nó xem mục ở giữa phạm vi trước. 

• Nếu không khớp, nó loại nửa không thể có giá trị đó, rồi xem giữa phần còn lại. 

• Lặp lại như vậy, cắt đôi dữ liệu mỗi lần, cho đến khi tìm thấy khớp. 

• Đặt search_mode là -2, nó làm tương tự nhưng bắt đầu từ mục cuối cùng thay vì đầu tiên.

Tại E2, nhập:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Công thức này lại trả về 200, một hàng hoàn toàn khác.

Chế độ tìm kiếm nhị phân cho kết quả sai. Ảnh: Tác giả.

Bởi vì tìm kiếm nhị phân chỉ hoạt động nếu lookup_array của bạn được sắp xếp. Vì cột Customer ID của chúng ta không được sắp, nó là C100, C101, C100, C102, C100, nên tìm kiếm nhị phân loại sai nửa dữ liệu và trả về khớp sai. 

Giờ hãy xem khi dữ liệu thực sự được sắp xếp sẽ ra sao. 

Sắp log theo Customer ID tăng dần (cả ba C100 ở đầu) và chạy lại cùng công thức. 

Chế độ tìm kiếm nhị phân hoạt động với dữ liệu đã sắp xếp. Ảnh: Tác giả.

Lần này, E2 trả về 120, khớp với kết quả tìm kiếm thường. Đó là sự khác biệt. Tìm kiếm nhị phân chỉ bỏ qua dữ liệu có thể loại trừ an toàn, và điều đó chỉ đúng khi dữ liệu được sắp ban đầu. 

Lồng công thức XLOOKUP()

Lồng một XLOOKUP() vào trong một XLOOKUP() khác khi kết quả của tra cứu đầu cần đưa vào tra cứu thứ hai thay vì một phạm vi cố định.

Giả sử bạn muốn tìm giá, nhưng cột lấy giá phụ thuộc vào một tra cứu khác, ví dụ hạng mức giá của khách hàng.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

Trong công thức này, XLOOKUP() bên trong trước tiên xác định phạm vi cột cần lấy dựa trên D9, sau đó XLOOKUP() bên ngoài dùng kết quả đó làm return_array. 

XLOOKUP() lồng nhau. Ảnh: Tác giả.

XLOOKUP() so với VLOOKUP()

Chúng ta đã đi sâu cách hoạt động của XLOOKUP(). Dưới đây là so sánh với VLOOKUP().

Có hai điểm đáng lưu ý nhanh: 

• VLOOKUP() mặc định khớp xấp xỉ. Nếu quên đối số thứ tư, nó sẽ không báo lỗi; mà âm thầm trả về giá trị sai.

• Nếu bạn chèn một cột vào trang tính dùng VLOOKUP(), công thức sẽ âm thầm bắt đầu kéo sai giá trị. XLOOKUP() tránh được vì nó tham chiếu trực tiếp phạm vi thay vì đếm cột.

Điều này không khiến VLOOKUP() lỗi thời. Nó vẫn ổn cho các bảng tĩnh, đơn giản. Nhưng với mọi thứ xây mới về sau, XLOOKUP() là lựa chọn mặc định an toàn hơn.

XLOOKUP() so với INDEX() và MATCH()

Trước khi có XLOOKUP(), INDEX() và MATCH() kết hợp là cách tiêu chuẩn để vượt qua giới hạn của VLOOKUP(). Cách này dùng hai hàm thay vì một. 

• MATCH() tìm vị trí của giá trị tra cứu. 

• INDEX() sau đó dùng vị trí đó để lấy kết quả từ nơi bạn chỉ định: trái hay phải đều được.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

So sánh với phiên bản XLOOKUP() mà ta đã dùng xuyên suốt bài viết:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() chỉ hoạt động trên Microsoft 365 và Excel 2021 trở về sau, như đã đề cập trước đó. Nếu sổ làm việc cần mở ở Excel 2019 hoặc 2016, hoặc bạn chia sẻ với người dùng phiên bản cũ hơn, INDEX() và MATCH() vẫn hoạt động ở mọi nơi.

Cũng có một trường hợp nhỏ hơn. 

INDEX() và MATCH() tách “tìm vị trí” khỏi “trả về giá trị,” hữu ích trong một số công thức lồng phức tạp. Điều này hiếm gặp, nên đa số người đọc sẽ không cần.

Ngoài hai trường hợp đó, XLOOKUP() là lựa chọn tốt hơn cho sổ làm việc mới. 

Lỗi XLOOKUP() thường gặp và cách khắc phục

Dưới đây là tham chiếu nhanh cho các vấn đề bạn hay gặp và cách xử lý từng lỗi.

Lỗi #N/A

Đây là phản hồi mặc định của XLOOKUP() khi không tìm được khớp. Tôi đã gặp trước đó khi lookup_value và lookup_array không khớp, và XLOOKUP() không có gì để đối chiếu ở cột A.

Cách khắc phục: Kiểm tra kỹ lookup_value thực sự tồn tại trong lookup_array và được viết/định dạng giống hệt. Nếu việc thiếu khớp là bình thường, hãy dùng đối số if_not_found để thay #N/A bằng nội dung hữu ích hơn như "Not Found" hoặc ô trống, như đã đề cập.

Phạm vi tra cứu không đúng

Điều này xảy ra khi lookup_array và return_array không khớp chính xác: sai hàng, sai cột, hoặc phạm vi khác kích thước. 

Tùy phiên bản Excel hoặc Sheets bạn dùng, lỗi này có thể hiện là #VALUE!, hoặc đôi khi là #N/A với thông báo riêng giải thích vấn đề thật sự. Đừng để #N/A đánh lừa khiến bạn nghĩ là thiếu khớp. 

Cách khắc phục: Đảm bảo lookup_array và return_array bao phủ cùng số hàng hoặc cột. Nếu lookup_array là A2:A5, thì return_array cũng phải trải các hàng 2 đến 5, không phải hàng 4 đến 5 hay bất kỳ phạm vi lệch nào khác.

Không khớp kiểu dữ liệu

VLOOKUP() có thể báo lỗi vì nó kỳ vọng số nhưng lại nhận văn bản. XLOOKUP() có thể gặp vấn đề tương tự. Nếu lookup_value là số, nhưng lookup_array lưu các giá trị đó dưới dạng văn bản, hoặc ngược lại, XLOOKUP() sẽ không tìm thấy khớp dù nhìn trên màn hình có vẻ giống hệt.

Cách khắc phục: Kiểm tra xem lookup_value và lookup_array cùng kiểu dữ liệu. Số lưu dưới dạng văn bản thường canh trái trong ô thay vì canh phải. Bọc lookup_value trong VALUE() để chuyển văn bản thành số, hoặc dùng TEXT() để làm ngược lại, tùy hướng không khớp.

Lỗi spill

Điều này xảy ra khi return_array bao phủ hơn một cột, nhưng các ô mà XLOOKUP() cần không trống. Excel cần không gian trống cạnh ô công thức để tràn kết quả. Nếu đã có thứ gì đó ở đó, cả công thức sẽ lỗi.

Cách khắc phục: Xóa nội dung các ô nơi kết quả cần tràn vào, rồi nhập công thức. Nếu bạn cần giữ nội dung ở các ô đó, hãy chuyển công thức XLOOKUP() sang nơi khác có không gian trống xung quanh.

Kết luận 

Giờ bạn đã biết dùng XLOOKUP() cho giá trị đơn, nhiều cột, dữ liệu thiếu, và thậm chí tra cứu lồng giữa hai phạm vi. Đó là khá nhiều nội dung.

Trong tương lai, hãy coi XLOOKUP() là mặc định cho mọi sổ làm việc mới. Nó linh hoạt hơn VLOOKUP(), dễ đọc hơn INDEX/MATCH(), và giúp bạn không phải bọc mọi công thức trong IFERROR().

Nếu muốn đi sâu hơn, cũng đáng để học riêng INDEX() và MATCH().