XLOOKUP là hàm dò tìm của Excel: bạn đưa một giá trị cần tìm, vùng để tìm và vùng chứa kết quả, hàm trả về giá trị tương ứng. Khác với VLOOKUP, XLOOKUP không bắt cột tìm phải nằm bên trái, không đếm số thứ tự cột, mặc định tìm khớp chính xác và cho phép tự ghi nội dung thay thế khi không tìm thấy. XLOOKUP có trong Microsoft 365, Excel 2021 trở về sau và Excel trên web; Excel 2019 và cũ hơn không có hàm này.
Bài viết giải thích sáu đối số của XLOOKUP, đi qua ví dụ giả định tra giá theo mã hàng, liệt kê những việc VLOOKUP làm vất vả mà XLOOKUP làm gọn, chỉ cách nhờ AI chuyển công thức VLOOKUP cũ sang XLOOKUP, và các bẫy dữ liệu khiến hàm trả #N/A dù mã hàng rõ ràng có trong bảng.
Cú pháp XLOOKUP: sáu đối số, ba bắt buộc
Cú pháp đầy đủ là XLOOKUP(giá trị tìm, vùng tìm, vùng trả về, nội dung khi không thấy, kiểu khớp, kiểu tìm). Ba đối số đầu bắt buộc, ba đối số sau có thể bỏ qua.
| Đối số | Ý nghĩa | Ghi chú khi dùng |
|---|---|---|
| lookup_value | Giá trị cần tìm, thường là một ô như A2 | Kiểu dữ liệu phải khớp với vùng tìm: chữ tìm trong chữ, số tìm trong số |
| lookup_array | Một cột hoặc một hàng để tìm | Không cần nằm bên trái vùng trả về |
| return_array | Cột hoặc hàng chứa kết quả | Phải cùng số dòng với vùng tìm; có thể chọn nhiều cột để trả nhiều giá trị |
| if_not_found | Nội dung trả về khi không tìm thấy | Ví dụ "Chưa có mã"; bỏ trống thì hàm trả #N/A |
| match_mode | Kiểu khớp | 0 là khớp chính xác (mặc định), -1 là chính xác hoặc nhỏ hơn kế tiếp, 1 là chính xác hoặc lớn hơn kế tiếp, 2 là dùng ký tự đại diện * và ? |
| search_mode | Chiều tìm | 1 là từ trên xuống (mặc định), -1 là từ dưới lên; 2 và -2 là tìm nhị phân trên dữ liệu đã sắp xếp |
Điểm cần nhớ nhất là mặc định khớp chính xác. VLOOKUP thì ngược lại: nếu bạn quên đối số cuối, VLOOKUP hiểu là tìm gần đúng và có thể trả về giá của một mã hàng khác mà không báo lỗi gì. Rất nhiều bảng báo giá sai trong văn phòng bắt nguồn từ chi tiết này.
Một lưu ý về dấu phân cách: các ví dụ trong bài viết theo kiểu dấu phẩy. Nếu Excel trên máy bạn gợi ý dấu chấm phẩy khi gõ hàm, hãy thay toàn bộ dấu phẩy ngăn đối số bằng dấu chấm phẩy.
Ví dụ giả định: tra đơn giá theo mã hàng trong sổ bán hàng
Một cửa hàng vật tư nông nghiệp ở Đồng Tháp có hai trang tính. Trang DanhMuc chứa cột A là mã hàng, cột B là tên hàng, cột C là đơn vị tính, cột D là đơn giá. Trang BanHang ghi từng lần bán, cột B là mã hàng nhân viên gõ vào. Mục tiêu: cột E của BanHang tự hiện đơn giá.
- Tại ô E2 của trang BanHang, gõ: =XLOOKUP(B2,DanhMuc!$A$2:$A$500,DanhMuc!$D$2:$D$500,"Chưa có mã"). Dấu $ giữ vùng danh mục đứng yên khi kéo công thức xuống.
- Kéo công thức xuống hết các dòng đã có. Dòng nào hiện "Chưa có mã" là mã nhân viên gõ sai hoặc hàng mới chưa thêm vào danh mục, cần xem lại ngay.
- Muốn lấy cùng lúc tên hàng, đơn vị và đơn giá, chọn vùng trả về là DanhMuc!$B$2:$D$500. Trong Microsoft 365 và Excel 2021, kết quả tự tràn sang ba ô bên phải; các ô đó phải trống, nếu không hàm báo #SPILL!.
- Nên chuyển trang DanhMuc thành Table (chọn vùng rồi bấm Ctrl+T, đặt tên tblDanhMuc). Công thức trở thành =XLOOKUP(B2,tblDanhMuc[MaHang],tblDanhMuc[DonGia],"Chưa có mã") và tự mở rộng khi bạn thêm mã hàng mới, không phải sửa số 500.
- Kiểm năm dòng ngẫu nhiên bằng cách tự tra tay trong danh mục, và cố ý gõ thử một mã không tồn tại để chắc rằng dòng đó hiện thông báo thay vì một con số.
Với cách đặt này, khi giá trong danh mục thay đổi, mọi dòng bán hàng cũ cũng đổi giá theo. Nếu bạn cần giữ giá tại thời điểm bán, hãy sao chép cột đơn giá và dán dạng giá trị cho các dòng đã chốt sổ, hoặc thiết kế bảng giá có cột ngày hiệu lực. Câu hỏi thiết kế đó thuộc danh mục thiết kế bảng và cơ sở dữ liệu nhỏ của trang.
Những việc VLOOKUP làm vất vả mà XLOOKUP làm gọn
Tra sang bên trái. VLOOKUP chỉ tìm ở cột đầu tiên của vùng và trả về cột bên phải. Nếu bảng nhân sự có mã nhân viên ở cột D mà bạn cần lấy họ tên ở cột B, VLOOKUP buộc bạn sắp xếp lại cột hoặc dùng INDEX kết hợp MATCH. XLOOKUP chỉ cần chỉ định vùng tìm là cột D, vùng trả về là cột B.
Không vỡ khi chèn thêm cột. VLOOKUP dùng số thứ tự cột, ví dụ 4. Ai đó chèn một cột vào giữa bảng tra, số 4 giờ trỏ vào cột khác và công thức lặng lẽ trả sai. XLOOKUP tham chiếu thẳng vào cột kết quả nên tự điều chỉnh theo.
Lấy giá trị mới nhất. Bảng nhập hàng ghi nhiều lần cho cùng một mã, bạn cần giá nhập lần gần nhất. Với search_mode bằng -1, XLOOKUP tìm từ dưới lên và trả về dòng cuối cùng khớp, miễn là bảng ghi theo thứ tự thời gian.
Tra theo bậc mà không cần sắp xếp. Bảng hoa hồng theo mức doanh số, bảng phí vận chuyển theo cân nặng: với match_mode bằng -1, XLOOKUP lấy mức lớn nhất không vượt quá giá trị cần tra. VLOOKUP gần đúng làm được việc tương tự nhưng bắt buộc bảng phải sắp xếp tăng dần, sai thứ tự là sai kết quả.
Tự xử lý trường hợp không tìm thấy. Thay vì bọc IFERROR bên ngoài như với VLOOKUP, bạn ghi thẳng nội dung thay thế vào đối số thứ tư. Cách này còn an toàn hơn, vì IFERROR che cả những lỗi khác như sai tên trang tính, khiến bạn không biết công thức đang hỏng.
Nhờ AI chuyển công thức VLOOKUP cũ sang XLOOKUP
Nhiều tệp trong công ty được làm từ nhiều năm trước, đầy VLOOKUP lồng IFERROR, có khi lồng thêm MATCH để lấy số cột. Nếu cả nhóm đã dùng Microsoft 365, chuyển sang XLOOKUP giúp công thức dễ đọc và ít vỡ hơn. Đây là việc AI làm khá tốt, miễn bạn cho nó đủ bối cảnh.
Câu lệnh mẫu: "Mình có công thức Excel sau ở ô F2 của trang tính DonHang: [dán công thức]. Trang BangGia có cột A là mã hàng (dạng chữ), cột D là giá bán lẻ. Hãy chuyển sang XLOOKUP, giữ nguyên kết quả cũ trong mọi trường hợp, kể cả khi không tìm thấy mã. Giải thích VLOOKUP cũ đang tìm gần đúng hay chính xác, và nếu là gần đúng thì kết quả mới có khác không." Câu cuối quan trọng: nếu công thức cũ bỏ quên đối số FALSE, nó đang tìm gần đúng, và bản XLOOKUP khớp chính xác sẽ cho kết quả khác. Khi đó bạn phát hiện ra bảng cũ đã sai từ lâu, chứ không phải AI sai.
Sau khi có công thức mới, đừng thay ngay vào cột cũ. Đặt nó ở một cột bên cạnh, thêm một cột so sánh bằng công thức kiểu =F2=G2, rồi lọc những dòng FALSE. Dòng nào lệch thì xem kỹ: hoặc dữ liệu có vấn đề, hoặc công thức cũ vốn đã sai. Chỉ khi cột so sánh toàn TRUE, hoặc mọi dòng lệch đã được giải thích, mới thay thế. Và nhớ rằng nếu tệp còn gửi cho đối tác dùng Excel 2016, họ sẽ thấy #NAME? ở mọi ô XLOOKUP.
Các bẫy khiến XLOOKUP trả #N/A dù mã có trong bảng
Phần lớn trường hợp XLOOKUP "không tìm thấy" không phải do công thức mà do dữ liệu hai bên trông giống nhau nhưng không giống hệt. Đây là các nguyên nhân hay gặp, theo thứ tự nên kiểm:
- Khoảng trắng thừa ở đầu hoặc cuối mã, thường do dán từ phần mềm bán hàng hay từ tin nhắn. Kiểm bằng hàm LEN so độ dài hai bên; xử lý tạm bằng cách tìm theo TRIM(B2), xử lý gốc là làm sạch cột.
- Số lưu dạng chữ ở một bên và dạng số ở bên kia, ví dụ mã 125 và "125". Excel coi hai giá trị này khác nhau. Ô có tam giác xanh nhỏ ở góc thường là số đang lưu dạng chữ.
- Số 0 ở đầu bị mất: mã "00125" khi nhập vào cột định dạng số thành 125. Cần thống nhất mã hàng là chữ ở mọi bảng.
- Vùng tìm và vùng trả về lệch kích thước, ví dụ A2:A500 và D2:D400. Trường hợp này hàm báo #VALUE! chứ không phải #N/A, nhưng hay gặp khi sửa tay.
- Quên khoá vùng bằng dấu $, kéo xuống vài trăm dòng thì vùng tìm trượt theo, các dòng cuối không còn nhìn thấy đầu bảng.
- Ký tự trông giống nhau: chữ O và số 0, gạch ngang và gạch nối dài, dấu tiếng Việt gõ theo hai kiểu bảng mã khác nhau. Hàm EXACT giúp xác nhận hai ô có giống hệt không.
Khi hỏi AI vì sao XLOOKUP trả #N/A, hãy gửi kèm kết quả của LEN, ISNUMBER và ISTEXT trên ô cần tìm và ô tương ứng trong bảng tra. Ba thông tin này giúp chatbot chẩn đoán đúng thay vì đề xuất viết lại công thức. Việc làm sạch dữ liệu tận gốc có hướng dẫn riêng ở danh mục làm sạch và chuẩn hoá dữ liệu.
Khi nào chưa nên dùng XLOOKUP
XLOOKUP gọn và an toàn hơn, nhưng không phải lúc nào cũng nên thay. Trường hợp rõ nhất là tệp phải chạy trên nhiều máy với phiên bản khác nhau: kế toán dùng Excel 2016, kho dùng Microsoft 365, đối tác mở bằng một phần mềm bảng tính khác. Hàm không được hỗ trợ ở bất kỳ máy nào trong chuỗi đó sẽ làm cả tệp mất giá trị. Lúc này INDEX kết hợp MATCH vẫn là lựa chọn chạy được ở gần như mọi phiên bản.
Trường hợp thứ hai là bảng rất lớn với hàng chục nghìn công thức dò tìm. Mỗi công thức dò trên toàn cột làm tệp tính chậm. Dù dùng hàm nào, bạn nên giới hạn vùng tìm ở đúng phần có dữ liệu hoặc dùng Table, và cân nhắc gom dữ liệu bằng Power Query thay vì để hàng nghìn ô cùng tra một bảng.
Trường hợp cuối là khi bạn chưa hiểu hàm. Một công thức XLOOKUP do AI viết, có match_mode và search_mode bạn không nắm, nguy hiểm hơn một VLOOKUP bạn hiểu rõ. Nếu chưa quen, hãy bắt đầu với ba đối số bắt buộc cộng đối số thứ tư, và chỉ thêm hai đối số cuối khi thật sự cần tìm theo bậc hay tìm từ dưới lên.
Câu hỏi thường gặp
XLOOKUP có trong Excel 2019 không?
Không. XLOOKUP có trong Microsoft 365, Excel 2021, các bản mới hơn và Excel trên web. Excel 2019, 2016 và cũ hơn không có hàm này, gõ vào sẽ báo lỗi #NAME?. Nếu cần chạy trên bản cũ, hãy dùng INDEX kết hợp MATCH hoặc VLOOKUP với đối số cuối là FALSE.
XLOOKUP có tìm được theo hai điều kiện không?
Có. Cách phổ biến là nối hai điều kiện thành một, ví dụ tìm giá trị ghép từ mã hàng và mã kho trong vùng ghép tương ứng của bảng tra. Cách khác là dùng phép nhân các điều kiện để tạo mảng gồm số 1 và số 0 rồi tìm số 1. Hãy nhờ AI viết và kiểm kỹ trên vài dòng.
Vì sao XLOOKUP trả về số 0 thay vì ô trống?
Khi ô kết quả trong bảng tra để trống, XLOOKUP trả về giá trị trống mà Excel hiển thị thành 0 trong ô số. Đây là hành vi chung của các hàm dò tìm. Nếu muốn hiện trống, bạn có thể bọc thêm điều kiện kiểm tra kết quả có rỗng không, hoặc dùng định dạng số ẩn số 0.
Có nên thay hết VLOOKUP trong tệp cũ sang XLOOKUP không?
Chỉ nên thay khi mọi người dùng tệp đều có phiên bản hỗ trợ XLOOKUP, và nên thay từng cột một kèm cột so sánh kết quả cũ mới. Thay hàng loạt một lúc dễ làm lộ hoặc che những sai sót cũ mà không ai nhận ra, rồi bảng báo cáo bỗng đổi số.
XLOOKUP tìm có phân biệt chữ hoa chữ thường không?
Không. XLOOKUP coi "ab01" và "AB01" là giống nhau. Nếu mã hàng của bạn phân biệt chữ hoa thường, cần kết hợp thêm hàm EXACT trong một công thức mảng. Trường hợp này hiếm trong văn phòng, nhưng đáng biết khi mã do hệ thống ngoài tạo ra.
Dùng XLOOKUP trong Google Sheets có được không?
Google Sheets đã bổ sung hàm XLOOKUP với cú pháp tương tự Excel, nhưng một số chi tiết có thể khác nhau giữa hai nền tảng. Khi chuyển tệp qua lại, hãy kiểm lại vài dòng kết quả. Các hàm đặc thù của Google Sheets có bài riêng ở danh mục Google Sheets của trang.