2026-10-01 · Power Query & Excel
Một bảng kê vật tư 12 dòng, cột Thành tiền lấy đơn giá từ bảng giá đặt bên cạnh, tổng là 209.165.500 đồng. Có người chèn thêm cột "ĐVT" vào giữa bảng giá. Cột Thành tiền dùng VLOOKUP về 0 ở cả 12 dòng, không ô nào báo lỗi. Cột dùng XLOOKUP vẫn ra 209.165.500 đồng.
Khác biệt đáng quan tâm giữa XLOOKUP và VLOOKUP nằm ở những chỗ như vậy: hàm nào cho ra số sai mà bảng vẫn trông bình thường. Bài này chạy thử cả hai hàm trong Excel Microsoft 365 trên các file demo của EzTech (một bảng bán hàng 9.998 dòng với 35 mặt hàng, cùng vài bảng tra nhỏ), ghi lại từng chỗ hai hàm cho kết quả khác nhau và những trường hợp vẫn nên giữ VLOOKUP.
Cú pháp XLOOKUP và VLOOKUP đặt cạnh nhau
Cùng việc tra đơn giá theo mã hàng, bảng giá nằm ở cột G (Mã hàng), H (Tên hàng), I (Đơn giá):
- VLOOKUP:
=VLOOKUP(A2,$G$2:$I$9,3,FALSE)tìm A2 ở cột đầu tiên của vùng G:I, trả về cột thứ 3 của vùng đó. - XLOOKUP:
=XLOOKUP(A2,$G$2:$G$9,$I$2:$I$9)tìm A2 trong cột G, trả về giá trị cùng dòng ở cột I.
VLOOKUP nhận một vùng và một con số thứ tự cột. XLOOKUP nhận thẳng cột để tìm và cột để lấy. Cú pháp đầy đủ của XLOOKUP có thêm ba tham số tùy chọn:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- if_not_found: chữ trả về khi không tìm thấy. Bỏ trống thì trả #N/A.
- match_mode: 0 là khớp chính xác và là mặc định; -1 lấy giá trị khớp, không có thì lấy giá trị nhỏ hơn liền kề; 1 lấy giá trị khớp, không có thì lấy giá trị lớn hơn liền kề; 2 cho phép ký tự đại diện * và ?.
- search_mode: 1 tìm từ dòng đầu xuống (mặc định); -1 tìm từ dòng cuối lên.
Mặc định của hai hàm ngược nhau: VLOOKUP bỏ trống tham số cuối thì tra gần đúng, XLOOKUP bỏ trống thì tra chính xác. Phần lớn khác biệt bên dưới bắt đầu từ đây.
Bốn tình huống VLOOKUP ra kết quả sai mà không báo lỗi
1. Bỏ trống tham số cuối của VLOOKUP
Tham số cuối của VLOOKUP (range_lookup) bỏ trống thì Excel hiểu là TRUE. Trang hướng dẫn của Microsoft ghi rõ: chế độ này yêu cầu cột đầu tiên của bảng đã được sắp xếp, nếu chưa thì giá trị trả về có thể không như mong đợi. Trên thực tế công thức vẫn ra một giá trị, chỉ là giá trị của dòng khác.
Chạy thử trên file bán hàng 9.998 dòng: lấy nhóm hàng cho từng dòng từ danh mục 35 mặt hàng, công thức không có FALSE ở cuối.
- Danh mục sắp xếp theo cột Nhóm hàng: 8.927/9.998 dòng nhận sai nhóm, trong đó chỉ 144 dòng hiện #N/A, số còn lại hiện một tên nhóm trông hoàn toàn bình thường. Cả 608 dòng "Cáp sạc Type-C" nhận nhóm "Âm thanh" thay vì "Phụ kiện".
- Cùng danh mục đó sắp xếp A-Z theo tên hàng (bằng lệnh Sort của Excel): 0 dòng sai.
Cùng một công thức, đúng hay sai tùy vào việc có ai sắp xếp lại danh mục hay không. Mã chưa có trong danh mục còn khó phát hiện hơn. File demo có 12 mã vật tư cần tra, danh mục chỉ có VT001 đến VT006. Với FALSE, 4 mã chưa có (VT007, VT009, VT011, VT015) báo #N/A. Bỏ trống tham số cuối thì cả 4 mã nhận tên "Ván MDF phủ melamine" của mã VT006, mã đứng cuối danh mục.
XLOOKUP mặc định tra chính xác nên không có bẫy này. Nếu vẫn dùng VLOOKUP, luôn gõ đủ FALSE (hoặc 0) ở tham số cuối.
2. Chèn thêm cột vào bảng tra
Quay lại ví dụ đầu bài. Công thức Thành tiền trong file demo là =IFERROR(VLOOKUP(A2,$G$2:$I$9,3,0)*C2,""). Chèn một cột trống "ĐVT" giữa Tên hàng và Đơn giá, Excel tự nới vùng tra thành $G$2:$J$9 nhưng số 3 giữ nguyên. Cột thứ 3 của vùng giờ là cột ĐVT đang trống, VLOOKUP trả 0, Thành tiền bằng 0 ở cả 12 dòng.
Khi cột ĐVT đã được điền chữ, phép nhân báo lỗi và IFERROR đổi lỗi đó thành chuỗi rỗng. Cả 12 ô Thành tiền trống, tổng vẫn là 0, không ô nào báo lỗi.
Công thức XLOOKUP =XLOOKUP(A2,$G$2:$G$9,$I$2:$I$9)*C2 sau khi chèn cột tự đổi thành $J$2:$J$9, vẫn trỏ đúng cột Đơn giá. Tổng giữ nguyên 209.165.500 đồng.
3. Cột cần lấy nằm bên trái cột tra
VLOOKUP chỉ tìm ở cột đầu tiên của vùng. Danh mục đặt Nhóm hàng ở cột A, Tên hàng ở cột B thì VLOOKUP không tra được nhóm hàng theo tên. Cách làm quen thuộc là đảo thứ tự cột, hoặc dùng INDEX kết hợp MATCH:
=INDEX($A$2:$A$36,MATCH(A2,$B$2:$B$36,0))=XLOOKUP(A2,$B$2:$B$36,$A$2:$A$36)
Chạy trên 9.998 dòng, cả hai công thức đều cho 0 dòng sai. INDEX/MATCH chạy được trên mọi phiên bản Excel, XLOOKUP ngắn hơn và dễ đọc hơn.
4. Bảng bậc chiết khấu bị đảo thứ tự
Tra gần đúng có chỗ dùng đúng: bảng bậc chiết khấu theo doanh số. File demo có 4 bậc (từ 0, 100 triệu, 500 triệu, 1 tỷ đồng; chiết khấu 0%, 2%, 5%, 8%) và 12 khách hàng. Bảng bậc xếp tăng dần thì =VLOOKUP(B2,$F$2:$G$5,2,TRUE) và =XLOOKUP(B2,$F$2:$F$5,$G$2:$G$5,,-1) cho cùng kết quả ở cả 12 khách.
Đảo bảng bậc thành xếp giảm dần (1 tỷ đứng đầu), VLOOKUP sai cả 12 khách: 6 ô báo #N/A, 6 ô ra tỷ lệ sai. XLOOKUP với match_mode -1 vẫn đúng 12/12, vì chế độ này không đòi bảng phải sắp xếp.
XLOOKUP làm thêm được những gì
Báo "không tìm thấy" bằng dòng chữ của mình
Tham số if_not_found thay cho việc bọc IFERROR: =XLOOKUP(A2,$D$2:$D$7,$E$2:$E$7,"Chưa có trong danh mục"). Với 4 mã chưa có ở ví dụ trên, ô kết quả hiện đúng dòng chữ đó. IFERROR bắt mọi loại lỗi, kể cả lỗi do công thức hỏng như ở tình huống chèn cột. Khi vẫn dùng VLOOKUP, IFNA an toàn hơn vì chỉ bắt #N/A, các lỗi khác vẫn hiện ra để người xem thấy.
Tìm từ dưới lên để lấy lần gần nhất
VLOOKUP luôn trả dòng khớp đầu tiên. Bảng bán hàng xếp theo ngày tăng dần, tra ngày mua theo tên khách thì VLOOKUP trả lần mua đầu tiên. Chạy trên 26 khách hàng của file demo: VLOOKUP trả các ngày từ 01/01/2024 đến 07/01/2024. XLOOKUP với search_mode -1, =XLOOKUP(E2,$A$2:$A$9999,$B$2:$B$9999,,0,-1), trả ngày mua gần nhất, từ 25/12/2025 đến 31/12/2025.
Trả nhiều cột bằng một công thức
=XLOOKUP(B2,$G$2:$G$9,$H$2:$I$9) trả cả Tên vật tư lẫn Đơn giá, kết quả tràn sang hai ô liền nhau: mã VT004 ra "Bulong M12" và 6.500. Với VLOOKUP phải viết hai công thức, mỗi công thức một số thứ tự cột.
Khi nào vẫn nên dùng VLOOKUP
Khi file sẽ được mở bằng Excel 2019 hoặc Excel 2016. Trang hướng dẫn XLOOKUP của Microsoft ghi hàm này không có ở hai phiên bản đó; nó có ở Excel cho Microsoft 365, Excel 2021 và Excel 2024. Lưu thử một file có XLOOKUP rồi mở phần XML bên trong file, công thức được ghi là _xlfn.XLOOKUP(...), tức một hàm mà Excel đời cũ không biết. Microsoft giải thích các phiên bản cũ không nhận ra hàm mới và hiện lỗi #NAME? thay cho kết quả.
Xem phiên bản đang dùng ở File → Account → About Excel. Nếu người nhận file dùng bản cũ, giữ VLOOKUP với tham số cuối là FALSE, hoặc dùng INDEX/MATCH khi cần tra sang trái. Hai cách này chạy trên mọi phiên bản.
Chọn hàm nào: bảng tóm tắt
| Tình huống | Nên dùng | Cần chú ý |
|---|---|---|
| Tra chính xác theo mã, file chỉ mở bằng Excel 365, 2021, 2024 | XLOOKUP | Mặc định đã là khớp chính xác |
| File gửi người dùng Excel 2016, 2019 | VLOOKUP có FALSE, hoặc INDEX/MATCH | XLOOKUP sẽ hiện #NAME? |
| Cột cần lấy nằm bên trái cột tra | XLOOKUP hoặc INDEX/MATCH | VLOOKUP không tra được |
| Bảng tra hay bị chèn, xóa cột | XLOOKUP | VLOOKUP giữ số thứ tự cột cũ |
| Tra theo bậc: chiết khấu, hoa hồng, thuế lũy tiến | XLOOKUP match_mode -1, hoặc VLOOKUP TRUE | VLOOKUP cần bảng bậc xếp tăng dần |
| Lấy lần phát sinh gần nhất | XLOOKUP search_mode -1 | VLOOKUP chỉ trả dòng khớp đầu tiên |
| Ghép hai bảng hàng chục nghìn dòng, lặp lại mỗi tháng | Merge Queries trong Power Query | Làm một lần, các tháng sau chỉ Refresh |
Câu hỏi thường gặp
VLOOKUP báo #N/A dù mã hai bên nhìn y hệt nhau?
Thường do dấu cách thừa hoặc số đang lưu dạng chữ. Trong một file demo, 11/12 mã khách hàng có dấu cách ở đầu hoặc cuối và cả 11 dòng báo #N/A; bọc TRIM quanh giá trị cần tra thì hết. XLOOKUP cũng gặp đúng lỗi này: với cả hai hàm, " KH003" có dấu cách và "KH003" là hai giá trị khác nhau. Cách dò và sửa từng loại lỗi có ở bài làm sạch dữ liệu trong Excel.
Có nên sửa hết VLOOKUP cũ sang XLOOKUP?
Không cần sửa hàng loạt. Việc nên làm trước là rà các công thức VLOOKUP thiếu tham số cuối và các bảng tra hay bị chèn cột. Công thức đã có FALSE, bảng tra ổn định thì vẫn chạy đúng.
Excel của tôi không có XLOOKUP thì làm thế nào?
Dùng INDEX kết hợp MATCH với số 0 ở cuối MATCH để tra chính xác. Cặp hàm này tra được sang trái và không bị lệch khi chèn cột, vì INDEX trỏ thẳng vào cột cần lấy thay vì dùng số thứ tự.
Tra cứu nhanh và học tiếp
Cú pháp và ví dụ của từng hàm có ở trang tra cứu hàm XLOOKUP và hàm VLOOKUP. Khi phải ghép hai bảng lớn từ file mới mỗi tháng, cách bền hơn là dùng Power Query, có hướng dẫn ở bài Merge Queries thay VLOOKUP.
Khóa DATA FINANCE của EzTech đi từ Power Query chuyên sâu trong Excel đến báo cáo quản trị trên Power BI, dành cho người làm kế toán, tài chính. Có thể tự kiểm tra mức nắm Power Query hiện tại bằng bài trắc nghiệm Power Query miễn phí.
