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