Hàm Vlookup, các lỗi thường gặp, nguyên nhân và cách khắc phục

Hàm VLOOKUP là một trong các hàm phổ biến nhất trong Excel. Đây cũng là một trong những hàm phức tạp nhất mà lỗi thường gặp chính là #N/A. Bài viết này sẽ hướng dẫn bạn cách khắc phục lỗi hàm Vlookup 

Bạn cần một phép toán chính xác:

Tham số cuối cùng trong hàm VLOOKUP, range_lookup, yêu cầu bạn chọn lựa một phép toán chính xác hay gần đúng.

Trong hầu hết các trường hợp, mọi người tìm kiếm một sản phẩm cụ thể, một đơn hàng, một người nhân viên hay một khách hàng, họ cần một phép toán chính xác. Khi tìm kiếm một giá trị duy nhất, FALSE cần nhập vào tham số range_lookup

Tham số này tùy chọn, nhưng nếu để trống nó, giá trị TRUE sẽ được mặc định. Giá trị TRUE dựa trên dữ liệu đã được sắp xếp theo thứ tự tăng dần.

Hình dưới đây minh họa hàm VLOOKUP với range_lookup được loại bỏ, và kết quả không chính xác bị trả lại.

Giải pháp: Nếu bạn tìm một giá trị duy nhất, nhập FALSE vào tham số cuối. Hàm VLOOKUP trên sẽ được nhập thành  =VLOOKUP(H3,B3:F11,2,FALSE).

Xem thêm: Hàm tìm kiếm VLOOKUP và HLOOKUP trong Excel

Cố định các tham số trong bảng:

Có thể bạn muốn lồng ghép nhiều hàm VLOOKUP với nhau để tìm nhiều thông tin khác nhau. Nếu sao chép hàm VLOOKUP sang nhiều ô tính, bạn cần cố định các tham số.

Hình dưới đây sẽ ví dụ trường hợp hàm VLOOKUP bị nhập sai. Vùng dữ liệu nhập sai thể hiện trong tham số lookup_value và table_array.

Giải pháp. Bảng dữ liệu tra cứu được nhập vào tham số table_array, bảng này cần được cố định để thuận tiện cho việc sao chép hàm VLOOKUP.

Chọn phần tham số trong công thức, nhấn F4 để cố định chúng. Công thức sẽ được nhấp vào như thế này: =VLOOKUP($H$3,$B$3:$F$11,4,FALSE).

Trong ví dụ này, cả hai giá trị lookup_value và table_array đều cần cố định lại, tuy nhiên, thông thường, bạn chỉ cần cố định giá trị table_array.

Chèn thêm một cột vào bảng biểu:

Địa chỉ của cột, hay hoặc col_index_num trong hàm VLOOKUP thường linh động. Nếu chèn thêm cột vào bảng, cột mới sẽ khiến hàm VLOOKUP ra kết quả sai lệch.

Ví dụ, cột “Quantity” là cột thứ ba trong bảng, nhưng sau khi chèn cột mới, nó trở thành cột thứ tư. Tuy nhiên, hàm VLOOKUP chưa được cập nhật khiến kết quả nhận được bị sai lệch.

Giải pháp 1: Một giải pháp có thể bảo vệ trang tính của bạn là không cho phép người dùng chèn thêm cột mới. Nếu người dùng cố thực hiện điều này thì không thể áp dụng giải pháp 1.

Giải pháp 2: Một giải pháp khác đó là lồng hàm MATCH vào tham số col_index_num trong hàm VLOOKUP.

Hàm MATCH có thể sử dụng để tìm kiếm và chọn đúng cột cần thiết hỗ trợ cho hàm VLOOKUP. Điều này sẽ giúp cho chỉ số col_index_num luôn đúng, nghĩa là dù chèn thêm cột vào bảng tính cũng sẽ không ảnh hưởng tới kết quả của hàm VLOOKUP.

Công thức dưới đây sẽ minh họa cho giải pháp trên. 

Xem thêm: Hàm vlookup 2 điều kiện trong Excel và các ứng dụng thường gặp

Bảng biểu của bạn được mở rộng:

Khi chèn thêm nhiều hàng vào bảng, bạn cần cập nhật hàm VLOOKUP để đảm bảo những hàng mới cũng được thêm vào các giá trị cần do tìm. Hình dưới đây minh họa hàm VLOOKUP không dò tìm hết các giá trị trên toàn bộ bảng.

Giải pháp: Để định dạng vùng dữ liệu của bảng biểu, chọn vùng dữ liệu bạn sẽ thêm vào tham số table_array, chọn Home > Format as Table rồi chọn một kiểu định dạng. Nhấp chọn thẻ Design dưới thẻ Table Tools và thay đổi tên bảng.

Hàm VLOOKUP dưới đây cho thấy bảng biểu Fruitlist được sử dụng:

Hàm VLOOKUP không thể dò tìm các giá trị bên trái

Một hạn chế của hàm VLOOKUP là nó không thể dò tìm các giá trị bên trái nhìn sang trái của nó. Nó sẽ nhìn xuống cột bên trái của bảng và trả về thông tin từ bên phải.

Giải pháp: Sử dụng kết hợp các hàm INDEX và MATCH của Excel là một giải pháp linh hoạt thay cho hàm VLOOKUP. Ví dụ dưới đây cho thấy nó được sử dụng để trả lại thông tin ở bên trái của cột mà bạn đang tìm kiếm.

 

Xem ngay: Hàm VLOOKUP nâng cao – VLOOKUP nhiều cột, nhiều điều kiện

Bảng biểu của bạn chứa những giá trị trùng

Hàm VLOOKUP chỉ cho ra kết quả của một giá trị là giá trị đầu tiên mà nó tìm thấy.

Nếu bảng biểu của bạn chứa nhiều giá trị trùng nhau, hàm VLOOKUP không thể cho ra kết quả.

Giải pháp 1. Nếu bạn không muốn loại bỏ những giá trị trùng lặp, bạn có thể chọn bảng tính và nhấp vào Remove Duplicates trong thẻ Data.

Giải pháp 2. Đối với trường hợp này, thay vì sử dụng hàm VLOOKUP, hãy sử dụng PivotTable để lựa chọn một giá trị và đưa ra danh sách kết quả.

Bảng dưới đây là danh sách các đơn đặt hàng. Giả sử bạn muốn tìm kiếm đơn đặt hàng cho cùng một loại trái cây.

Bảng PivotTable được sử dụng để giúp người dùng chọn một loại Fruit ID từ danh sách lọc và danh sách tổng hợp các đơn hàng.

Nguồn: Ablebits, dịch và biên tập bởi Hocexcel Online.

Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel:

Một số hàm cơ bản thường gặp như:

• SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
• COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
• Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…
• Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…

Một số công cụ hay sử dụng như:

• Định dạng theo điều kiện với Conditional formatting
• Thiết lập điều kiện nhập dữ liệu với Data Validation
• Cách đặt Name và sử dụng Name trong công thức
• Lập báo cáo với Pivot Table…

Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online