Tại sao hàm VLOOKUP của tôi bị lỗi?
VLOOKUP là cách phổ biến nhất để trích xuất dữ liệu giữa các trang tính trong Excel. Tuy nhiên, không gì làm giảm năng suất nhanh hơn việc thấy một cột đầy lỗi #N/A trong khi bạn biết chắc chắn dữ liệu đang nằm ngay trước mắt.
#N/A - Value is not available to the formula or function (VLOOKUP)
Nói một cách đơn giản, lỗi này có nghĩa là Excel đã tìm trong danh sách nguồn nhưng không thấy kết quả khớp. Vấn đề hiếm khi nằm ở bản thân công thức. Thay vào đó, nó thường do những mâu thuẫn nhỏ trong dữ liệu mà mắt người thường bỏ qua nhưng Excel thì không thể bỏ qua.
Các "thủ phạm" thường gặp
Excel làm việc cực kỳ chính xác theo từng ký tự. Nếu chỉ cần thừa một khoảng trắng hoặc sai lệch định dạng, việc tìm kiếm sẽ thất bại. Dưới đây là những lý do phổ biến nhất khiến bạn gặp lỗi này:
- Nhầm lẫn chế độ tìm kiếm: Bạn đã không yêu cầu Excel tìm kiếm chính xác và nó bị nhầm lẫn bởi dữ liệu chưa được sắp xếp.
- Số so với Văn bản: Bạn đang tìm mã sản phẩm
101(dạng số), nhưng danh sách nguồn lại lưu trữ nó dưới dạng văn bản ('101). - Khoảng trắng ẩn: Một khoảng trắng thừa ở cuối chữ "iPhone " sẽ không bao giờ khớp với "iPhone".
- Tìm sai chỗ: Giá trị tìm kiếm của bạn không nằm ở cột đầu tiên của vùng dữ liệu đã chọn.
Giải pháp 1: Ép buộc tìm kiếm chính xác
Hầu hết người dùng không nhận ra rằng nếu bạn bỏ trống đối số cuối cùng của hàm VLOOKUP, Excel sẽ mặc định là tìm kiếm "Tương đối" (Approximate). Điều này gần như không bao giờ là thứ bạn muốn khi tìm mã ID hoặc tên.
Hãy sửa công thức bằng cách thêm 0 hoặc FALSE vào cuối. Thay đổi từ:
=VLOOKUP(A2, D2:E100, 2)
Thành thế này:
=VLOOKUP(A2, D2:E100, 2, FALSE)
Thay đổi đơn giản này buộc Excel phải tìm kết quả khớp 100% trước khi trả về giá trị. Đây là cách nhanh nhất để ổn định các công thức của bạn.
Giải pháp 2: Đồng bộ kiểu dữ liệu
Excel coi số 500 và chuỗi văn bản "500" là hai thực thể khác nhau. Nếu bạn thấy một hình tam giác màu xanh lá cây nhỏ ở góc ô, Excel đang coi các con số của bạn là văn bản.
Mẹo "Text to Columns"
Cách nhanh nhất để sửa cả một cột số đang bị lưu dưới dạng văn bản là sử dụng thẻ Data. Chọn dữ liệu của bạn, nhấp vào Text to Columns và nhấn Finish ngay lập tức. Excel sẽ ngay lập tức chuyển đổi các ô đó thành số thực.
Mẹo xử lý bằng công thức
Nếu bạn không thể thay đổi dữ liệu nguồn, bạn có thể sử dụng mẹo trong công thức. Để chuyển một số thành văn bản ngay lập tức, hãy thêm một chuỗi trống vào giá trị tìm kiếm:
=VLOOKUP(A2 & "", D2:E100, 2, FALSE)
Giải pháp 3: Loại bỏ khoảng trắng ẩn
Dữ liệu được nhập từ các công cụ web hoặc CRM thường đi kèm với các khoảng trắng thừa ở cuối. Nếu giá trị tìm kiếm là "Samsung" nhưng trong bảng nguồn lại là "Samsung ", hàm sẽ không tìm thấy.
Hàm TRIM là cứu cánh của bạn ở đây. Nó tự động loại bỏ tất cả những khoảng trắng thừa gây khó chịu đó:
=VLOOKUP(TRIM(A2), D2:E100, 2, FALSE)
Nếu dữ liệu rác nằm trong chính bảng nguồn, hãy sử dụng =TRIM() trong một cột phụ để làm sạch trước. Điều này đảm bảo danh sách chính của bạn luôn chuẩn xác.
Giải pháp 4: Giúp thông báo lỗi chuyên nghiệp hơn
Đôi khi một giá trị thực sự bị thiếu, và điều đó không sao cả. Nhưng lỗi #N/A trông rất mất thẩm mỹ trên một báo cáo chuyên nghiệp. Hãy sử dụng IFNA để thay thế lỗi bằng thứ gì đó dễ đọc hơn, như dấu gạch ngang hoặc "Không tìm thấy".
=IFNA(VLOOKUP(A2, D2:E100, 2, FALSE), "-")
Điều này giúp bảng tính của bạn trông chuyên nghiệp ngay cả khi thiếu dữ liệu. Nó cũng ngăn chặn lỗi làm hỏng các công thức tính toán khác ở phía sau.
Giải pháp 5: Nâng cấp lên hàm XLOOKUP
Bạn đang sử dụng Microsoft 365? Nếu đúng vậy, hãy từ bỏ hoàn toàn VLOOKUP. XLOOKUP dễ viết hơn và mạnh mẽ hơn nhiều. Nó mặc định là tìm kiếm chính xác và xử lý lỗi mà không cần thêm các hàm bổ trợ.
=XLOOKUP(A2, D2:D100, E2:E100, "Không tìm thấy")
Nó nhanh hơn, an toàn hơn và sẽ không bị hỏng nếu sau này bạn thêm cột mới vào bảng. Hầu hết các chuyên gia Excel hiện nay đã chuyển sang sử dụng hàm này.
Kiểm tra lại công việc
Vẫn chưa được? Hãy thử ba bước kiểm tra nhanh sau:
- Chạy thử nghiệm so khớp: Trong một ô trống, nhập
=A2=D2để so sánh giá trị tìm kiếm với một ô trong danh sách nguồn. Nếu kết quả làFALSE, bạn đang gặp vấn đề về định dạng ẩn. - Xem cách hàm hoạt động: Sử dụng Formulas > Evaluate Formula để xem Excel tính toán từng bước. Nó sẽ cho bạn thấy chính xác vị trí lỗi xảy ra.
- Kiểm tra chế độ tính toán: Đôi khi chế độ tính toán bị đặt thành "Thủ công" (Manual). Nhấn
F9để buộc làm mới và xem lỗi có biến mất không.