Vén màn bí ẩn: Cách sửa lỗi #N/A trong Excel (áp dụng VLOOKUP, HLOOKUP, MATCH)

Chào bạn, chắc hẳn khi làm việc với Excel, bạn không ít lần “giật mình” khi thấy ô kết quả hiển thị lỗi #N/A đúng không? Đây là một trong những lỗi phổ biến nhất khi chúng ta sử dụng các hàm dò tìm như VLOOKUP, HLOOKUP hay MATCH. Đừng lo lắng, bài viết này sẽ giúp bạn hiểu rõ nguyên nhân và biết cách sửa lỗi #N/A trong Excel một cách hiệu quả, đặc biệt là khi dùng các hàm này nhé!

Hiểu rõ nguyên nhân lỗi #N/A trong hàm dò tìm VLOOKUP/HLOOKUP

Theo mặc định, các hàm tra cứu thông tin trong bảng thường yêu cầu dữ liệu phải được sắp xếp theo thứ tự tăng dần để hoạt động chính xác khi bạn muốn tìm kiếm tương đối. Tuy nhiên, các hàm VLOOKUP và HLOOKUP đều có một tham số rất quan trọng là range_lookup mà không phải ai cũng để ý kỹ.

Tham số range_lookup có hai giá trị:

• TRUE (hoặc bỏ qua): Hàm sẽ tìm kiếm một giá trị khớp tương đối. Nếu không tìm thấy giá trị chính xác, nó sẽ trả về giá trị lớn nhất nhỏ hơn hoặc bằng giá trị tìm kiếm. Để kết quả đúng, bảng dữ liệu của bạn phải được sắp xếp theo thứ tự tăng dần.
• FALSE: Hàm sẽ tìm kiếm một giá trị khớp chính xác.

Nếu bạn đang muốn tìm kiếm một giá trị chính xác mà lại vô tình để range_lookup là TRUE (hoặc không điền gì), trong khi bảng dữ liệu của bạn lại không được sắp xếp, thì rất dễ xảy ra lỗi #N/A hoặc thậm chí là trả về kết quả sai hoàn toàn.

Hãy xem ví dụ dưới đây để hình dung rõ hơn:

Alt: Bảng dữ liệu mẫu về trái cây và giá tiền, hiển thị lỗi #N/A cho “Chuối” và giá sai cho “Lê” khi dùng hàm VLOOKUP với tham số range_lookup là TRUE trên bảng chưa sắp xếp.

Trong ví dụ này, không chỉ “Chuối” bị lỗi #N/A mà “Lê” còn trả về giá sai. Điều này xảy ra bởi vì khi dùng range_lookup là TRUE, hàm VLOOKUP tìm kiếm một kết quả khớp tương đối. Không có kết quả gần khớp nào cho “Chuối” và “Lê” đứng trước “Đào” theo thứ tự bảng chữ cái, dẫn đến hàm trả về thông tin không chính xác. Để tránh những lỗi dữ liệu như thế này, bạn nên tìm hiểu thêm về các cách xử lý dữ liệu chuẩn trong Excel, ví dụ như cách lọc dữ liệu trùng trong excel để đảm bảo độ chính xác cho bảng tính của mình.

Cách sửa lỗi #N/A cho VLOOKUP và HLOOKUP hiệu quả

Để khắc phục lỗi #N/A (và cả lỗi trả về giá trị sai) khi sử dụng VLOOKUP và HLOOKUP trong trường hợp cần tìm kiếm chính xác, giải pháp rất đơn giản:

Bạn hãy thay đổi tham số range_lookup thành FALSE.

Cú pháp sẽ trông như thế này:
=VLOOKUP(giá_trị_tìm_kiếm, bảng_dò_tìm, số_thứ_tự_cột_lấy_giá_trị, FALSE)
=HLOOKUP(giá_trị_tìm_kiếm, bảng_dò_tìm, số_thứ_tự_dòng_lấy_giá_trị, FALSE)

Việc đặt range_lookup là FALSE sẽ yêu cầu hàm tìm kiếm đúng giá trị bạn cung cấp. Nếu nó tìm thấy, bạn sẽ có kết quả chính xác. Nếu không tìm thấy, hàm sẽ trả về #N/A – và đây là lúc bạn biết rằng giá trị bạn đang tìm kiếm không tồn tại trong bảng dữ liệu của mình. Quay lại ví dụ trên, khi dùng VLOOKUP với FALSE, “Lê” sẽ trả về giá chính xác nhưng “Chuối” vẫn sẽ #N/A vì “Chuối” không có trong danh sách tìm kiếm. Để hiểu rõ hơn về cách sử dụng các hàm tra cứu, bạn có thể tham khảo thêm cách sử dụng hàm hlookup trong excel.

Alt: Minh họa cú pháp hàm VLOOKUP với tham số range_lookup được đặt là FALSE, giúp tìm kiếm chính xác giá trị và sửa lỗi #N/A thông thường.

Khắc phục lỗi #N/A với hàm MATCH

Nếu bạn đang sử dụng hàm MATCH để tìm vị trí của một giá trị trong một phạm vi, và cũng gặp phải lỗi #N/A, thì nguyên nhân cũng tương tự như VLOOKUP/HLOOKUP. Hàm MATCH cũng có một tham số gọi là match_type để chỉ định kiểu khớp:

• 1 (hoặc bỏ qua): Tìm kiếm giá trị lớn nhất nhỏ hơn hoặc bằng giá trị cần tìm. Yêu cầu phạm vi phải được sắp xếp tăng dần.
• 0: Tìm kiếm giá trị khớp chính xác.
• -1: Tìm kiếm giá trị nhỏ nhất lớn hơn hoặc bằng giá trị cần tìm. Yêu cầu phạm vi phải được sắp xếp giảm dần.

Để sửa lỗi #N/A với hàm MATCH và đảm bảo kết quả tìm kiếm chính xác, bạn hãy đặt tham số match_type thành 0 (số không).

Cú pháp sẽ là:
=MATCH(giá_trị_tìm_kiếm, phạm_vi_tìm_kiếm, 0)

Lưu ý rằng, việc sử dụng 0 sẽ giúp bạn tìm chính xác vị trí của giá trị, và nếu giá trị đó không tồn tại, hàm sẽ trả về #N/A, giúp bạn dễ dàng nhận biết và xử lý dữ liệu. Trong quá trình làm việc, nếu bạn còn gặp các lỗi khác như cách sửa lỗi #div/0 trong excel, hãy nhớ kiểm tra lại các tham số hàm nhé.

Kết luận

Lỗi #N/A trong Excel không phải là điều gì quá đáng sợ, đặc biệt là khi bạn đã hiểu rõ nguyên nhân và biết cách khắc phục. Với các hàm dò tìm như VLOOKUP, HLOOKUP và MATCH, chìa khóa nằm ở việc kiểm soát tham số range_lookup (hoặc match_type). Luôn nhớ rằng, khi cần tìm kiếm chính xác, hãy sử dụng FALSE cho VLOOKUP/HLOOKUP và 0 cho hàm MATCH. Việc này không chỉ giúp bạn tránh lỗi mà còn đảm bảo độ chính xác cho toàn bộ bảng tính của mình. Hãy áp dụng ngay những thủ thuật này để công việc trên Excel của bạn trở nên mượt mà và hiệu quả hơn nhé!