Những lỗi thường gặp khi viết hàm Vlookup trong Google Sheet
Làm thế nào để thực hiện một hàm Vlookup không bị lỗi trong Google Sheet? Hãy cùng Học Excel Online xem qua những trường hợp lỗi thường gặp khi làm việc với hàm VLOOKUP trong Google Sheet sau đây.
Google Sheets VLOOKUP là một trong những hàm hữu ích nhất nhưng khó nhất. Nó giúp tìm kiếm dữ liệu phù hợp trên nhiều trang tính. Tuy nhiên, có những trường hợp khi công thức của bạn có thể trả về những lỗi như #N/A, #NAME?, #VALUE!, and #REF!.
Chúng ta hãy cùng nhau thử và trả lời câu hỏi sau, “Tại sao hàm VLOOKUP không hoạt động?”
Xem nhanh
Các lỗi VLOOKUP phổ biến nhất
Lý do đầu tiên đơn giản là không có kết quả nào phù hợp trong phạm vi bạn đã khoanh vùng.
Các trường hợp khác có nghĩa là bạn đã mắc lỗi ở đâu đó trong công thức. Hãy xem xét kỹ hơn những gì có thể xảy ra.
Sai dấu phân cách
Dấu phân cách là những ký tự được sử dụng trong công thức làm dấu phân cách. Ví dụ: nếu bạn ở Vương quốc Anh hoặc Hoa Kỳ, rất có thể công thức của bạn yêu cầu dấu phẩy làm dấu phân cách:
=VLOOKUP(A2,$F$2:$G$10,2,FALSE)
Và chính ngôn ngữ bảng tính của bạn sẽ kiểm soát sự lựa chọn của dấu phân tách. Các ngôn ngữ khác nhau yêu cầu các dấu phân tách khác nhau.
Để kiểm tra ngôn ngữ của bạn trong Google Sheets, hãy chuyển đến File > Spreadsheets settings. Bạn sẽ thấy ngôn ngữ trong General Tab:
Để hiểu được phân tách hàm VLOOKUP cần dựa trên ngôn ngữ của bạn, chỉ cần bắt đầu nhập hàm. Google Sheets sẽ cho bạn biết công thức của bạn trông chính xác như thế nào và những dấu phân cách nào phải được sử dụng:
Nếu tất cả các dấu phân cách đều đúng như hàm VLOOKUP trong Google Sheet, bạn vẫn gặp lỗi, hãy kiểm tra các trường hợp có thể xảy ra khác và giải pháp của chúng bên dưới.
Dữ liệu không chính xác
Công thức không tìm thấy gì và trả về lỗi #N/A?
Giải pháp đơn giản nhất – rất có thể là bạn nhập dữ liệu không chính xác. Điều này có thể xảy ra trường hợp đặc biệt nếu bạn nhập công thức trực tiếp vào thanh công thức thay vì ô.
Tên hàm không chính xác
Nếu bạn thấy #NAME? Lỗi, rất có thể bạn viết sai tên hàm – thiếu hoặc viết sai chính tả một hoặc hai chữ cái.
Kiểm tra điều đó và sửa lỗi chính tả thì hàm VLOOKUP sẽ hoạt động trở lại.
Tham chiếu ô VLOOKUP không chính xác
Công thức hoạt động tốt cho đến khi bạn thêm một vài dòng hoặc cột vào bảng và lỗi #N/A xuất hiện màu xanh lam.
Kiểm tra xem phạm vi tìm kiếm và ô có giá trị cần tìm được nhập chính xác hay không?
Nếu bạn sử dụng tham chiếu ô tương đối (ví dụ: A1) thay vì tham chiếu tuyệt đối (ví dụ: $A$1) và sau đó sửa đổi bảng (ví dụ: thêm cột), dữ liệu sẽ thay đổi, tham chiếu sẽ thay đổi và công thức sẽ tham chiếu đến ô sai:
Thêm cột “Article”. Cột “Price” không được bao gồm trong phạm vi nữa, do đó không thể tìm thấy gia.
Nếu điều đó xảy ra, chỉ cần Undo lại và suae các tham chiếu.
Bạn có thể sử dụng công cụ Formulas trong Power Tools add-on để chuyển đổi tất cả các tham chiếu tương đối thành tuyệt đối trong một vài cú nhấp chuột:
Tham số “is_sorted” không chính xác
Công thức VLOOKUP của bạn được đặt để trả về kết quả khớp gần đúng (TRUE), trong khi cột tra cứu không được sắp xếp.
Để khắc phục điều đó, hãy sắp xếp cột tra cứu A thành Z hoặc thay đổi TRUE thành FALSE.
Cột tra cứu không chính xác
Có thể bạn đã quên rằng hàm VLOOKUP trong Google Sheets luôn tìm kiếm cột đầu tiên (ngoài cùng bên trái) của phạm vi tra cứu. Nếu giá trị cho tham đố search_key nằm trong cột thứ hai hoặc một số khác, thì hàm VLOOKUP sẽ không hoạt động và trả về lỗi #N/A:
Lưu ý: Ghi nhớ hàm VLOOKUP không thể tìm kiếm những giá trị bên trái của nó.
Số cột không chính xác
Đôi khi tham số thứ ba của Google Sheets VLOOKUP được cho các giá trị không chính xác.
Nó không được nhỏ hơn 1 và nhiều hơn tổng số cột trong phạm vi tìm kiếm. Nếu số không chính xác, hàm VLOOKUP sẽ báo lỗi #VALUE!.
Lưu ý: Số lượng của cột phải là 1 hoặc nhiều hơn. Tất nhiên, không chắc bạn sẽ nhập 0 hoặc -1, nhưng nếu bạn sử dụng một hàm khác để tìm dãy số từ một cột được yêu cầu, bạn sẽ gặp nhiều lỗi.
Nếu điều đó xảy ra, hãy đếm số cột trong phạm vi đã nhập và đảm bảo rằng bao gồm tham số thứ ba của hàm VLOOKUP.
Tham chiếu không chính xác
Phạm vi tìm kiếm có thể nằm trong một bảng khác hoặc thậm chí một bảng tính khác của Google.
Bạn sẽ nhận được thông báo lỗi #REF! Từ Google.
Có nghĩa là phạm vi bạn đưa vào công thức không chính xác và không thể xác định được phạm vi đó.
Định dạng số không chính xác
Nói cách khác, định dạng của ô có giá trị bắt buộc (D3 trong trường hợp này) và định dạng của cột ngoài cùng bên trái trong phạm vi tìm kiếm (A3:A21 trong trường hợp này) khác nhau (số và văn bản).
Điều này có thể xảy ra khi bạn sử dụng mã số thay vì nhập bằng văn bản (số đơn hàng, ID, ngày tháng và mã số khác).
Như trong ví dụ bên trên, các giá trị trông giống nhau nhưng mục được nhập trong D6 được định dạng như một con số trong khi A10 là văn bản. Một văn bản không thể được coi như một con số, làm cho hàm VLOOKUP trả về lỗi #N/A trong Google Sheets.
Làm thế nào để giải quyết nhanh chóng vấn đề này?
1. Bạn có thể thêm hàm TEXT sẽ chuyển đổi bất kỳ giá trị nào thành văn bản.
Hàm được sử dụng như sau:
=VLOOKUP(TEXT(D6,”#”),$A$3:$B$21,2,FALSE)
Trong khi thay thế tham chiếu ô D6 thành một hàm khác:
TEXT(D6,”#”)
Các tham số của hàm TEXT rất đơn giản:
- Tham số đầu tiên, số, chỉ ra ô cần định dạng.
- Tham số thứ hai, định dạng là một mẫu mà thôi sẽ chuyển đổi giá trị của ô.
2. Nếu chỉ có một lỗi như thế này, bạn có thể sửa lỗi nhanh hơn bằng cách thay đổi định dạng số. Chọn ô và nhấn Format > Number > Định dạng văn bản trong menu Google Sheets. Nội dung của nó sẽ được thay đổi thành văn bản:
Như bạn cũng có thể nhìn thấy, lỗi đã xảy ra vì chúng ta đang tìm giá trị văn bản trong số các giá trị văn bản khác.
Khoảng trắng thừa và các ký tự đặc biệt
Hàm không thể tìm thấy giá trị bắt buộc vì ô chứa thừa khoảng trắng hoặc các ký tự đặc biệt (xuống dòng và các ký tự khác).
Trong ví dụ này, hàm VLOOKUP trả về lỗi vì có ai khoảng trắng được nhập vào D4 một cách vô tình. Vì hàm so sánh các ký hiệu, việc tìm kiếm sẽ không thành công:
Lỗi này có thể xảy ra khá thường xuyên và hầu như không thể kiểm tra bằng mắt thường. Ví dụ: nếu giá trị bao gồm hai từ, thì một khoảng trống thừa có thể nằm giữa các từ.
Bạn cũng có thể nhập nhầm những ký tự đặc biệt như vậy bị ẩn trên màn hình (bảng, ngắt dòng,…).
Đây là giải pháp:
- Các chức năng văn bản TRIM và CLEAN sẽ giúp và loại bỏ các ký tự tự ẩn không mong muốn:
=VLOOKUP(TRIM(CLEAN(D4)),$A$3:$B$21,2,FALSE)
Hàm TRIM xóa các khoảng trắng thừa trong khi hàm CLEAN xóa tất cả các ký tự không in được.
Mẹo: Bạn luôn có thể sử dụng kết hợp các chức năng này “chỉ trong trường hợp” để tránh bất kỳ lỗi nào.
- Hoặc sử dụng công cụ Remove từ Power Tools để xóa tất cả các ký tự không mong muốn cùng một lúc:
Thay thế lỗi VLOOKUP bằng văn bản
Tôi nghĩ rằng bạn sẽ đồng ý với thực tế là các lỗi không làm cho bảng của bạn trông đẹp hơn. Có cách nào để loại bỏ chúng không?
Vâng, có nhưng sẽ không có ảnh hưởng gì nếu trước tiên hãy kiểm tra lại xem công thức VLOOKUP của bạn trong Google Sheets có được xây dựng chính xác hay không:
- Bắt đầu với phạm vi tra cứu. Bạn đã đặt nó một cách chính xác? Giá trị của bạn có xuất hiện ở các cột ngoài cùng bên trái của phạm vi này không?
- Đảm bảo tham số “is_sorted” là chính xác: TRUE hoặc FALSE, tùy thuộc vào kết quả phù hợp và cách sắp xếp phạm vi của bạn.
- Kiểm tra tất cả các định dạng số có thể không khớp và dấu cách thừa hoặc các ký tự đặc biệt,
Nếu mọi thứ đều ổn nhưng công thức không trả về giá trị, hãy thêm hàm IFERROR vào đó:
=IFERROR(VLOOKUP(D9,$A$3:$B$21,2,FALSE),”No items”)
Cú pháp của hàm IFERROR rất dễ hiểu:
- Value – giá trị mà chúng ta kiểm tra lỗi. Nếu mọi thứ đều ổn, kết quả tính toán của chúng ta sẽ trả về.
- [value_if_error] – giá trị mà hàm trả về nếu tham số đầu tiên bị lỗi.
Trong trường hợp này, hãy nhấn F9 hiển thị “No items” bởi vì hàm VLOOKUP không thể tìm thấy bất kỳ kết quả nào phù hợp.
Học Excel Online đã giới thiệu bạn cách kiểm tra lỗi thường gặp của hàm VLOOKUP trong Google Sheet. Nếu còn bất kỳ câu hỏi nào dừng ngại ngần mà cmt xuống phía bne dưới để chúng tôi có thể giải đpá thỏa đáng cho bạn trong thời gian ngắn nhất.
