VLOOKUP, XLOOKUP hay INDEX MATCH: chọn hàm tra cứu nào và cách sửa lỗi #N/A

Trong các bài test Excel khi tuyển dụng, câu hỏi xuất hiện thường xuyên nhất là: "Ghép dữ liệu từ bảng này sang bảng kia." Nghe đơn giản, nhưng đây là chỗ nhiều ứng viên bị loại — không phải vì không biết hàm, mà vì không xử lý được lỗi #N/A khi dữ liệu thực tế không sạch.

Bài này giải quyết cả hai phần: chọn đúng hàm và sửa đúng lỗi.

Bài toán tra cứu là gì?

Bạn có hai bảng:

• Bảng dữ liệu chính: danh sách 500 đơn hàng, mỗi đơn có Mã sản phẩm.
• Bảng danh mục: danh sách 80 sản phẩm với Mã sản phẩm, Tên sản phẩm, Đơn giá, Nhóm hàng.

Việc cần làm: điền Tên sản phẩm và Đơn giá vào bảng đơn hàng, dựa trên mã.

Đó chính là tra cứu. Ba công cụ chính giải bài này là VLOOKUP, XLOOKUP và cặp INDEX + MATCH.

XLOOKUP — nên là lựa chọn mặc định

XLOOKUP có trên Microsoft 365, Office 2021 và Office 2024. Nếu máy bạn có hàm này, hãy dùng nó.

=XLOOKUP(giá_trị_tìm, mảng_tìm, mảng_trả_về, [nếu_không_tìm_thấy], [chế_độ_khớp], [chế_độ_dò])

Ví dụ với bảng trên:

=XLOOKUP(B2, DanhMuc!A:A, DanhMuc!B:B, "Không có mã")

Vì sao XLOOKUP tốt hơn hẳn:

Lấy nhiều cột trong một công thức:

=XLOOKUP(B2, DanhMuc!$A$2:$A$100, DanhMuc!$B$2:$D$100, "Không có")

Kết quả tràn ra ba ô liền kề — tên, đơn giá, nhóm hàng.

Tra cứu từ dưới lên (lấy bản ghi mới nhất, ví dụ lần mua gần nhất của một khách):

=XLOOKUP(B2, LichSu!A:A, LichSu!C:C, "Chưa mua", 0, -1)

Tham số cuối -1 nghĩa là dò từ dòng cuối lên.

VLOOKUP — vẫn cần biết, vì file người khác đầy nó

=VLOOKUP(giá_trị_tìm, vùng_bảng, số_thứ_tự_cột, [kiểu_khớp])

=VLOOKUP(B2, DanhMuc!$A$2:$D$100, 2, FALSE)

Ba quy tắc bắt buộc nhớ:

1. Luôn kết thúc bằng FALSE (hoặc 0) khi cần khớp chính xác. Bỏ trống tham số này, Excel dùng khớp gần đúng và trả kết quả sai một cách âm thầm — nguy hiểm hơn cả báo lỗi.
2. Cột chứa giá trị tìm phải là cột đầu tiên của vùng bảng. VLOOKUP không nhìn sang bên trái.
3. Vùng bảng phải cố định bằng $ ($A$2:$D$100) nếu bạn định kéo công thức xuống. Nhấn F4 để thêm nhanh.

Điểm yếu chí mạng của VLOOKUP trong thực tế công việc: nó dùng số thứ tự cột. Ai đó chèn thêm một cột vào giữa bảng danh mục, mọi công thức VLOOKUP vẫn chạy nhưng trả về dữ liệu của cột khác. Sai lặng lẽ là kiểu sai tệ nhất.

Cách giảm rủi ro nếu buộc phải dùng VLOOKUP:

=VLOOKUP(B2, DanhMuc!$A$2:$D$100, MATCH("Đơn giá", DanhMuc!$A$1:$D$1, 0), FALSE)

Số cột giờ được tính theo tên tiêu đề, chèn cột không còn phá công thức.

INDEX + MATCH — linh hoạt nhất, chạy trên mọi phiên bản

=INDEX(vùng_kết_quả, MATCH(giá_trị_tìm, vùng_tìm, 0))

=INDEX(DanhMuc!$B$2:$B$100, MATCH(B2, DanhMuc!$A$2:$A$100, 0))

Cách hiểu đơn giản: MATCH trả lời "giá trị này nằm ở dòng thứ mấy", INDEX trả lời "lấy giá trị ở dòng thứ đó trong cột kia".

Ưu điểm:

• Chạy trên mọi phiên bản Excel, kể cả Office 2010.
• Tra cứu được cả hai chiều, giống XLOOKUP.
• Không phụ thuộc số thứ tự cột.
• Tính toán nhanh hơn VLOOKUP trên bảng rất lớn, vì chỉ đọc hai cột thay vì cả vùng bảng.

Nhược điểm duy nhất: khó đọc hơn với người mới.

Tra cứu hai chiều (dò cả dòng lẫn cột — ví dụ tìm doanh thu của "Miền Bắc" trong "Tháng 7" từ một bảng ma trận):

=INDEX($B$2:$M$10, MATCH($A15,$A$2:$A$10,0), MATCH(B$14,$B$1:$M$1,0))

Với XLOOKUP, cùng bài toán viết là:

=XLOOKUP($A15, $A$2:$A$10, XLOOKUP(B$14, $B$1:$M$1, $B$2:$M$10))

Bảng so sánh nhanh

Kết luận thực dụng:

• Dùng XLOOKUP nếu máy hỗ trợ và file chỉ lưu hành nội bộ.
• Dùng INDEX + MATCH nếu file phải gửi ra ngoài, không rõ người nhận dùng Excel bản nào.
• Dùng VLOOKUP khi sửa file có sẵn của người khác, hoặc khi đi thi chứng chỉ theo giáo trình cũ.

Tra cứu theo nhiều điều kiện

Bài toán thật thường không tra theo một khóa. Ví dụ: tìm đơn giá theo cả mã sản phẩm và tháng áp dụng.

Cách 1 — XLOOKUP với điều kiện nhân:

=XLOOKUP(1, (BangGia!$A$2:$A$500=B2)*(BangGia!$B$2:$B$500=C2), BangGia!$C$2:$C$500, "Không có giá")

Phép nhân hai mảng logic cho ra 1 ở đúng dòng thỏa cả hai điều kiện.

Cách 2 — INDEX + MATCH với điều kiện nhân:

=INDEX(BangGia!$C$2:$C$500, MATCH(1, (BangGia!$A$2:$A$500=B2)*(BangGia!$B$2:$B$500=C2), 0))

Trên Excel bản cũ, công thức này phải kết thúc bằng Ctrl + Shift + Enter.

Cách 3 — cột khóa ghép (dễ hiểu và dễ bảo trì nhất):

Tạo một cột phụ ở cả hai bảng: =B2 & "|" & C2, rồi tra cứu bình thường theo cột đó. Cách này ít "ngầu" nhưng người khác mở file ra vẫn hiểu được — một tiêu chí quan trọng khi làm việc nhóm.

Khớp gần đúng: khi nào thật sự cần

Khớp gần đúng dùng cho các bài toán theo bậc thang: xếp loại theo điểm, tính thuế lũy tiến, chiết khấu theo mức doanh số.

Bảng ngưỡng phải sắp xếp tăng dần:

=XLOOKUP(A2, $D$2:$D$5, $E$2:$E$5, "", -1)

Tham số -1 nghĩa là "nếu không khớp chính xác, lấy giá trị nhỏ hơn gần nhất".

Với VLOOKUP, tương đương là bỏ FALSE hoặc dùng TRUE:

=VLOOKUP(A2, $D$2:$E$5, 2, TRUE)

Cách này tốt hơn IF lồng nhiều tầng ở một điểm quyết định: khi ngưỡng thay đổi, bạn chỉ sửa bảng, không phải sửa hàng trăm công thức.

Bảy nguyên nhân gây lỗi #N/A và cách xử lý

#N/A không phải lỗi công thức — nó nghĩa là "tôi tìm nhưng không thấy". Bảy nguyên nhân, xếp theo tần suất thực tế:

1. Khoảng trắng thừa. "SP001 " và "SP001" là hai giá trị khác nhau. Kiểm tra bằng =LEN(B2) so với =LEN(D2). Sửa bằng TRIM cho cả hai bảng, hoặc bọc trực tiếp: =XLOOKUP(TRIM(B2), ...).

2. Số bị lưu dưới dạng chữ. Mã "00123" nhập ở bảng này là chữ, bảng kia là số. Dấu hiệu: ô có tam giác xanh ở góc, và giá trị căn trái thay vì căn phải. Sửa: chọn cột → Data → Text to Columns → Finish, hoặc nhân với 1 bằng Paste Special.

3. Sai vùng tra cứu do quên cố định $. Kéo công thức xuống, vùng bảng trôi theo. Nhấn F4 hoặc chuyển bảng danh mục thành Table (Ctrl + T) rồi tham chiếu theo tên.

4. Ký tự không nhìn thấy được. Dữ liệu tải từ hệ thống hoặc web thường chứa ký tự char(160) (non-breaking space) mà TRIM không xóa được. Dùng: =TRIM(SUBSTITUTE(B2, CHAR(160), " ")).

5. Giá trị thật sự không tồn tại. Đây là lỗi "đúng". Cách xử lý là hiển thị thông báo rõ ràng chứ không phải giấu đi:

=XLOOKUP(B2, ..., ..., "Thiếu trong danh mục")

6. Dữ liệu chưa sắp xếp khi dùng khớp gần đúng. Với VLOOKUP kiểu TRUE, bảng ngưỡng phải tăng dần, nếu không kết quả sẽ sai hoặc lỗi.

7. Khác biệt về dấu và cách viết tiếng Việt. "Hà Nội" và "Hà nội" thì XLOOKUP vẫn khớp (không phân biệt hoa thường), nhưng "Hà Nội" và "HàNội" thì không. Với dữ liệu do nhiều người nhập, hãy chuẩn hóa bằng Data Validation ngay từ đầu — xem bài Data Validation và Conditional Formatting.

Quy trình kiểm tra khi tra cứu ra kết quả lạ

1. Đếm số dòng lỗi: =COUNTIF(F:F,"#N/A") hoặc =SUMPRODUCT(--ISNA(F2:F500)).
2. Lọc riêng các dòng #N/A để xem chúng có điểm chung gì không.
3. So LEN của một cặp giá trị đáng lẽ phải khớp.
4. Kiểm tra kiểu dữ liệu bằng =ISTEXT(B2) và =ISTEXT(D2).
5. Nếu vẫn không ra, thử =XLOOKUP(TRIM(CLEAN(B2)), ...) để loại cả khoảng trắng lẫn ký tự lạ.

Chỉ khi đã hiểu vì sao lỗi, mới bọc IFERROR. Bọc trước khi hiểu là cách nhanh nhất để đưa số sai vào báo cáo.

Bài tập thực hành

1. Ghép hai bảng. Tạo bảng đơn hàng 100 dòng và bảng danh mục 20 sản phẩm. Điền tên và đơn giá bằng XLOOKUP, rồi viết lại bằng INDEX + MATCH và bằng VLOOKUP. So sánh thời gian viết và độ dễ đọc.

Câu hỏi thường gặp

XLOOKUP có làm file chạy chậm hơn VLOOKUP không?

Trên bảng vài nghìn dòng, khác biệt không đáng kể. Trên bảng hàng trăm nghìn dòng, XLOOKUP và INDEX+MATCH thường nhanh hơn VLOOKUP vì chỉ đọc các cột cần thiết. Yếu tố ảnh hưởng tốc độ lớn hơn nhiều là việc bạn tham chiếu cả cột (A:A) trên file rất lớn.

File dùng XLOOKUP gửi cho người dùng Office 2016 thì sao?

Họ sẽ thấy lỗi #NAME? và công thức hiển thị thành _xlfn.XLOOKUP. Giá trị đã tính vẫn còn nếu bạn chuyển kết quả thành giá trị tĩnh (Paste Values) trước khi gửi. Nếu file cần tiếp tục tính toán ở phía họ, hãy viết bằng INDEX + MATCH.

Có nên luôn bọc IFERROR quanh hàm tra cứu?

Không. IFERROR che mọi loại lỗi, kể cả #REF! do bạn xóa nhầm cột. Với hàm tra cứu, hãy dùng tham số "nếu không tìm thấy" của XLOOKUP, hoặc IFNA — chỉ bắt riêng lỗi #N/A và để các lỗi khác lộ ra để bạn còn biết mà sửa.

Tra cứu trả về nhiều dòng kết quả thì dùng gì?

Hàm tra cứu chỉ trả một kết quả. Khi cần tất cả dòng thỏa điều kiện, dùng FILTER (Microsoft 365), hoặc PivotTable, hoặc Advanced Filter trên bản cũ.

Vì sao VLOOKUP trả về kết quả sai chứ không báo lỗi?

Gần như luôn do quên tham số FALSE. Ở chế độ khớp gần đúng, VLOOKUP lấy giá trị nhỏ hơn gần nhất trong danh sách — nghĩa là bạn nhận về đơn giá của một sản phẩm khác mà không hề có cảnh báo. Đây là lý do chính khiến Microsoft đặt khớp chính xác làm mặc định cho XLOOKUP.

Nên dùng Power Query thay cho hàm tra cứu khi nào?

Khi bạn phải ghép nhiều bảng, làm định kỳ mỗi tuần/tháng, hoặc dữ liệu nguồn lớn tới mức công thức khiến file ì. Power Query có thao tác Merge Queries làm đúng việc của VLOOKUP nhưng tự động chạy lại chỉ với một lần bấm Refresh. Xem bài Làm sạch dữ liệu trong Excel.

Nguồn tham khảo

• XLOOKUP function – Microsoft Support
• Look up values with VLOOKUP, INDEX, or MATCH – Microsoft Support
• How to correct a #N/A error – Microsoft Support
• XLOOKUP vs INDEX and MATCH – Exceljet

Kết luận

Chọn hàm tra cứu không phải câu hỏi khó: dùng XLOOKUP nếu có, INDEX + MATCH nếu cần tương thích, và hiểu VLOOKUP đủ để đọc file người khác.

Phần khó — và cũng là phần phân biệt người dùng Excel thành thạo — là xử lý dữ liệu bẩn khiến tra cứu thất bại. Khi gặp #N/A, đừng vội bọc IFERROR. Hãy hỏi "vì sao không tìm thấy" trước đã.

Bài viết liên quan

• 30 hàm Excel dân văn phòng dùng hằng ngày
• Làm sạch dữ liệu trong Excel
• PivotTable từ A đến Z
• 15 lỗi Excel thường gặp