Khi sử dụng công thức VLOOKUP trong Excel, đôi khi bạn có thể gặp phải lỗi # N / A xấu xí.Điều này xảy ra khi công thức của bạn không thể tìm thấy giá trị tra cứu.
Trong hướng dẫn này, tôi sẽ chỉ cho bạn các cách khác nhau để sử dụng IFERROR và VLOOKUP để xử lý các lỗi # N / A này trong trang tính.
Sử dụng IFERROR kết hợp với hàm VLOOKUP để hiển thị nội dung nào đó có ý nghĩa thay cho lỗi # N / A (hoặc bất kỳ lỗi nào khác).
Trước khi đi vào chi tiết cách sử dụng tổ hợp này, chúng ta hãy xem nhanh hàm IFERROR để xem nó hoạt động như thế nào.
Nội dung
Mô tả hàm IFERROR
Sử dụng hàm IFERROR, bạn có thể chỉ định điều gì sẽ xảy ra khi công thức hoặc tham chiếu ô trả về lỗi.
Đây là cú pháp của hàm IFERROR.
= IFERROR (giá trị, giá trị_if_error)
- giá trị - Đây là thông số để kiểm tra lỗi.Trong hầu hết các trường hợp, đó là một công thức hoặc một tham chiếu ô.Khi sử dụng hàm VLOOKUP với IFERROR, công thức VLOOKUP sẽ là tham số này.
- giá trị_if_error - Đây là giá trị được trả về khi xảy ra lỗi.Các loại lỗi sau được đánh giá: # N / A, #REF !, # DIV / 0 !, #VALUE !, #NUM !, #NAME ?, và #NULL !.
Các lý do có thể khiến hàm VLOOKUP trả về lỗi # N / A
Hàm VLOOKUP có thể trả về lỗi # N / A vì bất kỳ lý do nào sau đây:
- Không tìm thấy giá trị tra cứu trong mảng tra cứu.
- Có khoảng trống ở đầu, cuối hoặc dấu cách kép trong giá trị tra cứu (hoặc mảng bảng).
- Có lỗi đánh máy trong giá trị tra cứu hoặc giá trị tra cứu trong mảng.
Bạn có thể sử dụng IFERROR kết hợp với hàm VLOOKUP để xử lý tất cả các nguyên nhân lỗi này.Tuy nhiên, bạn nên lưu ý đến lý do số 2 và số 3 và sửa chúng trong dữ liệu nguồn, thay vì để IFERROR xử lý chúng.
Lưu ý: IFERROR sẽ xử lý lỗi bất kể điều gì đã gây ra lỗi.Nếu bạn chỉ muốn xử lý các lỗi do VLOOKUP không thể tìm thấy giá trị tra cứu, hãy sử dụng IFNA để thay thế.Điều này sẽ đảm bảo các lỗi khác ngoài # N / A không được xử lý và bạn có thể điều tra các lỗi khác này.
Bạn có thể sử dụng hàm TRIM để xử lý dấu cách ở đầu, cuối và dấu cách kép.
Thay thế lỗi VLOOKUP # N / A bằng văn bản có nghĩa
Giả sử bạn có một tập dữ liệu giống như sau:
Như bạn có thể thấy, công thức VLOOKUP trả về lỗi vì giá trị tra cứu không có trong danh sách.Chúng tôi đang tìm điểm của Glen, nó không có trong bảng tỷ số.
Mặc dù đây là một tập dữ liệu rất nhỏ, nhưng bạn có thể kết thúc với một tập dữ liệu khổng lồ, nơi bạn phải kiểm tra sự xuất hiện của nhiều mục.Bạn sẽ gặp lỗi # N / A cho mỗi trường hợp không tìm thấy giá trị.
Đây là công thức bạn có thể sử dụng để hiểu điều gì đó có ý nghĩa và không mắc lỗi # N / A.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "Không tìm thấy")
Công thức trên trả về văn bản "Không tìm thấy" thay vì lỗi # N / A.Bạn cũng có thể sử dụng cùng một công thức để trả về khoảng trống, số không hoặc bất kỳ văn bản có ý nghĩa nào khác.
VLOOKUP lồng nhau với hàm IFERROR
Nếu bạn đang sử dụng VLOOKUP và các bảng tra cứu của bạn được trải rộng trên cùng một trang tính hoặc các trang tính khác nhau, bạn cần kiểm tra giá trị VLOOKUP thông qua tất cả các bảng này.
Ví dụ, trong tập dữ liệu được hiển thị bên dưới, có hai bảng riêng biệt về tên và điểm số của học sinh.
Nếu tôi phải tìm điểm của Grace trong tập dữ liệu này, tôi cần sử dụng hàm VLOOKUP để kiểm tra bảng đầu tiên và nếu không tìm thấy giá trị trong đó, hãy kiểm tra bảng thứ hai.
Đây là công thức IFERROR lồng nhau mà tôi có thể sử dụng để tìm giá trị:
=IFERROR(VLOOKUP(G3,$A$2:$B$5,2,0),IFERROR(VLOOKUP(G3,$D$2:$E$5,2,0),"Not Found"))
Sử dụng hàm VLOOKUP với IF và ISERROR (trước Excel 2007)
Hàm IFERROR đã được giới thiệu trong Excel 2007 dành cho Windows và Excel 2016 trên Mac.
Nếu bạn đang sử dụng phiên bản trước, hàm IFERROR sẽ không hoạt động trên hệ thống của bạn.
Bạn có thể sao chép chức năng của hàm IFERROR bằng cách kết hợp hàm IF với hàm ISERROR.
Để tôi nhanh chóng chỉ cho bạn cách sử dụng kết hợp IF và ISERROR thay vì IFERROR.
Trong ví dụ trên, thay vì sử dụng IFERROR, bạn cũng có thể sử dụng công thức được hiển thị trong ô B3:
= IF (ISERROR (A3), "Không tìm thấy", A3)
Phần ISERROR của công thức kiểm tra lỗi (bao gồm cả lỗi # N / A) và trả về TRUE nếu tìm thấy lỗi, nếu không thì là FALSE.
- Nếu TRUE (chỉ ra lỗi), hàm IF trả về giá trị được chỉ định ("Không tìm thấy" trong trường hợp này).
- Nếu FALSE (có nghĩa là không có lỗi), hàm IF sẽ trả về giá trị này (A3 trong ví dụ trên).
IFERROR và IFNA
IFERROR xử lý tất cả các loại lỗi, trong khi IFNA chỉ xử lý lỗi # N / A.
Khi xử lý các lỗi do hàm VLOOKUP, bạn cần đảm bảo rằng mình sử dụng đúng công thức.
Khi bạn muốn xử lý các lỗi khác nhau, vui lòngSử dụng IFERROR.Lỗi hiện có thể do nhiều yếu tố gây ra (chẳng hạn như công thức sai, phạm vi được đặt tên sai chính tả, không tìm thấy giá trị tra cứu và giá trị trả về sai từ bảng tra cứu).IFERROR không quan trọng, nó thay thế tất cả các lỗi này bằng giá trị được chỉ định.
Sử dụng IFNA khi bạn chỉ muốn xử lý lỗi # N / A, nhiều khả năng là do công thức VLOOKUP không thể tìm thấy giá trị tra cứu.
