Bạn có đang cặm cụi luyện tập hàm VLOOKUP không?
Bạn đã bao giờ thấy khó chịu khi đã nhập đúng cú pháp hàm VLOOKUP rồi mà kết quả vẫn báo là #N/A [Not Available], không có thông tin để hiển thị không?
Trong bài viết này, SAPP sẽ chỉ ra 3 lỗi phổ biến khi sử dụng hàm VLOOKUP và cách sửa chúng nhé.
Lỗi 1: Sai Định Dạng [Định Dạng Text [Kiểu Chữ] Cho Các Số]
Cách giải quyết:
+ Cách 1: Sử dụng hàm Text.
+ Cách 2: Nhân tất cả các giá trị tra cứu [lookup values] với 1.
Cách thực hiện:
Giả sử cột chứa giá trị Lookup_value [Cột C] chứa giá trị lưu dạng số còn cột đầu tiên của lookup_array [Cột A] ở dạng text. Ta làm như sau:
+ Cách 1: Ta chèn thêm một Hàm Text để định dạng lại giá trị của cột lookup_value [Cột A] thành dạng text để chúng ăn khớp với định dạng của cột A [Dạng text]
E2=Vlookup[Text[C2,0],$A$2:$D$10,4,0]
Sau đó kéo công thức ô E2 xuống đến ô E10, ta sẽ có được giá trị cần tham chiếu về.
Lưu ý: Hàm Text chỉ nhận tham số Reference là một ô [1 cell] nên ta phải chuyển tham số lookup_Value ở công thức ban đầu thành tham chiếu 1 ô [ô C2], nếu không, hàm sẽ trả về #N/A!
+ Cách 2: Nhân thêm 1 đơn vị và chuyển giá trị sang cột khác.
Cũng với trường hợp trên, thay vì chèn thêm Hàm Text thì bạn cũng có thể chuyển cột chứa số ở dạng text bằng cách lấy giá trị của ô đó nhân cho 1.
Ví dụ: cột A đang ở dạng text ta có thể chuyển nó thành dạng số vào cột B như sau:
B2=A2*1
sau đó kéo xuống cho đến hết ô B10. Lúc này công thức của hàm Vlookup ở ô E2 [ví dụ] như sau:
E2=Vlookup[B2:B10,$B$2:$D$10,3,0]
Tuy nhiên nếu một ô nào đó trong cột A có chứa giá trị là ký tự [Chữ a chẳng hạn] chứ không phải là số ở dạng chữ, bạn nhân cho 1 đơn vị thì kết quả cũng sẽ bị lỗi. Và dĩ nhiên Kết quả trả về ở ô E2 cũng sẽ là #N/A!
Lỗi 2: Thừa Dấu Cách Ở Cuối Cùng Của Điều Kiện Tìm Kiếm
Trong ví dụ dưới đây, nếu nhìn qua, hàm VLOOKUP có vẻ như là đúng và sẽ hoạt động. Nhưng tại sao không có giá trị kết quả nào được hiển thị?
Câu trả lời đó là: bạn hãy nhấn vào một ô bất kỳ trong giá trị tìm kiếm [lookup values]. Bạn sẽ phát hiện ra: có một [hoặc vài] dấu cách ở cuối cùng của ô đó.
Lỗi này thường xảy ra khi bạn lấy dữ liệu từ nhiều hệ thống nguồn khác nhau hoặc thông qua logic lập trình thiếu sót mà bỏ qua việc loại bỏ dấu cách.
Cách giải quyết:
Cách đơn giản nhất để khắc phục lỗi này là bạn chọn lần lượt từng giá trị: giá trị dùng làm điều kiện và giá trị tương ứng trong vùng điều kiện và xóa triệt để các dấu cách.
Bạn cũng có thể sử dụng Data – Text to Columns để xóa tất cả dấu cách.
Lỗi 3: Không Khóa Mảng Tham Chiếu Tìm Kiếm
Thông thường, hiếm có ai dùng hàm VLOOKUP chỉ để tìm kiếm 1 giá trị. Nếu bạn thêm 1 trường vào bảng dữ liệu hoặc tìm kiếm dựa vào giá trị, bạn sẽ cần kéo xuống các ô dưới để sao chép công thức. Khi bạn nháy đúp chuột vào ô kết quả bất kỳ thì bạn có thể nhận thấy chỉ có một nửa công thức hoạt động.
Nhìn vào ví dụ dưới đây, do không khóa mảng nên giá trị tìm kiếm [lookup values] không thể ghép tương ứng với giá trị ở mảng tìm kiểm [lookup arrays], từ đó gây ra lỗi #N/A.
Cách giải quyết:
Sử dụng phím F4 để khóa mảng tham chiếu trước khi bạn kéo công thức xuống để copy cho các ô ở sau. Khi sử dụng F4, hiển thị của mảng dữ liệu được chọn sẽ được cố định và có dấu $ ở trước vị trí của ô và số thứ tự của dòng, ví dụ $B$4:$C$10.
VLOOKUP là hàm tìm kiếm cực kỳ thông dụng trong Excel. Cách dùng hàm này về cơ bản không quá khó. Tuy nhiên, nếu không chú ý, người dùng rất dễ gặp phải những lỗi sai gây cản trở công việc. Muốn tránh được vấn đề này, đừng bỏ qua bài viết tổng hợp 3 lỗi hàm VLOOKUP thường gặp ngay sau đây.Mục lục
- 1 Hàm VLOOKUP là gì?
- 2 3 lỗi phổ biến khi sử dụng hàm VLOOKUP
- 2.1 Lỗi hàm VLOOKUP #N/A
- 2.1.1 Lỗi 1: Giá trị tìm kiếm không có trong cột đầu tiên của vùng bảng tra cứu table_array
- 2.1.2 Lỗi 2: Không tìm thấy giá trị cần tìm trong bảng tra cứu
- 2.2 Lỗi hàm VLOOKUP #NAME?
- 2.3 Lỗi hàm VLOOKUP #VALUE!
- 2.3.1 Lỗi 1: Giá trị cần tìm kiếm quá dài
- 2.3.2 Lỗi 2: Tham đối số chỉ mục cột lấy giá trị chứ văn bản hoặc nhỏ hơn 0
- 2.1 Lỗi hàm VLOOKUP #N/A
- 3 Tạm kết về 3 lỗi hàm VLOOKUP trong Excel phổ biến nhất
- 3.1 Comments
Hàm VLOOKUP là gì?
Hàm VLOOKUP dùng để dò tìm dữ liệu trong một bảng, một vùng dữ liệu theo hàng dọc và trả về dữ liệu theo hàng ngang tương ứng.
Công thức hàm VLOOKUP:
=VLOOKUP[lookup_value,table_array,col_index_num,[range_lookup]]
Trong đó:
● lookup_value: giá trị cần dò tìm
● table_array: bảng giá trị dò
● col_index_num: thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò
● range_lookup: phạm vi tìm kiếm, TRUE [1] là dò tìm gần chính xác, FALSE [0] là dò tìm chính xác.
3 lỗi phổ biến khi sử dụng hàm VLOOKUP
Trong quá trình sử dụng hàm VLOOKUP, tình trạng hàm VLOOKUP bị lỗi. 3 lỗi phổ biến nhất khi sử dụng hàm này chính là lỗi #N/A, lỗi #VALUE và lỗi #NAME. Hãy cùng Tinhocmos tìm hiểu đặc điểm, nguyên nhân và cách khắc phục từng lỗi nhé.
Lỗi hàm VLOOKUP #N/A
N/A trong tiếng Anh là Not Available – không tồn tại, không có sẵn. Đây là lỗi thường gặp nhất. Nó xảy ra khi hàm VLOOKUP không thể tìm được giá trị bạn đang dò tìm. Nguyên nhân chính và cách xử lý:
Lỗi 1: Giá trị tìm kiếm không có trong cột đầu tiên của vùng bảng tra cứu table_array
VD: Bài toán yêu cầu tính Thuế dựa vào bảng thuế của từng sản phẩm bên dưới như hình. Công thức chọn bảng
Để xử lý, bạn chọn lại bảng tra cứu dữ liệu sao cho cột đầu tiên bên trái chứa giá trị dò tìm là được.
Lỗi 2: Không tìm thấy giá trị cần tìm trong bảng tra cứu
Tương tự như trên, ví dụ bạn cần tra thuế của sản phẩm bao da nhưng bảng sản phẩm lại không cho, kết quả thuế sẽ trả về #N/A như hình.
Cách khắc phục duy nhất là bổ sung thông tin ở bảng phụ
> Xem thêm: Hàm VLOOKUP Trong Excel: Nên Dùng VLOOKUP, HLOOKUP Hay INDEX & MATCH?
Lỗi hàm VLOOKUP #NAME?
Lỗi này xảy ra khi bạn viết sai chính tả 1 hàm trong Excel. Để giải quyết, bạn nhấn vào ô VLOOKUP và kiểm tra thật kỹ các ký tự trong công thức. Nếu không có vấn đề nào khác, công thức sẽ hoạt động bình thường khi lỗi chính tả được sửa lại.
Như ví dụ: Do nhập sai công thức hàm LEFT thành LEFTt nên kết quả phân loại ra lỗi #NAME?
Sau khi sửa lại đúng, máy tính sẽ xếp chính xác tên sản phẩm cho bạn.
>>Xem thêm: Hướng dẫn sử dụng hàm HLOOKUP trong Excel
Lỗi hàm VLOOKUP #VALUE!
Có 2 nguyên nhân cơ bản dẫn đến lỗi #VALUE! như sau:
Lỗi 1: Giá trị cần tìm kiếm quá dài
Kích thước tối đa của giá trị tra cứu VLOOKUP là 255 ký tự. Khi nhập quá 255 ký tự, công thức VLOOKUP sẽ bị lỗi #VALUE!
Để giải quyết, bạn nên thay thế công thức VLOOKUP bằng công thức INDEX và MATCH kết hợp.
Lỗi 2: Tham đối số chỉ mục cột lấy giá trị chứ văn bản hoặc nhỏ hơn 0
Lỗi này xảy ra do tốc độ đánh máy nên nhầm lẫn thứ tự cột trong bảng tra cứu.
Bạn chỉ cần kiểm tra lại và sửa cho đúng, công thức hàm VLOOKUP sẽ hoạt động bình thường.
Tạm kết về 3 lỗi hàm VLOOKUP trong Excel phổ biến nhất
Như vậy, bài viết này của Trung tâm tin học văn phòng MOS đã giới thiệu 3 lỗi hàm VLOOKUP và cách xử lý:
- Lỗi hàm VLOOKUP #N/A.
- Lỗi hàm VLOOKUP #VALUE.
- Lỗi hàm VLOOKUP #NAME.
Hy vọng các thông tin này sẽ hữu ích với các bạn trong quá trình học tập và làm việc.
Ngoài ra, nếu công việc của bạn gắn liền với Tin học văn phòng nhưng vì quá bận rộn nên bạn không có thời gian tham gia các lớp học học trực tiếp ở trung tâm thì bạn có thể tham khảo khóa Luyện thi MOS online các môn Excel, Word, PowerPoint, và Excel AZ từ cơ bản đến nâng cao và ứng dụng của Trung tâm tin học văn phòng MOS nhé!