Hướng dẫn cách sửa lỗi #N/A trong hàm VLOOKUP

Sửa lỗi #N/A của VLOOKUP trong Excel

Với công thức Vlookup, thông báo lỗi #N/A (hay còn có nghĩa là “không khả dụng”) thường được hiển thị mỗi khi Excel không thể tìm thấy được giá trị mà bạn muốn tra cứu. Lỗi này xuất hiện khi có một trong các lí do sau:

1. Lỗi đánh máy hay sắp xếp nhầm trong giá trị tra cứu

Lỗi xếp nhầm chữ thường hay xảy ra khi làm việc với một lượng dữ liệu lớn gồm nhiều hàng hay khi giá trị tra cứu được bạn gõ trực tiếp vào công thức.

2. Lỗi # N/A trong tìm kiếm gần đúng bằng hàm VLOOKUP

Nếu như bạn đang dùng một công thức với đối sánh gần đúng (là đối số range_lookup là TRUE hoặc là bỏ qua), công thức VLOOKUP của bạn lúc này có thể trả về lỗi # N/A trong hai trường hợp sau:

• Nếu như giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong mảng tra cứu.
• Nếu như cột tra cứu không sắp xếp theo thứ tự tăng dần.

3. Lỗi # N/A trong tìm kiếm chính xác hàm VLOOKUP

4. Cột tra cứu không phải là cột bên trái của bảng dữ liệu

Như bạn đã biết, một trong các hạn chế đáng kể nhất của hàm VLOOKUP đó chính là nó không thể thực hiện dò tìm phía bên trái cột tra cứu. Vì thế, cột tra cứu sẽ luôn luôn là cột bên trái ngoài cùng của bảng dò tìm. Ở thực tế, bạn thường quên đi điều này, từ đó dẫn đến hàm VLOOKUP sẽ không hoạt động vì lỗi N/A.

Giải pháp: Nếu như bạn không thể tái cấu trúc dữ liệu để cột tra cứu là cột bên trái, thì bạn có thể kết hợp các hàm INDEX và MATCH để tiến hành thay thế linh hoạt cho hàm VLOOKUP. Lúc này, bạn sẽ tìm thấy thông tin chi tiết cùng ví dụ về công thức trong hướng dẫn – Cách sử dụng hàm INDEX / MATCH để thực hiện tra cứu các giá trị bên trái.

5. Số được định dạng ở dạng văn bản

Một lỗi N/A khác cũng rất thường gặp của hàm VLOOKUP đó chính là do những con số được định dạng ở dạng văn bản, tại cột dò tìm hoặc là vùng tra cứu.

Điều này sẽ thường xảy ra khi bạn thực hiện nhập dữ liệu từ một cơ sở dữ liệu bên ngoài hoặc là do bạn đã gõ dấu nháy đơn trước số.

Những số này cũng có thể được lưu trữ dưới định dạng chung. Với trường hợp này, sẽ có một dấu hiệu đáng chú ý đó là số được canh lề trái của ô.

Giải pháp: Nếu như đây chỉ là một số duy nhất, thì bạn chỉ cần nhấp vào biểu tượng lỗi rồi nhấn chọn “Convert To Number” từ danh sách tùy chọn là xong.

Nếu như nhiều số bị ảnh hưởng, bạn hãy chọn tất cả, sau đó nhấp chuột phải vào vùng chọn và chọn Format Cells>Number tab>Number và nhấp OK.

6. Các khoảng trống lớn hoặc dấu đầu dòng

Đây là một trong những nguyên nhân rõ ràng nhất của lỗi VLOOKUP N/A bởi vì mắt người không thể nhìn thấy các không gian đặc biệt, nhất là khi làm việc với những bảng lớn, nơi mà hầu hết những mục nằm dưới thanh cuộn.

Trường hợp 1: Khoảng trắng trong cột dò tìm (với công thức VLOOKUP)

Nếu như các khoảng trống dư thừa đang xuất hiện trong bảng chính của bạn, thì bạn có thể đảm bảo công thức VLOOKUP bằng việc gói đối số lookup_value với hàm TRIM:

= VLOOKUP (TRIM ($F2), $A$2: $C$10,3, FALSE)

Trường hợp 2: Khoảng trắng trong cột tra cứu

Nếu như có khoảng trắng xuất hiện ở cột tra cứu, thì không có cách nào để có thể tránh được lỗi VLOOKUP # N/A. Thay vì sử dụng hàm VLOOKUP,thì lúc này bạn có thể dùng một công thức mảng với sự kết hợp giữa hàm INDEX / MATCH và TRIM:

= INDEX ($C$2: $C$10, MATCH (TRUE, TRIM ($A$2: $A$10) = TRIM ($F$2), 0))

Vì đây là một công thức mảng, nên bạn lưu ý đừng quên nhấn Ctrl + Shift + Enter, không phải phím Enter để hoàn thành nó nhé.

Xem thêm: Hướng dẫn cách xóa nhiều dòng trong Excel

Lỗi #NAME hàm VLOOKUP

Đây được xem trường hợp dễ xảy ra nhất – lỗi #NAME thường xuất hiện khi bạn vô tình đánh sai tên hàm. Giải pháp lúc này, hiển nhiên là – kiểm tra chính tả rồi.

Hàm VLOOKUP không hoạt động (giới hạn của hàm, vấn đề và giải pháp)

Ngoài việc sở hữu một cú pháp khá phức tạp, hàm VLOOKUP còn có nhiều hạn chế đáng kể khác so với bất cứ hàm Excel khác. Do các hạn chế này, nên bạn dường như khó để thực hiện tạo chính xác công thức VLOOKUP. Theo dõi các giải pháp dưới đây, bạn sẽ tìm thấy cách giải quyết cho một vài tình huống phổ biến khi VLOOKUP không thành công đấy.

1. VLOOKUP không phân biệt chữ hoa chữ thường

Hàm VLOOKUP sẽ không phân biệt chữ hoa và chữ thường. Vì thế, nếu như bảng của bạn có các mục tương tự chỉ khác nhau chữ hoa, chữ thường, thì công thức Vlookup cũng sẽ trả lại giá trị đầu tiên được tìm thấy.

Giải pháp: Nên dùng một hàm Excel khác để có thể thực hiện tra cứu theo chiều dọc (chẳng hạn như LOOKUP, SUMPRODUCT, INDEX / MATCH) và kết hợp với hàm EXACT để chọn đúng trường hợp. Bạn cũng có thể tìm thấy những giải thích chi tiết cùng các ví dụ công thức trong hướng dẫn – 4 cách để làm một vlookup phân biệt chữ thường và chữ hoa trong Excel.

2. VLOOKUP trả về giá trị tìm thấy đầu tiên

Hàm VLOOKUP sẽ trả về giá trị đầu tiên mà nó tìm thấy trong cột trả về phù hợp so với giá trị tra cứu. Tuy vậy, bạn cũng có thể buộc ham trả về cái thứ 2, 3, 4 hay bất cứ trường hợp nào mà bạn muốn. Nếu như bạn cần phải nhận được tất cả những giá trị, bạn sẽ phải dùng kết hợp các hàm INDEX, SMALL và ROW.

Giải pháp: Những ví dụ về công thức có sẵn để tải về tại đây:

• Nhận biết những lần xuất hiện thứ 2, 3, 4, v.v …
• Nhận tất cả những lần xuất hiện trùng lặp của giá trị tra cứu

3. Một cột mới được chèn vào hoặc gỡ bỏ khỏi bảng

Nhưng khá đáng tiếc là những công thức VLOOKUP sẽ ngừng hoạt động khi một cột mới bị xóa hoặc là được thêm vào bảng tra cứu. Điều này có thể xảy ra vì cú pháp của hàm VLOOKUP thường yêu cầu bạn phải cung cấp toàn bộ bảng và một số nhất định cho biết cột nào bạn đang muốn trả lại dữ liệu. Tất nhiên, cả bảng và số cột trả lại sẽ thay đổi mỗi khi bạn xóa một cột hiện có hoặc là chèn một cột mới.

Giải pháp: INDEX / MATCH lại được sử dụng lần nữa để thực hiện giải quyết vấn đề này. Trong hàm INDEX & MATCH, bạn sẽ chỉ định những cột tra cứu và cột trả kết quả một cách riêng biệt. Lúc này, kết quả là bạn có thể xóa hoặc là chèn nhiều cột bạn muốn mà không cần phải lo lắng về việc cập nhật mọi công thức VLOOKUP có liên quan .

4. Các ô tham chiếu thay đổi khi sao chép công thức đến ô khác

Giải pháp: Luôn dùng tham chiếu ô tuyệt đối (với dấu $) trong vùng chọn, ví dụ như: $A$2: $C$100 hoặc $A:$C. Tại thanh công thức, bạn cũng có thể nhanh chóng chuyển đổi giữa những loại tham chiếu khác nhau bằng việc nhấn F4.

Hàm VLOOKUP với hàm IFERROR / ISERROR

Nếu như bạn không muốn cho người dùng thấy được tất cả những thông báo lỗi N/A, VALUE hoặc là NAME thì bạn có thể trả lại ô trống hoặc là hiển thị thông điệp của riêng mình bạn. Bạn có thể thực hiện điều này bằng việc tiến hành lồng công thức VLOOKUP của bạn vào trong hàm IFERROR trong Excel 2013, 2010 và 2007 hoặc là hàm IF / ISERROR trong những phiên bản Excel trước đó.

Sử dụng VLOOKUP với IFERROR

Cú pháp của hàm IFERROR khá đơn giản

IFERROR (value, value_if_error)

Bạn tiến hành nhập giá trị để có thể kiểm tra lỗi trong đối số thứ nhất, và tại đối số thứ 2 bạn tiến hành chỉ định giá trị trả về nếu như có lỗi xảy ra. Ví dụ, công thức IFERROR / VLOOKUP sau đang trả về một ô trống khi không tìm thấy giá trị tra cứu:

= IFERROR (VLOOKUP ($F$2, $B$2: $C$10,2, FALSE), “”)

Nếu như bạn thích hiển thị thông điệp của mình thay cho các biểu hiện lỗi thông thường, thì hãy đánh chúng vào dấu ngoặc kép như minh họa bên dưới:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),”Oops, no match is found. Please try again!”)

Dùng hàm VLOOKUP với ISERROR

Hàm IFERROR hiện có ở phiên bản Excel 2007, tại những phiên bản Excel trước bạn sẽ phải dùng kết hợp hàm IF và ISERROR như thế này:

=IF(ISERROR(VLOOKUP formula), “Your message if any“, VLOOKUP formula)

Ví dụ như đây là công thức IF / ISERROR / VLOOKUP tương tự so với công thức IFERROR / VLOOKUP ở trên:

=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))

Trên đây là các thông tin về cách sửa lỗi #N/A trong hàm VLOOKUP mà bạn nên nắm để có thể thao tác thành thạo hơn trong Excel. Đừng quên tìm hiểu thêm các thông tin bổ ích khác tại trang web của chúng tôi nhé!

Xem thêm: Hướng dẫn cách đổi trục biểu đồ trong Excel