Nội dung bài viết
VLOOKUP gần như là hàm đầu tiên kế toán viên nào cũng học. Nhưng trong đối chiếu, nó có vài điểm yếu gây sai số âm thầm: lệch cột khi chèn thêm cột, khớp gần đúng khi quên đối số cuối, không tra được về bên trái. Bài này so sánh XLOOKUP VLOOKUP qua một ví dụ đối chiếu bảng kê hoá đơn đầu vào với sổ chi tiết công nợ phải trả, có công thức dùng ngay. Ví dụ được ACCA Consulting Vietnam dựng lại từ quy trình đối chiếu hằng tháng thường gặp.
1. So sánh nhanh XLOOKUP VLOOKUP: khác biệt cốt lõi
| Điểm so sánh | VLOOKUP | XLOOKUP |
|---|---|---|
| Kiểu khớp mặc định | Gần đúng nếu bỏ trống đối số cuối | Khớp chính xác |
| Chỉ định cột trả về | Bằng số thứ tự cột, lệch khi chèn cột | Bằng vùng cột, không lệch |
| Tra về bên trái cột khoá | Không được | Được |
| Khi không tìm thấy | Trả #N/A, phải bọc IFERROR | Có sẵn đối số giá trị thay thế |
| Tìm từ dưới lên (lấy dòng cuối) | Không có sẵn | Có (search_mode = -1) |
| Phiên bản Excel | Mọi phiên bản | Excel 2021, Microsoft 365; Google Sheets cũng có |
Cú pháp XLOOKUP: =XLOOKUP(giá_trị_tìm, vùng_tìm, vùng_trả_về, [nếu_không_thấy], [kiểu_khớp], [kiểu_tìm]). Ba đối số đầu là đủ cho phần lớn việc đối chiếu.
2. Ví dụ: đối chiếu 200 hoá đơn đầu vào với sổ chi tiết
Tình huống giả định: tháng 3, bạn có bảng kê hoá đơn đầu vào tải từ hệ thống hoá đơn điện tử (sheet BangKe) và sổ chi tiết phải trả xuất từ phần mềm kế toán (sheet So). Mục tiêu: biết hoá đơn nào đã ghi sổ, ghi đúng tiền chưa, hoá đơn nào bị sót. Cách tải bảng kê hàng loạt có trong bài tải hàng loạt hoá đơn đầu vào.
- 1Tạo cột khoá
Ở cả hai sheet, thêm cột Khoa = MST & "|" & số hoá đơn, vì số hoá đơn có thể trùng giữa các nhà cung cấp.
- 2Chuẩn hoá khoá
Dùng TRIM và bỏ số 0 đầu nếu một bên ghi 0000123, bên kia ghi 123.
- 3Kéo số tiền từ sổ sang bảng kê
=XLOOKUP([@Khoa],So[Khoa],So[TongTien],"Chưa ghi sổ")
- 4Tính chênh lệch
=IF(ISNUMBER([@TienSo]),[@TongTien]-[@TienSo],"")
- 5Đối chiếu chiều ngược
Ở sheet So, tra ngược sang BangKe để tìm bút toán không có hoá đơn tương ứng.
Với VLOOKUP, bước 3 buộc cột Khoa phải nằm bên trái cột TongTien trong sheet So, và bạn phải đếm số thứ tự cột. Chỉ cần ai đó chèn thêm cột “Ghi chú” là công thức lấy nhầm cột.
- Khớp số tiền182 hoá đơn
- Chưa ghi sổ9 hoá đơn
- Lệch số tiền6 hoá đơn
- Có trong sổ, không có trên bảng kê3 hoá đơn
Ví dụ minh hoạ, số liệu giả định. 18 trường hợp không khớp là danh sách cần kiểm tra lại với chứng từ gốc.
Xem dạng bảng
| Khớp số tiền | 182 hoá đơn |
| Chưa ghi sổ | 9 hoá đơn |
| Lệch số tiền | 6 hoá đơn |
| Có trong sổ, không có trên bảng kê | 3 hoá đơn |
3. Tra nhiều điều kiện và các tình huống nâng cao
Tra theo 2 điều kiện mà không cần cột khoá
Nếu không muốn thêm cột phụ, XLOOKUP có thể tra theo nhiều điều kiện bằng phép nhân mảng: =XLOOKUP(1,(So[MST]=[@MST])*(So[SoHD]=[@SoHD]),So[TongTien],"Chưa ghi sổ"). Mỗi điều kiện trả về đúng/sai, nhân lại bằng 1 khi tất cả đều đúng. Cách này tiện nhưng chậm hơn với bảng hàng chục nghìn dòng; khi đó cột khoá vẫn là lựa chọn tốt.
Lấy lần thanh toán gần nhất
Muốn biết ngày thanh toán gần nhất cho một nhà cung cấp trong sổ tiền gửi (sắp xếp theo ngày tăng dần): =XLOOKUP([@MST],SoTG[MST],SoTG[Ngay],"Chưa thanh toán",0,-1). Đối số cuối bằng -1 nghĩa là tìm từ dưới lên.
Trả về nhiều cột một lúc
=XLOOKUP([@MST],NCC[MST],NCC[[Ten]:[DiaChi]]) trả về cả tên và địa chỉ sang các ô bên cạnh. Chừa trống các ô bên phải để tránh lỗi #SPILL!.
4. Bảng xử lý lỗi thường gặp khi đối chiếu
| Hiện tượng | Nguyên nhân hay gặp | Cách xử lý |
|---|---|---|
| #N/A dù nhìn thấy dữ liệu | Một bên là số, một bên là văn bản | Đồng nhất kiểu: dùng TEXT hoặc VALUE cho cả hai cột khoá |
| #N/A với số hoá đơn | Có số 0 đầu hoặc khoảng trắng thừa | TRIM, thống nhất độ dài số hoá đơn |
| Lấy nhầm số tiền | VLOOKUP thiếu FALSE hoặc lệch cột | Chuyển sang XLOOKUP, khớp chính xác |
| Chỉ ra kết quả đầu tiên | Khoá bị trùng (1 hoá đơn ghi 2 bút toán) | Dùng SUMIFS để cộng, hoặc COUNTIFS để phát hiện trùng |
| #NAME? | Phiên bản Excel không có XLOOKUP | Dùng INDEX/MATCH thay thế |
| #SPILL! | Vùng trả về bị chặn bởi ô có dữ liệu | Xoá các ô cản bên phải hoặc bên dưới |
Trường hợp “một hoá đơn ghi nhiều bút toán” rất phổ biến (ví dụ tách tiền hàng và thuế). Khi đó đối chiếu bằng tổng là đúng hơn: =SUMIFS(So[TongTien],So[Khoa],[@Khoa]). Xem thêm các hàm hỗ trợ ở bài 20 hàm Excel kế toán dùng hằng ngày.
5. Dùng trợ lý AI hỗ trợ viết công thức đối chiếu
- “Viết công thức XLOOKUP tra theo 2 điều kiện MST và SoHD từ Table So, trả về TongTien, nếu không thấy ghi ‘Chưa ghi sổ’. Excel 365.”
- “Chuyển công thức VLOOKUP sau sang XLOOKUP và giải thích khác biệt: [dán công thức].”
- “Liệt kê các nguyên nhân khiến XLOOKUP trả #N/A khi cột khoá là số hoá đơn tải từ hệ thống hoá đơn điện tử.”
Chỉ đưa cấu trúc bảng và công thức cho AI. Bảng kê hoá đơn chứa MST, tên đối tác, giá trị giao dịch; không dán lên công cụ AI công cộng. Toàn bộ quy trình đối chiếu hoá đơn với sổ có trong bài đối chiếu hoá đơn đầu vào với sổ sách.
Câu hỏi thường gặp
Có nên thay toàn bộ VLOOKUP trong file cũ bằng XLOOKUP?
Không cần thay hết ngay. Ưu tiên các file đối chiếu dùng hằng tháng và các công thức VLOOKUP thiếu đối số FALSE. Nếu file còn được mở trên máy dùng Excel đời cũ, giữ INDEX/MATCH.
XLOOKUP có chậm hơn VLOOKUP không?
Với bảng vài nghìn dòng, khác biệt không đáng kể. Với bảng rất lớn tra nhiều điều kiện bằng phép nhân mảng, nên chuyển sang cột khoá hoặc dùng Power Query để gộp bảng.
Google Sheets có dùng được XLOOKUP không?
Có, Google Sheets đã hỗ trợ XLOOKUP với cú pháp tương tự. Nên thử trên một bản sao trước khi áp cho file thật.
ACCA Consulting Vietnam có đối chiếu hoá đơn đầu vào cho doanh nghiệp không?
Có. Đối chiếu hoá đơn với sổ sách là một phần trong dịch vụ kế toán trọn gói; doanh nghiệp đang tự làm cũng có thể đăng ký kiểm tra sơ bộ sổ sách miễn phí.
Đối chiếu chính xác, không còn hoá đơn bị sót
ACCA Consulting Vietnam (ACCA Consulting VN) cung cấp dịch vụ kế toán tại TP.HCM, đối chiếu hoá đơn và sổ sách hằng tháng. Nhận tư vấn.
Nhận tư vấn miễn phíZaloWhatsApp
