Khắc phục lỗi #N/A khi dùng VLOOKUP và INDEX/MATCH với điều kiện phụ

Chào mọi người,

Mình thường xuyên dùng VLOOKUP và INDEX/MATCH để tra cứu dữ liệu. Tuy nhiên, gần đây mình gặp một tình huống khá khó chịu: khi tìm kiếm một giá trị mà không có trong bảng dữ liệu, VLOOKUP trả về #N/A, còn INDEX/MATCH thì báo lỗi tương tự. Điều này làm ảnh hưởng đến các phép tính khác hoặc làm báo cáo trông không chuyên nghiệp.

Thông thường, mọi người hay dùng hàm IFNA hoặc IFERROR để xử lý, ví dụ:

=IFNA(VLOOKUP(A1, B:C, 2, FALSE), "Không tìm thấy")

Hoặc:

=IFERROR(INDEX(C:C, MATCH(A1, B:B, 0)), "Không tìm thấy")

Nhưng hôm nay, mình muốn chia sẻ một cách khác, đặc biệt hữu ích khi bạn cần kết hợp tra cứu với một điều kiện phụ nữa.

Giả sử bạn muốn tìm giá của một mặt hàng (ở cột B) dựa trên tên mặt hàng (ở cột A), nhưng chỉ khi mặt hàng đó thuộc một danh mục cụ thể (ví dụ, ở cột D).

Cách làm truyền thống sẽ khá phức tạp, có thể phải dùng thêm hàm SUMIFS hoặc AGGREGATE. Tuy nhiên, với sự trợ giúp của hàm XLOOKUP (nếu bạn dùng Excel 365 hoặc các phiên bản mới), việc này trở nên đơn giản hơn nhiều.

Nếu bạn vẫn đang dùng các phiên bản cũ hơn, có thể kết hợp INDEX và AGGREGATE như sau:

=INDEX(C:C, AGGREGATE(15, 6, ROW(B:B)-ROW(B$1)+1/( (B:B=A1)*(D:D="Điện tử") ), 1))

Trong đó:

• C:C là cột chứa giá trị bạn muốn trả về.
• A1 là tên mặt hàng cần tìm.
• B:B là cột chứa tên mặt hàng.
• D:D="Điện tử" là điều kiện phụ (chỉ tìm mặt hàng thuộc danh mục "Điện tử").
• AGGREGATE(15, 6, ..., 1) sẽ trả về số thứ tự dòng của kết quả đầu tiên thỏa mãn điều kiện, bỏ qua lỗi.

Cách này tuy hơi dài nhưng giúp bạn xử lý được nhiều trường hợp phức tạp mà không cần dùng đến IFNA hay IFERROR cho từng ô.

Không biết mọi người còn cách nào hay hơn để xử lý lỗi #N/A trong các trường hợp có điều kiện không? Cùng thảo luận nhé!

Chào bạn,

Mình cũng hay gặp trường hợp này lắm. Đúng là lỗi #N/A làm phiền thật. Ngoài cách dùng IFNA hay IFERROR như bạn chia sẻ, mình thấy còn một cách khác cũng khá hay, đó là kết hợp ISNA với IF.

Ví dụ với VLOOKUP:

=IF(ISNA(VLOOKUP(A1, B:C, 2, FALSE)), "Không tìm thấy", VLOOKUP(A1, B:C, 2, FALSE))

Ưu điểm của cách này là nó chỉ thực hiện VLOOKUP một lần. Bạn đã thử cách này chưa, thấy nó có hiệu quả hơn không?

Chào bạn,

Rất đồng ý với bạn về việc lỗi #N/A này gây khó chịu thật. Cách dùng IF(ISNA(...), ..., ...) của bạn rất hay đó, nó tránh được việc gọi hàm tra cứu hai lần, vừa gọn gàng lại vừa hiệu quả.

Mình cũng có một cách khác muốn chia sẻ, đặc biệt khi bạn cần tra cứu với nhiều điều kiện phụ. Thay vì lồng nhiều IF hoặc AND vào MATCH, mình thường dùng SUMIFS hoặc AVERAGEIFS (nếu muốn lấy giá trị trung bình) kết hợp với IFERROR.

Ví dụ, nếu bạn muốn lấy giá trị từ cột C dựa vào điều kiện ở cột A và cột B:

``excel =IFERROR(SUMIFS(C:C, A:A, ValueA, B:B, ValueB), "Không tìm thấy") ``

Cách này xử lý được nhiều điều kiện cùng lúc mà vẫn gọn gàng. Bạn thấy sao về cách này?