Bất cứ ai làm việc trong lĩnh vực kiểm toán đều sử dụng một công cụ vô cùng đắc lực chính là Excel. Tuy nhiên trong quá trình làm việc, rất nhiều người gặp lỗi hàm VLOOKUP. Đây là một trong những hàm tìm kiếm phổ biến nhất của Excel. Ắt hẳn ai cũng sẽ “phát điên” khi nhập chính xác cú pháp hàm VLOOKUP rồi nhưng kết quả vẫn là Not Available – Không có thông tin để hiển thị. Vậy lỗi hàm VLOOKUP là gì và cách khắc phục nó như thế nào? Vấn đề này sẽ được giải đáp trong bài viết dưới đây.

Lỗi 1: Sai Định Dạng

Lỗi phổ biến khiến người dùng không thể thực hiện hàm VLOOKUP là lỗi sai định dạng

Người dùng thường gặp lỗi định dạng khi dùng định dạng kiểu chữ (text) cho các số. Lúc này, 2 cách giải quyết thường được sử dụng là:

• Dùng hàm Text thay thế
• Nhân tất cả các giá trị tra cứu (lookup values) với 1

Cách thực hiện:

Chúng ta giả sử Cột C (Lookup_value) chứa giá trị lưu dạng số còn Cột A (Lookup_array) chứa giá trị lưu dạng Text. Ta sẽ tiến hành như sau:

+ Cách 1: Người dùng chèn thêm hàm Text để định dạng lại giá trị của Cột A (lookup_array) thành dạng Text. Từ đó chúng sẽ ăn khớp với định dạng Text của Cột A.

Sau đó, người dùng kéo công thức ô E2 xuống đến ô E10. Từ đó sẽ nhận được giá trị tham chiếu về.

E2=Vlookup(Text(C2,0),$A$2:$D$10,4,0)

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ẽ bị lỗi không thực hiện được.

+ Cách 2: Nhân thêm 1 đơn vị và chuyển giá trị sang cột khác.

Tương tự như trường hợp trên, người dùng chèn thêm hàm Số để định dạng lại giá trị của Cột C (lookup_value) thành dạng Số. Từ đó chúng sẽ ăn khớp với định dạng Text của Cột C. Sau đó, người dùng kéo công thức ô B2 xuống đến ô B10. Từ đó sẽ nhận được giá trị tham chiếu về.

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 bằng công thức B2=A2*1

E2=Vlookup(B2:B10,$B$2:$D$10,3,0)

Lưu ý: Bất cứ ô nào trong cột A chứa giá trị ký tự (chữ b) chứ không phải dạng số, người dùng nhân cho bất cứ đơn vị nào thì kết quả trả về ở ô E2 cũng sẽ bị lỗi.

Lỗi 2: Thừa Dấu Cách Ở Cuối Cùng Của Điều Kiện Tìm Kiếm

Thừa dấu cách ở cuối ô điều kiện tìm kiếm khiến hàm VLOOKUP bị lỗi

Trong một số trường hợp, người dùng đã nhập đúng định dạng của hàm VLOOKUP nhưng kết quả trả về vẫn bị lỗi. Câu trả lời chính là thừa dấu cách trong ô giá trị tìm kiếm (lookup values).

Cách phát hiện: Người dùng nhấn vào một ô bất kỳ trong ô giá trị tìm kiếm, nếu dòng cuối cùng của ô có một hoặc vào dấu cách thì ắt hẳn bạn đã mắc phải lỗi này khiến hàm VLOOKUP không thể thực hiện.

Thông thường, việc người dùng lấy dữ liệu từ nhiều nguồn khác nhau và quên loại bỏ dấu cách dễ dẫn đến lỗi nói trên.

Cách giải quyết: Người dùng chọn lần lượt giá trị dùng làm điều kiện và giá trị tương ứng, nếu phát hiện dấu cách có thể dễ dàng xóa bỏ để khắc phục lỗi. Ngoài ra, tính năng Data – Text to Columns trong Excel cũng có thể dùng để xóa tất cả dấu cách một cách nhanh chóng.

Lỗi 3: Không Khóa Mảng Tham Chiếu Tìm Kiếm

Không khóa mảng tham chiếu sẽ khiến hàm VLOOKUP bị lỗi

Thông thường, người ta sẽ dùng hàm VLOOKUP để tìm kiếm nhiều giá trị. Khi thêm 1 trường vào bảng dữ liệu, người dùng sẽ kéo các ô xuống dưới để copy công thức cho các ô ở sau. Khi nháy đúp chuột vào ô kết quả bất kỳ, người dùng dễ dàng nhận ra chỉ có một nửa công thức hoạt động.

Chính vì vậy, người dùng không khóa mảng nên giá trị tìm kiếm (lookup values) không thể tương ứng với mảng tham chiếu lookup arrays, từ đó hàm VLOOKUP không thể thực hiện.

Cách giải quyết:

Trước khi kéo công thức xuống để copy cho các ô ở sau, người dùng sẽ dùng phím F4 để để khóa mảng tham chiếu. Phím F4 sẽ thêm dấu $ trước vị trí của ô và số thứ tự của dòng, ví dụ như $B$4:$C$10.

Bài viết này đã cung cấp cho bạn thông tin 3 lỗi sai phổ biến nhất khiến bạn không thể thực hiện được hàm VLOOKUP. Chúc các bạn thành công!

Hàm VLOOKUP là một hàm trợ thủ đắc lực trong Excel mà mọi người dùng đều phải công nhận. Nếu một ngày hàm VLOOKUP của bạn không hoạt động, nghĩa là bạn đã gặp lỗi hàm VLOOKUP. Bài viết này sẽ giúp bạn trả lời câu hỏi "Tại sao hàm VLOOKUP không chạy", đồng thời đưa ra các giải pháp khắc phục vấn đề này.

XEM NHANH BÀI VIẾT

Cách khắc phục lỗi

NA trong lỗi hàm VLOOKUP

NA: Not available, đây là lỗi hàm VLOOKUP phổ biến mà người dùng hay mắc phải khi sử dụng hàm tham chiếu VLOOKUP trong file Excel của bạn. Lỗi

NA trong hàm VLOOKUP này xuất phát từ nhiều nguyên nhân, các bạn hãy thử kiểm tra xem mình có đang mắc những lỗi nào dưới đây nhé:

Lỗi chính tả trong tham chiếu giá trị được tìm kiếm (Lookup Value) của công thức hàm VLOOKUP

Trong khi sử dụng, nguồn dữ liệu của bạn có thể đến từ nhiều hệ thống khác nhau như web, từ file của người khác,... Lúc này, rất có thể dữ liệu đã phát sinh lỗi hay chứa các ký tự lạ. Việc bạn cần làm là rà soát lỗi chính tả, hay toàn bộ dữ liệu trong trang tính thì lỗi

NA trong hàm VLOOKUP này sẽ biến mất.

Lỗi

N/A trong hàm VLOOKUP khi dò tìm các giá trị gần đúng

Nhắc lại cho bạn đọc nhớ, công thức hàm VLOOKUP chính là:

\=VLOOKUP(,< bảng dữ liệu >,< cột lấy giá trị >,[ TRUE / FALSE ])

Với tham số thứ 4, nếu bạn không điền gì hoặc điền ''TRUE'' thì khi đó, hàm VLOOKUP sẽ dò tìm giá trị gần đúng. Lỗi

N/A trong hàm VLOOKUP xuất hiện khi:

1. Giá trị cần tìm kiếm nhỏ hơn giá trị nhỏ nhất trong mảng tìm kiếm
2. Cột tìm kiếm không được sắp xếp theo thứ tự nhỏ đến lớn

Cột tìm kiếm trong hàm VLOOKUP không ở vị trí đầu tiên khi tra cứu

Như đã nhắc rất nhiều bạn đọc, vị trí các cột rất quan trọng khi sử dụng hàm VLOOKUP. Hãy xem hình minh họa sau:

Lưu ý: Với tình huống bạn không thể thay đổi vị trí cột dữ liệu cho đúng, hãy sử dụng hàm MATCH và INDEX thay thế cho hàm VLOOKUP để tra cứu nhé. Chi tiết đọc bài bên dưới:

Xem thêm: Cách dùng hàm MATCH - trợ thủ đắc lực cho hàm tìm kiếm trong Excel

Lỗi dữ liệu số nhưng ở định dạng Text (văn bản) gây nên lỗi

NA

Hãy cùng nhìn vào ví dụ dưới đây để hiểu về lỗi hàm VLOOKUP:

Cách khắc phục lỗi hàm VLOOKUP:

1. Chọn toàn bộ những ô bị lỗi này.
2. Nhấn phím tắt CTRL + 1 để mở hộp thoại Number Format.
3. Chọn thẻ Number > Number rồi bấm OK.

Hoặc làm theo hướng dẫn đơn giản hơn như hình dưới đây:

Lỗi

NA do thừa khoảng trắng ở đầu ô hoặc cuối ô

Đôi khi, màn hình hiển thị thông báo lỗi

N/A là do khi điền dữ liệu, người dùng đã điền thừa khoảng trắng. Cách khắc phục vấn đề này rất đơn giản. Cùng xem bài viết dưới đây để đọc những hướng dẫn chi tiết hơn

Xem thêm: Hướng dẫn cách xóa khoảng trắng hiệu quả bằng hàm CHAR trong Excel

Lỗi

VALUE trong hàm VLOOKUP

Cũng như lỗi

N/A, lỗi

VALUE trong hàm VLOOKUP cũng là một lỗi được người dùng nhận định là dễ mắc phải. Chủ yếu thông báo hiển thị lỗi này xuất phát từ 3 nguyên nhân chính sau đây:

Giá trị tra cứu (Lookup value) của hàm VLOOKUP dài hơn 255 ký tự

Có thể bạn chưa biết, giới hạn cho tham số Lookup value trong công thức hàm VLOOKUP chỉ cho phép tối đa 255 ký tự. Và việc đối tượng bạn tra cứu có nhiều hơn số ký tự cho phép thì hiển nhiên, lỗi

VALUE xuất hiện.

Cách khắc phục: Trong tình huống này, hàm MATCH kết hợp INDEX lại là một giải pháp tuyệt vời thay thế được tính năng của hàm VLOOKUP.

Xem thêm: Hướng dẫn cách kết hợp hàm INDEX và MATCH thay thế hàm Vlookup trong Excel

Lỗi do tham số cột lấy dữ liệu về trong hàm VLOOKUP nhỏ hơn 1

Nhắc lại bạn đọc về công thức hàm VLOOKUP như sau:

\=VLOOKUP(,< bảng dữ liệu >,< cột lấy giá trị >,[ TRUE / FALSE ])

Nếu có giá trị nhỏ hơn 1 (<1), thì Excel sẽ thông báo lỗi

VALUE tới người dùng.

Lỗi

NAME trong hàm VLOOKUP

Với lỗi

NAME, có rất nhiều nguyên nhân khiến Excel hiển thị lỗi này khi người dùng sử dụng hàm VLOOKUP để tìm kiếm hay tra cứu. Cùng kiểm tra xem bạn có mắc những nguyên nhân sau đây hay không nhé:

Do hàm VLOOKUP không phân biệt chữ viết hoa hay viết thường

Hàm VLOOKUP được mặc định trả về kết quả đầu tiên khi tìm được, bất kể là chữ hoa hay chữ thường. Có lẽ bạn không để ý sự mặc định này nên các dữ liệu có sự khác biệt về viết hoa hay viết thường. Vậy thì làm thế nào nhỉ?

Cách khắc phục: Sử dụng các hàm tra cứu theo chiều dọc như LOOKUP, SUMPRODUCT, INDEX / MATCH,.. kết hợp với hàm EXACT để tra cứu đúng đối tượng cần tìm. Chi tiết đọc bài viết dưới đây.

Xem thêm: Hàm vlookup báo lỗi

REF và cách khắc phục

Do tham chiếu trong hàm VLOOKUP bị thay đổi do copy hay fill down công thức xuống dưới

Đây là lý do tại sao bạn đọc hay được khuyến khích sử dụng những kí tự $ để khoá tham chiếu (như $A$1:$I$8 thay vì A1:I8) khi copy hay di chuyển công thức nhé. Lỗi

NAME khi dùng hàm VLOOKUP có thể khắc phục nếu bạn luôn khóa công thức của mình nhé.

Sử dụng hàm VLOOKUP kết hợp IFERROR

Công thức hàm IFERROR:

\=IFERROR(< giá trị cần kiểm tra lỗi >, < giá trị trả về nếu có lỗi >)

Và khi kết hợp với hàm VLOOKUP:

\=IFERROR(VLOOKUP(…), < thông báo lỗi >)

Kết quả thay vì thông báo lỗi, sẽ là một ô trắng. Bạn đọc có thể thay đổi thông báo lỗi tùy theo ý muốn (như là "Lỗi rồi nè'') để nhận định được giá trị nào bị lỗi.

Xem thêm: Hướng dẫn dùng hàm IfError trên Excel

Sử dụng hàm VLOOKUP kết hợp với ISERROR

Nhược điểm của hàm IFERROR là không dùng được trong các phiên bản cũ hơn 2007. Với những Excel phiên bản cũ, hãy dùng công thức kết hợp với hàm VLOOKUP như sau:

\=IF( ISERROR ( VLOOKUP(…), < thông báo lỗi > ), VLOOKUP (…) )

Kết luận

Bài viết trên đã tổng hợp một số lỗi mà người dùng hay mắc phải khi sử dụng hàm VLOOKUP để tìm kiếm cũng như tra cứu dữ liệu.