Mẹo xử lý lỗi #N/A khi dò tìm với INDEX-MATCH

Chào các bạn trong chuyên mục Hàm và công thức Excel!

Mình thường xuyên sử dụng cặp đôi INDEX và MATCH để thay thế VLOOKUP vì nó linh hoạt hơn. Tuy nhiên, đôi khi gặp phải lỗi #N/A khi giá trị dò tìm không có trong bảng. Hôm nay, mình muốn chia sẻ một mẹo nhỏ để xử lý tình huống này một cách gọn gàng mà không cần dùng đến IFERROR lồng vào.

Tình huống của mình là thế này:

• Cần lấy thông tin từ một bảng dữ liệu lớn.
• Sử dụng INDEX(Vùng_kết_quả, MATCH(Giá_trị_tìm, Vùng_tìm, 0)) để lấy dữ liệu.
• Nếu Giá_trị_tìm không tồn tại trong Vùng_tìm, công thức trả về #N/A.

Thay vì viết:

=IFERROR(INDEX(Vùng_kết_quả, MATCH(Giá_trị_tìm, Vùng_tìm, 0)), "Không tìm thấy")

Mình đã tìm ra cách kết hợp MATCH với một phép so sánh để trả về một giá trị mặc định nếu không tìm thấy. Cụ thể, mình dùng thêm hàm AGGREGATE.

Công thức sẽ trông như thế này:

=INDEX(Vùng_kết_quả, AGGREGATE(15, 6, ROW(Vùng_tìm)-ROW(Vùng_tìm[0])+1/(Vùng_tìm=Giá_trị_tìm), 1))

Trong đó:

• AGGREGATE(15, 6, ..., 1): Hàm AGGREGATE với tùy chọn 15 (hàm SMALL) và tùy chọn 6 (bỏ qua lỗi). Nó sẽ tìm vị trí nhỏ nhất (ở đây là vị trí đầu tiên tìm thấy).
• ROW(Vùng_tìm)-ROW(Vùng_tìm[0])+1: Tạo ra một mảng các số thứ tự tương ứng với các hàng trong Vùng_tìm.
• /(Vùng_tìm=Giá_trị_tìm): Đây là phần quan trọng. Nếu Vùng_tìm=Giá_trị_tìm là TRUE (tìm thấy), phép chia này sẽ cho ra 1 (vì 1/TRUE = 1). Nếu là FALSE (không tìm thấy), nó sẽ ra lỗi #DIV/0!.

Khi AGGREGATE kết hợp với tùy chọn bỏ qua lỗi (số 6), nó sẽ bỏ qua tất cả các lỗi #DIV/0! và chỉ trả về vị trí của giá trị tìm thấy. Nếu không tìm thấy, AGGREGATE sẽ trả về lỗi, và lúc này ta có thể dùng IFERROR bên ngoài để xử lý lỗi đó (hoặc để trống nếu muốn).

Ví dụ:

Giả sử bạn có:

• Dữ liệu sản phẩm ở A2:A10 (Tên sản phẩm)
• Mã sản phẩm ở B2:B10
• Bạn muốn tìm tên sản phẩm dựa vào mã.

Công thức:

=INDEX($A$2:$A$10, AGGREGATE(15, 6, ROW($B$2:$B$10)-ROW($B$2)+1/($B$2:$B$10=