Lỗi #N/A Trong Hàm VLOOKUP VÀ HLOOKUP

Lỗi #N/A trong excel là vấn nạn, là lỗi thường gặp của rất nhiều người dùng Excel, quan trọng là làm thế nào để sửa lỗi #N/A này , đặc biệt là khi sử dụng hàm VLOOKUP hoặc HLOOKUP. Trong bài viết này Học TV sẽ giúp các bạn thoát khỏi nỗi ám ảnh mang tên lỗi #N/A này nhé.

1. Lỗi #N/A là gì

Trong tiếng Anh, N/A là viết tắt của "No Answer" hoặc "Not Available" hoặc "Not Applicable", có nghĩa là không có câu trả lời hoặc không tồn tại câu trả lời phù hợp hoặc không thể áp dụng để tìm câu trả lời. Do đó, lỗi #N/A khi trong công thức excel có nghĩa là không thể tìm được giá trị tương thích.

Đặc biệt, lỗi này thường xảy ra khi sử dụng hàm VLOOKUP và hàm HLOOKUP vì Excel không thể xác định giá trị theo yêu cầu tìm kiếm. Ngay dưới đây, chúng ta sẽ đi sâu vào xử lý lỗi #N/A khi sử dụng hai hàm này.

2. Cách khắc phục lỗi #N/A

Lỗi xảy ra vì vấn đề ở vùng tìm kiếm.

Vùng tìm kiếm có 2 vấn đề chính: một là bạn chưa cố định vùng tìm kiếm, hai là giới hạn vùng tìm kiếm chưa đủ rộng.

Giải pháp đơn giản là giới hạn lại vùng tìm kiếm và cố định vùng tìm kiếm bằng ký tự "$"

Lỗi xảy ra khi kết hợp hàm VLOOKUP/HLOOKUP với hàm LEFT, RIGHT hoặc MID.

Cùng theo dõi ví dụ sau đây:

Ta cần dùng HLOOKUP để dò TÊN NGÀNH theo MÃ NGÀNH cho trước:

• Điều kiện tìm kiếm: chỉ ra ký tự đứng thứ 2 bằng hàm MID
• Vùng tìm kiếm: đã cố định toàn bộ bảng tra cứu điểm
• Số thứ tự của dòng chứa giá trị đang tìm: dòng thứ 2 của bảng tra cứu điểm

=HLOOKUP(MID(A2,2,1),$B$7:$E$8,2,0)Cú pháp hàm đã hoàn toàn chuẩn xác, thế mà khi ấn Enter vẫn bị báo lỗi khiến chúng ta tự hỏi WTF ?  Ứ hiểu làm sao, từ chối hiểu 

Nguyên nhân ở đây do sự khó nhận diện chữ và con số của excel, định dạng kết quả của hàm MID (hay LEFT hoặc RIGHT) luôn là văn bản, nhưng khu vực tìm kiếm chỉ đưa kết quả là con số.

Giải pháp trong trường hợp này cần chuyển đổi kết quả của MID thành con số, nên chúng ta dùng hàm VALUE để sửa lỗi này như sau:

=HLOOKUP(VALUE(MID(A2,2,1)),$B$7:$E$8,2,0)

Giá trị tham chiếu không tồn tại

Nguyên nhân phổ biến nhất của lỗi #N/A là với các hàm VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH khi một công thức không thể tìm thấy giá trị được tham chiếu. Ví dụ: giá trị tra cứu của bạn không tồn tại trong dữ liệu nguồn.

Công thức trong ô E2 là =VLOOKUP(D2,$D$6:$E$8,2,FALSE).  Không thể tìm thấy giá trị Chuối, do đó, công thức sẽ trả về lỗi #N/A.

Trong trường hợp này, không có giá trị “Chuối” nào được liệt kê trong bảng tra cứu, do đó, hàm VLOOKUP trả về lỗi #N/A.

Giải pháp: Hãy đảm bảo rằng giá trị tra cứu tồn tại trong dữ liệu nguồn hoặc sử dụng trình khắc phục lỗi như IFERROR trong công thức. 

Ví dụ: =IFERROR(FORMULA(),0), trong đó:

• Nếu công thức của bạn đánh giá ra lỗi thì sẽ hiển thị 0, nếu không thì hiển thị kết quả của công thức
• Bạn có thể sử dụng "" để không hiển thị nội dung gì hoặc thay thế bằng văn bản của riêng mình: =IFERROR(FORMULA(),"Thông báo Lỗi ở đây")

Dữ liệu khác nhau

Giá trị tra cứu và dữ liệu nguồn là các loại dữ liệu khác nhau. Ví dụ: bạn tìm cách để hàm VLOOKUP tham chiếu một số nhưng dữ liệu nguồn lại được lưu trữ dưới dạng văn bản.

Ví dụ hiển thị một công thức VLOOKUP trả về lỗi #N/A vì mục tra cứu được định dạng là số nhưng bảng tra cứu lại được định dạng là văn bản.

Giải pháp: Hãy đảm bảo rằng các dữ liệu của bạn cùng loại với nhau. Bạn có thể kiểm tra định dạng ô bằng cách chọn một ô hoặc dải ô, rồi bấm chuột phải và chọn Format Cell (Định dạng Ô) > Number (hoặc nhấn Ctrl+1), và thay đổi định dạng số, nếu cần.

Có khoảng trống thừa trong các ô

Bạn có thể sử dụng hàm TRIM để loại bỏ mọi khoảng trống ở đầu hoặc cuối. Các ví dụ sau sử dụng hàm TRIM được lồng trong một hàm VLOOKUP để loại bỏ các khoảng trống ở đầu khỏi các tên nằm trong A2:A7 và trả về tên phòng ban.

Công thức trong ô E3 là {=VLOOKUP(D2,TRIM(A2:B7),2,FALSE)} và cần được nhập bằng tổ hợp phím CTRL+SHIFT+ENTER.

= VLOOKUP (D2, TRIM (A2: B7), 2, FALSE)

Tra cứu kết quả tương đối và chính xác

Theo mặc định, các hàm tra cứu thông tin trong bảng phải được sắp xếp theo thứ tự tăng dần. Tuy nhiên, các hàm VLOOKUP và HLOOKUP đều chứa tham đối range_lookup yêu cầu hàm tìm kết quả khớp chính xác, ngay cả khi bảng không được sắp xếp. Để tìm kết quả khớp chính xác, hãy đặt tham đối range_lookup thành FALSE. Xin lưu ý rằng việc sử dụng TRUE, là tham đối yêu cầu hàm tìm kiếm kết quả khớp tương đối, có thể không chỉ gây ra lỗi #N/A mà còn có khả năng trả về các kết quả sai như được trình bày trong ví dụ dưới đây.

Trong ví dụ này, không chỉ giá trị “Chuối” trả về lỗi #N/A mà giá trị “Lê” còn trả về giá tiền sai. Điều này xảy ra do việc sử dụng tham đối TRUE, tham đối này sẽ yêu cầu hàm VLOOKUP tìm kiếm một kết quả khớp tương đối thay cho một kết quả khớp chính xác. Không tồn tại kết quả gần khớp nào cho giá trị “Chuối” và “Lê” đứng trước “Đào” theo thứ tự bảng chữ cái. Trong trường hợp này, việc sử dụng hàm VLOOKUP kèm theo tham đối FALSE sẽ trả về giá tiền chính xác cho giá trị “Lê” nhưng giá trị “Chuối” vẫn sẽ trả về lỗi #N/A vì không có giá trị “Chuối” tương ứng nào trong danh sách tra cứu.

Nếu bạn đang sử dụng hàm MATCH, hãy thử thay đổi giá trị của tham đối match_type nhằm chỉ định thứ tự sắp xếp cho bảng. Để tìm kết quả khớp chính xác, hãy đặt tham đối match_type thành 0 (không).

Trên đây là tất cả các trường hợp có thể gây lỗi #N/A trong hàm VLOOKUP VÀ HLOOKUP cũng như cách khắc phục lỗi. Nếu vẫn chưa sửa được lỗi #NA hãy comment dưới bài viết này nhé.