Khắc phục lỗi #N/A khi dùng VLOOKUP hoặc HLOOKUP do dữ liệu trùng lặp

Chào mọi người,

Dạo gần đây mình hay gặp một vấn đề khá khó chịu khi sử dụng hàm VLOOKUP và HLOOKUP, đó là lỗi #N/A. Sau khi debug một hồi thì mình phát hiện ra nguyên nhân không phải do sai vùng tìm kiếm hay sai cột trả về, mà là do dữ liệu trong cột đầu tiên của bảng tra cứu (hoặc hàng đầu tiên đối với HLOOKUP) có chứa các giá trị trùng lặp.

Khi có nhiều hơn một giá trị trùng khớp, Excel sẽ chỉ trả về kết quả của lần trùng khớp đầu tiên mà nó tìm thấy. Nếu giá trị bạn đang tìm kiếm không nằm trong các giá trị trùng khớp đầu tiên đó, thì bạn sẽ nhận được lỗi #N/A.

Ví dụ:

Giả sử bạn có bảng dữ liệu với cột Mã Sản Phẩm có các giá trị bị lặp:

Mã SP | Tên SP
-------|-------
A001 | Bàn
A002 | Ghế
A001 | Tủ
B003 | Giường

Nếu bạn dùng VLOOKUP("A001", ... ), Excel sẽ chỉ trả về "Bàn" (của dòng đầu tiên có A001) chứ không phải "Tủ".

Cách khắc phục:

1. Kiểm tra và loại bỏ dữ liệu trùng lặp: Đây là cách tốt nhất nếu có thể. Bạn có thể dùng tính năng Remove Duplicates của Excel hoặc dùng các hàm như COUNTIF để xác định và xử lý các dòng trùng lặp.

2. Sử dụng kết hợp với hàm khác để tạo khóa duy nhất: Nếu không thể loại bỏ trùng lặp, bạn có thể tạo một cột phụ chứa giá trị duy nhất bằng cách ghép nối cột cần tra cứu với một số thứ tự hoặc một ký tự đặc biệt. Ví dụ, nếu cột A là Mã SP, bạn có thể tạo cột B với công thức =A2&COUNTIF($A$2:A2,A2) rồi kéo xuống. Sau đó, dùng VLOOKUP với cột này.

3. Sử dụng FILTER (Excel 365): Nếu bạn dùng phiên bản Excel mới, hàm FILTER sẽ giúp bạn lấy tất cả các kết quả phù hợp thay vì chỉ kết quả đầu tiên.

Hy vọng chia sẻ này giúp ích cho các bạn khi làm việc với Excel!

Tình huống này đúng là khá phổ biến và dễ gây lúng túng thật. Mình cũng đã từng phải "vật lộn" với lỗi #N/A do dữ liệu trùng lặp khi dùng VLOOKUP/HLOOKUP.

Ngoài các giải pháp bạn và bạn @[Tên người dùng trước] đã chia sẻ, mình còn có một cách nữa muốn thử nghiệm, đó là kết hợp VLOOKUP với hàm IFERROR để xử lý các trường hợp không tìm thấy. Tuy nhiên, cách này chỉ giúp "ẩn" lỗi #N/A đi chứ không giải quyết triệt để vấn đề trùng lặp.

Liệu có cách nào để tự động xác định và đánh dấu các dòng bị trùng lặp trong bảng tra cứu không nhỉ? Mình nghĩ sẽ hữu ích cho việc làm sạch dữ liệu trước khi tra cứu.

Đúng là lỗi #N/A do dữ liệu trùng lặp với VLOOKUP/HLOOKUP gây đau đầu thật! Mình cũng từng gặp tình huống này rồi.

Để khắc phục, ngoài việc thủ công đi xóa bớt dữ liệu trùng, mình thường dùng kết hợp INDEX và MATCH. Hàm này linh hoạt hơn VLOOKUP/HLOOKUP ở chỗ nó cho phép mình chỉ định rõ cột/hàng nào sẽ tra cứu và cột/hàng nào sẽ trả về, nên việc xử lý dữ liệu trùng cũng dễ hơn.

Hoặc, nếu bạn muốn giữ nguyên VLOOKUP/HLOOKUP, có thể thử thêm một cột phụ trước bảng tra cứu để tạo ra mã duy nhất cho từng dòng, ví dụ: =A2&"_"&COUNTIF($A$2:A2,A2). Sau đó dùng VLOOKUP/HLOOKUP tra theo mã duy nhất này.

Bạn đã thử cách nào chưa? Chia sẻ thêm kinh nghiệm của bạn nhé!

Đúng là lỗi #N/A với VLOOKUP/HLOOKUP do dữ liệu trùng lặp gây khó chịu thật. Mình cũng hay gặp trường hợp này.

Ngoài việc dùng INDEX-MATCH như bạn đã chia sẻ, mình còn hay dùng thêm một cách nữa khi cần lấy giá trị duy nhất theo điều kiện. Nếu bạn muốn lấy giá trị đầu tiên hoặc cuối cùng trong nhóm trùng lặp, có thể thử kết hợp VLOOKUP với MAX(IF(...)) hoặc MIN(IF(...)) trong một mảng (nhấn Ctrl+Shift+Enter nếu không dùng Office 365).

Ví dụ, để lấy giá trị đầu tiên của mã A trong cột B, bạn có thể dùng: =VLOOKUP(A2,IF({1,0},B:B,C:C),2,FALSE).

Bạn có hay áp dụng các hàm mảng cho trường hợp này không?