Giải pháp cho các lỗi NA, Name, Value trong VLOOKUP

Bài viết dưới đây do Siêu Marketing biên soạn sẽ đưa ra giải pháp cho những khó khăn mà bạn gặp phải khi sử dụng hàm VLOOKUP trên các phiên bản Excel như 2003, 2007, 2010, 2013 và 2016.

Bạn đã nhập công thức VLOOKUP và ánh đèn hy vọng lóe sáng khi nhấn Enter, nhưng thay vào đó, bạn lại đối mặt với các thông báo lỗi như #N/A, #NAME, #VALUE hoặc những lỗi khác.

Ôn lại kiến thức cơ bản về VLOOKUP

Trước khi tiếp cận việc sửa lỗi VLOOKUP, việc cần thiết là bạn phải nắm được cách thức hoạt động của hàm này. Dưới đây là một số bài viết hữu ích về VLOOKUP mà bạn có thể tham khảo:

Hướng dẫn sử dụng VLOOKUP trong Excel

Có vốn kiến thức căn bản từ 4 bài viết về VLOOKUP, bây giờ, bạn có thể gặp phải một số lỗi cụ thể sau đây.

Cách khắc phục lỗi NA trong VLOOKUP:

Lỗi NA, có nghĩa là “Không có sẵn” trong tiếng Anh, là một trong những lỗi thường gặp khi sử dụng hàm VLOOKUP. Nếu xuất hiện lỗi này, bạn cần kiểm tra các yếu tố sau:

Lỗi chính tả trong giá trị cần tra cứu (Lookup Value)

Khi VLOOKUP báo lỗi NA, bước đầu tiên chúng ta cần làm là xác minh chính tả của giá trị cần tìm kiếm.

Trong số các trường hợp thường gặp, một số người thường mắc phải lỗi thừa khoảng trăng ở vị trí cuối chuỗi. Để phát hiện lỗi này mà mắt thường không thể, chúng ta có thể áp dụng hàm LEN.

Dưới đây là một ví dụ:

Xử lý lỗi NA khi sử dụng VLOOKUP
Dùng hàm LEN để nhận biết khoảng trắng dư thừa trong giá trị cần tra cứu

Trong ví dụ này, A2 và A3 có vẻ giống hệt nhau khi quan sát bằng mắt, nhưng khi kiểm tra số ký tự thì A2 chỉ có 5 ký tự, còn A3 thì lại có 6. Để loại bỏ các khoảng trống không cần thiết, bạn có thể áp dụng hàm TRIM trong công thức như ví dụ sau:

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

Lỗi #N/A khi VLOOKUP thực hiện tìm kiếm gần đúng

Sự xuất hiện của lỗi #N/A khi bạn cài đặt VLOOKUP sử dụng tùy chọn tra cứu gần đúng ([range_lookup] là TRUE hoặc không được định rõ), có thể bắt nguồn từ hai nguyên nhân:

  • Giá trị cần tìm kiếm – lookup_value nhỏ hơn giá trị nhỏ nhất nằm trong phạm vi tra cứu. Ví dụ dưới đây minh họa trường hợp doanh thu chỉ là 95, thấp hơn giá trị nhỏ nhất có trong cột dữ liệu tra cứu là 100.
Xử lý lỗi NA trong VLOOKUP
Giải quyết lỗi NA trong VLOOKUP
  • Cột chứa giá trị cần tìm kiếm không được sắp xếp lần lượt tăng dần: cột doanh thu cần phải được sắp xếp từ thấp đến cao. Bạn có thể giải quyết vấn đề này bằng cách sắp xếp lại cột đó cho phù hợp.
Xử lý lỗi NA trong vlookup
Cách giải quyết lỗi NA trong vlookup
vlookup

Lỗi #N/A không phải lúc nào cũng là lỗi thực sự

Nếu trong bảng dữ liệu không chứa giá trị đang được tìm kiếm, lỗi #N/A có thể phát sinh.

Không đặt cột tìm kiếm ở vị trí đầu tiên khi sử dụng VLOOKUP

VLOOKUP có một nhược điểm lớn là không thể tra cứu thông tin từ bên trái của cột chứa giá trị tra cứu, do đó cần đặt cột này ở cạnh trái cùng của bảng tìm kiếm. Thực tế cho thấy đôi khi chúng ta lãng quên việc này và VLOOKUP bị lỗi N/A do sắp xếp không đúng cột.

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

Cách khắc phục: Nếu không thể sắp xếp lại cột tra cứu thành cột đầu tiên, hãy áp dụng kết hợp hàm INDEXMATCH làm phương án thay thế linh động cho VLOOKUP. Chi tiết cùng ví dụ về cách dùng công thức này bạn sẽ tìm thấy trong hướng dẫn – Cách sử dụng hàm INDEX / MATCH để tìm kiếm giá trị bên trái.

Xem thêm: Lý do vì sao INDEX và MATCH lại ưu việt hơn VLOOKUP trong Excel

Số có định dạng như văn bản:

Lỗi N/A khác trong VLOOKUP xuất hiện khi số đang được xét dưới dạng văn bản, có thể ở cột tìm kiếm hoặc ở vùng tra cứu.

Thường gặp phải khi dữ liệu được nhập từ cơ sở dữ liệu ngoại lai, hoặc có sự hiện diện của dấu nháy đơn trước số khi nhập liệu.

The indications of numbers being formatted as text in Excel

Trường hợp số đó được đặt trong ô có định dạng chung, sẽ thấy rằng số sẽ được căn lề trái.

Cách xử lý: Nếu chỉ có một vài số, nhấn vào biểu tượng thông báo lỗi và chọn “Convert To Number” từ menu xuất hiện.

Converting numbers formatted as text to the normal numbers format.

Đối với trường hợp nhiều số bị ảnh hưởng, chọn chúng cùng lúc, click chuột phải và chọn Format Cells>Number tab>Number, sau đó nhấn OK.

Dư khoảng trống ở đầu hoặc cuối ô:

Lỗi Vlookup N/A thường xuất phát từ việc có khoảng trắng thừa, điều mà mắt thường khó có thể phát hiện, đặc biệt là khi làm việc với bảng dữ liệu lớn và các mục được cuộn qua thanh trượt.

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

Nếu bảng chính của bạn có những khoảng trắng không mong muốn, điều chỉnh công thức Vlookup bằng cách sử dụng hàm TRIM cho đối số lookup_value có thể giải quyết vấn đề:

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

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

Khi cột tra cứu chứa khoảng trắng thừa, lỗi VLOOKUP # N/A là không thể tránh khỏi. Thay vì dùng VLOOKUP, hãy dùng công thức mảng kết hợp INDEX / MATCH và TRIM:

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

Nhớ nhấn Ctrl + Shift + Enter để hoàn tất công thức mảng, không phải chỉ nhấn Enter:

Lỗi #VALUE trong VLOOKUP:

Một trong những lỗi thường gặp trong Microsoft Excel là lỗi #VALUE!, xuất hiện khi có vấn đề về kiểu dữ liệu của giá trị đang được dùng trong công thức. Trong trường hợp sử dụng hàm VLOOKUP, có hai nguyên nhân phổ biến dẫn tới lỗi VALUE!.

Giá trị được tìm kiếm dài hơn 255 ký tự

Xin nhấn mạnh rằng khi sử dụng hàm VLOOKUP, không thể tìm kiếm giá trị nằm trong phạm vi từ 256 ký tự trở lên. Trường hợp giá trị bạn muốn tra cứu vượt qua giới hạn đã nêu, lỗi VALUE sẽ xảy ra:

Phương án giải quyết: Hãy thử dùng hàm INDEX / MATCH để thay thế. Chẳng hạn, trong trường hợp dưới đây, hàm INDEX / MATCH vận hành mượt mà:

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

 

Thiếu đường dẫn đến bảng tính tra cứu

Đối với việc lấy dữ liệu từ bảng tính khác, điều cần thiết là phải điền đường dẫn tới file một cách đầy đủ. Điều đó có nghĩa là bạn cần ghi rõ tên file (kể cả đuôi mở rộng) trong ngoặc vuông [], sau đó là tên sheet và dấu chấm than. Hơn nữa, nếu tên file hoặc sheet có chứa khoảng trắng, bạn cần đặt chúng trong dấu nháy kép.

Đây là cấu trúc của một công thức VLOOKUP đầy đủ khi tra cứu từ bảng tính khác là :

Một ví dụ cụ thể của công thức như sau:

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

Công thức trên sẽ tìm giá trị trong ô A2 nằm trên cột B của Sheet1 trong file “New Prices” và sẽ đem lại giá trị tương ứng từ cột D.

Nếu bỏ sót bất cứ phần nào trong đường dẫn, công thức VLOOKUP sẽ không thể hoạt động và sẽ hiển thị lỗi #VALUE, trừ khi bảng tính cần tìm kiếm đang được mở.

Giá trị col_index_num nhỏ hơn 1

Rất hiếm khi người ta nhập một số nhỏ hơn “1” làm đối số để chỉ ra cột chứa giá trị cần trả về. Tuy nhiên, đôi khi sự cố này có thể phát sinh nếu đối số col_index_num là kết quả từ hàm Excel khác nhúng vào trong công thức Vlookup.

Lúc đó, nếu đối số col_index_num nhỏ hơn 1, Vlookup sẽ hiện lỗi #VALUE!

Nhưng nếu đối số col_index_num lớn hơn số lượng cột của vùng dữ liệu được chọn, Vlookup sẽ báo lỗi #REF!

Lỗi #NAME trong VLOOKUP:

Trường hợp lỗi NAME xảy ra là do bạn gõ nhầm chính tả tên hàm.

Cách giải quyết là đơn giản, chỉ cần kiểm tra lại chính tả của tên hàm.

Vấn đề trong việc VLOOKUP không chạy đúng

VLOOKUP có cú pháp tương đối phức tạp và nhiều hạn chế mà không hàm Excel nào khác gặp phải. Chính những hạn chế đó là nguyên nhân khiến cho công thức Vlookup thỉnh thoảng không đưa ra kết quả như kỳ vọng. Dưới đây là một số giải pháp cho những tình huống VLOOKUP thất bại.

Vlookup không phân biệt chữ cái in hoa và thường

Về cơ bản, hàm VLOOKUP không phân biệt hoa thường. Nếu bạn có những dữ liệu trong bảng giống hệt nhau chỉ khác về chữ hoa chữ thường, Vlookup sẽ chỉ mang lại giá trị xuất hiện đầu tiên mà không cân nhắc đến case.

Cách khắc phục: Sử dụng hàm Excel khác có khả năng tra cứu theo chiều dọc (như LOOKUP, SUMPRODUCT, hoặc INDEX / MATCH) cùng với hàm EXACT để giúp bạn thực hiện việc tìm kiếm chính xác. Bạn có thể tìm hiểu thêm các hướng dẫn cụ thể và ví dụ minh họa công thức trong các bài hướng dẫn.

Đây là 4 phương pháp xây dựng hàm VLOOKUP có khả năng phân biệt chữ cái in hoa và in thường trong Excel.

VLOOKUP tìm và trả lại kết quả đầu tiên

Bạn đã biết rằng hàm VLOOKUP sẽ đưa ra kết quả đầu tiên nó tìm được trong cột kết quả phù hợp với giá trị cần tìm. Nhưng có thể buộc nó tìm ra kết quả thứ 2, 3, 4, hoặc bất kỳ kết quả nào mà bạn yêu cầu. Trong trường hợp bạn muốn lấy tất cả các kết quả khớp, bạn nên sử dụng sự kết hợp của các hàm INDEX, SMALL và ROW.

Giải pháp: Công thức mẫu có thể được tải về từ đường dẫn sau:

  • Xác định các kết quả thứ 2, 3, 4, v.v…
  • Lấy ra mọi kết quả trùng lặp với giá trị cần tìm.

Chèn hoặc xóa một cột ra khỏi dải dữ liệu

Nếu thực hiện chèn hoặc loại bỏ một cột trong bảng tra cứu thì công thức VLOOKUP sẽ bị ảnh hưởng. Điều này do hàm VLOOKUP yêu cầu bạn nhập cả dải dữ liệu lẫn chỉ số cột cụ thể để lấy dữ liệu. Chắc chắn cả dải dữ liệu lẫn chỉ số cột mà bạn muốn lấy thông tin đều thay đổi khi chèn hoặc loại bỏ một cột.

Giải pháp: Hàm INDEX / MATCH được áp dụng để giải quyết vấn đề này. Hàm INDEX & MATCH cho phép bạn định rõ cột cần tra và cột cần lấy kết quả, giúp bạn có thể thêm hoặc bớt cột mà không cần chỉnh sửa lại công thức VLOOKUP.

Thay đổi ô tham chiếu khi công thức được sao chép

Giải pháp: Sử dụng tham chiếu tuyệt đối (có dấu $) cho các ô được chọn, ví dụ: $A$2:$C$100 hoặc $A:$C. Bạn có thể dễ dàng chuyển đổi tham chiếu ô ngay trong thanh công thức bằng phím F4.

Công thức VLOOKUP kết hợp với hàm IFERROR / ISERROR

Khi bạn muốn tránh cho người dùng thấy thông báo lỗi N/A, VALUE, hoặc NAME, bạn có thể trả về ô trống hoặc thông báo lỗi tùy chỉnh của mình. Bằng cách gói công thức VLOOKUP của mình bên trong hàm IFERROR trong Excel 2013, 2010 và 2007 hoặc sử dụng hàm IF / ISERROR trong các phiên bản Excel cũ hơn, bạn có thể thực hiện việc này.

Áp dụng VLOOKUP cùng với IFERROR

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

IFERROR(giá_trị, giá_trị_khi_có_lỗi)

Nhập giá trị bạn muốn kiểm tra lỗi vào đối số đầu tiên và định nghĩa giá trị để trả về khi có lỗi trong đối số thứ hai.

Ví dụ, công thức VLOOKUP kết hợp với IFERROR sẽ 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),””)

Công thức IFERROR / VLOOKUP trả về một ô trống thay vì thông điệp lỗi.

Nếu bạn muốn hiển thị thông điệp lỗi do bạn định trước thay thế cho thông báo lỗi thông thường, bạn có thể viết nó trong dấu ngoặc kép như sau:

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),”Ồ, không tìm thấy khớp. Xin hãy thử lại!”)

Hiển thị thông điệp mà bạn định nghĩa thay thế cho lỗi VLOOKUP.

Áp dụng VLOOKUP với ISERROR

Trong các phiên bản Excel trước 2007, khi không có hàm IFERROR, bạn phải sử dụng sự kết hợp của hàm IF và ISERROR như sau:

=IF(ISERROR(VLOOKUP formula), “Thông điệp nếu có”, VLOOKUP formula)

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

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

Siêu Marketing mong rằng với những hướng dẫn này, bạn sẽ có thể giải quyết các vấn đề thường gặp khi sử dụng hàm Vlookup.

Trương Thành Tài

    [submission_id id-lien-he]

    0
      0
      Đơn hàng
      Đơn hàng trốngQuay lại Shop