Cách xử lý lỗi #N/A trong Excel: Nguyên nhân và cách khắc phục nhanh

Trong quá trình sử dụng Excel, lỗi #N/A là một trong những lỗi khá phổ biến, đặc biệt khi làm việc với các hàm tìm kiếm và đối chiếu dữ liệu như VLOOKUP, HLOOKUP, XLOOKUP, MATCH, INDEX hoặc các công thức kết hợp nhiều hàm.

Khi xuất hiện #N/A, Excel đang thông báo rằng công thức không tìm thấy giá trị phù hợp với điều kiện được yêu cầu. Tuy nhiên, trong nhiều trường hợp, dữ liệu thực tế vẫn tồn tại và nguyên nhân lại nằm ở khoảng trắng thừa, sai định dạng dữ liệu, nhập sai giá trị tìm kiếm hoặc tham số của công thức.

Biết cách xác định đúng nguyên nhân sẽ giúp bạn xử lý lỗi #N/A nhanh chóng mà không cần xóa hoặc nhập lại toàn bộ dữ liệu.

Lỗi #N/A trong Excel là gì?

#N/A là viết tắt của Not Available, có thể hiểu là “không có dữ liệu phù hợp” hoặc “không tìm thấy kết quả”.

Ví dụ, bạn có bảng danh sách nhân viên:

Mã nhân viên	Họ và tên	Phòng ban
NV001	Nguyễn Văn An	Kế toán
NV002	Trần Văn Bình	Kinh doanh
NV003	Lê Thị Hoa	Nhân sự

Nếu sử dụng công thức:

=VLOOKUP("NV005",A2:C4,2,FALSE)

Excel sẽ trả về:

#N/A

vì mã NV005 không tồn tại trong bảng dữ liệu.

Điều quan trọng là #N/A không nhất thiết có nghĩa công thức bị viết sai. Nó thường có nghĩa Excel đã thực hiện việc tìm kiếm nhưng không tìm thấy giá trị đáp ứng điều kiện.

Những nguyên nhân thường gặp khiến Excel xuất hiện #N/A

Có khá nhiều nguyên nhân dẫn đến lỗi #N/A. Trong thực tế, phổ biến nhất là giá trị cần tìm không tồn tại, dữ liệu có khoảng trắng thừa hoặc hai ô chứa dữ liệu có vẻ giống nhau nhưng thực chất khác kiểu dữ liệu.

Giá trị cần tìm không tồn tại

Đây là nguyên nhân đơn giản nhất.

Ví dụ:

=XLOOKUP(E2,A2:A100,B2:B100)

Nếu giá trị trong ô E2 không xuất hiện trong vùng A2, Excel có thể trả về #N/A.

Trước tiên, hãy kiểm tra chính xác giá trị cần tìm có tồn tại trong bảng dữ liệu hay không.

Bạn có thể sử dụng:

=COUNTIF(A2:A100,E2)

Nếu kết quả bằng 0, có khả năng giá trị trong E2 không xuất hiện trong vùng A2.

Dữ liệu có khoảng trắng thừa

Đây là một lỗi rất thường gặp khi sao chép dữ liệu từ website, phần mềm khác hoặc nhập dữ liệu thủ công.

Ví dụ, ô A2 chứa:

NV001

nhưng ô E2 lại chứa:

NV001 

Có một khoảng trắng ở cuối. Hai giá trị nhìn gần như giống nhau nhưng Excel có thể xem chúng là hai chuỗi khác nhau.

Bạn có thể kiểm tra bằng hàm:

=LEN(A2)

và:

=LEN(E2)

Nếu số ký tự khác nhau dù nhìn bằng mắt thấy giống nhau, rất có thể dữ liệu đang chứa khoảng trắng hoặc ký tự thừa.

Để loại bỏ khoảng trắng thông thường, có thể sử dụng:

=TRIM(A2)

Sau đó sao chép kết quả và dán lại dưới dạng giá trị nếu cần.

Số và văn bản có cùng hình thức nhưng khác kiểu dữ liệu

Một nguyên nhân khác là một bên được lưu dưới dạng số, bên còn lại được lưu dưới dạng văn bản.

Ví dụ:

12345

có thể là số trong một ô nhưng lại là chuỗi văn bản "12345" trong ô khác.

Khi thực hiện tìm kiếm, Excel có thể không coi hai giá trị này là giống nhau.

Bạn có thể kiểm tra kiểu dữ liệu bằng:

=ISNUMBER(A2)

hoặc:

=ISTEXT(A2)

Nếu một ô trả về TRUE với ISNUMBER còn ô kia trả về TRUE với ISTEXT, bạn cần chuẩn hóa lại dữ liệu trước khi tìm kiếm.

Để chuyển văn bản dạng số thành số, có thể sử dụng:

=VALUE(A2)

Trong một số trường hợp, bạn cũng có thể sử dụng:

=--A2

Vùng tìm kiếm bị chọn sai

Với các hàm như VLOOKUP, việc chọn sai vùng dữ liệu cũng có thể dẫn đến #N/A.

Ví dụ:

=VLOOKUP(E2,A2:C100,2,FALSE)

Excel sẽ tìm giá trị trong cột đầu tiên của vùng A2, tức cột A.

Nếu giá trị cần tìm thực tế nằm ở cột B nhưng bạn lại chọn vùng bắt đầu từ cột A, công thức có thể không cho kết quả như mong muốn.

Khi gặp #N/A, hãy kiểm tra lại vùng tìm kiếm và xác định xem giá trị cần tìm có thực sự nằm trong cột đầu tiên của vùng VLOOKUP hay không.

Sử dụng sai chế độ tìm kiếm

Với VLOOKUP, tham số cuối cùng rất quan trọng.

Ví dụ:

=VLOOKUP(E2,A2:C100,FALSE)

Trong thực tế thường viết đầy đủ:

=VLOOKUP(E2,A2:C100,2,FALSE)

Trong đó FALSE yêu cầu Excel tìm kiếm chính xác.

Nếu sử dụng TRUE hoặc bỏ qua tham số này, Excel có thể thực hiện tìm kiếm gần đúng. Điều này đặc biệt dễ gây ra kết quả không như mong muốn nếu bảng dữ liệu chưa được sắp xếp phù hợp.

Đối với mã nhân viên, mã sản phẩm, số điện thoại hoặc các loại dữ liệu cần đối chiếu chính xác, FALSE thường là lựa chọn phù hợp.

Cách xử lý lỗi #N/A bằng IFNA

Một trong những cách đơn giản nhất để xử lý lỗi #N/A là sử dụng hàm IFNA.

Ví dụ công thức ban đầu:

=VLOOKUP(E2,A2:C100,2,FALSE)

Nếu không tìm thấy dữ liệu, công thức trả về #N/A.

Bạn có thể sửa thành:

=IFNA(VLOOKUP(E2,A2:C100,2,FALSE),"Không tìm thấy")

Khi đó, thay vì hiển thị:

#N/A

Excel sẽ hiển thị:

Không tìm thấy

Cách này đặc biệt hữu ích khi tạo bảng báo cáo, bảng thống kê hoặc file Excel dùng cho người khác nhập dữ liệu.

Sử dụng IFERROR để xử lý #N/A

Ngoài IFNA, bạn cũng có thể sử dụng IFERROR.

Ví dụ:

=IFERROR(VLOOKUP(E2,A2:C100,2,FALSE),"Không tìm thấy dữ liệu")

IFERROR có thể xử lý nhiều loại lỗi Excel khác nhau, không chỉ #N/A.

Chẳng hạn, công thức có thể gặp các lỗi như:

#N/A
#VALUE!
#DIV/0!
#REF!

Nếu muốn xử lý riêng lỗi #N/A, IFNA thường rõ ràng hơn. Nếu muốn tạo công thức có khả năng xử lý nhiều lỗi, IFERROR sẽ thuận tiện hơn.

Xử lý lỗi #N/A với XLOOKUP

Nếu đang sử dụng phiên bản Excel hỗ trợ XLOOKUP, bạn có thể xử lý lỗi #N/A trực tiếp ngay trong công thức.

Ví dụ:

=XLOOKUP(E2,A2:A100,B2:B100,"Không tìm thấy")

Trong công thức này, nếu Excel không tìm thấy giá trị trong E2, thay vì trả về #N/A, Excel sẽ hiển thị:

Không tìm thấy

Đây là một trong những ưu điểm đáng chú ý của XLOOKUP so với cách sử dụng VLOOKUP truyền thống.

Xử lý lỗi #N/A khi dùng MATCH

Hàm MATCH cũng thường xuất hiện #N/A khi không tìm thấy giá trị.

Ví dụ:

=MATCH(E2,A2:A100,0)

Nếu E2 không tồn tại trong A2, Excel trả về #N/A.

Bạn có thể sử dụng:

=IFNA(MATCH(E2,A2:A100,0),"Không tìm thấy")

Cách này giúp bảng tính dễ đọc hơn và tránh việc người dùng nhìn thấy các mã lỗi khó hiểu.

Xử lý lỗi #N/A khi kết hợp INDEX và MATCH

INDEX và MATCH thường được sử dụng cùng nhau để tìm kiếm dữ liệu.

Ví dụ:

=INDEX(B2:B100,MATCH(E2,A2:A100,0))

Nếu MATCH không tìm thấy E2, toàn bộ công thức có thể trả về #N/A.

Bạn có thể xử lý bằng:

=IFNA(INDEX(B2:B100,MATCH(E2,A2:A100,0)),"Không tìm thấy")

Như vậy, khi dữ liệu không tồn tại, Excel sẽ hiển thị thông báo dễ hiểu thay vì mã lỗi.

Cách xử lý #N/A do dữ liệu có khoảng trắng

Nếu nghi ngờ lỗi xuất phát từ khoảng trắng, có thể làm sạch dữ liệu trước khi thực hiện tìm kiếm.

Ví dụ:

=TRIM(A2)

TRIM giúp loại bỏ các khoảng trắng dư thừa giữa và xung quanh văn bản trong những trường hợp phù hợp.

Tuy nhiên, nếu dữ liệu được sao chép từ website hoặc hệ thống khác, đôi khi có thể xuất hiện ký tự khoảng trắng đặc biệt mà TRIM không xử lý được.

Trong trường hợp đó, có thể kết hợp CLEAN và SUBSTITUTE tùy loại dữ liệu.

Ví dụ:

=TRIM(CLEAN(A2))

Với những dữ liệu phức tạp hơn, việc làm sạch dữ liệu trước khi xây dựng công thức tìm kiếm thường là giải pháp tốt hơn việc cố gắng sửa công thức.

Cách kiểm tra nhanh giá trị có tồn tại hay không

Nếu chưa biết nguyên nhân gây ra #N/A, bạn có thể kiểm tra dữ liệu bằng COUNTIF.

Ví dụ:

=COUNTIF(A:A,E2)

Nếu kết quả lớn hơn 0, giá trị E2 có xuất hiện trong cột A.

Nếu kết quả bằng 0, Excel không tìm thấy giá trị giống E2 trong cột A.

Đây là một cách kiểm tra đơn giản nhưng rất hữu ích khi xử lý các bảng dữ liệu lớn.

Một ví dụ thực tế

Giả sử bạn có bảng sản phẩm:

Mã SP	Tên sản phẩm	Giá
SP001	Bàn làm việc	1500000
SP002	Ghế văn phòng	850000
SP003	Kệ sách	1200000

Tại ô E2, bạn nhập:

SP002

Công thức:

=VLOOKUP(E2,A2:C4,3,FALSE)

sẽ trả về:

850000

Nhưng nếu E2 nhập:

SP005

thì kết quả là:

#N/A

Để thay thế lỗi bằng thông báo dễ hiểu, bạn có thể dùng:

=IFNA(VLOOKUP(E2,A2:C4,3,FALSE),"Không tìm thấy sản phẩm")

Kết quả sẽ là:

Không tìm thấy sản phẩm

Điều này đặc biệt hữu ích khi xây dựng các file Excel quản lý kho, danh sách nhân viên, bảng chấm công, bảng lương, danh mục sản phẩm hoặc báo cáo doanh thu.

Khi nào nên dùng IFNA và khi nào nên dùng IFERROR?

Hai hàm này khá giống nhau nhưng mục đích sử dụng có sự khác biệt.

IFNA phù hợp khi bạn chỉ muốn xử lý lỗi #N/A.

Ví dụ:

=IFNA(XLOOKUP(E2,A2:A100,B2:B100),"Không tìm thấy")

Trong khi đó, IFERROR phù hợp khi bạn muốn xử lý nhiều loại lỗi.

Ví dụ:

=IFERROR(A2/B2,0)

Nếu phép tính gặp lỗi, Excel sẽ trả về 0 thay vì hiển thị mã lỗi.

Vì vậy, nếu bạn đang xử lý một công thức tìm kiếm và muốn biết rõ trường hợp “không tìm thấy dữ liệu”, IFNA thường là lựa chọn dễ hiểu hơn.

Những điều không nên làm khi gặp lỗi #N/A

Khi nhìn thấy #N/A, nhiều người có thói quen sử dụng IFERROR ngay lập tức để che lỗi. Cách này giúp bảng tính đẹp hơn nhưng không phải lúc nào cũng giải quyết được nguyên nhân thực sự.

Nếu công thức đang bị lỗi do nhập sai mã, sai vùng tìm kiếm hoặc dữ liệu bị sai định dạng, việc che lỗi có thể khiến bạn không nhận ra vấn đề.

Do đó, tốt nhất nên kiểm tra theo thứ tự:

Giá trị tìm kiếm → dữ liệu có tồn tại → khoảng trắng → kiểu dữ liệu → vùng tìm kiếm → tham số tìm kiếm → công thức xử lý lỗi.

Khi đã xác định được nguyên nhân, bạn mới nên dùng IFNA hoặc IFERROR để thiết kế cách hiển thị phù hợp.

Mẹo hạn chế lỗi #N/A trong các file Excel

Nếu thường xuyên làm việc với dữ liệu lớn, bạn có thể giảm đáng kể lỗi #N/A bằng cách chuẩn hóa dữ liệu ngay từ đầu.

Các mã sản phẩm, mã nhân viên hoặc mã khách hàng nên có một định dạng thống nhất. Không nên để cùng một loại mã nhưng một số ô là số, một số ô lại là văn bản.

Khi sao chép dữ liệu từ nguồn bên ngoài, nên kiểm tra khoảng trắng và ký tự đặc biệt.

Đối với công thức tìm kiếm, nên sử dụng tham số tìm kiếm chính xác khi cần đối chiếu mã hoặc thông tin cụ thể.

Nếu sử dụng Excel phiên bản mới, XLOOKUP thường là lựa chọn thuận tiện vì có thể chỉ định trực tiếp nội dung muốn hiển thị khi không tìm thấy kết quả.

Ngoài ra, khi xây dựng bảng tính cho người khác sử dụng, nên thiết kế thông báo dễ hiểu như “Không tìm thấy dữ liệu”, “Mã sản phẩm không tồn tại” hoặc “Vui lòng kiểm tra lại mã” thay vì để nguyên mã lỗi #N/A.

Lời kết

Lỗi #N/A trong Excel thực chất không quá khó xử lý nếu xác định đúng nguyên nhân. Trong phần lớn trường hợp, lỗi xuất hiện vì Excel không tìm thấy giá trị phù hợp, dữ liệu có khoảng trắng thừa, số và văn bản bị khác kiểu dữ liệu hoặc công thức tìm kiếm chưa được thiết lập chính xác.

Với các công thức như VLOOKUP, XLOOKUP, MATCH và INDEX/MATCH, bạn nên kiểm tra dữ liệu trước khi tìm cách che lỗi. Khi công thức đã hoạt động chính xác, có thể sử dụng IFNA hoặc IFERROR để thay thế #N/A bằng thông báo thân thiện hơn.

Đối với người thường xuyên sử dụng Excel để quản lý công việc, việc hiểu bản chất của #N/A không chỉ giúp sửa một lỗi cụ thể mà còn giúp xây dựng các bảng tính ổn định, dễ kiểm tra và chuyên nghiệp hơn.

Mẫu Đơn.