Cách sửa lỗi #N/A trong Excel hàm VLOOKUP và HLOOKUP chi tiết từ A đến Z

Cách sửa lỗi #N/A trong Excel hàm VLOOKUP và HLOOKUP là thủ thuật bạn đang tìm hiểu? Bài viết này của FASTCARE chính là tất cả những gì bạn cần.

Nhiều khi viết hàm VLOOKUP hay HLOOKUP “hết hơi” mà nhấn Enter lại ra #N/A khiến bạn không biết lý do và khắc phục ra sao. Áp dụng ngay cách sửa lỗi #N/A trong Excel hàm VLOOKUP và HLOOKUP mà FASTCARE hướng dẫn chi tiết dưới đây bạn nhé!

banner-fastcare-blog-desktop-tet-thay-pin-chua-850x200

Tìm hiểu xem lỗi #N/A trong Excel là gì? 

N/A thường là viết tắt của các cụm từ tiếng Anh như [Not Available], [Not Applicable], hoặc [No Answer] và đều được hiểu là không khả dụng, không sẵn có. Do vậy bạn cũng có thể hiểu lỗi NA trong Excel là không tìm được câu trả lời, giá trị tương thích hoặc kết quả không như ý muốn của bạn. 

Đặc biệt, lỗi #N/A trong Excel xảy ra nhiều nhất đối với những trường hợp sử dụng các hàm tìm kiếm giá trị, có thể kể đến là VLOOKUP và HLOOKUP. Vậy nguyên nhân xảy ra lỗi #N/A trong Excel ở đây là gì? 

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

Thông thường, bạn sẽ gặp rắc rối với lỗi #N/A trong Excel hàm VLOOKUP và HLOOKUP do một trong số những nguyên nhân sau đây:

  • Sử dụng sai công thức các hàm tìm kiếm VLOOKUP và HLOOKUP.
  • Lỗi #N/A xuất hiện do giá trị cần tra cứu không tồn tại hoặc có sẵn trong vùng dữ liệu, vùng tìm kiếm dữ liệu chưa đủ rộng. 
  • Vùng tìm kiếm không được cố định nên khi bạn thực hiện lệnh copy trong Excel, vùng dữ liệu sẽ bị thay đổi dẫn đến sai sót giá trị hàm.
  • Giá trị cần tra cứu có định dạng không khớp với kiểu dữ liệu của hàm.
  • Ô tham chiếu chứa các khoảng trống thừa mà bạn không để ý.
  • Lỗi #N/A khi sử dụng VLOOKUP, HLOOKUP kết hợp đồng thời với các hàm khác như: MID, LEFT, RIGHT… 

Cách sửa lỗi #N/A trong Excel hàm VLOOKUP và HLOOKUP

Sau khi đã hiểu lỗi #N/A trong Excel là gì cũng như xác định được nguyên nhân xảy ra lỗi #N/A chắc chắn bạn sẽ cần đến những cách sửa lỗi #N/A trong Excel hàm VLOOKUP và HLOOKUP mà FASTCARE tổng hợp trong phần này. 

Cách sửa lỗi #N/A trong Excel do sử dụng sai công thức hàm 

VLOOKUP và HLOOKUP là 2 hàm trong Excel khiến khá nhiều người dùng nhầm lẫn bởi công năng cũng như công thức áp dụng của chúng. Do vậy, nếu bạn điền vào công thức hàm những tham số không đúng với bảng dữ liệu, việc gặp lỗi #N/A là điều không thể tránh khỏi.

Công thức hàm Vlookup Hlookup

Bạn có thể tham khảo bảng so sánh 2 công thức hàm VLOOKUP và HLOOKUP trên đây và ý nghĩa bên dưới để biết cách dùng chính xác nhất. 

Cách sửa lỗi #N/A trong Excel do sử dụng sai công thức hàm 

Cách sửa lỗi #N/A do vùng dữ liệu không đầy đủ

Lựa chọn vùng tìm kiếm không đầy đủ cũng có thể khiến cho bạn không thể tìm kiếm được giá trị tra cứu và Excel hiển thị lỗi #N/A. 

Ví dụ: Tìm mã sản phẩm Táo trong bảng dữ liệu như hình:

Cách sửa lỗi #N/A do vùng dữ liệu không đầy đủ-1

Bạn có thể thấy ở tại bảng này không hề xuất hiện dữ liệu của Táo nên khi bạn nhập hàm =VLOOKUP(“Táo”;B2:D6;2;0) trong bảng Excel sẽ cho ra hiển thị #N/A ngay lập tức.

Cách sửa lỗi #N/A do vùng dữ liệu không đầy đủ-2

Cách sửa lỗi #N/A trong Excel hàm VLOOKUP lúc này sẽ có 2 phương án: một là bạn cần đổi lại giá trị tìm kiếm; hai là bổ sung thêm/chỉnh sửa lại giá trị trong bảng.

Cách sửa lỗi 1: Đổi lại yêu cầu sản phẩm cần tìm kiếm từ Táo thành Dưa hấu với công thức =VLOOKUP(“Dưa hấu”;B2:D6;2;0) sẽ trả kết quả đúng với yêu cầu của mình. 

Cách sửa lỗi #N/A do vùng dữ liệu không đầy đủ-3

Cách sửa lỗi 2: Bổ sung thêm giá trị của Táo vào trong bảng và nhập lệnh =VLOOKUP(“Táo”;B2:D7;2;0) để tìm kiếm mã sản phẩm. 

Cách sửa lỗi #N/A do vùng dữ liệu không đầy đủ-4

Có thể bạn quan tâm:

Cách sửa lỗi #N/A do vùng tìm kiếm không được cố định

#N/A còn xuất hiện trong những trường hợp bạn thực hiện lệnh Copy + Paste các hàm VLOOKUP và HLOOKUP chủ yếu là do bạn đã quên không cố định lại vùng tìm kiếm dữ liệu. Cách sửa lỗi #N/A trong Excel hàm HLOOKUP và VLOOKUP ở trường hợp này vô cùng đơn giản chỉ với 1 nút F4. 

Ví dụ: Tương tự bảng dữ liệu bên trên, bạn có thể thấy hàm VLOOKUP tìm kiếm Dưa hấu đang để công thức =VLOOKUP(“Dưa hấu”;B2:D6;2;0) chưa được cố định vùng tìm kiếm.

Cách sửa lỗi #N/A do vùng tìm kiếm không được cố định

Cách giải quyết: Bạn cần bôi đen vùng tìm kiếm, trong ví dụ này là B2:D6 sau đó nhấn phím F4 trên màn hình để cố định lại bảng, bạn sẽ thấy lúc này, ở công thức đã biến đổi thành $B$2:$D$6 không thể thay đổi nên bạn hoàn toàn có thể copy mà không lo bị sai sót.

Cách sửa lỗi #N/A do vùng tìm kiếm không được cố định-2

Cách sửa lỗi #N/A do lỗi kết hợp các hàm 

Đối với một số bảng dữ liệu phức tạp, bắt buộc chúng ta cần phải sử dụng kết hợp nhiều hàm cùng một lúc để đưa ra được kết quả tìm kiếm đúng ý nhất. Và chính việc sử dụng đồng thời “hàm lồng hàm” có thể sẽ khiến nhiều bạn rối não về độ phức tạp và đi đến những sai sót về kết quả. 

► Trường hợp lỗi giá trị khi kết hợp VLOOKUP/ HLOOKUP với hàm MID 

Lấy ví dụ cho trường hợp này FASTCARE sẽ có một bảng dưới đây với yêu cầu dùng hàm HLOOKUP để tìm kiếm điểm khu vực dựa vào SBD.

lỗi giá trị khi kết hợp VLOOKUP/ HLOOKUP với hàm MID -1

Ở đây, chúng ta sẽ sử dụng hàm MID để lọc khu vực thi của thí sinh sau đó sẽ dùng HLOOKUP để đưa ra số điểm tương ứng với khu vực. Công thức lúc bấy giờ sẽ là =HLOOKUP(MID(A3;3;1);$A$12:$F$13;2;0). 

Trong đó: 

MID(A3;3;1): Lấy 1 ký tự thứ 3 ở ô A3 (trong trường hợp này sẽ là 1)

$A$12:$F$13: Là bảng chứa dữ liệu khu vực thi và điểm khu vực

2: Lựa chọn hàng 2 để lấy điểm khu vực

0: Là lấy giá trị chính xác tuyệt đối

lỗi giá trị khi kết hợp VLOOKUP/ HLOOKUP với hàm MID-2

Tuy nhiên, nếu để nguyên như này, chắc chắn kết quả bạn nhận được sẽ chỉ là lỗi #N/A mà thôi. Lý do là bởi hàm MID sẽ chỉ cho ra giá trị ở định dạng Text trong khi bảng dữ liệu điểm thi lại thuộc định dạng số. Cách khắc phục lỗi #N/A trong Excel hàm HLOOKUP lúc này sẽ cần đến sự trợ giúp của hàm Value. 

Bạn cần chỉnh sửa lại công thức hàm để chuyển kết quả của hàm MID về dạng chữ số đó là =HLOOKUP(VALUE(MID(A3;3;1));$A$12:$F$13;2;0) sẽ được số liệu tương ứng mà chúng ta cần.

lỗi giá trị khi kết hợp VLOOKUP/ HLOOKUP với hàm MID-3

► Trường hợp thừa khoảng trống khi kết hợp VLOOKUP/ HLOOKUP với hàm LEFT/RIGHT

Đối với các trường hợp hay phải kết hợp các hàm Left/Right với VLOOKUP hoặc HLOOKUP, lỗi #N/A thường xuất hiện chủ yếu do lỗi đánh máy trong quá trình chuẩn bị các bảng dữ liệu của bạn, điển hình là thừa khoảng trống, dư dấu cách. 

Ví dụ: Đối với 2 bảng dữ liệu sau đây, mình cần điền tên hãng vào bảng mã lô hàng tương ứng. Trường hợp này có thể thấy ở bảng chú thích có để thông tin mã hàng ở phần đuôi nên chúng ta sẽ cần sử dụng thêm hàm RIGHT. 

Công thức hàm cần áp dụng lúc này sẽ là =VLOOKUP(RIGHT(A3;2);D10:E15;2;0). Tuy nhiên, bạn lại nhận về thông báo lỗi #N/A một cách khó hiểu như hình.

Trường hợp thừa khoảng trống khi kết hợp VLOOKUP/ HLOOKUP với hàm LEFT/RIGHT

Điều này xảy ra có lẽ do trong quá trình lập bảng bạn đã để thừa ký tự dẫn đến việc tìm kiếm bị thất bại. Ở ví dụ này, trong mục bảng thông tin phần SS đã bị đánh thừa thêm một dấu cách nên hiển nhiên bạn sẽ không thể truy xuất tên hãng tương ứng.

banner-fastcare-blog-desktop-tet-phu-kien-qua-tot-850x200

Trường hợp thừa khoảng trống khi kết hợp VLOOKUP/ HLOOKUP với hàm LEFT/RIGHT-1

Cách sửa lỗi #N/A trong Excel hàm VLOOKUP và HLOOKUP bây giờ vô cùng đơn giản bởi chỉ cần 1 thao tác xóa dấu cách thừa để đưa giá trị về đúng như dữ liệu yêu cầu.

Trường hợp thừa khoảng trống khi kết hợp VLOOKUP/ HLOOKUP với hàm LEFT/RIGHT-2

Lời kết

Trên đây là một số cách sửa lỗi #N/A trong Excel hàm VLOOKUP và HLOOKUP đơn giản và phổ biến nhất mà FASTCARE đem lại với mong muốn có thể giúp bạn giải quyết những rắc rối. Hy vọng với các thủ thuật này, bạn có thể chinh phục mọi công thức hàm VLOOKUP và HLOOKUP trong Excel thành công nhé!