CÁCH KHẮC PHỤC VÀ SỬA LỖI HÀM VLOOKUP KHÔNG HOẠT ĐỘNG

CÁCH KHẮC PHỤC VÀ SỬA LỖI HÀM VLOOKUP KHÔNG HOẠT ĐỘNG

Bài viết dưới đây, Học Excel Online sẽ hướng dẫn những bạn xử lý sự cố hàm Vlookup ko hoạt động trong Excel 2016, 2013, 2010, 2007 và 2003. Ngoài ra, Học Excel Online sẽ hướng dẫn cách khắc phục những lỗi thường gặp hàm Vlookup như #N/A, #NAME VÀ #VALUE và những hạn chếcủa Vlookup. Trong những bài viết trước chúng ta đã tìm hiểu những khía cạnh khác nhau của hàm Vlookup trong Excel. Tuy nhiên hàm Vlookup vẫn còn rất nhiều sự phức tạp và hạn chế nên sở hữu rất nhiều vấn đề về lỗi khác nhau. Trong bài viết này sẽ giảng giải thuần tuý về những thông tin lỗi #N/A, #NAME VÀ #VALUE của VLOOKUP cũng như những giải pháp để xử lý. 

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

Trong công thức hàm Vlookup, thông tin lỗi #N/A (tức là Not Available – “ko sở hữu sẵn”) được hiển thị lúc Excel ko thể tìm thấy trị giá tra cứu. Mang thể xuất phát bởi một số lý do sau:

1. Lỗi đánh máy hoặc sắp xếp sai trị giá tra cứu

Lỗi xếp nhầm chữ thường xảy ra lúc bạn làm việc với lượng dữ liệu to bao gồm hàng nghìn dòng hoặc lúc trị giá tra cứu được gõ trực tiếp vào công thức.

2. Lỗi # N/A trong tìm kiếm sắp đúng bằng hàm VLOOKUP

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

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

3. Lỗi # N/A trong tìm kiếm chuẩn xác hàm VLOOKUP

Nếu bạn đang tìm kiếm với đối sánh chuẩn xác (đối số range_lookup được đặt thành FALSE) và ko tìm thấy trị giá chuẩn xác, thì lỗi #N/A cũng được trả về.

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

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

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

Giải pháp: Nếu ko thể tái cấu trúc dữ liệu để cột tra cứu là cột bên trái, bạn sở hữu thể sử dụng kết hợp những 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 sử dụng hàm INDEX / MATCH để tra cứu những trị giá bên trái.

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

Một lỗi N/A khác của hàm VLOOKUP là do những con số được định dạng dưới dạng văn bản, bảng chính hoặc bảng tra cứu.  Điều này thường xảy ra lúc bạn nhập dữ liệu từ một cơ sở vật chất dữ liệu bên ngoài hoặc do bạn đã gõ dấu nháy đơn trước số.

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

Những số cũng sở hữu thể được lưu trữ ở định dạng Chung. Trong trường hợp này, sở hữu một tín hiệu đáng chú ý – những số được căn chỉnh ở phía bên 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.

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

Nếu nhiều số bị tác độ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.

6. Những khoảng trống to hoặc dấu đầu dòng

Đây là nguyên nhân khó phát hiện  nhất vì mắt thường khó sở hữu thể phát hiện ra những khoảng trống thừa đó, đặc thù là lúc làm việc với những bảng to nơi hầu hết những mục nhập nằm dưới cuộn. Khoảng trong trắng cột dò tìm (với công thức VLOOKUP) Nếu những khoảng trống dư thừa xuất hiện trong bảng chính của bạn, bạn sở hữu thể sử dụng công thức Vlookup bằng cách lồng đối số lookup_value với hàm TRIM: =VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)

Lỗi khoảng trong trắng cột dò tìm

Khoảng trong trắng cột tra cứu Nếu những khoảng trắng thừa xuất hiện trong cột tra cứu, ko sở hữu cách nào để tránh lỗi VLOOKUP #N/A. Thay vì hàm VLOOKUP, bạn sở hữu thể sử dụng công thức mảng với sự kết hợp của 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:

Lỗi khoảng trong trắng cột tra cứu

Lỗi #VALUE trong công thức VLOOKUP

Microsoft Excel hiển thị lỗi #VALUE! lỗi nếu một trị giá được trong công thức ko đúng. Đối với hàm VLOOKUP, lỗi #VALUE thường mắc phải do Hai lý do sau đây.

  • Trị giá tra cứu vượt quá 255 ký tự

Nên nhớ VLOOKUP ko thể tra cứu những trị giá chứa 256 ký tự trở lên. Nếu những trị giá tra cứu của bạn vượt quá giới hạn này thì sẽ xuất hiện lỗi VALUE:

Lỗi #VALUE trong công thức VLOOKUP

Giải pháp : Sử dụng công thức INDEX / MATCH tương tự để thay thế. Trong ví dụ trên, hàm INDEX / MATCH sau đây hoạt động lý tưởng: =INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7= F$2,0),0))

Giải pháp lỗi #VALUE trong công thức VLOOKUP

 

  • Ko phân phối đườ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 đủ tới tệp đó. Chuẩn 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 cũng cần phải đặt chúng trong dấu nháy đơn trong trường hợp tệp hoặc tên bảng tính sở hữu chứa khoảng trống. Đây là cấu trúc của công thức hoàn chỉnh để thực hiện vlookup từ một sổ làm việc khác: =VLOOKUP(lookup_value, ‘[workbook name]sheet name’!table_array, col_index_num, FALSE) Một công thức thực sự sở hữu 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 trị giá ô A2 trong cột B của Sheet1 tệp “New Prices” và trả về một trị giá thích 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ẽ ko hoạt động và trả lại lỗi #VALUE (trừ lúc bảng tính tra cứu hiện đang mở).

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

Khó sở hữu trường hợp mà nhập một số nhỏ hơn “1” để xác định cột trị giá trả về. Tuy nhiên, nó sở hữu thể xảy ra nếu đối số này được trả về bởi một số hàm Excel khác được lồng trong 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 to hơn số cột trong mảng bảng được chỉ định, công thức Vlookup trả về lỗi #REF! lỗi.

Lỗi VLOOKUP #NAME

Đâ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 rà soát chính tả:)

VLOOKUP ko hoạt động (hạn chế, sự cố 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 sở hữu 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ả ko như mong đợi. Dưới đây bạn sẽ tìm thấy những giải pháp cho một vài tình huống phổ biến lúc VLOOKUP ko thành công.

Article post on: nongdanmo.com

  • Vlookup ko phân biệt chữ hoa chữ thường

Hàm VLOOKUP ko phân biệt chữ hoa và chữ thường. Vì vậy, nếu bảng của bạn sở hữu nhiều mục tương tự chỉ khác nhau chữ hoa, chữ thường, công thức Vlookup sẽ trả về trị giá được tìm thấy trước 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 sở hữu 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 để sở hữu thể tương trợ bạn tra cứu. Bạn sở hữu thể tìm thấy những giảng giải 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ề trị giá tìm thấy trước tiên

Như bạn đã biết, hàm VLOOKUP trả về trị giá trước tiên nó tìm thấy trong cột trả về thích hợp với trị giá tra cứu. Tuy nhiên, bạn sở hữu thể buộc nó trả về chiếc 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ả những trị giá, bạn sẽ phải sử dụng kết hợp những hàm INDEX, SMALL và ROW. Giải pháp: Những ví dụ công thức sở hữu sẵn để tải về tại đây:

  • Nhận diện 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 trị giá tra cứu.
  • Một cột mới được chèn vào hoặc gỡ bỏ khỏi hàng

Những công thức VLOOKUP sẽ ko hoạt động lúc 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 phân phối 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 lúc bạn xóa một cột hiện sở hữu hoặc chèn một cột mới. INDEX / MATCH được sử dụng để khắc phục vấn đề này. Trong hàm INDEX & MATCH, bạn chỉ định những cột tra cứu và cột trả kết quả riêng biệt, kết quả là bạn sở hữu thể xóa hoặc chèn nhiều cột tùy ý mà ko cần phải cập nhật mọi công thức vlookup liên quan .

  • Những ô tham chiếu thay đổi lúc sao chép công thức tới ô 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 sở hữu thể nhanh chóng chuyển đổi giữa những 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 ko muốn để người sử dụng thấy tất cả những thông tin lỗi N/A, VALUE hoặc NAME, bạn sở hữu thể trả lại ô trống hoặc hiển thị nội dung của riêng bạn. Bạn sở hữu 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 thuần tuý: IFERROR (trị giá, giá trị_if_error) Bạn tiến hành nhập trị giá để rà soát lỗi trong đối số thứ nhất, và trong đối số thứ Hai bạn chỉ định trị giá trả về nếu lỗi xảy ra.  Ví dụ, công thức IFERROR / VLOOKUP trả về một ô trống lúc ko tìm thấy trị giá 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 sở hữu ở phiên bản Excel 2007, trong những 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 kỳ vọng thông qua những hướng dẫn trên bạn sở hữu thể xử lý tất cả những lỗi sở hữu thể xảy ra của hàm Vlookup. 


--- Cập nhật: 29-01-2023 --- nongdanmo.com tìm được thêm bài viết 6 Lỗi khiến hàm VLOOKUP không hoạt động trong Excel từ website www.ketoan.vn cho từ khoá lỗi hàm vlookup ko chạy.

VLOOKUP làm một trong những hàm phổ biến nhất trong Excel vì sự tiện lợi và hữu ích. Tuy nhiên sở hữu rất nhiều lỗi khiến cho cho hàm VLOOKUP ko thể hoạt động và khiến cho người sử dụng đau đầu tìm cách sửa chữa. Ketoan.vn sẽ chỉ ra 6 lỗi làm hàm VLOOKUP ko hoạt động và cách khắc phục những lỗi này nhé.

Source: nongdanmo.com

1. Mở rộng bảng

Như trong hình dưới, những bạn sở hữu thể thấy do bảng tìm kiếm ở dưới đã được mở rộng thêm trị giá tìm kiếm. Tuy nhiên hàm VLOOKUP chưa được sửa nên vẫn tham chiếu tới vùng dữ liệu cũ.

Để khắc phục vấn đề này thì những bạn cần chỉnh sửa lại vùng tham chiếu của hàm VLOOKUP tới toàn bộ bảng mới sau lúc đã được mở rộng như hình dưới.

2. Chèn thêm cột

Một lý do khiến cho hàm VLOOKUP ko thể hoạt động phổ biến nữa đó chính là chèn thêm cột. Như trong hình những bạn sở hữu thể thấy đối số col_index_num trong hàm vẫn là 2. Tuy nhiên bảng tham chiếu đã bị chèn thêm cột vào giữa nên cột tham chiếu đó phải là 3 mới đúng.

Để khắc phục vấn đề này thì những bạn chỉ cần sửa lại đối số col_index_num cho đúng. Như trong trường hợp này thì chỉ cần sửa đối số col_index_num thành 3 là hàm sở hữu thể hoạt động một cách thông thường.

3. Kết quả tìm kiếm sắp đúng

Hàm VLOOKUP sở hữu Hai trị giá của đối số range_lookup đó là:

Via @: nongdanmo.com

  • TRUE: Tham chiếu tới kết quả sắp đúng nhất
  • FALSE: Tham chiếu tới kết quả chuẩn xác

Trong nhiều trường hợp những bạn cần tham chiếu tới kết quả chuẩn xác nhưng lại bỏ qua đối số range_lookup thì hàm sẽ mặc định là TRUE. Vì thế hàm sẽ trả về kết quả sắp đúng nhất chứ ko phải kết quả chuẩn xác. Để khắc phục thì những bạn cần nhập đối số range_lookup là FALSE nếu muốn tham chiếu tới kết quả chuẩn xác.

4. Nhất quyết vùng dữ liệu

Bảng dữ liệu tra cứu được nhập vào thông số table_array cần được nhất thiết để thuận tiện cho việc sao chép hàm VLOOKUP. Như trong hình dưới do đối số table_array ko được nhất thiết nên lúc sao chép xuống những ô bên dưới thì bảng tham chiếu cũng đã bị thay đổi theo.

Để khắc phục vấn đề này thì những bạn chỉ cần nhấn F4 để nhất thiết bảng tham chiếu tại đối số table_array.

5. Hàm VLOOKUP ko thể tìm kiếm những trị giá bên trái của bảng tham chiếu

Một hạn chế của hàm VLOOKUP là nó ko thể tìm kiếm những trị giá bên trái. Như trong hình bên dưới thì cột cần tìm là cột thuế suất ở bên trái của cột tham chiếu là cột loại hàng. Do vậy hàm VLOOKUP sẽ ko thể tìm thấy được kết quả và trả về trị giá lỗi.

Để khắc phục vấn đề này thì những bạn cần chỉnh sửa bảng tham chiếu sao cho cột tìm kiếm ở bên phải cột tham chiếu nhé.

6. Bảng tham chiếu chứa những trị giá bị trùng

Hàm VLOOKUP chỉ sở hữu thể trả về bảng trước tiên thích hợp với trị giá bạn đã tìm kiếm. Nếu bảng của bạn chứa những trị giá bị trùng thì hàm VLOOKUP sẽ ko hoạt động. Như trong hình dưới do bảng tham chiếu chứa Hai trị giá Đất nên hàm VLOOKUP sẽ trả về trị giá lỗi. Để khắc phục thì những bạn cần rà soát lại bảng tìm kiếm sao cho ko sở hữu trị giá bị trùng.

Tương tự, bài viết trên đã chỉ ra 6 lỗi làm hàm VLOOKUP ko hoạt động và cách khắc phục những lỗi này. Kỳ vọng bài viết sẽ hữu ích với những bạn trong quá trình làm việc. Chúc những bạn thành công!

Xem thêm

Sửa lỗi #VALUE! lúc sử dụng hàm VLOOKUP trong Excel

Cách sử dụng hàm VLOOKUP giữa Hai Sheet, Hai file Excel khác nhau

Cách khắc phục nhanh lỗi #N/A lúc sử dụng hàm VLOOKUP

Cách dễ nhất để phân biệt hai hàm VLOOKUP và HLOOKUP

Cách sử dụng hàm VLOOKUP lồng vào hàm VLOOKUP trong Excel

Article post on: nongdanmo.com

Recommended For You

About the Author: Bảo