Chủ đề này mô tả các lý do VLOOKUP phổ biến nhất về kết quả sai trên hàm và đưa ra các đề xuất cho việc sử dụng hàm INDEX và MATCH thay vào đó.
Mẹo: Ngoài ra, hãy tham khảo Thẻ Tham chiếu Nhanh: Mẹo khắc phục sự cố VLOOKUP thể hiện những lý do phổ biến cho #NA cố trong tệp PDF thuận tiện. Bạn có thể chia sẻ tệp PDF với người khác hoặc in để tham khảo.
Sự cố: Giá trị tra cứu không nằm ở cột đầu tiên trong đối table_array số
Một ràng buộc của hàm VLOOKUP là nó chỉ có thể tìm các giá trị ở 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 nằm trong cột đầu tiên của mảng, bạn sẽ #N/A.
Trong bảng sau đây, chúng tôi muốn truy xuất số lượng đơn vị đã bán cho Cải xoê.
Kết quả #N/A vì giá trị tra cứu "Kale" xuất hiện trong cột thứ hai (Nông sản) của đối số table_array A2:C10. Trong trường hợp này, Excel đang tìm trong cột A, không phải cột B.
Giải pháp: Bạn có thể thử khắc phục sự cố này bằng cách điều chỉnh hàm VLOOKUP để tham chiếu cột chính xác. Nếu không thể, hãy thử di chuyển các cột của bạn. Điều đó cũng có thể rất không thể thực hiện, nếu bạn có bảng tính lớn hoặc phức tạp trong đó giá trị ô là kết quả của các phép tính khác—hoặc có thể có các lý do lô-gic khác khiến bạn chỉ đơn giản là không thể di chuyển các cột. Giải pháp là sử dụng kết hợp hàm INDEX và MATCH, có thể tra cứu giá trị trong cột bất kể vị trí của giá trị đó trong bảng tra cứu. Xem phần tiếp theo.
Thay vào đó, hãy cân nhắc việc sử dụng hàm INDEX/MATCH
INDEX và MATCH là các tùy chọn tốt cho nhiều trường hợp mà trong đó hàm VLOOKUP không đáp ứng nhu cầu của bạn. Ưu điểm chính của hàm INDEX/MATCH là bạn có thể tra cứu giá trị trong cột ở bất kỳ vị trí nào trong bảng tra cứu. Hàm INDEX trả về một giá trị từ một bảng/phạm vi đã xác định—theo vị trí của nó. Hàm MATCH trả về vị trí tương đối của một giá trị trong bảng/phạm vi. Dùng hàm INDEX và MATCH cùng nhau trong công thức để tra cứu giá trị trong 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ó một số lợi ích khi sử dụng hàm INDEX/MATCH thay vì hàm VLOOKUP:
-
Với hàm INDEX và MATCH, giá trị trả về không cần nằm trong cùng cột với cột tra cứu. Giá trị này khác với hàm VLOOKUP, trong đó giá trị trả về phải nằm trong phạm vi đã xác định. Chuyện này thế nào? Với hàm VLOOKUP, bạn phải biết số cột có chứa giá trị trả về. Mặc dù điều này có vẻ không khó khăn, nhưng bạn có thể rườm rà khi có một bảng lớn và phải đế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 mình, bạn phải đếm lại và cập nhật đối col_index_num của mình. Với hàm INDEX và MATCH, không cần đếm vì 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 chỉ định cả hai. Điều này có nghĩa là bạn có thể tra cứu các giá trị theo cả chiều dọc và chiều ngang.
-
Có thể dùng hàm INDEX và MATCH để tra cứu các giá trị trong bất kỳ cột nào. Không giống như hàm VLOOKUP— trong đó bạn chỉ có thể tra cứu giá trị ở cột đầu tiên của 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, cột cuối cùng hoặc bất kỳ vị trí nào ở giữa.
-
Hàm INDEX và MATCH cung cấp khả năng linh hoạt trong việc tạo tham chiếu động cho cột có chứa 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 phá vỡ hàm INDEX và MATCH. Mặt khác, hàm VLOOKUP ngắt nếu bạn cần thêm cột vào bảng—vì nó tạo tham chiếu tĩnh đến bảng.
-
Hàm INDEX và MATCH cung cấp sự linh hoạt cao hơn với các trận đấu. Hàm INDEX và MATCH có thể tìm thấy kết quả khớp chính xác hoặc giá trị lớn hơn hoặc nhỏ hơn giá trị tra cứu. VLOOKUP sẽ chỉ tìm giá trị khớp gần nhất với giá trị (theo mặc định) hoặc giá trị chính xác. VLOOKUP cũng giả định rằng cột đầu tiên trong mảng bảng được sắp xếp theo bảng chữ cái và giả sử bảng của bạn không được thiết lập theo cách đó, hàm VLOOKUP sẽ trả về kết quả khớp gần nhất đầu tiên trong bảng, mà có thể không phải là dữ liệu bạn đang tìm kiếm.
Cú pháp
Để xây dựng cú pháp cho hàm INDEX/MATCH, bạn cần sử dụng đối số mảng/tham chiếu từ hàm INDEX và lồng cú pháp MATCH vào bên trong. Thao tác này có dạng:
=INDEX(array hoặc reference, MATCH(lookup_value,lookup_array,[match_type])
Chúng ta hãy dùng hàm INDEX/MATCH để thay thế hàm VLOOKUP từ ví dụ ở trên. Cú pháp sẽ trông như thế này:
=INDEX(C2:C10,MATCH(B13,B2:B10,0))
Trong tiếng Anh đơn giản nó có nghĩa là:
=INDEX(trả về một giá trị từ C2:C10, mà sẽ MATCH(Kale, nằm ở đâu đó trong mảng B2:B10, trong đó giá trị trả về là giá trị đầu tiên tương ứng với Kale))
Công thức tìm kiếm giá trị đầu tiên trong C2:C10 tương ứng với Kale (trong B7) và trả về giá trị trong C7 (100), là giá trị đầu tiên khớp với Kale.
Sự cố: Không tìm thấy kết quả khớp chính xác
Khi tham range_lookup là FALSE—và hàm VLOOKUP không thể tìm thấy kết quả khớp chính xác trong dữ liệu của bạn—nó trả về giá #N/A.
Giải pháp: Nếu bạn chắc chắn rằng dữ liệu liên quan tồn tại trong bảng tính của mình và hàm VLOOKUP không nắm bắt được dữ liệu đó, hãy dành thời gian để xác minh rằng các ô được tham chiếu không có khoảng trống ẩn hoặc ký tự không in ra. Ngoài ra, hãy đảm bảo rằng các ô tuân theo kiểu dữ liệu chính xác. Ví dụ: ô có số sẽ được định dạng là Số, chứ không phải Văn bản.
Ngoài ra, hãy cân nhắc sử dụng hàm CLEANhoặc TRIM để dọn sạch dữ liệu trong các ô.
Sự cố: Giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong mảng
Nếu đối range_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ẽ thấy lỗi #N/A. TRUE tìm kiếm kết quả khớp tương đối trong mảng và trả về giá trị nhỏ hơn giá trị tra cứu gần nhất.
Trong ví dụ sau đây, giá trị tra cứu là 100 nhưng không có giá trị nào trong dải ô B2:C10 nhỏ hơn 100; do đó có lỗi.
Giải pháp:
-
Sửa giá trị tra cứu nếu cần.
-
Nếu bạn không thể thay đổi giá trị tra cứu và cần sự linh hoạt cao hơn với các giá trị khớp, hãy cân nhắc sử dụng hàm INDEX/MATCH thay vì hàm VLOOKUP—hãy xem mục ở trên trong bài viết này. Với hàm INDEX/MATCH, bạn có thể tra cứu các giá trị lớn hơn, nhỏ hơn hoặc bằng giá trị tra cứu. Để biết thêm thông tin về cách sử dụng hàm INDEX/MATCH thay vì hàm VLOOKUP, hãy tham khảo mục trước đó trong chủ đề này.
Sự cố: Cột tra cứu không được sắp xếp theo thứ tự tăng dần
Nếu tham đối range_lookup được đặt thành TRUE—và một trong các cột tra cứu của bạn không được sắp xếp theo thứ tự tăng dần (A-Z) —bạn sẽ thấy lỗi #N/A.
Giải pháp:
-
Thay đổi hàm VLOOKUP để tìm kết quả khớp chính xác. Để làm điều đó, hãy đặt tham range_lookup cho kết quả là FALSE. Không cần sắp xếp cho FALSE.
-
Sử dụng hàm INDEX/MATCH để tra cứu giá trị trong bảng chưa sắp xếp.
Sự cố: Giá trị là một số điểm trôi nổi lớn
Nếu bạn có giá trị thời gian hoặc số thập phân lớn trong các ô, Excel trả về lỗi #N/A do độ chính xác của điểm nổi. Số điểm trôi nổi là các số nằm sau dấu thập phân. (Excel lưu trữ các giá trị thời gian dưới dạng số điểm trôi nổi.) Excel không thể lưu trữ các số có các điểm trôi nổi rất lớn, vì vậy để hàm hoạt động chính xác, các số điểm trôi nổi sẽ cần được làm tròn đến 5 vị trí thập phân.
Giải pháp: Rút ngắn số bằng cách làm tròn chúng tới năm vị trí thập phân bằng hàm ROUND .
Bạn cần thêm trợ giúp?
Bạn luôn có thể hỏi chuyên gia trong Cộng đồng kỹ thuật Excel hoặc nhận hỗ trợ trong Cộng đồng.
