Vấn đề và cách khắc phục lỗi thường gặp khi sử dụng VLOOKUP/HLOOKUP
Hàm VLOOKUP và HLOOKUP là những hàm tra cứu mạnh mẽ trong Excel và các bảng tính khác, nhưng chúng cũng có thể gây ra lỗi nếu không được sử dụng đúng cách. Dưới đây là một số lỗi phổ biến, nguyên nhân chi tiết và cách khắc phục:
1. Lỗi N/A (Không tìm thấy giá trị):
Nguyên nhân:
Giá trị tìm kiếm không tồn tại:
Giá trị bạn đang tìm kiếm (lookup_value) không có trong cột/hàng đầu tiên của bảng tra cứu (table_array).
Sai chính tả hoặc định dạng không khớp:
Ví dụ: “Apple” khác với “apple” hoặc 123 (dạng số) khác với “123” (dạng text).
Khoảng trắng thừa:
Có thể có khoảng trắng thừa ở đầu hoặc cuối giá trị tìm kiếm hoặc trong cột/hàng tra cứu.
lookup_value nằm ngoài phạm vi bảng:
Giá trị tìm kiếm nhỏ hơn giá trị nhỏ nhất trong cột/hàng tìm kiếm (nếu `range_lookup` là TRUE/1 hoặc bỏ qua).
Sai vị trí cột/hàng trả về:
`col_index_num` (VLOOKUP) hoặc `row_index_num` (HLOOKUP) vượt quá số cột/hàng trong `table_array`.
Dữ liệu trong table_array bị lỗi:
Có ô trong `table_array` chứa lỗi (ví dụ: DIV/0!, VALUE!).
Cách khắc phục:
Kiểm tra chính tả và định dạng:
Đảm bảo giá trị tìm kiếm chính xác và khớp với định dạng trong cột/hàng tìm kiếm. Sử dụng hàm `TRIM()` để loại bỏ khoảng trắng thừa.
Kiểm tra dữ liệu trong bảng tra cứu:
Đảm bảo giá trị tìm kiếm có trong cột/hàng đầu tiên của bảng tra cứu.
Sử dụng hàm IFERROR để xử lý lỗi:
Thay vì hiển thị N/A, bạn có thể hiển thị một thông báo thân thiện hơn hoặc giá trị mặc định. Ví dụ: `=IFERROR(VLOOKUP(A1,B1:C10,2,FALSE),”Không tìm thấy”)`
Kiểm tra `col_index_num`/`row_index_num`:
Đảm bảo số cột/hàng trả về nằm trong phạm vi của bảng tra cứu.
Kiểm tra phạm vi tìm kiếm (table_array):
Đảm bảo phạm vi này bao gồm cả cột/hàng tìm kiếm và cột/hàng trả về.
Kiểm tra xem dữ liệu đã được sắp xếp chưa (nếu `range_lookup` là TRUE/1):
Nếu `range_lookup` là TRUE/1, cột/hàng đầu tiên của `table_array` phải được sắp xếp theo thứ tự tăng dần.
Sử dụng tìm kiếm gần đúng (range_lookup = TRUE) cẩn thận:
Tìm kiếm gần đúng có thể trả về kết quả không mong muốn nếu dữ liệu không được sắp xếp đúng cách.
Đảm bảo không có lỗi trong table_array:
Sửa các lỗi trong dữ liệu của `table_array` trước khi sử dụng VLOOKUP/HLOOKUP.
2. Lỗi REF! (Tham chiếu không hợp lệ):
Nguyên nhân:
`col_index_num` (VLOOKUP) hoặc `row_index_num` (HLOOKUP) quá lớn:
Số cột/hàng trả về lớn hơn số cột/hàng trong `table_array`.
`table_array` tham chiếu đến ô/phạm vi đã bị xóa:
Phạm vi dữ liệu được tham chiếu trong `table_array` đã bị xóa hoặc di chuyển.
Cách khắc phục:
Kiểm tra `col_index_num`/`row_index_num`:
Đảm bảo số cột/hàng trả về không vượt quá số cột/hàng trong `table_array`.
Kiểm tra `table_array`:
Đảm bảo phạm vi dữ liệu trong `table_array` vẫn tồn tại và không bị xóa hoặc di chuyển. Điều chỉnh công thức nếu phạm vi đã thay đổi.
Sử dụng tên phạm vi:
Đặt tên cho `table_array` để công thức tự động cập nhật khi bạn chèn hoặc xóa cột/hàng.
3. Lỗi VALUE! (Giá trị không hợp lệ):
Nguyên nhân:
`col_index_num` (VLOOKUP) hoặc `row_index_num` (HLOOKUP) không phải là số:
Cột/hàng trả về phải là một số nguyên dương.
`range_lookup` không phải là TRUE/FALSE hoặc 1/0:
Giá trị này phải là một trong hai giá trị này.
`table_array` không phải là một phạm vi hợp lệ:
`table_array` phải là một tham chiếu đến một vùng dữ liệu hợp lệ.
Cách khắc phục:
Kiểm tra `col_index_num`/`row_index_num`:
Đảm bảo giá trị này là một số nguyên dương hợp lệ.
Kiểm tra `range_lookup`:
Đảm bảo giá trị này là TRUE/FALSE hoặc 1/0.
Kiểm tra `table_array`:
Đảm bảo phạm vi này là một tham chiếu hợp lệ đến một vùng dữ liệu.
4. Kết quả sai (khi `range_lookup` là TRUE/1):
Nguyên nhân:
Dữ liệu trong cột/hàng đầu tiên của `table_array` không được sắp xếp:
Khi `range_lookup` là TRUE/1 (tìm kiếm gần đúng), cột/hàng đầu tiên của `table_array` phải được sắp xếp theo thứ tự tăng dần. Nếu không, kết quả sẽ không chính xác.
Giá trị tìm kiếm nằm giữa hai giá trị trong cột/hàng đầu tiên của `table_array`:
Khi tìm kiếm gần đúng, nếu giá trị tìm kiếm nằm giữa hai giá trị, hàm sẽ trả về giá trị tương ứng với giá trị nhỏ hơn trong hai giá trị đó.
Cách khắc phục:
Sắp xếp dữ liệu:
Đảm bảo cột/hàng đầu tiên của `table_array` được sắp xếp theo thứ tự tăng dần.
Sử dụng tìm kiếm chính xác (`range_lookup` = FALSE/0):
Nếu bạn muốn tìm kiếm giá trị chính xác, hãy sử dụng `range_lookup` = FALSE/0. Điều này sẽ trả về N/A nếu giá trị không được tìm thấy.
Ví dụ minh họa:
VLOOKUP:
`=VLOOKUP(A2, Sheet2!A1:B10, 2, FALSE)`
A2:
Giá trị cần tìm kiếm.
Sheet2!A1:B10:
Phạm vi dữ liệu để tìm kiếm (table_array). Cột A là cột tìm kiếm, cột B là cột chứa giá trị trả về.
2:
Số thứ tự cột cần trả về giá trị (trong phạm vi `table_array`).
FALSE:
Tìm kiếm chính xác (tìm đúng giá trị trong cột tìm kiếm).
HLOOKUP:
`=HLOOKUP(A2, Sheet2!A1:J2, 2, FALSE)`
A2:
Giá trị cần tìm kiếm.
Sheet2!A1:J2:
Phạm vi dữ liệu để tìm kiếm (table_array). Hàng 1 là hàng tìm kiếm, hàng 2 là hàng chứa giá trị trả về.
2:
Số thứ tự hàng cần trả về giá trị (trong phạm vi `table_array`).
FALSE:
Tìm kiếm chính xác (tìm đúng giá trị trong hàng tìm kiếm).
Lời khuyên:
Luôn kiểm tra kỹ dữ liệu và công thức của bạn để tránh các lỗi phổ biến.
Sử dụng `range_lookup = FALSE` (tìm kiếm chính xác) khi có thể để đảm bảo tính chính xác của kết quả.
Sử dụng hàm `IFERROR` để xử lý lỗi và cung cấp thông báo thân thiện hơn cho người dùng.
Đặt tên phạm vi cho `table_array` để dễ quản lý và cập nhật công thức.
Sử dụng các công cụ kiểm tra lỗi của Excel để tìm và sửa lỗi trong công thức.
Hi vọng những thông tin này giúp bạn hiểu rõ hơn về các lỗi thường gặp khi sử dụng VLOOKUP/HLOOKUP và cách khắc phục chúng. Chúc bạn thành công!
https://mangviettelcantho.vn/index.php?language=vi&nv=news&nvvithemever=d&nv_redirect=aHR0cHM6Ly92aWVjbGFtdHBoY20ub3JnLw==