Lỗi #N/A trong hàm VLOOKUP VÀ HLOOKUP

Lỗi # N / A trong excel là một lỗi phổ biến của hầu hết người dùng Excel, nhưng điều quan trọng nhất là làm thế nào để sửa lỗi # N / A này, đặc biệt là # Không có lỗi khi sử dụng hàm VLOOKUP. chức năng hoặc HLOOKUP. Nếu bạn chưa biết cách khắc phục lỗi này cũng đừng lo lắng hãy theo dõi bài viết này của thủ thuật vn nhé. Trong bài viết này, thủ thuật vn sẽ giúp bạn thoát khỏi nỗi ám ảnh mang tên # N / A lỗi trong hàm VLOOKUP VÀ HLOOKUP của bạn đây.

1. Lỗi # N / A là gì

Trong tiếng Anh, N / A là viết tắt của “Không có câu trả lời” hoặc là “Không có sẵn“ hoặc là “Không áp dụng“, nghĩa là không có câu trả lời hoặc không có câu trả lời phù hợp hoặc không thể áp dụng để tìm câu trả lời. Do đó, Lỗi # N / A thường thấp trong Excel nghĩa là không thể tìm thấy giá trị tương thích.

Đặc biệt, lỗi này thường xuyên xảy ra khi sử dụng hàm VLOOKUP và chức năng HLOOKUP vì Excel không thể xác định giá trị trên yêu cầu tìm kiếm. Ngay dưới đây, thủ thuật vn sẽ đi sâu cùng bạn xử lý lỗi # N / A khi sử dụng 2 hàm này.

2. Cách sửa lỗi # N / A trong VLOOKUP

Một. Lỗi xảy ra do sự cố trong khu vực tìm kiếm.

Khu vực tìm kiếm có hai vấn đề chính: một là bạn chưa cố định khu vực tìm kiếm, hai là giới hạn khu vực tìm kiếm không đủ rộng.

Giải pháp đơn giản là giới hạn vùng tìm kiếm và cố định vùng tìm kiếm bằng ký tự “$”

b. Xảy ra lỗi khi kết hợp hàm VLOOKUP / HLOOKUP với hàm LEFT, RIGHT hoặc MID.

Hãy làm theo ví dụ sau:

Chúng ta cần sử dụng HLOOKUP để tìm kiếm TÊN NGÀNH theo MÃ NGÀNH đã cho:

• Điều kiện tìm kiếm: chỉ định ký tự thứ hai bằng MID. chức năng
• Khu vực tìm kiếm: cố định bảng tra cứu tất cả các điểm
• Số thứ tự của hàng chứa giá trị đang được tìm kiếm: Hàng thứ 2 của bảng tra cứu điểm

= HLOOKUP (MID (A2,2,1), $ B $ 7: $ E $ 8,2,0)

Cú pháp hàm hoàn toàn đúng nhưng khi nhấn Enter vẫn báo lỗi khiến chúng ta thắc mắc WTF? Tôi không hiểu làm thế nào, từ chối hiểu

Nguyên nhân ở đây là do khó nhận dạng chữ và số trong excel, định dạng kết quả của hàm MID (hoặc LEFT hoặc RIGHT) luôn là text, nhưng vùng tìm kiếm chỉ trả về kết quả là số.

Giải pháp trong trường hợp này cần phải chuyển đổi kết quả của MID thành một số, vì vậy chúng tôi sử dụng hàm VALUE để sửa lỗi này như sau:

= HLOOKUP (VALUE (MID (A2,2,1)), $ B $ 7: $ E $ 8,2,0)

C. Giá trị tham chiếu không tồn tại

Nguyên nhân phổ biến nhất của lỗi # N / A khi sử dụng hàm VLOOKUP, HLOOKUP, LOOKUP hoặc MATCH khi một công thức không thể tìm thấy giá trị được tham chiếu. Ví dụ: giá trị tra cứu của bạn không tồn tại trong dữ liệu nguồn.

Công thức trong ô E2 là = VLOOKUP (D2, $ D $ 6: $ E $ 8,2, FALSE). Không thể tìm thấy giá trị Banana, vì vậy công thức trả về lỗi # N / A.

Trong trường hợp này, không có giá trị “Banana” nào được liệt kê trong bảng tra cứu, vì vậy hàm VLOOKUP trả về # N / A lỗi.

Giải pháp: Đảm bảo giá trị tra cứu tồn tại trong dữ liệu nguồn hoặc sử dụng trình gỡ lỗi như IFERROR trong công thức.

Ví dụ: = IFERROR (CÔNG THỨC (), 0)trong đó:

• Nếu công thức của bạn cho kết quả là một lỗi, hãy hiển thị 0, nếu không, hãy hiển thị kết quả của công thức
• Bạn có thể dùng “” để hiển thị không có gì hoặc thay thế nó bằng văn bản của riêng bạn: = IFERROR (FORMULA (), “Thông báo lỗi ở đây”)

d. Nhiều dữ liệu khác nhau

Giá trị tra cứu và dữ liệu nguồn là các kiểu dữ liệu khác nhau. Ví dụ: chúng tôi đang cố gắng để hàm VLOOKUP tham chiếu đến một số, nhưng dữ liệu nguồn được lưu trữ dưới dạng văn bản.

Ví dụ cho thấy một công thức VLOOKUP trả về lỗi # N / A vì mục tra cứu được định dạng là một số nhưng bảng tra cứu được định dạng dưới dạng văn bản.

Giải pháp: Đảm bảo dữ liệu của bạn thuộc cùng một loại. Bạn có thể kiểm tra định dạng ô bằng cách chọn một ô hoặc dải ô, sau đó nhấp chuột phải và chọn Định dạng ô (Định dạng ô)> Số (hoặc nhấn Ctrl + 1) và thay đổi định dạng số, nếu cần.

e. Có thêm không gian trong các ô

Bạn có thể sử dụng hàm TRIM để xóa bất kỳ khoảng trắng đầu hoặc cuối. Các ví dụ sau đây sử dụng hàm TRIM được lồng trong một hàm Vlookup để xóa các khoảng trắng ở đầu khỏi các tên có trong A2: A7 và trả về các tên bộ phận.

Công thức trong ô E3 là {= VLOOKUP (D2, TRIM (A2: B7), 2, FALSE)} và cần được nhập bằng CTRL + SHIFT + ENTER.

= VLOOKUP (D2, TRIM (A2: B7), 2, FALSE)

f. Tra cứu kết quả tương đối và chính xác

Theo mặc định, các hàm tra cứu thông tin trong bảng phải được sắp xếp theo thứ tự tăng dần. Tuy nhiên, cả hai hàm VLOOKUP và HLOOKUP đều chứa. lý lẽ range_lookup cho hàm này biết để tìm một kết quả phù hợp chính xác, ngay cả khi bảng không được sắp xếp. Để tìm một kết hợp chính xác, hãy đặt. lý lẽ range_lookup Pháo đài SAI. Xin lưu ý rằng việc sử dụng TRUE, là đối số yêu cầu hàm tìm kiếm đối sánh tương đối, không chỉ có thể gây ra # N / A lỗi nhưng cũng có khả năng trả về kết quả sai như thể hiện trong ví dụ bên dưới.

Trong ví dụ này, không chỉ giá trị “Banana” trả về lỗi # N / A mà giá trị “Pear” trả về giá trị sai. Điều này xảy ra do việc sử dụng đối số TRUE, đối số này yêu cầu hàm VLOOKUP tìm kiếm đối sánh tương đối thay vì đối sánh chính xác. Không có kết quả khớp nào gần đúng cho các giá trị “Banana” và “Pear” đứng trước “Peach” theo thứ tự bảng chữ cái. Trong trường hợp này, việc sử dụng hàm VLOOKUP với đối số FALSE sẽ trả về giá đúng cho giá trị “Pear” nhưng giá trị “Banana” vẫn sẽ trả về lỗi # N / A vì không có giá trị “Bananas”. ”Tương ứng trong danh sách tra cứu.

Nếu bạn đang sử dụng hàm MATCH, hãy thử thay đổi giá trị của đối số match_type để chỉ định thứ tự sắp xếp cho bảng. Để tìm một đối sánh chính xác, hãy đặt đối số match_type thành 0 (không).

Phần kết:

Vì vậy, chúng tôi đã tìm ra tất cả các nguyên nhân # N / A lỗi trong hàm VLOOKUP VÀ HLOOKUP cũng như cách khắc phục lỗi. Nếu vẫn không khắc phục được lỗi #NA, hãy comment bên dưới bài viết này.