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 #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_arrayA2: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 INDEX và MATCH 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ô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.
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.
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 .
?DienDan.Edu.Vn cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn. https://diendan.edu.vn/
?Các bạn tham khảo hướng dẫn tải file trên Quản Trị Excel tại đây: http://www.quantriexcel.info/2018/11/huong-dan-tai-file-tren-quan-tri-excel.html
DienDan.Edu.Vn Cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn.DienDan.Edu.Vn! là một website với tiêu chí chia sẻ thông tin,... Bạn có thể nhận xét, bổ sung hay yêu cầu hướng dẫn liên quan đến bài viết. Vậy nên đề nghị các bạn cũng không quảng cáo trong comment này ngoại trừ trong chính phần tên của bạn.Cám ơn.
