hoctin.vn

Cách Sửa Lỗi #N/A Của Hàm VLOOKUP trong Excel

Cách Sửa Lỗi #N/A Của Hàm VLOOKUP Trong Excel

by

 

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. Vậy lỗi N/A là gì, nguyên nhân, cách khắc phục và sửa lỗi này sẽ được hoctin.vn chia sẻ với các bạn trong bài viết này.

Mục Lục Bài Viết

1. Lỗi #N/A Trong Excel Là Gì?

Lỗi N/A cho biết rằng một công thức không thể tìm thấy giá trị được tham chiếu mà công thức đó được yêu cầu tìm. Lỗi này không do lỗi sai hàm, sai công thức. Vậy N/A nghĩa là gì?

 

N/A trong tiếng anh là viết tắt của Not Available/ Không tồn tại, không có sẵn.

 

Các lí do hàm VLOOKUP không chạy do lỗi N/A có thể liệt kê như sau:

  • Giá trị tra cứu không nằm trong cột đầu tiên trong đối số table_array
  • Lỗi NA trong tìm kiếm gần đúng bằng hàm VLOOKUP
  • Lỗi NA trong tìm kiếm chính xác hàm VLOOKUP
  • Giá trị tra cứu của bạn không tồn tại trong dữ liệu nguồn
  • Gặp lỗi khi viết hàm, không cố định vùng dữ liệu dò tìm trước khi copy công thức hàng loạt.

2. 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.
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

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:

Sửa lỗi hàm VLOOKUP không hoạt động vì lỗi #N/A


3, 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:

Lỗi #N/A trong tìm kiếm gần đúng bằng hàm VLOOKUP do giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong mảng tra cứu

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 trong tìm kiếm gần đúng bằng hàm VLOOKUP do cột tra cứu không được sắp xếp theo thứ tự tăng dần

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.

4. 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.

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

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”)


Đọc thêm về: Cách sử dụng hàm IF kết hợp VLOOKUP trong Excel

5, 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 #NA 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.

Lỗi N/A trong vlookup do Số được định dạng dưới dạng văn bản


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:

Lỗi N/A trong vlookup do có khoảng trống thừa ở đầu hoặc cuối ô cần dò tìm



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 trong vlookup do có khoảng trống thừa ở đầu hoặc cuối ô trong cột tra cứu

6, Lỗi N/A Hàm VLOOKUP Do Gặp Lỗi Khi Viết Hàm, 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:

Lỗi N/A hàm vlookup do gặp lỗi khi viết hàm, không cố định vùng dữ liệu dò tìm trước khi copy công thức hàng loạt

 

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:

Sửa Lỗi N/A hàm vlookup do gặp lỗi khi viết hàm, không cố định vùng dữ liệu dò tìm trước khi copy công thức hàng loạt

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.

Ngoài ra, để ứng dụng hiệu quả Excel, bạn cần phải sử dụng tốt các hàm, các công cụ khác của Excel, hãy cùng đón đọc series bài viết ứng dụng các hàm excel:

Hàm VLOOKUP

Hàm HLOOKUP

Hàm IF

Hàm AND

Hàm OR

 

Leave a Comment