G-LEARNING
G-LEARNING
Thảo luận 0 thảo luận
Vỗ tay 0 vỗ tay
Lượt xem 336 lượt xem

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

May 21 2017

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. hay lỗi #REF

Cú pháp của hàm Vlookup

=VLOOKUP(Lookup_value, Table_array, Col_index_ num, [Range_lookup])

  • Lookup_value(*): Giá trị  cần dò 
  • Table_array(*): Bảng cần dò tìm
  • Col_index_ num(*): số thứ tự cột cần trả kết quả
  • [Range_lookup] : Với 0 là do tìm chính xác và 1 là dò tìm tương đối.

Lưu ý khi sử dụng 

  • * Là những giá trị bắt buộc phải đưa vào trong hàm
  • Giá trị dò tìm phải thuộc cột đầu tiên trong bảng dò tìm nếu không có sẽ dẫn đến lỗi #NA
  • Số thứ tự cột cần trả ra kết quả phải <= chiều rộng của bàng dữ liệu nếu không ra lỗi #REF

    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.

    Hàm Vlookup

    Hàm Vlookup

    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êmHà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 table_array.

    Cố định tham số trong bảng

    Cố định tham số trong bảng

    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 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.

    Cho thêm cột

    Cho thêm cột

    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êmHàm vlookup trong Excel và các ứng dụng nâng cao 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.

    Mở rộng bảng

    Mở rộng 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 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 ngayHà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.

    Table with duplicated rows

    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 Gitiho.com.

    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 Gitiho.com. Đâ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: Gitiho.com

    Cảm ơn các bạn đã theo dõi bài viết nếu thấy hay và hữu ích hãy chia sẻ cho nhiều người biết và đừng quên coment ý kiến của mình để đội ngũ phát triển Gitiho đưa ra các bài viết chất lượng hơn nữa.


    Đánh giá bài viết này

    Tài liệu kèm theo bài viết

    Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Cơ Bản

    Thảo luận 0 câu trả lời
    Lượt xem 336 lượt xem
    Vỗ tay vỗ tay

    0 Bình luận

    @ 2020 - Bản quyền của Công ty cổ phần công nghệ giáo dục Gitiho Việt Nam
    Giấy chứng nhận Đăng ký doanh nghiệp số: 0109077145, cấp bởi Sở kế hoạch và đầu tư TP. Hà Nội