Hàm VLOOKUP Lại Lỗi NA, Name, Value - Blog Học Excel Online

Trong bài viết này, Học Excel Online sẽ giúp các bạn giải quyết các vấn đề phổ biến với VLOOKUP trong Excel 2003, Excel 2007, Excel 2010, Excel 2013, Excel 2016.

Bạn viết VLOOKUP xong, hồi hộp bấm Enter 1 cái, mong chờ kết quả hiện ra, nhưng không, bạn nhận được những lỗi như #N/A, #NAME, #VALUE hoặc 1 số lỗi khác

Nhắc lại kiến thức về VLOOKUP và đồng bọn

Trước khi đi bắt lỗi của VLOOKUP, bạn cần biết cách sử dụng hàm VLOOKUP như thế nào đã, nếu bạn quan tâm, đây là những bài viết về VLOOKUP để các bạn tham khảo:

Sử dụng VLOOKUP trong Excel

Sau khi đã luyện thành công kĩ thuật sử dụng VLOOKUP qua 4 bài viết vừa rồi, bây giờ bạn sẽ có thể gặp những lỗi sau đây

Xử lý lỗi NA khi sử dụng VLOOKUP:

NA trong tiếng anh nghĩa là “Not available”, lỗi này là 1 trong những lỗi hay gặp khi chúng ta sử dụng VLOOKUP. Bạn sẽ cần kiểm tra những thứ sau đây nếu gặp lỗi này trong quá trình sử dụng VLOOKUP:

Lỗi chính tả trong giá trị được tìm kiếm (Lookup Value)

Việc đầu tiên chúng ta cần xem khi gặp lỗi NA khi sử dụng VLOOKUP là kiểm tra lại lỗi chính tả của giá trị cần tra cứu.

Một trường hợp phổ biến mà nhiều bạn gặp phải, đó là giá trị cần tra cứu thừa dấu cách ở cuối, đối với trường hợp này, bạn không thể kiểm tra bằng mắt thường. Để phát hiện lỗi thừa dấu cách, ký tự khoảng trắng trong một ô, bạn có thể sử dụng hàm LEN.

Ví dụ:

Trong ví dụ trên, nếu nhìn bằng mắt thường thì giá trị của A2 và A3 đều như nhau. Nhưng số ký tự của A2 là 5 và của A3 là 6. Để khắc phục vấn đề này, cách đơn giản nhất bạn có thể tham khảo là dùng thêm hàm TRIM để loại bỏ khoảng trắng trong giá trị cần tra cứu. Công thức ví dụ như sau:

=VLOOKUP(TRIM(lookup_value), table_array, col_index_num, [range_lookup])

Lỗi #N/A khi sử dụng VLOOKUP để dò tìm gần đúng

Nếu bạn sử dụng VLOOKUP với tham số tra cứu là gần đúng ([range_lookup] là TRUE hoặc bỏ qua), thì việc gặp lỗi #N/A có thể do một trong 2 lý do sau đây

• Giá trị cần tra cứu – lookup_value nhỏ hơn giá trị nhỏ nhất trong vùng tra cứu như trong ví dụ sau đây,mức doanh thu là 95,nhỏ hơn số nhỏ nhất trong cột chứa dữ liệu tra cứu là 100.

• Cột chứa giá trị cần tìm kiếm không được sắp xếp theo thứ tự tăng dần: cột doanh thu không được sắp xếp theo thứ tự từ nhỏ tới lớn.  Cách khắc phục là bạn cần sắp xếp cột doanh thu lại cho đúng thứ tự tăng dần.

Lỗi #N/A khi … thực sự không phải lỗi

Khi giá trị chúng ta đang tìm kiếm không có trong bảng tìm kiếm thì lỗi #N/A cũng có thể xảy ra.

Cột tìm kiếm không ở vị trí đầu tiên khi tra cứu

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

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

Xem thêm: Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel

Số nhưng định dạng kiểu chữ:

Một lỗi N/A khác của hàm VLOOKUP là do các con số được định dạng dưới dạng văn bản, trong cột dò tìm hoặc vùng tra cứu.

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

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

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

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

Thừa khoảng trắng ở đầu ô hoặc cuối ô:

Đây là nguyên nhân rõ ràng nhất của lỗi Vlookup N/A vì mắt người không thể nhìn thấy những không gian đặc biệt, nhất là khi làm việc với các bảng lớn, nơi hầu hết các 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 các khoảng trống dư thừa xuất hiện trong bảng chính của bạn, bạn có thể đảm bảo công thức Vlookup bằng cách 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 có khoảng trắng xuất hiện trong cột tra cứu, không có cách nào để tránh được lỗi VLOOKUP # N/A. Thay vì dùng hàm VLOOKUP, bạn có thể sử dụng một công thức mảng với sự kết hợp các 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, đừng quên nhấn Ctrl + Shift + Enter chứ không phải là phím Enter để hoàn thành nó:

Tìm hiểu thêm: Xoá bỏ khoảng trắng, làm sạch dữ liệu trong Excel

Lỗi #VALUE khi sử dụng VLOOKUP:

Nói chung, Microsoft Excel sẽ hiển thị lỗi #VALUE! nếu giá trị được sử dụng trong công thức có kiểu dữ liệu sai. Đối với hàm VLOOKUP, có hai trường hợp thường gặp gây lỗi VALUE!.

Giá trị tra cứu vượt quá 255 ký tự

Xin lưu ý rằng hàm VLOOKUP không thể tìm kiếm các giá trị có từ 256 ký tự trở lên. Nếu giá trị tra cứu của bạn vượt quá giới hạn này, bạn sẽ nhận được lỗi VALUE:

Giải pháp: Hãy sử dụng hàm INDEX / MATCH thay thế. Trong ví dụ trên, hàm INDEX / MATCH hoạt động hoàn hảo:

= INDEX (C2: C7, MATCH (TRUE, INDEX (B2: B7 = F$2,0), 0))

Không có đường dẫn đầy đủ tới bảng tính tra cứu

Nếu bạn lấy dữ liệu từ một bảng tính khác, bạn phải bao gồm đường dẫn đầy đủ đến tệp đó. Chính xác hơn, bạn phải kèm theo tên của tệp (bao gồm cả phần mở rộng) trong dấu ngoặc vuông [], và sau đó chỉ định tên của trang tính và dấu chấm than. Ngoài ra, bạn nên đặt chúng trong dấu nháy trong trường hợp tệp hoặc tên trang tính có chứa khoảng trống.

Đây là cấu trúc công thức hoàn chỉnh của vlookup tra cứu dữ liệu từ một bảng tính khác:

= VLOOKUP (lookup_value, ‘[tên tệp] tên trang tính’! Table_array, col_index_num, FALSE)

Một công thức thực sự có thể trông như sau:

= VLOOKUP ($A$2, ‘[New Prices.xls] Sheet1’! $B:$D, 3, FALSE)

Công thức trên sẽ tìm kiếm giá trị ô A2 trong cột B của Sheet1 tệp “New Prices” và trả về một giá trị phù hợp tại cột D.

Nếu bất kỳ phần tử nào của đường dẫn bị thiếu, công thức VLOOKUP của bạn sẽ không hoạt động và trả lại lỗi #VALUE (trừ khi bảng tính tra cứu hiện đang mở).

Nên xem thêm: Thành chuyên gia Excel từ cơ bản tới nâng cao trong 7 ngày

Đối số col_index_num nhỏ hơn 1

Khó có tình huống nhập một số nhỏ hơn “1” để xác định cột giá trị trả về. Tuy vậy, điều này có thể xảy ra nếu đối số này là kết quả của một hàm Excel khác được lồng vào công thức Vlookup của bạn.

Vì vậy, nếu đối số col_index_num nhỏ hơn 1, công thức Vlookup của bạn sẽ trả về lỗi #VALUE!

Nếu đối số col_index_num lớn hơn số cột trong bảng dữ liệu được chọn, công thức Vlookup trả về lỗi #REF!

Lỗi #NAME khi sử dụng VLOOKUP:

Đây là trường hợp dễ nhất – lỗi NAME xuất hiện nếu bạn vô tình viết sai chính tả tên của hàm.

Giải pháp là rõ ràng – hãy kiểm tra chính tả:)

VLOOKUP không hoạt động (hạn chế, sự cố và giải pháp)

Ngoài việc có một cú pháp khá phức tạp, hàm  VLOOKUP có nhiều hạn chế đáng kể so với bất kỳ hàm Excel khác. Do những hạn chế này, công thức Vlookup thường mang lại kết quả không như mong đợi. Dưới đây bạn sẽ tìm thấy các giải pháp cho một vài tình huống phổ biến khi VLOOKUP không thành công.

Vlookup không phân biệt chữ hoa chữ thường

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

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

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

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

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

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

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

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

Giải pháp: INDEX / MATCH được dùng để giải quyết vấn đề này. Trong hàm INDEX & MATCH, bạn chỉ định các cột tra cứu và cột trả kết quả riêng biệt, kết quả là bạn có thể xóa hoặc chèn nhiều cột tùy ý mà không cần phải cập nhật mọi công thức vlookup liên quan .

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

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

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

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

Sử dụng VLOOKUP với IFERROR

Cú pháp của hàm IFERROR rất đơn giản:

IFERROR (giá trị, giá trị_if_error)

Bạn tiến hành nhập giá trị để kiểm tra lỗi trong đối số thứ nhất, và trong đối số thứ 2 bạn chỉ định giá trị trả về nếu lỗi xảy ra.

Ví dụ, công thức IFERROR / VLOOKUP 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 bạn thích hiển thị nội dung mà mình muốn thay vì những biểu hiện lỗi thông thường, hãy đánh chúng vào dấu ngoặc kép như thế này:

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

Sử dụng hàm VLOOKUP với ISERROR

Hàm IFERROR có ở phiên bản Excel 2007, trong các phiên bản Excel trước bạn sẽ phải sử 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ụ: đây là công thức IF / ISERROR / VLOOKUP tương tự 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à những lỗi hay gặp trong hàm Vlookup. Học Excel Online hy vọng thông qua những hướng dẫn trên bạn có thể xử lý tất cả các lỗi có thể xảy ra của hàm Vlookup.