Lỗi #N/A trong hàm VLOOKUP VÀ HLOOKUP

Lỗi #N/A trong hàm VLOOKUP VÀ HLOOKUP

Trình duyệt của bạn không tương hỗ nghe audio .

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 yếu tố chính : một là bạn chưa cố định và thắt chặt vùng tìm kiếm, hai là số lượng giới hạn vùng tìm kiếm chưa đủ rộng .Giải pháp đơn thuần là số lượng giới hạn lại vùng tìm kiếm và cố định và thắt chặt vùng tìm kiếm bằng ký tự ” USD “

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 :

loi na trong ham vlookup va hlookup 5f464c1160a7a

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à số lượng của excel, định dạng hiệu 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 tác dụng là số lượng .Giải pháp trong trường hợp này cần quy đổi hiệu quả của MID thành số lượng, nên tất cả 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)

loi na trong ham vlookup va hlookup 5f464c11d5a63

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

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

loi na trong ham vlookup va hlookup 5f464c11e437b

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à tài liệu nguồn là những loại tài liệu khác nhau. Ví dụ : bạn tìm cách để hàm VLOOKUP tham chiếu 1 số ít nhưng tài liệu nguồn lại được tàng trữ dưới dạng văn bản .

1 loi na trong ham vlookup va hlookup 5f464c11f287f

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.

loi na trong ham vlookup va hlookup 5f464c120d241

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

Bạn hoàn toàn có thể sử dụng hàm TRIM để vô hiệu 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 để vô hiệu những khoảng trống ở đầu khỏi những tên nằm trong A2 : A7 và trả về tên phòng ban .

loi na trong ham vlookup va hlookup 5f464c121b977

Công thức trong ô E3 là { = VLOOKUP ( D2, TRIM ( A2 : B7 ), 2, FALSE ) } và cần được nhập bằng tổng 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.

loi na trong ham vlookup va hlookup 5f464c12292c2

Trong ví dụ này, không riêng gì 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ẽ nhu yếu hàm VLOOKUP tìm kiếm một hiệu quả khớp tương đối thay cho một hiệu quả khớp đúng chuẩn. Không sống sót tác dụng gần khớp nào cho giá trị “ Chuối ” và “ Lê ” đứng trước “ Đào ” theo thứ tự bảng vần âm. 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 đúng mự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 list tra cứu .Nếu bạn đang sử dụng hàm MATCH, hãy thử đổi khác giá trị của tham đối match_type nhằm mục đích chỉ định thứ tự sắp xếp cho bảng. Để tìm tác dụng khớp đúng mự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é.

Bài viết liên quan

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *