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

Lưu ý:  Chúng tôi muốn cung cấp cho bạn nội dung trợ giúp mới nhất ở chính ngôn ngữ của bạn, nhanh nhất có thể. Trang này được dịch tự động nên có thể chứa các lỗi về ngữ pháp hoặc nội dung không chính xác. Mục đích của chúng tôi là khiến nội dung này trở nên hữu ích với bạn. Vui lòng cho chúng tôi biết ở cuối trang này rằng thông tin có hữu ích với bạn không? Dưới đây là bài viết bằng Tiếng Anh để bạn tham khảo dễ hơn.

Chủ đề này mô tả lý do phổ biến nhất cho ra kết quả sai trên hàm VLOOKUP , và cung cấp các gợi ý về cách dùng hàm INDEXMATCH thay vào đó.

Mẹo: Ngoài ra, tham chiếu đến các thẻ tham chiếu nhanh: mẹo khắc phục sự cố VLOOKUP mà trình bày các lý do thường gặp cho #NA vấn đề trong tệp PDF thuận tiện. Bạn có thể chia sẻ tệp PDF với những người khác hoặc in để tham khảo của riêng bạn.

Vấn đề: Giá trị tra cứu không nằm trong cột đầu tiên trong đối số table_array

Ràng buộc một về VLOOKUP là rằng nó chỉ có thể tìm các giá trị vào cột ngoài cùng bên trái trong mảng bảng. Nếu giá trị tra cứu của bạn không có trong cột đầu tiên của mảng, bạn sẽ nhìn thấy lỗi #N/A.

Trong bảng sau đây, chúng tôi muốn truy xuất số đơn vị đã bán cho khác.

Lỗi #NA trong hàm VLOOKUP: Giá trị tra cứu không nằm ở cột đầu tiên của mảng bảng

Lỗi #N/A kết quả vì giá trị tra cứu "Khác" xuất hiện trong cột thứ hai (sản phẩm) của đối số table_array A2:C10. Trong trường hợp này, Excel sẽ tìm kiếm nó trong cột A, không cột B.

Giải pháp: bạn có thể thử để sửa lỗi này bằng cách điều chỉnh của bạn về VLOOKUP để tham chiếu cột chính xác. Nếu đó là không có thể xảy ra, rồi thử di chuyển cột của bạn. Mà cũng có thể rất viển vông, nếu bạn có bảng tính lớn hoặc phức tạp mà giá trị ô là kết quả của phép tính khác — hoặc có thể có là các lý do lô-gic, tại sao bạn chỉ cần không thể di chuyển các cột xung quanh. Giải pháp là sử dụng một kết hợp hàm INDEX và MATCH, mà có thể tìm một giá trị trong một cột, bất kể vị trí vị trí của nó trong bảng tra cứu. Hãy xem phần tiếp theo.

Cân nhắc sử dụng chỉ mục/khớp thay vào đó

Hàm INDEXMATCH có các tùy chọn tốt cho nhiều trường hợp mà VLOOKUP không đáp ứng nhu cầu của bạn. Lợi thế chính của chỉ mục/khớp là rằng bạn có thể tìm một giá trị trong một cột trong bất kỳ vị trí nào trong bảng tra cứu. Hàm INDEX trả về một giá trị từ bảng/phạm vi đã xác định — theo vị trí của nó. KHỚP trả về vị trí tương đối của một giá trị trong một bảng/phạm vi. Dùng hàm INDEX và MATCH cùng nhau trong một công thức tra cứu một giá trị trong một bảng/mảng bằng cách xác định vị trí tương đối của giá trị trong bảng/mảng.

Có là một số lợi ích của việc sử dụng chỉ mục/khớp thay vì VLOOKUP:

  • Với hàm INDEX và MATCH, giá trị trả về không cần nằm trong cùng một cột là cột tra cứu. Đây là khác với VLOOKUP, trong đó giá trị trả về có nằm trong phạm vi đã xác định. Làm cách nào vấn đề này? Với VLOOKUP, bạn phải biết số cột có chứa giá trị trả về. Trong khi điều này không có vẻ khó khăn, có thể cumbersome khi bạn đã có một bảng lớn và có để đếm số cột. Ngoài ra, nếu bạn thêm/loại bỏ một cột trong bảng của bạn, bạn phải recount và Cập Nhật đối số col_index_num . Với hàm INDEX và MATCH, không có tính là bắt buộc là cột tra cứu khác với cột có giá trị trả về.

  • Với hàm INDEX và MATCH, bạn có thể xác định một hàng hoặc cột trong một mảng — hoặc xác định cho cả hai. Điều này có nghĩa là bạn có thể tra cứu các giá trị theo chiều dọc và ngang.

  • INDEX và MATCH có thể dùng để tra cứu các giá trị trong bất kỳ cột nào. Không giống như VLOOKUP — trong mà bạn có thể chỉ tra cứu các giá trị trong cột đầu tiên trong một bảng — INDEX và MATCH sẽ hoạt động nếu giá trị tra cứu của bạn nằm trong cột đầu tiên, lần cuối, hoặc bất kỳ chỗ nào ở giữa.

  • INDEX và MATCH cung cấp tính linh hoạt làm động tham chiếu tới cột chứa các giá trị trả về. Điều này có nghĩa là bạn có thể thêm cột vào bảng mà không có giới hạn INDEX và MATCH. Mặt khác, VLOOKUP ngắt nếu bạn cần phải thêm một cột vào bảng — bởi vì nó làm một tham chiếu tĩnh vào bảng.

  • INDEX và MATCH sẽ cung cấp các linh hoạt hơn với kết quả khớp. Hàm INDEX và MATCH có thể tìm thấy kết quả khớp chính xác, hoặc một giá trị lớn hơn hoặc nhỏ hơn giá trị tra cứu. VLOOKUP sẽ chỉ tìm kiếm một phù hợp nhất với một giá trị (theo mặc định) hoặc một giá trị chính xác. VLOOKUP cũng giả định theo mặc định cột đầu tiên trong mảng bảng được sắp xếp chữ cái và giả sử bảng của bạn không được thiết lập như vậy, VLOOKUP sẽ trả về phù hợp nhất đầu tiên trong bảng, có thể không dữ liệu bạn đang tìm kiếm.

Cú pháp

Để xây dựng cú pháp cho chỉ mục/khớp, bạn cần phải sử dụng đối số mảng/tham chiếu từ hàm INDEX và lồng cú pháp MATCH bên trong nó. Điều này có dạng:

= INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])

Hãy dùng chỉ mục/khớp để thay thế VLOOKUP từ ví dụ ở trên. Cú pháp sẽ trông như thế này:

= INDEX(C2:C10,MATCH(B13,B2:B10,0))

Bằng tiếng Anh đơn giản này có nghĩa là:

= Lập chỉ mục (một giá trị trả về từ C2:C10, sẽ khớp với (khác, đây là một nơi nào đó trong mảng B2: B10, trong đó giá trị trả về là giá trị đầu tiên tương ứng với khác))

Có thể sử dụng các hàm INDEX và MATCH để thay thế cho hàm VLOOKUP

Công thức tìm giá trị đầu tiên trong C2:C10 tương ứng với khác (trong B7) và trả về giá trị trong C7 (100), đó là giá trị đầu tiên phù hợp với khác.

Vấn đề: Khớp chính xác không tìm thấy

Khi đối số range_lookup là FALSE — và VLOOKUP là không thể tìm thấy kết quả khớp chính xác trong dữ liệu của bạn — đó trả về lỗi #N/A.

Giải pháp: nếu bạn chắc chắn dữ liệu quan hệ tồn tại trong bảng tính của bạn và VLOOKUP không bắt nó, mất thời gian để xác nhận rằng các ô được tham chiếu không ẩn khoảng trắng hoặc ký tự không in ra. Ngoài ra, hãy đảm bảo rằng các ô theo dõi kiểu dữ liệu chính xác. Ví dụ, ô có các số sẽ được định dạng dưới dạng sốvà không phải văn bản.

Ngoài ra, hãy cân nhắc bằng cách dùng hàm hoặc sạch hoặc cắt xén để dọn sạch dữ liệu trong ô.

Vấn đề: Giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong mảng

Nếu đối số ange_lookup được đặt là TRUE — và giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong mảng — bạn sẽ nhìn thấy lỗi #N/A. Hiển thị đúng cho một khoảng phù hợp trong mảng và trả về các gần nhất giá trị nhỏ hơn giá trị tra cứu.

Trong ví dụ sau, giá trị tra cứu là 100, nhưng có không có giá trị trong phạm vi B2:C10 có ít hơn 100; do đó lỗi.

Lỗi N/A trong hàm VLOOKUP khi giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong mảng

Giải pháp:

  • Sửa giá trị tra cứu theo nhu cầu.

  • Nếu bạn không thể thay đổi giá trị tra cứu và cần linh hoạt hơn với giá trị khớp nhau, hãy cân nhắc dùng chỉ mục/khớp thay vì VLOOKUP — hãy xem phần ở phía trên trong bài viết này. Với chỉ mục/khớp, bạn có thể tra cứu giá trị lớn hơn, ít hơn để, hoặc bằng giá trị tra cứu. Để biết thêm thông tin về cách dùng chỉ mục/khớp thay vì hàm VLOOKUP, tham khảo phần trước trong chủ đề này.

Vấn đề: Cột tra cứu sẽ không được sắp xếp theo thứ tự tăng dần

Nếu đối số ange_lookup được đặt là TRUE — và một cột tra cứu của bạn sẽ không được sắp xếp theo thứ tự tăng dần (A-Z) — bạn sẽ nhìn thấy lỗi #N/A.

Giải pháp:

  • Thay đổi hàm VLOOKUP tìm kiếm kết quả khớp chính xác. Để thực hiện điều này, hãy đặt đối số ange_lookup thành FALSE. Sắp xếp không là cần thiết cho FALSE.

  • Dùng hàm INDEX/MATCH tra cứu một giá trị trong bảng chưa sắp xếp.

Vấn đề: Giá trị là một số điểm lớn trôi nổi

Nếu bạn có giá trị thời gian hoặc số thập phân lớn trong ô, Excel trả về lỗi #N/A do trôi nổi điểm độ chính xác. Trôi nổi điểm số là số tiếp theo sau dấu thập phân. (Excel lưu trữ giá trị thời gian như trôi nổi điểm số.) Excel không thể lưu trữ số với điểm trôi nổi rất lớn, để cho hàm đó để hoạt động đúng cách, trôi nổi các chỉ số sẽ cần phải được làm tròn đến 5 chữ số thập phân.

Giải pháp: rút ngắn các số bằng cách chúng làm tròn lên tới năm chữ số thập phân với hàm ROUND .

Bạn có câu hỏi về một hàm cụ thể?

Đăng câu hỏi vào diễn đàn cộng đồng Excel

Giúp chúng tôi cải thiện Excel

Bạn có đề xuất về cách chúng tôi có thể cải thiện phiên bản Excel tiếp theo không? Nếu có, hãy xem qua các chủ đề tại Excel User Voice.

Xem Thêm

Phát triển kỹ năng Office của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Người dùng nội bộ Office

Thông tin này có hữu ích không?

Cảm ơn phản hồi của bạn!

Cảm ơn bạn đã phản hồi! Để trợ giúp tốt hơn, có lẽ chúng tôi sẽ kết nối bạn với một trong những nhân viên hỗ trợ Office của chúng tôi.

×