Hướng dẫn sửa lỗi #VALUE! khi sử dụng hàm XLOOKUP trong Excel

Nếu như bạn đang gặp phải lỗi VALUE trong Excel khi sử dụng hàm XLOOKUP, bài viết này sẽ giúp bạn ‘hóa giải’ nỗi đau này. Cùng Gitiho tìm hiểu nguyên nhân và giải pháp trong tình huống Excel trả về lỗi #VALUE! thay vì giá trị mong đợi nhé.

Xem thêm: Giới thiệu 14 hàm Excel mới trong tháng 03/2022 cho Office 365

Đăng ký ngay Khóa học chuyên sâu về công thức và hàm Excel từ A-Z

Nguyên nhân chung xảy ra #VALUE

#VALUE! là một trong những lỗi Excel phổ biến nhất khi bạn sử dụng các hàm tìm kiếm trong Excel trên trang tính của mình. Do đó, đừng quá lo lắng khi bạn gặp phải lỗi này sau khi nhấn Enter nhé. Trước tiên, chúng ta sẽ cần hiểu được lỗi VALUE trong Excel là gì và nguyên nhân dẫn đến lỗi này khi dùng hàm XLOOKUP.

Lỗi VALUE là tín hiệu Excel gửi tới bạn rằng: “Công thức bạn vừa nhập đã xảy ra lỗi," hoặc “Ô tính bạn đang tham chiếu đã xảy ra lỗi.” Ngay cả khi đã biết điều này, có thể bạn vẫn sẽ không hiểu được lỗi Excel đang nói tới là lỗi nào. Đúng vậy, vì lỗi VALUE trong Excel là một lỗi rất chung chung và khó xác định nguyên nhân xảy ra lỗi.

Vậy thì chúng ta sẽ cùng đi vào tình huống cụ thể khi gặp lỗi VALUE với hàm XLOOKUP nhé. 

Xem thêm: Hướng dẫn 3 cách sửa lỗi Value trong Excel

Cách sửa lỗi #VALUE khi sử dụng hàm XLOOKUP

Lỗi XLOOKUP không thống nhất tham chiếu

Nếu bạn nhìn thấy giá trị #VALUE! trong ô tính của mình, nguyên nhân rất có thể đến từ sự khác biệt giữa các kích thước dải ô tham chiếu bạn điền trong công thức XLOOKUP.

Ví dụ trong hình dưới đây, mình sử dụng XLOOKUP để điền dữ liệu từ bảng tổng hợp vào bảng phụ. Tuy nhiên, sau khi nhấn nút Enter, thứ Excel trả về không phải email của nhân viên, mà lại là lỗi #VALUE!.

Khi kiểm tra lại công thức, mình phát hiện ra lỗi nằm ở 2 khoảng tham chiếu không thống nhất trong công thức XLOOKUP. Cụ thể hơn:

• Tham số lookup_array (phạm vi cần tìm kiếm): F1:F18
• Tham số return_array (phạm vi chứa dữ liệu cần trả về): I1:I17

Thông thường, khi phải làm việc với các dải ô lên đến hàng trăm ô tính, chúng ta sẽ sử dụng tổ hợp phím tắt  Ctrl + Shift + ↓ để chọn toàn bộ các ô chứa giá trị trong cột. Thao tác này sẽ bỏ qua các ô trống ở cuối cột. Chính vì vậy, trong trường hợp các cột tham chiếu không kết thúc ở cùng một dòng, chúng ta sẽ gặp phải lỗi VALUE trong Excel khi dùng XLOOKUP.

Như vậy, chỉ cần đồng bộ tham chiếu trong 2 tham số lookup_array và return_array là công thức XLOOKUP của mình sẽ hoạt động như bình thường.

Xem thêm: Tổng hợp các phím tắt Excel thần thánh ai cũng phải biết

Lỗi XLOOKUP không sử dụng tham chiếu tuyệt đối

Ngay cả với công thức hàm tìm kiếm trong Excel như hình phía trên, chắc chắn mình vẫn có thể gặp phải lỗi khi kéo công thức này xuống các ô phía dưới. 

Tại ô B4 và B5, Excel trả về 2 giá trị trùng nhau. Tại sao lại như vậy? Nguyên nhân chính là do mình không sử dụng tham chiếu tuyệt đối cho dải ô tham chiếu trong công thức hàm XLOOKUP.

Tham chiếu tuyệt đối trong Excel giống như một dây xích cố định tham chiếu, tránh trường hợp tham chiếu thay đổi khi kéo công thức từ ô tính này sang các ô tính khác. Khi không sử dụng tham chiếu tuyệt đối, công thức XLOOKUP mình điền vào ô B2 sẽ bị thay đổi khi mình kéo công thức xuống các ô phía dưới. Do đó, hậu quả là Excel trả về giá trị sai tại ô B7.

Để sửa lỗi XLOOKUP này, mình sẽ bôi đen các tham chiếu dải ô trong công thức tại ô B2, sau đó nhấn nút F4 hoặc tổ hợp phím fn + F4 trên bàn phím.

Như bạn thấy, giữa các dải tham chiếu trong thanh công thức đã xuất hiện các ký tự $. Điều này đồng nghĩa với việc tham chiếu tuyệt đối đã được áp dụng. Lúc này, mình sẽ kéo công thức hàm tìm kiếm trong Excel đã sửa xuống các ô phía dưới.

Cách phòng tránh lỗi VALUE trong Excel khi dùng XLOOKUP

Để tránh cả 2 lỗi XLOOKUP chúng ta vừa đề cập ở phần trên, cách hữu hiệu nhất là chuyển đổi bảng dữ liệu của bạn thành bảng Excel. Bằng cách này, các cột dữ liệu sẽ luôn có cùng kích thước, và các tham chiếu trong công thức sẽ luôn được đặt dưới dạng tham chiếu tuyệt đối.

Để chuyển đổi phạm vi ô tính thành bảng Excel, bạn hãy bôi đen toàn bộ phạm vi này và nhấn tổ hợp phím tắt Ctrl + T.

Một khi dữ liệu của bạn đã được chuyển đổi sang bảng Excel, bạn có thể điền nhấn tổ hợp Ctrl + phím cách để lựa chọn toàn bộ cột trong bảng cho tham số hàm XLOOKUP.

Nhìn lên thanh công thức, bạn sẽ thấy các tham chiếu của XLOOKUP được hiển thị dưới dạng cột của bảng kèm theo tên tiêu đề của cột đã tham chiếu. Như vậy, bạn có thể dễ dàng kiểm soát được công thức hàm tìm kiếm trong Excel của mình mà không phải lo ngại về các lỗi có thể xảy ra.

Xem thêm: Hướng dẫn sử dụng hàm MATCH tìm dòng cuối trong Excel

Tổng kết

Hy vọng với kiến thức trong bài viết này, bạn đã hiểu được nguyên nhân dẫn đến lỗi VALUE trong Excel khi sử dụng XLOOKUP. Trong quá trình viết công thức hàm Excel, hãy nhớ kiểm tra thật kỹ từng tham số để đảm bảo không có bất kỳ sai sót nào dẫn đến các lỗi trên trang tính bạn nhé.

Để nâng cao kỹ năng sử dụng hàm Excel cho công việc, hãy đăng ký ngay khóa học Tuyệt đỉnh Excel chuyên sâu về công thức hàm tại đây. Khóa học sẽ cung cấp cho bạn kiến thức về các hàm Excel nâng cao và cách kết hợp các hàm để tạo nên công thức mạnh mẽ nhất. Tham gia khóa học ngay để nâng tầm kỹ năng của bạn ngay hôm nay!

Gitiho xin cảm ơn và chúc bạn thành công!