Áp dụng Cho: Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel 2016 cho Mac Thêm...
Chủ đề này mô tả những nguyên nhân phổ biến nhất dưới dạng để sao VLOOKUP của bạn có thể gặp trục trặc.
Vấn đề: Giá trị tra cứu không nằm trong cột đầu tiên trong đối số table_array
Một trong các hạn chế lớn nhất của 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. Vì vậy 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ẽ 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 là vì giá trị tra cứu "Khác" trong cột thứ hai (nông phẩm) trong A2:C10 đối số table_array , do vậy Excel 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 xung quanh cột của bạn. Mà có thể không thực tế cao 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.
Sử dụng chỉ MỤC/KHỚP thay vì VLOOKUP
Chỉ MỤC/MATCH có thể dùng VLOOKUP không đáp ứng nhu cầu của bạn. Lợi thế lớn nhất của chỉ SỐ/MATCH là bạn có thể tìm kiế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ừ một bảng/phạm đã xác định dựa trên vị trí của nó và MATCH 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, bạn có thể tìm 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 qua 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, không giống như VLOOKUP nơi trả về giá trị phải 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 có vẻ không thỏa thuận lớ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 cần thêm/loại bỏ mộ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 thậm chí 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.
- 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ư VLOOKUP nơi bạn chỉ có thể 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.
- Hàm INDEX và MATCH sẽ 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 của bạn và hàm INDEX và MATCH sẽ không ngắt. Mặt khác, VLOOKUP ngắt nếu bạn đã thêm cột vào bảng khi nào thì một tham chiếu tĩnh vào bảng.
- Hàm 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, 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 một cú pháp cho chỉ MỤC/KHỚP, bạ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ó. Để nó trông giống như:
= INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])
Hãy dùng chỉ MỤC/KHỚP để thay thế VLOOKUP trong 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 (tôi muốn 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, nơi giá trị trả về 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), vốn 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, 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ó, hãy đảm bảo rằng các ô được tham chiếu không có ẩn khoảng trắng hoặc ký tự không in ra. Cũng 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à văn bản không.
Sử dụng hàm CLEAN 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.
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/MATCH thay vì VLOOKUP. Với chỉ SỐ/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 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. Bạn sẽ không cần sắp xếp cho FALSE.
- Sử 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. (Lưu ý rằng 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 lớn trôi nổi, để 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
Blog tổng hợp những kinh nghiệm được học, áp dụng và chia sẻ
