Hàm Vlookup là một hàm rất thông dụng trong Excel. Tuy nhiên, khi sử dụng hàm này thì người dùng không chú ý sẽ dễ gặp một sổ lỗi cơ bản. Dưới đây là danh sách chi tiết các lỗi thường gặp khi sử dụng hàm Excel Vlookup, cũng như cách sửa lỗi.
Các lỗi phổ biến thường gặp khi sử dụng hàm Vlookup
1. Lỗi #N/A
Lỗi xảy ra nếu hàm Vlookup không tìm thấy lookup_value phù hợp.
Nguyên nhân gây ra lỗi phụ thuộc vào đối số [range_lookup] được cung cấp:
- Nếu [range_lookup] = TRUE [hoặc bỏ qua] - lỗi #N/A được tạo ra nếu giá trị nhỏ nhất trong cột bên tay trái của table_array lớn hơn lookup_value được cung cấp.
Lỗi # N/A cũng có thể xuất hiện trong trường hợp nếu cột bên trái của table_array không theo thứ tự tăng dần.
- Nếu [range_lookup] = FALSE - lỗi #N/A có thể là do lookup_value không được tìm thấy trong cột đầu tiên của table_array.
Nếu vẫn không hiểu lý do tại sao lại bị lỗi hàm Vlookup khi sử dụng hàm Vlookup này, bạn hãy thử cách sửa lỗi hàm Vlookup # N/A của Taimienphi.vn nhé.
2. Lỗi #REF!
Nguyên nhân : Đối số col_index_num được cung cấp lớn hơn số trong cột table_array được cung cấp.
Hoặc:
Công thức cố gắng tham chiếu các ô không tồn tại. Điều này có thể do lỗi tham chiếu khi Vlookup được sao chép vào các ô khác.
Nếu cần trợ giúp thêm về lỗi hàm Vlookup này, bạn có thể truy cập trang Vlookup #REF!
3. Lỗi #VALUE!
Nguyên nhân : Đối số col_index_num được cung cấp nhỏ hơn 1 hoặc không phải là số
Hoặc:
Đối số [range_lookup] được cung cấp là giá trị không phải là số và không bằng TRUE hoặc FALSE.
4. Lỗi các giá trị giống nhau nhưng Excel nghĩ khác
Để kiểm tra nhanh xem nếu 2 giá trị bằng nhau, sử dụng toán tử “=”. Trong ví dụ này thêm một dấu cách sau “Chocolate” trong ô D2. Vì vậy mặc dù giá trị trong ô A2 và D2 trông có vẻ giống nhau, đều là “Chocolate”, nhưng giá trị thực sự trong ô D2 là “Chocolate “. Vì thế mà giá trị 2 ô không bằng nhau.
Cách kiểm tra: Nếu nghi ngờ, chỉ cần sử dụng toán tử = để nhanh chóng kiểm tra xem các giá trị có bằng nhau không. Giá trị trả về là TRUE, tức là Excel cho rằng các giá trị đó bằng nhau, và cho phép hàm Vlookup làm việc.
5. Bạn đã chọn toàn bộ bảng?
Kiểm tra xem tham số thứ 2, table_array, đã chọn toàn bộ bảng tra cứu. Trong ví dụ náy, bỏ qua giá trị cuối cùng “Words” trong bảng. Rõ rang khi cố gắng tìm kiếm từ “Words”, hàm Vlookup sẽ trả lại thông báo lỗi nói rằng “Words” không phải là một phần của table_array.
Cách kiểm tra: Nhấn F2 hoặc tìm trong thanh công thức để xem nếu hàm Vlookup đang tìm kiếm toàn bộ bảng.
Mẹo khác: Nếu có nhiều ô Vlookup mà bạn đã sao chép và dán, thử kiểm tra ít nhất một vài trong số các ô đó để xem các tham chiếu ô đã chuyển. Nếu muốn khóa tham chiếu table_array [trong ví dụ này là A2: A14], làm nổi bật tham chiếu bằng cách nhấn F4 một lần nữa để nhận $A$2:$A$14.
6. Tìm kiếm cột không tồn tại
Kiểm tra tham số thứ 3 để đảm bảo rằng Vlookup đang tìm kiếm cột bên phải. Trong ví dụ này, table_array chỉ có 1 cột. Nhưng tham số thứ 3 trong công thức [col_index_num] là 2, tức là Vlookup cần lấy cột thứ 2.
Vì Vlookup không được cung cấp trong cột thứ 2, nó sẽ trả lại thông báo lỗi. Để mọi thứ trở nên dễ dàng hơn, nó sẽ trả về lỗi #REF thay vì trả về lỗi #N/A điển hình.
Cách kiểm tra: Nhấn F2 hoặc tìm trong thanh công thức để xem bảng mà bạn đã chọn có số cột mà bạn đang yêu cầu hay không. Lưu ý rằng đâylà lỗi duy nhất trả về lỗi #REF. Tức là nếu nhận được thông báo lỗi #REF, bạn đã có thể xác định được vấn đề.
7. Muốn tìm một lỗi, nhưng Excel tìm thấy lỗi khác
Điều này xảy ra khi bạn cho phép Vlookup trả lại một giá trị không chính xác, bằng cách cung cấp giá trị TRUE cho tham số thứ 4, range_lookup. Về cơ bản, nếu để trống tham số, hoặc nhập FALSE, Vlookup sẽ trả về một lỗi. Nếu bạn nhập TRUE, nó sẽ trả về giá trị chính xác, hoặc nếu giá trị chính xác không tồn tại, nó sẽ trả lại giá trị lớn nhất sau lookup_value, giả sử bảng tìm kiếm [table_array] được sắp xếp theo thứ tự tăng dần.
Cách kiểm tra: Nhấn F2 hoặc tìm trong thanh công thức. Đảm bảo bạn nhập giá trị FALSE hoặc để trống tham số cuối. Nếu muốn lấy giá trị lớn nhất tiếp theo, nhớ sắp xếp cột bên trái của bảng tra cứu theo thứ tự tăng dần.
8. Lỗi Circular References
Nếu không may gặp phải lỗi circular references giữa các bảng tìm kiếm [lookup table], giá trị tìm kiếm [lookup value] và Vlookup, đó là do lỗi công thức.
Khi bạn vô tình có tài liệu tham khảo thông tư giữa bảng tra cứu, giá trị tra cứu và vlookup, đó chỉ là công thức cho thảm hoạ [nếu bạn không biết bạn đang làm gì].
Cách kiểm tra: Tắt circular reference. Nếu có circular reference, ngay lập tức Excel sẽ trả lại lỗi. Chỉnh sửa theo cách thoát circular reference, sau đó bật lại nếu cần.
9. Lỗi Double Entry
Nếu chẳng may có môt giá trị trong cột tìm kiếm được nhập 2 lần [hoặc nhiều hơn] nhưng bạn không nhận ra. Hàm Vlookup luôn luôn tìm kiếm giá trị đầu tiên và bỏ qua phần còn lại. Vì các giá trị là chính xác, nên Excel đang chọn “sai giá trị”.
Cách kiểm tra: Kiểm tra nhanh xem có giá trị nào mà bạn đang tìm kiếm đã được nhập vào trước đó.
10. Lỗi Floating Point Bug
Đôi khi bạn có thể tìm kiếm cột có chứa các con số được tính. Có những thời điểm mà Vlookup không nhận ra giá trị vì lỗi floating point bug. Cách tốt nhất là “làm sạch” các giá trị trong cột khi bị lỗi thường gặp khi sử dụng hàm vlookup này là bằng cách sử dụng hàm Round. Ví dụ:
=ROUND[[value],2]
Cách kiểm tra: Cách này chỉ áp dụng cho các cột Vlookup có các giá trị được tính. Đôi khi trong một số trường hợp nếu kiểm tra nhưng không phát hiện được vấn đề, nhưng Vlookup vẫn không hoạt động.
Trong trường hợp đó, chỉ cần sử dụng hàm Round để kiểm tra xem Vlookup đã hoạt động chưa. Chẳng hạn như ví dụ dưới đây:
Nắm được các lỗi thường gặp khi sử dụng hàm Vlookup cũng như cách sửa lỗi giúp bạn sử dụng Excel một cách chuyên nghiệp hơn.
Vlookup là một trong những hàm Excel phổ biến nhất. Mặc dù là hàm đơn giản nhưng khá nhiều người dùng gặp phải các vấn đề đau đầu khi sử dụng hàm. Trong bài viết này Taimienphi.vn sẽ tổng hợp và giới thiệu cho bạn các lỗi thường gặp khi sử dụng hàm Vlookup cũng như cách sửa lỗi
Cách sửa lỗi #N/A trong Excel Lỗi hàm Vlookup, sửa lỗi #N/A khi dùng hàm Vlookup trong Excel Sử dụng 2 hàm VLOOKUP để tính toán nhanh hơn Cách sử dụng hàm Vlookup trong Excel, cú pháp và ví dụ Cách sử dụng kết hợp hàm Vlookup với hàm Left Lỗi thường gặp khi sử dụng hàm SUMIF trong Excel
Trong quá trình làm việc với bảng tính Excel, đôi khi bạn gặp phải tình huống Excel không nhảy công thức. Mặc dù bạn đã lập công thức đúng 100% nhưng khi Enter thì nó không hiển thị kết quả mà nó hiển thị nội dung của công thức hoặc khi bạn lập được công thức tính toán xong trên một ô sau đó bạn tiến hành kéo công thức này để áp dụng cho nhiều dữ liệu thì nó không tự động tính toán cho bạn? chẳng lẽ phải nhập công thức cho từng ô dữ liệu? Vấn đề tương đối đơn giản, Dân Tài Chính sẽ hướng dẫn bạn cách khắc phục trong một nốt nhạc :-]. Nguyên do không cập nhập kết quả tính toán là một trong các nguyên nhân sau:
1. Bạn đã nhập công thức vào ô có định dạnh Text
Trong hình trên ta thấy ô B5 có định dạng là Text nên công thức không thể tự động tính được. Cách khắc phục là bạn chuyển định dạng của ô B5 sang General, Number hoặc bất cứ dạng nào khác Text là được. Sau đó nhập lại công thức hoặc nhấn F2 và Enter.
2. Excel đang ở chế độ chỉ hiển thị công thức
Đây là 1 chế độ của Excel, được thiết lập trong mục Options/Advanced/Show formulas in cells instead of their calculated results [hoặc phím tắt ctrl + `]
Ở chế độ này toàn bộ các công thức trong 1 sheet sẽ chỉ hiển thị nội dung công thức mà không hiển thị kết quả.Khi bạn viết công thức thì cũng chỉ trả về nội dung công thức chứ không trả về kết quả.Cách khắc phục:– Tắt tính năng này bằng cách bỏ dấu tích trong mục Show formulas in cells instead of their calculated results
– Hoặc bạn có thể bấm phím tắt Ctrl + ` một lần nữa để tắt tính năng chỉ hiển thị công thức
Cách sửa lỗi NA trong Excel hiệu quả, có ví dụ minh họa
Le Linh 01/11/2021
Lỗi #N/A! là lỗi thường gặp trong Excel và nếu bạn chưa biết cách khắc phục. Hãy cùng theo dõi bài viết sau để hiểu rõ hơn về lỗi #N/A! cũng như cách sửa khi gặp phải lỗi này nhé.
Hướng dẫn được thực hiện trên laptop hệ điều hành Windows 10 với phiên bản Excel 2013. Bạn cũng có thể thực hiện tương tự trên các sản phẩm Excel 2007, 2010, 2013, 2019 và trên các dòng laptop có cùng hệ điều hành hay dòng MacOS có cùng thao tác khác.
I. Lỗi #N/A! trong Excel là gì?
N/A trong tiếng anh có thể là [Not Available] hoặc [No Answer] hoặc [Not Applicable]. Cả 3 từ trên đều có thể hiểu là không sẵn có hoặc không thể áp dụng.
Hay có thể hiểu lỗi #N/A nghĩa là trong công thức của bạn có chứa nội dung không có sẵn trong vùng dữ liệu thuộc Excel, dẫn đến việc tính toán, hoàn thành công thức sẽ không có kết quả như ý muốn.
1. Ví dụ về lỗi #N/A! trong Excel
#N/A! trong Excel là lỗi được trả về khi công thức không tìm thấy giá trị.
2. Một số hàm dễ mắc phải lỗi #N/A! trong Excel
Các hàm có thể dễ mắc lỗi #N/A! như: VLOOKUP, HLOOKUP, IF,...
II. Nguyên nhân gây lỗi #N/A! và cách khắc phục
1. Lỗi #N/A! do vùng tìm kiếm không đầy đủ
Nguyên nhân: Do hàm tính không tìm thấy giá trị. Nghĩa là trong vùng tìm kiếm bạn nhập vào không có dữ liệu để truy suất.
Ví dụ: Tìm sản phẩm Xoài trong bảng dữ liệu sau:
Bước 1: Mình nhập hàm =VLOOKUP["XOÀI",A2:D6,4,0] vào bảng tính Excel.
Bước 2: Sau đó bạn nhấn phím Enter. Vì bảng dữ liệu không có tên cần tìm nên Excel sẽ xuất hiện lỗi #N/A! như hình.
Cách khắc phục: Kiểm tra dữ liệu tính toán của hàm và chỉnh sửa lại. Ở đây mình chỉnh lại sản phẩm cần tìm là Sầu Riêng với công thức =VLOOKUP["Sầu Riêng",B2:D6,2,0] nên Excel trả kết quả như hình.
2. Lỗi #N/A! do không cố định vùng tìm kiếm khi copy công thức
Nguyên nhân: Khi không cố định vùng tìm kiếm thì công thức copy trong Excel sẽ tự động lấy dữ liệu từ ô đầu tiên xuống cho các ô còn lại thì vùng tìm kiếm sẽ thay đổi theo.
Ví dụ: Như bảng minh họa trên mình sử dụng công thức để tìm sản phẩm Sầu Riêng nhưng không cố định vùng dữ liệu. Khi mình copy xuống dưới thì Excel sẽ báo lỗi như hình.
Cách khắc phục: Để khắc phục lỗi này cần cố định lại vùng dữ liệu trong công thức.
Bước 1: Trong vùng dữ liệu chọn ở công thức tính. Bạn bôi đen vùng dữ liệu đó. Ở đây mình chọn bôi đen ô B2:D6 như hình.
Bước 2: Trên bàn phím bạn ấn phím F4. Excel sẽ tự động cố định vùng dữ liệu được chọn như hình. Sau đó bạn có thể copy mà không sợ dữ liệu bị thay đổi.
3. Lỗi #N/A! khi sử dụng hàm Vlookup, Hlookup kết hợp với MID, LEFT, RIGHT
Trường hợp 1: Đối với hàm MID.
Nguyên nhân: Thường gặp lỗi #N/A! khi định dạng không trùng khớp giữa kết quả hiển thị và dữ liệu tra cứu.
Ví dụ: Dùng hàm Hlookup để tìm điểm khu vực dựa và số báo danh của sinh viên.
Bước 1: Trong bảng tính mình nhập hàm =HLOOKUP[MID[A3,4,1],$C$9:$F$10,2,0] như hình.
Trong đó:
- MID[A3,4,1]: Lấy 1 kí tự thứ 4 ở ô A3 [cụ thể là số 1].
- $C$9:$F$10: Bảng chứa khu vực thi và điểm khu vực.
- 2: Lấy điểm khu vực.
- 0: Lấy giá trị chính xác tuyệt đối.
Bước 2: Minh nhấn phím Enter thì Excel báo lỗi như hình.
Cách khắc phục: Có 2 cách khắc phục lỗi do nguyên nhân này gây ra là:
- Chuyển kết quả của hàm Mid về dạng số bằng hàm Value.
- Định dạng trùng khớp giữa dữ liệu tra cứu và kết quả hiển thị.
Ở đây, mình sử dụng cách hàm Value để xử lý lỗi trên. Bằng cách thêm hàm Value vào như sau =HLOOKUP[VALUE[MID[A3,4,1]],$C$9:$F$10,2,0].
Sau đó bạn ấn phím Enter. Excel sẽ hiển thị kết quả như hình.
Trường hợp 2: Đối với hàm LEFT.
Ví dụ: Điền tên hãng vào mã lô hàng tương ứng.
Bước 1: Mình nhập vào bảng tính Excel công thức hàm Vlookup: =VLOOKUP[LEFT[B3,3],E8:F11,2,0] như hình.
Giải thích hàm:
- VLOOKUP: Là tên hàm tính toán dò tìm dữ liệu.
- LEFT[A3,3]: Là lệnh hàm tìm dữ liệu ô A3 với 3 ký tự.
- E8:F11: Là vùng dữ liệu tìm kiếm.
Bước 2: Bạn ấn phím Enter. Excel sẽ hiển thị kết quả như hình.
Cách khắc phục: Để khắc phục lỗi này bạn kiểm tra và chỉnh lại giá trị trong hàm LEFT vì ô bảng tính chỉ chứa 2 ký tự mà ở đây mình lấy giá trị bằng 3 như sau =VLOOKUP[LEFT[A3,2],HANGSANXUAT,2,0]. Sau khi bạn sửa lại giá trị thì Excel hiển thị kết quả như hình.
Trường hợp 3: Đối với hàm RIGHT.
Ví dụ: Điền tên hãng vào mã lô hàng tương ứng.
Bước 1: Mình nhập vào bảng tính Excel công thức hàm Vlookup: =VLOOKUP[RIGHT[A3,2],D7:E11,2,0] như hình.
Trong đó:
- RIGHT[A3,2]: Lấy 2 kí tự ở bên phải của ô A3 [cụ thể ở đây là SS].
- D7:E11: Vị trí của BẢNG THÔNG TIN chứa mã hàng sản xuất.
- 2: Vị trí lấy ở cột tên hãng.
- 0: Chọn kiểu giá trị chính xác tuyệt đối.
Bước 2: Bạn nhấn phím Enter. Excel sẽ hiển thị kết quả như hình.
Ở đây mình đã kiểm tra giá trị cần lấy và hàm tính toàn là đúng vậy mà Excel vẫn báo lỗi #N/A!. Nguyên nhân là vì trong điều kiện cần lấy ở bảng thông tin có chứa khoảng trắng.
Cách khắc phục: Ở đây mình kiểm tra và xóa bỏ khoảng trắng trong mã hàng SX ở bảng thông tin và Excel hiển thị kết quả như hình.
4. Tại sao lỗi #N/A vẫn xảy ra khi có điều kiện tìm kiếm trong vùng điều kiện
Nguyên nhân: Lỗi #N/A xuất hiện ở trường hợp này là do trong điều kiện tìm kiếm hoặc giá trị tương ứng trong vùng điều kiện tìm kiếm có thừa một hoặc một số dấu cách ở đằng sau.
Cách khắc phục:
- 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.
- Nhấn phím F2 nếu thấy dấu nháy chuột nằm cách xa ký tự cuối cùng một hoặc một vài dấu cách thì bạn xóa các dấu cách thừa đi. Làm như vậy cho cả 2 giá trị.
5. Dùng hàm ISNA để sửa lỗi #N/A! trong Excel
Đối với lỗi này thì bạn cũng có thể sử dụng hàm ISNA để sửa lỗi 1 cách nhanh, dễ dàng chỉ với thao tác lồng hàm ISNA vào.
Trên đây là bài viết về lỗi #N/A! trong Excel và những cách sửa lỗi #N/A! trong Excel. Hy vọng bài viết sẽ giúp bạn thực hiện được hiểu rõ hơn về lỗi này và nếu bạn có góp ý hãy để lại bình luận bên dưới và đứng quên chia sẻ nếu thấy hữu ích bạn nhé.
7.357 lượt xem