Lỗi #N/A trong hàm VLOOKUP – Nguyên nhân và cách sửa lỗi

N/A được viết tắt bởi cụm từ trong tiếng anh là Not Available có nghĩa là không tồn tại hay không có sẵn. Lỗi này thường xảy ra khi sử dụng các hàm dò tìm, tham chiếu. Đối tượng cần dò tìm, tham chiếu không có sẵn trong vùng cần tra cứu nên báo lỗi #N/A

Khi làm việc với hàm VLOOKUP bạn thường xuyên gặp các lỗi trong excel, trong đó phổ biến nhất là lỗi #N/A.

Lỗi #N/A trong hàm VLOOKUP do giá trị tra cứu không nằm trong cột đầu tiên trong đối số Table_array

Khi sử dụng hàm VLOOKUP ta thường quên đi lưu ý này và dẫn đến việc hàm VLOOKUP không hoạt động vì lỗi N/A. Như ví dụ dưới đây:

• Tại ô C19, công thức là =VLOOKUP(B10,$A$3:$E$7,4,0) nhằm tìm kiếm Họ tên của ID V00038.
• Để tra cứu V00038, cột đầu tiên của table_array (vùng dữ liệu dò tìm) phải là cột ID tức cột C, nhưng công thức lại đặt vùng dữ liệu dò tìm từ cột A đến cột E.

Cách sửa lỗi này đơn giản là thay đổi Table_array và đổi số thứ tự cột Col_index_num thành =VLOOKUP(B10,$C$3:$E$7,2,0). Khi đó hàm VLOOKUP chạy ra kết quả như sau:

Lỗi #N/A trong tìm kiếm gần đúng bằng hàm VLOOKUP

Nếu bạn đang sử dụng một công thức với đối sánh gần đúng (đối số range_lookup là TRUE hoặc bỏ qua), công thức Vlookup của bạn có thể trả về lỗi # N/A trong hai trường hợp:

• Nếu giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong mảng tra cứu.
• Nếu cột tra cứu không được sắp xếp theo thứ tự tăng dần.

Trường Hợp 1: Lỗi #N/A nếu giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong mảng tra cứu

Ví dụ Điểm tổng kết môn của sinh viên Trần Mạnh Thắng và Nguyễn Thị Việt Hồng lần lượt tại ô B5 và B9 là 3.0 và 4.6 nhỏ hơn giá trị nhỏ nhất trong vùng dữ liệu dò tìm là 5 thì hàm VLOOKUP sẽ trả về lỗi #N/A như sau:

Trường hợp này cần đặt thêm hàm IF để so sánh với giá trị nhỏ nhất của vùng tìm kiếm: nếu Điểm tổng kết môn lớn hơn hoặc bằng giá trị nhỏ nhất này (ô G4) thì trả về hàm VLOOKUP, nếu không thì trả về cụm “Không đạt điều kiện”

Tại ô C5: =IF($B5>=$G4,VLOOKUP($B5,$G$4:$J$10,3,TRUE),“Không đạt điều kiện”)

Trường Hợp 2: Lỗi #N/A nếu cột tra cứu không được sắp xếp theo thứ tự tăng dần

Khi sử dụng hàm VLOOKUP tìm kiếm gần đúng 1/TRUE, người dùng phải đảm bảo thông tin trong bảng Table_array (vùng dữ liệu dò tìm) phải được sắp xếp theo thứ tự tăng dần.

Ví dụ dưới đây khi Table_array không được sắp xếp theo thứ tự tăng dần thì hàm VLOOKUP tìm kiếm các giá trị Điểm tổng kết môn của vùng điểm chưa được sắp xếp sẽ trả về lỗi #N/A:

Lỗi #N/A này có thể khắc phục đơn giản bằng cách sắp xếp lại vùng dữ liệu dò tìm Table_array theo thứ tự tăng dần.

Lỗi #N/A trong tìm kiếm chính xác hàm VLOOKUP

Một trong những nguyên nhân phổ biển nhất làm công thức Excel không ra kết quả khi tìm kiếm chính xác bằng hàm VLOOKUP là do vùng dữ liệu dò tìm không chữa dữ liệu cần tìm.

Như ví dụ dưới đây, tại ô B15 và B16, hàm VLOOKUP có cùng công thức, chỉ khác giá trị dò tìm lần lượt là A15 và A16 nhưng ô B15 trả về kết quả tìm tên trong excel còn ô B16 trả về lỗi #N/A.

Nguyên nhân do cột đầu tiên của vùng dữ liệu dò tìm $A$2:$C$10 không chứa giá trị V00039 nên hàm VLOOKUP không trả về kết quả cần tìm được.

Cách khắc phục là sử dụng hàm IFERROR hoặc IFNA để trả về văn bản thông báo không tìm được kết quả cần tìm:

=IFNA(=VLOOKUP($A16,$A$2:$C$10,2,0),”Not available”)

=IFERROR(VLOOKUP($A15,$A$2:$C$10,2,0),”Not available”)

Lỗi #N/A trong VLOOKUP do giá trị tra cứu không tồn tại trong dữ liệu nguồn

Ngoài việc lỗi #N/A không thể tránh khỏi do dữ liệu cần tìm không nằm trong khu vực dữ liệu dò tìm Table_array thì lỗi này còn có thể xuất hiện do giá trị tra cứu hoặc vùng dữ liệu dò tìm gặp lỗi định dạng như sau:

• Số được định dạng dưới dạng văn bản
• Có khoảng trống thừa ở đầu hoặc cuối ô cần dò tìm
• Có khoảng trống thừa ở đầu hoặc cuối ô trong cột tra cứu

Trường Hợp 1: Lỗi #N/A do số được định dạng dưới dạng văn bản

Khi số bị định dạng dưới dạng văn bản (dữ liệu được mặc định căn lề trái hoặc xuất hiện tam giác màu xanh lá cây ở góc trên cùng bên trái ô) thì hàm VLOOKUP không trả được kết quả dò tìm nếu cùng một giá trị tìm kiếm được định dạng văn bản và số ở lookup_value và table_array và ngược lại.

Trường hợp hay xảy ra nhất là khi bạn nhập dữ liệu từ data bên ngoài hoặc do gõ dấu nháy đơn [ ‘ ] trước số như ô A12 dưới đây. Cách khắc phục: chuyển về định dạng số bằng cách ấn vào dấu chấm than >> Convert To Number.

Trường Hợp 2: Lỗi #N/A do có khoảng trống thừa ở đầu hoặc cuối ô cần dò tìm

Cách khắc phục: cần loại tất cả khoảng trống đầu và cuối khỏi văn bản, ví dụ ‘ V00003 ’ cần xử lý thành ‘V00003’, chỉ để lại một khoảng trống giữa các từ,

Ví dụ ‘ Nguyễn Anh Tuấn’ cần xử lý thành ‘Nguyễn Anh Tuấn’ bằng cách sử dụng hàm TRIM:

Trường Hợp 3: Có khoảng trống thừa ở đầu hoặc cuối ô trong cột tra cứu

Tương tự trường hợp 2 ở trên, cách xử lý đơn giản nhất mà không phải dùng các hàm phức tạp hơn như INDEX và MATCH là thêm một cột tra cứu đã qua xử lý loại bỏ khoảng trắng bằng hàm TRIM rồi tìm kiếm VLOOKUP dựa trên dữ liệu mới này như ví dụ dưới đây:

Lỗi #N/A hàm VLOOKUP do không cố định vùng dữ liệu dò tìm trước khi copy công thức hàng loạt

Lỗi này khá là phổ biến do người dùng quên cố định giá trị tra cứu Lookup_Value và vùng dữ liệu dò tìm Table_array, lỗi excel không nhảy công thức đúng dẫn đến không tìm ra được kết quả cần tra cứu:

Cách khắc phục lỗi #N/A cực kì đơn giản, chỉ cần F4 vùng dữ liệu dò tìm để cố định dải ô như sau:

Trên đây là những nguyên nhân, cách khắc phục và sửa lỗi #N/A thường gặp khi sử dụng hàm VLOOKUP. Chúc các bạn áp dụng thành công và vận dụng tốt trong công việc và học tập.