VLOOKUP là một trong những công thức tra cứu phổ biến trong các hàm Excel. Và đây cũng là một trong những công cụ quan trọng nhất trong tính toán. Các lỗi cần lưu ý với hàm Vlookup là gì?
Bạn đã bao giờ cảm thấy bực mình khi nhập đúng cú pháp trong hàm VLOOKUP nhưng kết quả vẫn báo # N / A (Không khả dụng) và không có thông tin để hiển thị cụ thể? Trong bài viết này, 123job sẽ chỉ ra cho bạn những lỗi thường gặp khi sử dụng hàm VLOOKUP và cách khắc phục nhanh chóng.
I. Lỗi Định dạng Sai (Định dạng Văn bản (Kiểu chữ) Đối với Số)
Cách sửa lỗi của hàm Vlookup trong Excel
1. Cách giải quyết
Phương pháp 1: Sử dụng chức năng Văn bản trong Excel.
Phương pháp 2: Nhân tất cả các giá trị tra cứu với 1.
2. Làm thế nào để làm điều đó
Giả sử nếu cột chứa giá trị Lookup_value (Cột C) chứa giá trị được lưu trữ ở định dạng số và cột đầu tiên của lookup_array (Cột A) là văn bản. Chúng tôi làm như sau:
Cách 1: Chúng ta phải chèn một hàm Text để định dạng lại các giá trị của cột lookup_value (Cột A) thành định dạng văn bản để chúng khớp với định dạng của cột A (Định dạng văn bản)
E2 = Vlookup (Văn bản (C2,0), $ A $ 2: $ D $ 10,4,0)
Sau đó kéo công thức bằng hàm vlookup trong ô E2 xuống ô E10, khi đó chúng ta sẽ nhận được giá trị để tham chiếu. Lưu ý: Hàm Text chỉ có thể chấp nhận tham số Reference là một ô (1 ô), vì vậy chúng ta sẽ phải chuyển đổi tham số lookup_Value trong công thức gốc thành tham chiếu 1 ô (ô C2), nếu không, làm thế nào để sử dụng hàm vlookup sẽ trả về # N / A!
Cách 2: Nhân với 1 đơn vị rồi chuyển giá trị sang các cột khác.
Cũng trong trường hợp trên, thay vì chèn hàm Text trong Excel, bạn cũng có thể chuyển đổi cột chứa số ở dạng văn bản bằng cách nhân giá trị của ô đó với 1.
Ví dụ: cột A ở dạng văn bản thì ta có thể chuyển thành dạng số thành cột B như sau: B2 = A2 * 1. Sau đó kéo xuống cho đến hết ô B10. Bây giờ công thức để sử dụng hàm Vlookup trong ô E2 (ví dụ) như sau: E2 = Vlookup (B2: B10, $ B $ 2: $ D $ 10,3,0). Nhưng nếu trong một ô nào đó trong cột A chứa giá trị là một ký tự (chữ a chẳng hạn) chứ không phải là một số ở dạng văn bản, thì bạn nhân nó với 1, kết quả cũng sẽ sai. Vì vậy, tất nhiên nếu kết quả trả về trong ô E2 cũng sẽ là # N / A!
II. Lỗi của khoảng trống thừa ở cuối điều kiện tìm kiếm
Lỗi này bạn thường xảy ra khi lấy dữ liệu từ nhiều hệ thống nguồn khác nhau hoặc thông qua logic lập trình có sai sót mà bạn bỏ qua để loại bỏ các khoảng trắng.
Giải pháp: Cách sử dụng hàm vlookup đơn giản để khắc phục lỗi này là bạn nên chọn lần lượt từng giá trị: giá trị dùng làm điều kiện và giá trị tương ứng trong vùng điều kiện để loại bỏ hoàn toàn các khoảng trắng. Bạn cũng có thể sử dụng chức năng vlookup Data – Text to Columns để loại bỏ tất cả các khoảng trắng.
III. Không khóa mảng tham chiếu tìm kiếm
Thông thường, rất ít người sử dụng hàm Vlookup chỉ để tìm kiếm một vài giá trị. Nếu bạn thêm một trường vào bảng dữ liệu hoặc tìm kiếm dựa trên các giá trị khác nhau, bạn sẽ cần phải cuộn xuống các ô bên dưới để có thể sao chép công thức bằng hàm vlookup. Khi bạn nhấp đúp vào bất kỳ ô kết quả nào, bạn có thể thấy rằng chỉ một nửa số công thức sử dụng hàm vlookup hoạt động. Hãy xem ví dụ bên dưới, vì mảng không bị khóa nên các giá trị tra cứu không thể khớp với các giá trị trong mảng tra cứu, do đó gây ra lỗi # N / A.
Cách giải quyết: Sử dụng hàm vlookup trong phím F4 để khóa mảng tham chiếu trước khi bạn kéo công thức xuống để sao chép cho các ô sau này. Khi sử dụng F4, màn hình của mảng dữ liệu được chọn sẽ cố định và có dấu $ phía trước vị trí ô và số dòng, ví dụ: $ B $ 4: $ C $ 10.
IV. Xử lý lỗi NA khi sử dụng hàm VLOOKUP
1. Lỗi chính tả trong giá trị được tìm kiếm (giá trị Vlookup)
Dữ liệu của chúng ta có thể đến từ nhiều nguồn, nguồn từ các hệ thống khác như: Nguồn web, nguồn đánh máy thủ công từ một tài liệu in (phải không các bạn kiểm toán viên?). Đây là nơi xảy ra lỗi dữ liệu, sinh ra nhiều ký tự lạ. Nếu chúng ta kiểm tra lỗi chính tả, làm sạch dữ liệu thì nhiều khả năng lỗi #NA này sẽ biến mất.
2. Lỗi # N / A khi sử dụng hàm VLOOKUP để phát hiện gần đúng
Cú pháp để sử dụng hàm VLOOKUP như bạn đã biết là: = VLOOKUP (,, [ TRUE / FALSE ] )
Nếu trong tham số thứ 4 chúng ta bỏ qua hoặc để nó là TRUE, thì hãy loại bỏ hàm Vlookup đang được sử dụng bởi hàm vlookup, loại được sử dụng để phát hiện gần đúng. Trong trường hợp này, lỗi # N / A xảy ra khi:
Giá trị cần tìm nhỏ hơn giá trị nhỏ nhất trong mảng tìm kiếm
Cột tìm kiếm không được sắp xếp theo thứ tự từ nhỏ nhất đến lớn nhất
3. Lỗi # N / A khi… thực sự không phải là lỗi
Khi giá trị chúng tôi đang tìm kiếm không có trong bảng tra cứu, lỗi # N / A có thể xảy ra một lần nữa.
4. Cột tìm kiếm không nằm ở vị trí đầu tiên khi tra cứu
Lỗi này như tiêu đề đã nói, vị trí cột là quan trọng
Trong trường hợp nếu bạn không thể thay đổi vị trí trong các cột dữ liệu, hãy tham khảo tại sao hàm INDEX và MATCH lại tốt hơn dùng hàm VLOOKUP và có thể giúp bạn xử lý trường hợp này một cách tốt nhất.
5. Số nhưng định dạng phông chữ:
Để giải quyết các lỗi này chúng ta thực hiện như sau: Chọn tất cả các ô lỗi này, nhấn tổ hợp phím tắt CTRL + 1, hộp thoại Number Format hiện ra, nhấn vào thẻ Number> Number rồi nhấn OK.
6. Khoảng trắng thừa ở đầu hoặc cuối ô
Có 3 nguyên nhân chính gây ra lỗi #VALUE khi sử dụng hàm VLOOKUP
Giá trị tra cứu Vlookup dài hơn 255 ký tự Giải pháp cho trường hợp này: Sử dụng Chỉ mục kết hợp với Đối sánh
Đường dẫn đến bảng tra cứu bị sai Nếu bạn sử dụng hàm Vlookup để tra cứu dữ liệu trong tệp Excel khác và đường dẫn đến tệp Excel này không hoạt động thì có thể do tệp excel không còn được lưu ở đó nữa. Hay là vì lý do truy cập.
Tham số cột nhận dữ liệu đến giá trị nhỏ hơn 1 Thông thường, cú pháp của hàm VLOOKUP như sau:
= VLOOKUP (,, [ TRUE / FALSE ] )
Nếu giá trị nhỏ hơn 1, bạn sẽ gặp lỗi #VALUE khi sử dụng hàm VLOOKUP
V. Xử lý lỗi công thức Excel với IFERROR hoặc ISERROR
Cách xử lý lỗi công thức Excel với IFERROR
1. Sử dụng hàm VLOOKUP kết hợp với IFERROR
Cú pháp để sử dụng hàm IFERROR như sau:
= IFERROR (,)
Do đó, chúng ta có thể sử dụng hàm VLOOKUP kết hợp như sau: = IFERROR (VLOOKUP (…),)
Nếu bạn không muốn tạo ô trống khi sử dụng hàm Vlookup với giá trị lỗi, bạn có thể thay thế “” bằng các thông báo lỗi chẳng hạn như “Lỗi”.
2. Sử dụng hàm VLOOKUP với ISERROR
Vì vậy IFERROR mới chỉ được giới thiệu từ Excel 2007, vì vậy nếu bạn vẫn cần xử lý và làm việc với các tệp Excel cũ hơn thì hàm ISERROR sẽ giúp bạn xử lý lỗi của hàm Vlookup. Cú pháp sử dụng hàm như sau: = IF (ISERROR (VLOOKUP (…),), VLOOKUP (…))
TẠI VÌ. Tổng kết
Trên đây là những lỗi thường gặp khi bạn sử dụng hàm Vlookup. Hi vọng các bạn sẽ thu thập được những kiến thức bổ ích nhất khi sử dụng các hàm trong excel để việc tính toán trở nên dễ dàng hơn. Chúc bạn thành công!
Bài viết này giúp bạn nắm bắt được thông tin về các thủ thuật khi sử dụng các ứng dụng văn phòng. Để từ đó có thể sử dụng trực tiếp những thủ thuật này để đáp ứng các yêu cầu của bạn. Việc hiểu biết các thông tin trên sẽ giúp cho công việc của bạn được thực hiện dễ dàng và thuận tiện hơn. Ngoài ra, nếu bạn còn điều gì thắc mắc, hãy truy cập website: https://www.amorstay.com.vn/ để biết thêm các thông tin chi tiết hơn nhé.
留言