Khi sử dụng hàm dò tìm Vlookup nếu người dùng không chú ý sẽ dễ gặp một sổ lỗi cơ bản như lỗi #N/A, #VALUE… Chính vì vậy, hocexcelcoban.com sẽ tổng hợp và hướng dẫn bạn các lỗi thường gặp khi sử dụng hàm Vlookup bị lỗi cũng như cách sửa lỗi
NHẮC LẠI KIẾN THỨC VỀ HÀM VLOOKUP
Hàm VLOOKUP (Tra cứu dọc) tìm kiếm một giá trị trong cột ngoài cùng bên trái của bảng và sau đó trả về một giá trị trong cùng một hàng từ một cột khác mà bạn chỉ định.
Cú pháp:
=VLOOKUP(Lookup_value, Table, Col_index_num, [Range_lookup])
- Lookup_value – Giá trị cần tìm trong cột đầu tiên của bảng.
- Table – Bảng để truy xuất một giá trị.
- Col_index – Cột trong bảng để truy xuất một giá trị.
- range_lookup – [tùy chọn] TRUE = đối sánh gần đúng (mặc định). FALSE = đối sánh chính xác
CÁC LỖI PHỔ BIẾN THƯỜNG GẶP KHI SỬ DỤNG HÀM VLOOKUP
1. HÀM VLOOKUP BỊ LỖI #N/A
Khi hàm VLOOKUP không thể tìm thấy giá trị trong bảng tra cứu, hàm này sẽ trả về lỗi #N/A.
– Bạn có thể xem thêm về Hàm Vlookup nâng cao tại đây: Học excel cơ bản
1.1 LỖI #N/A KHÔNG CÓ TRONG BẢNG TÌM KIẾM
Khi giá trị chúng ta đang tìm kiếm không có trong bảng tìm kiếm thì lỗi #N/A cũng có thể xảy ra.
Hình 1: Hàm vlookup lỗi #N/A
1.2 SỬ DỤNG VLOOKUP ĐỂ DÒ TÌM GẦN ĐÚNG
Hình 2: Hàm vlookup bị lỗi #N/A TRUE
Nếu tham số range_lookup chúng ta bỏ qua hoặc để là TRUE thì loại VLOOKUP đang được sử dụng là loại dùng để dò tìm gần đúng. Trong trường hợp này, lỗi #N/A phát sinh khi:
- Giá trị cần tìm kiế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ự nhỏ đến lớn
1.3 KHI TRA CỨU CỘT TÌM KIẾM KHÔNG Ở VỊ TRÍ ĐẦU TIÊN
Vị trí cột rất quan trọng ở đây:
Hình 3: Lỗi #N/A khi sử dụng vlookup
Trong trường hợp bạn không thể thay đổi vị trí các cột trong dữ liệu, bạn nên sử dụng hàm INDEX và MATCH sẽ tốt hơn sử dụng hàm VLOOKUP và giúp bạn xử lý trường hợp này.
1.4 SỐ NHƯNG ĐỊNH DẠNG KIỂU CHỮ:
Ví dụ như hình vẽ sau đây:
Hình 4: Số định dạng kiểu chữ
Để giải quyết lỗi này, chúng ta sẽ làm như sau: Chọn toàn bộ những ô bị lỗi này, bấm phím tắt CTRL + 1 hộp thoại Number Format sẽ được hiện ra, bấm chọn thẻ Number > Number rồi bấm OK. Hoặc làm như hình vẽ bên dưới:
1.5 SỬA LỖI CHÍNH TẢ TRONG GIÁ TRỊ ĐƯỢC TÌM KIẾM (LOOKUP VALUE)
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, nguồn từ Web, nguồn được gõ tay lại từ 1 tài liệu đã in ra . Đây là nơi mà dữ liệu phát sinh lỗi, phát sinh những kí tự lạ. Nếu chúng ta rà soát lại lỗi chính tả, làm sạch dữ liệu xong, thì nhiều khả năng lỗi #NA này sẽ biến mất.
2.XỨ LÝ LỖI #VALUE KHI SỬ DỤNG VLOOKUP:
Một vài nguyên nhân chính sau đây:
2.1 GIÁ TRỊ TRA CỨU BẰNG VLOOKUP CÓ ĐỘ DÀI LỚN HƠN 255 KÍ TỰ
Kích thước tối đa của giá trị tra cứu VLOOKUP là 255 ký tự. Thay vào đó chúng ta có thể giải quyết bằng kết hợp INDEX với MATCH
2.2 THAM CHIẾU KHÔNG CHÍNH XÁC
Nếu bạn sử dụng VLOOKUP để tra cứu dữ liệu trong 1 file Excel khác và đường dẫn đến file Excel này không hoạt động, có thể do file excel không còn được lưu ở đó nữa hoặc vì lý do quyền truy cập.
2.3 CỘT THAM SỐ LẤY DỮ LIỆU VỀ NHỎ HƠN 1
Bình thường, cú pháp của VLOOKUP như sau:
=VLOOKUP(Lookup_value, Table, Col_index_num, [Range_lookup])
Nếu tham số Col_index_num có giá trị nhỏ hơn 1, bạn sẽ gặp lỗi #VALUE khi sử dụng VLOOKUP
3. Lỗi #NAME khi sử dụng VLOOKUP:
3.1 HÀM VLOOKUP BỊ LỖI SAI CHÍNH TẢ
Nếu hàm VLOOKUP bị lỗi của bạn không phải là #VALUE hoặc #N/A, thì đó có thể là lỗi #NAME. Đừng hoảng hốt! Đây là lỗi VLOOKUP dễ nhất để khắc phục.
Hình 5: Hàm vlookup bị lỗi #NAME
Một lỗi #NAME xuất hiện khi bạn viế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ự đánh vần chính xác VLOOKUP không.
3.2 TRONG QUÁ TRÌNH COPY CÔNG THỨC THAM CHIẾU BỊ THAY ĐỔI
Khi bạn copy hay di chuyển công thức, nếu bạn gặp phải lỗi #NAME, hãy nghĩ xem công thức của bạn đã có những kí tự $ để khoá tham chiếu hay chưa.
VD: Thay vì viết V1:L6, bạn cần viết là $V$1:$L$6
3.3 CỘT ĐƯỢC THÊM HOẶC BỚT TRONG BẢNG TRA CỨU
Vấn đề khi xử dụng VLOOKUP mà chúng ta rất hay gặp đó là: thay đổi cấu trúc bảng tính: thêm và bớt cột, khi thêm và bớt cột như vậy, VLOOKUP có tham số Col_index không được cập nhật theo, gây nên lỗi #NAME.
Giải pháp cho trường hợp này: Sử dụng hàm INDEX và MATCH
4. Lỗi #REF! khi sử dụng VLOOKUP:
Nguyên nhân của lỗi #REF! :
- Đối số col_index_num được cung cấp lớn hơn số trong cột table_array được cung cấp.
- Công thức cố gắng tham chiếu các ô không tồn tại. Điều này có thể do lỗi tham chiếu khi Vlookup được sao chép vào các ô khác.
Hình 6: Lỗi #REF! khi sử dụng VLOOKUP
5. SỬ DỤNG HÀM IFERROR HOẶC ISERROR ĐỂ XỬ LÝ LỖI:
5.1 SỬ DỤNG VLOOKUP KẾT HỢP VỚI IFERROR, IFNA
Khi hàm VLOOKUP không thể tìm thấy giá trị trong bảng tra cứu, hàm này sẽ trả về lỗi #N/A… Hàm IFERROR cho phép bạn bắt lỗi và trả về giá trị tùy chỉnh của riêng bạn khi có lỗi.
Cú pháp:
=IFERROR(VLOOKUP(value, Table, Col_index, [Range_lookup]), < thông báo lỗi >)
HÌnh 7: Xử lý lỗi VLOOKUP kết hợp IFERROR
Nếu bạn có giá trị tra cứu trong ô H2 và giá trị tra cứu trong một dải ô có tên bảng và bạn muốn ô trống nếu không tìm thấy bảng tra cứu nào, bạn có thể sử dụng:
= IFERROR ( VLOOKUP ( H2 , B2:E9, 4 , FALSE ), "" )
Nếu bạn muốn trả lại thông báo “Not found” khi không tìm thấy kết quả phù hợp nào, hãy sử dụng
= IFERROR ( VLOOKUP ( H2 , B2:E9, 4 , FALSE ), "Not found" )
Tương tự IFNA
Trong Excel 2013, hàm IFNA có sẵn để xử lý các lỗi #N/A cụ thể. Cú pháp sử dụng giống như với IFERROR:
= IFERROR ( VLOOKUP ( H2 , B2:E9, 4 , FALSE ), "Not found" ) = IFNA ( VLOOKUP ( H2 , B2:E9, 4 , FALSE ), "Not found" )
5.2 SỬ DỤNG VLOOKUP KẾT HỢP VỚI ISERROR
Vì IFERROR chỉ được giới thiệu kể từ Excel 2007, nếu các bạn vẫn cần xử lý và làm việc với những file Excel cũ hơn, thì hàm ISERROR sẽ giúp các bạn xử lý lỗi của VLOOKUP. Cú pháp sử dụng như sau:
=IF( ISERROR ( VLOOKUP(…), thông báo lỗi ), VLOOKUP (…) )
KẾT LUẬN
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Quan trọng bài học này chúng tôi mong bạn nắm được các lỗi thường gặp khi sử dụng hàm Vlookup bị lỗi cũng như cách sửa lỗi giúp bạn sử dụng Excel một cách chuyên nghiệp hơn.
GÓC HỌC TẬP
- Hỗ trợ giải đáp: tham gia fanpage https://www.facebook.com/HocExcelCoBa… và nhóm thảo luận: https://www.facebook.com/groups/excel…
- 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: hocexcelcoban
- Xem toàn bộ bài giảng Học Excel tại đây:https://goo.gl/IqvnjK
THAM KHẢO THÊM
>> Hàm VLOOKUP kết hợp hàm IF >> Hàm VLOOKUP trong Excel >> Hàm VLOOKUP 2 điều kiện >> Hàm VLOOKUP kết hợp hàm SUMIF