Cách khắc phục lỗi VLOOKUP trong Excel

Cách khắc phục lỗi VLOOKUP trong Excel 1

Có một vài điều làm nổi bật mồ hôi ở người dùng Microsoft Excel hơn là nghĩ về lỗi VLOOKUP. Nếu bạn không quá quen thuộc với Excel, thì VLOOKUP là một trong những hàm khó nhất hoặc ít nhất là một trong những hàm ít được hiểu nhất.

Mục đích của VLOOKUP là tìm kiếm và trả lại dữ liệu từ một cột khác trong bảng tính Excel của bạn. Thật không may, nếu bạn nhận được công thức VLOOKUP sai, Excel sẽ gây ra lỗi cho bạn. Hãy để Vượt qua một số lỗi VLOOKUP phổ biến và giải thích cách khắc phục chúng.

Hạn chế của VLOOKUP

Trước khi bạn bắt đầu sử dụng VLOOKUP, bạn nên lưu ý rằng đó luôn là lựa chọn tốt nhất cho người dùng Excel.

Để bắt đầu, nó có thể được sử dụng để tra cứu dữ liệu ở bên trái của nó. Nó cũng chỉ hiển thị giá trị đầu tiên mà nó tìm thấy, có nghĩa là VLOOKUP không phải là một tùy chọn cho phạm vi dữ liệu với các giá trị trùng lặp. Cột tìm kiếm của bạn cũng cần phải là cột bên trái xa nhất trong phạm vi dữ liệu của bạn.

Trong ví dụ dưới đây, cột xa nhất (cột A) được sử dụng làm cột tìm kiếm. Không có giá trị trùng lặp trong phạm vi và dữ liệu tra cứu (trong trường hợp này là dữ liệu từ cột B) là ở bên phải của cột tìm kiếm.

Một ví dụ về công thức VLOOKUP đang được sử dụng trong Excel

Các hàm INDEX và MATCH sẽ là các lựa chọn thay thế tốt nếu bất kỳ vấn đề nào trong số này là một vấn đề, cũng như chức năng XLOOKUP mới trong Excel, hiện đang trong giai đoạn thử nghiệm beta.

VLOOKUP cũng yêu cầu dữ liệu được sắp xếp theo hàng để có thể tìm kiếm và trả lại dữ liệu chính xác. HLOOKUP sẽ là một lựa chọn tốt nếu đây không phải là trường hợp.

Có một số hạn chế bổ sung đối với các công thức VLOOKUP có thể gây ra lỗi, vì chúng tôi sẽ giải thích thêm.

Lỗi VLOOKUP và # N / A

Một trong những lỗi VLOOKUP phổ biến nhất trong Excel là lỗi # Không có lỗi. Lỗi này xảy ra khi VLOOKUP có thể tìm thấy giá trị mà bạn đang tìm kiếm.

Để bắt đầu, giá trị tìm kiếm có thể không tồn tại trong phạm vi dữ liệu của bạn hoặc bạn có thể đã sử dụng giá trị sai. Nếu bạn thấy lỗi N / A, hãy kiểm tra kỹ giá trị trong công thức VLOOKUP của bạn.

Nếu giá trị là chính xác, thì giá trị tìm kiếm của bạn không tồn tại. Điều này giả sử bạn sử dụng VLOOKUP để tìm kết quả khớp chính xác, với phạm vi_lookup đối số được đặt thành SAI.

VLOOKUP trả về lỗi NA trong Excel

Trong ví dụ trên, tìm kiếm một thẻ học sinh với số 104 (trong tế bào G4) trả về một Lỗi # N / A bởi vì số ID tối thiểu trong phạm vi là 105.

Nếu phạm vi_lookup đối số ở cuối công thức VLOOKUP của bạn bị thiếu hoặc được đặt thành THẬT, sau đó VLOOKUP sẽ trả về lỗi # N / A nếu phạm vi dữ liệu của bạn không được sắp xếp theo thứ tự tăng dần.

Nó cũng sẽ trả về lỗi # N / A nếu giá trị tìm kiếm của bạn nhỏ hơn giá trị thấp nhất trong phạm vi.

Một ví dụ khác về Lỗi NA với VLOOKUP trong Excel, do phạm vi dữ liệu không được sắp xếp đúng

Trong ví dụ trên, thẻ học sinh các giá trị được trộn lẫn. Mặc dù giá trị của 105 tồn tại trong phạm vi, VLOOKUP có thể chạy một tìm kiếm chính xác với phạm vi_lookup đối số được đặt thành THẬT bởi vì cột A isn Sắp xếp theo thứ tự tăng dần.

Các lý do phổ biến khác cho các lỗi # N / A bao gồm sử dụng cột tìm kiếm không phải là bên trái xa nhất và sử dụng tham chiếu ô cho các giá trị tìm kiếm có chứa số nhưng được định dạng dưới dạng văn bản hoặc chứa các ký tự dư thừa như khoảng trắng.

Khắc phục sự cố lỗi #VALUE

Các #GIÁ TRỊ lỗi thường là một dấu hiệu cho thấy công thức chứa hàm VLOOKUP không đúng theo một cách nào đó.

Trong hầu hết các trường hợp, điều này thường là do ô mà bạn đang tham chiếu là giá trị tìm kiếm của bạn. Các Kích thước tối đa của giá trị tra cứu VLOOKUP là 255 ký tự.

Nếu bạn xử lý các ô chứa chuỗi ký tự dài hơn, thì VLOOKUP đã thắng có thể xử lý chúng.

Công thức VLOOKUP trả về lỗi GIÁ TRỊ trong Excel

Cách giải quyết duy nhất cho việc này là thay thế công thức VLOOKUP của bạn bằng công thức INDEX và MATCH kết hợp. Ví dụ: trong đó một cột chứa một ô có chuỗi hơn 255 ký tự, có thể sử dụng hàm MATCH lồng bên trong INDEX để xác định vị trí dữ liệu này.

Trong ví dụ dưới đây, MỤC LỤC trả về giá trị trong ô B 4, sử dụng phạm vi trong cột A để xác định đúng hàng. Điều này sử dụng lồng nhau TRẬN ĐẤU Hàm để xác định chuỗi trong cột A (chứa 300 ký tự) khớp với ô H4.

Trong trường hợp này, đó là tế bào A4, với MỤC LỤC trở về 108 (giá trị của B 4).

Một ví dụ về công thức INDEX và MATCH kết hợp trong Excel

Lỗi này cũng sẽ xuất hiện nếu bạn đã sử dụng một tham chiếu không chính xác đến các ô trong công thức của mình, đặc biệt là nếu bạn sử dụng một phạm vi dữ liệu từ một sổ làm việc khác.

Tài liệu tham khảo Workbook cần được đặt trong dấu ngoặc vuông để công thức hoạt động chính xác.

Công thức VLOOKUP tham chiếu nhiều sổ làm việc Excel

Nếu bạn gặp lỗi #VALUE, hãy kiểm tra kỹ công thức VLOOKUP của bạn để xác nhận rằng các tham chiếu ô của bạn là chính xác.

#NAME và VLOOKUP

Nếu lỗi VLOOKUP của bạn không phải là lỗi #VALUE hoặc lỗi # N / A, thì nó có thể là một lỗi #TÊN lỗi. Trước khi bạn hoảng hốt khi nghĩ về điều này, hãy yên tâm. Đây là lỗi VLOOKUP dễ nhất để khắc phục.

Công thức VLOOKUP sai chính tả trong Excel, trả về lỗi NAME

Một lỗi #NAME xuất hiện khi bạn cài đặt sai chính tả một hàm trong Excel, cho dù đó là VLOOKUP hoặc một hàm khác như SUM. Nhấp vào ô VLOOKUP của bạn và kiểm tra kỹ xem bạn có thực sự viết đúng VLOOKUP không.

Nếu không có vấn đề nào khác, công thức VLOOKUP của bạn sẽ hoạt động khi lỗi này được khắc phục.

Sử dụng các hàm Excel khác

Nó có một tuyên bố táo bạo, nhưng các chức năng như VLOOKUP sẽ thay đổi cuộc sống của bạn. Ít nhất, nó sẽ thay đổi cuộc sống làm việc của bạn, biến Excel thành một công cụ mạnh hơn để phân tích dữ liệu.

Nếu VLOOKUP không phù hợp với bạn, thì hãy tận dụng các hàm Excel hàng đầu này để thay thế.

Cách khắc phục lỗi VLOOKUP trong Excel 2