Hàm VLOOKUP lại lỗi NA, Name, Value – xử lý lỗi VLOOKUP

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, hoảng sợ bấm Enter 1 cái, mong đợi hiệu 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 xuất sắc kĩ thuật sử dụng VLOOKUP qua 4 bài viết vừa qua, giờ đây bạn sẽ hoàn toàn 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 tất cả 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 quy trình sử dụng VLOOKUP :

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

Việc tiên phong tất cả 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 thông dụng 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, so với trường hợp này, bạn không hề 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 chừng trắng trong một ô, bạn hoàn toà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 yếu tố này, cách đơn thuần nhất bạn hoàn toàn có thể tìm hiểu thêm là dùng thêm hàm TRIM để vô hiệu khoảng chừ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ỏ lỡ ), thì việc gặp lỗi # N / A hoàn toàn có thể do một trong 2 nguyên 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ị tất cả 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 hoàn toàn 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 hề 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 tiễn, tất cả 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 giải trí vì lỗi N / A .

The lookup column should be the leftmost column of the table array, otherwise VLOOKUP displays the N/A error.

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 những số lượng đượ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 tài liệu từ một cơ sở tài liệu bên ngoài hoặc do bạn đã gõ dấu nháy đơn trước số .

The indications of numbers being formatted as text in Excel

Các số này cũng hoàn toàn có thể được tàng trữ ở định dạng chung. Trong trường hợp này, có một tín hiệu đáng quan tâm – 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.

Converting numbers formatted as text to the normal numbers format.

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 do rõ ràng nhất của lỗi Vlookup N / A vì mắt người không hề nhìn thấy những khoảng trống đặc biệt quan trọng, nhất là khi thao tác với những bảng lớn, nơi 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ững khoảng trống dư thừa Open trong bảng chính của bạn, bạn hoàn toàn có thể bảo vệ 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)

excel dialog box launcher 1 26

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

Nếu có khoảng chừng trắng Open 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 hoàn toàn có thể sử dụng một công thức mảng với sự tích hợp những 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ó:

1 excel dialog box launcher 1 27

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 tài 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 chú ý quan tâm rằng hàm VLOOKUP không hề tìm kiếm những giá trị có từ 256 ký tự trở lên. Nếu giá trị tra cứu của bạn vượt quá số lượng giới hạn này, bạn sẽ nhận được lỗi VALUE :

excel dialog box launcher 1 28

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:

Xem thêm: Web Novel

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

excel dialog box launcher 1 29

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

Nếu bạn lấy tài liệu từ một bảng tính khác, bạn phải gồm có đường dẫn khá đầy đủ đến tệp đó. Chính xác hơn, bạn phải kèm theo tên của tệp ( gồm có cả phần lan rộng ra ) 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 hảo của vlookup tra cứu tài 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ự hoàn toàn 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ể thành phần 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 giải trí 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 viên 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ó trường hợp nhập một số ít nhỏ hơn “ 1 ” để xác lập cột giá trị trả về. Tuy vậy, điều này hoàn toàn có thể xảy ra nếu đối số này là tác dụng 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 tài 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 Open 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 tác dụng không như mong đợi. Dưới đây bạn sẽ tìm thấy những giải pháp cho một vài trường hợp thông dụng khi VLOOKUP không thành công xuất sắc .

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ự như chỉ khác nhau chữ hoa, chữ thường, công thức Vlookup sẽ trả về giá trị được tìm thấy tiên phong 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ị tiên phong nó tìm thấy trong cột trả về tương thích với giá trị tra cứu. Tuy nhiên, bạn hoàn toà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ổng thể những giá trị, bạn sẽ phải sử dụng phối hợp những 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 giải trí 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 nhu yếu bạn cung ứng hàng loạt bảng cũng như 1 số ít nhất định cho biết bạn muốn trả về tài liệu từ cột nào. Đương nhiên, cả bảng và số của cột trả về đều đổi khác 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ổng thể những thông tin lỗi N / A, VALUE hoặc NAME, bạn hoàn toàn có thể trả lại ô trống hoặc hiển thị nội dung của riêng bạn. Bạn hoàn toà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 những 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 thuần :

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

Bạn triển khai 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),””)

The IFERROR / VLOOKUP formula returns a blank cell instead of the error message.

Nếu bạn thích hiển thị nội dung mà mình muốn thay vì những bộc lộ lỗi thường thì, 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!”)

Display your message instead of VLOOKUP error.

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

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

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

Xem thêm: Max Skill King

Ví dụ : đây là công thức IF / ISERROR / VLOOKUP tựa như 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.

Source: https://sgn888.net
Category: Thủ Thuật

Bài viết liên quan