Trong bài viết này, Học Excel Online sẽ xem xét cách sử dụng và cách sửa lỗi #N/A khi kết hợp hàm IFERROR với hàm VLOOKUP trong Excel. Ngoài ra, bạn sẽ học cách thực hiện liên tiếp các VLOOKUP trong Excel bằng cách chèn nhiều hàm IFERROR vào một hàm khác nhau.

VLOOKUP và IFERROR trong Excel – hai hàm này này có thể khá khó hiểu, chưa nói đến khi chúng được kết hợp với nhau. Trong bài viết này, bạn sẽ được học một vài ví dụ để dễ dàng giải quyết cách sử dụng và các minh họa rõ ràng của công thức.

Nếu bạn không có nhiều kinh nghiệm với hàm IFERROR và VLOOKUP, có thể bạn nên tham khảo các khái niệm cơ bản của chúng trước bằng cách làm theo nhấn vào các liên kết.

Hàm IFERROR VLOOKUP để xử lý #N/A và các lỗi khác

Khi Excel VLOOKUP không tìm thấy giá trị tra cứu, nó sẽ xuất hiện lỗi #N/A, như sau:

Tùy thuộc vào doanh nghiệp của bạn, bạn có thể muốn che giấu lỗ bằng văn bản, không hoặc khoảng trống.

Ví dụ 1: Công thức IFERROR VLOOKUP thay thế tất cả các lỗi bằng văn bản.

Nếu bạn muốn thay thế ký hiệu lỗi bằng văn bản tùy chỉnh, hãy chèn công thức IFERROR trong VLOOKUP và nhập bất kỳ văn bản nào bạn muốn vào tham số thứ hai (value_if_error), ví dụ: “Not found”:

IFERROR(VLOOKUP(…),”Not found”)

Với giá trị tra cứu ở B2 trong bảng chính và phạm vi tra cứu A2:B4 trong bảng tra cứu, công thức có dạng sau:

=IFERROR(VLOOKUP(B2,’Lookup table’!$A$2:$B$5, 2, FALSE), “Not found”)

Hình bên dưới hiển thị công thức hàm IFERROR VLOOKUP trong Excel:

Kết quả trông dễ hiểu hơn và ít đáng sợ hơn nhiều, phải không?

Theo cách tương tự, bạn có thể sử dụng INDEX MATCH cùng với IFERROR:

=IFERROR(INDEX(‘Lookup table’!$B$2:$B$5,MATCH(B2,’Lookup table’!$A$2:$A$5,0)), “Not found”)

Công thức IFERROR INDEX MATCH đặc biệt hữu ích khi bạn muốn kéo các giá trị từ một cột nằm ở bên trái cột tra cứu (tra cứu bên trái) và trả về văn bản khi không tìm thấy gì.

Ví dụ 2: IFERROR với hàm VLOOKUP để trả về trống hoặc 0 nếu không tìm thấy gì

Nếu bạn không muốn hiển thị bất kỳ thứ gì khi không tìm thấy giá trị tra cứu, hãy để hàm IFERROR hiển thị một chuỗi trống (“”):

IFERROR(VLOOKUP(…),””)

Trong ví dụ này, công thức như sau:

=IFERROR(VLOOKUP(B2,’Lookup table’!$A$2:$B$5, 2, FALSE), “”) 

Nếu như bạn thấy, nó không trả về gì khi giá trị tra cứu không có trong danh sách tìm kiếm.

Nếu bạn muốn thay thế lỗi bằng giá trị 0, hãy đặt 0 vào tham số cuối cùng:

=IFERROR(VLOOKUP(B2,’Lookup table’!$A$2:$B$5, 2, FALSE), 0)

Chú ý: Hàm IFERROR trong Excel bắt tất cả các lỗi, không chỉ lỗi #N/A. Điều đó là tốt hay xấu? Tất cả phụ thuộc vào mục tiêu của bạn. Nếu bạn muốn che giấu tất cả các lỗi có thể xảy ra, IFERROR VLOOKUP là cách để thực hiện. Nhưng nó có thể là một kỹ thuật không khôn ngoan trong ví dụ này.

Ví dụ: Nếu bạn đã tạo một dải ô đã đặt tên cho dữ liệu bảng của mình và viết sai chính tả tên đó trong công thức VLOOKUP, IFERROR sẽ bắt lỗi #NAME? và thay thế nó bằng “not found” hay bất kỳ văn bản nào khác bạn cung cấp. Do đó, bạn có thể không bao giờ biết công thức của mình đưa ra kết quả sai trừ khi bạn tự phát hiện lỗi typo. Trong trường hợp này, một cách tiếp cận hợp lý hơn sẽ chỉ mắc lỗi #N/A. Đối với điều này, hãy sử dụng công thức IFNA VLOOKUP trong Excel cho Office 365, Excel 2009, Excel 2016 và Excel 3013, IF ISNA VLOOKUP trong tất cả phiên bản của Excel.

Điều mấu chốt là hãy cẩn thận khi chọn hàm phù hợp cho công thức VLOOKUP.

Chèn IFERROR vào trong hàm VLOOKUP để tìm kiếm

Hãy tưởng tượng tình huống sau: bạn tìm kiếm một giá trị cụ thể trong một danh sách và không tìm thấy nó. Bạn có những lựa chọn nào? Gặp lỗi N/A hay hiển thị thông báo. Trên thực tế, có một tùy chọn thứ ba – nếu VLOOKUP của bạn gặp sự cố, thì hãy tìm kiếm thứ khác.

Ví dụ: hãy tạo một số loại bảng thông tin cho người dùng mà nó sẽ hiển thị số máy chi nhánh cho từng văn phòng cụ thể. 

Vì vậy, làm thế nào để bạn kéo phần mở rộng từ cột B dựa trên số văn phòng trong D2? Với công thức VLOOKUP này:

=VLOOKUP($D$2,$A$2:$B$7,2,FALSE)

Và nó sẽ hoạt động tốt miễn là người dùng nhập một số hợp lệ vào D2. Nhưng nếu người dùng nhập một số không tồn tại thì sao? Trong trường hợp này, hãy để họ gọi cho văn phòng trung tâm! Đối với điều này, bạn gán công thức trên vào tham số giá trị của IFERROR và đặt VLOOKUP khác trong tham số value_if_error

Công thức hoàn chỉnh hơi dài nhưng hoạt động rất hiệu quả:

=IFERROR(VLOOKUP(“office “&$D$2,$A$2:$B$7,2,FALSE),VLOOKUP(“central office”,$A$2:$B$7,2,FALSE))

Nếu oso văn phòng được tìm thấy, người dùng sẽ nhận được số máy tương ứng từ chi nhánh:

Nếu không tìm thấy số chi nhánh, phần mở rộng văn phòng trung tâm sẽ hiển thị:

Để công thức thu gọn hơn, bạn có thể sử dụng một cách tiếp cận khác:

Trước tiên, hãy kiểm tra xem số trong D2 có trong cột tra cứu hay không (vui lòng lưu ý rằng chúng ta đặt col_index_num thành 1 để công thức tìm kiếm và trả về giá trị ở cột A): VLOOKUP(D2,$A$2:$B$7,1,FALSE)

Nếu số văn phòng được chỉ định không được tìm thấy, thì chúng ta tìm kiếm chuỗi “văn phòng trung tâm” (central office), chuỗi này chắc chắn nằm trong danh sách tra cứu. Đối với điều này, bạn chèn  hàm IFERROR vào hàm VLOOKUP và lồng toàn bộ tổ hợp này vào bên trong một hàm VLOOKUP khác:

=VLOOKUP(IFERROR(VLOOKUP(D2,$A$2:$B$7,1,FALSE),”central office”),$A$2:$B$7,2)

Đây là một công thức khác nhưng cho ra cùng kết quả:

Nhưng lý do để tra cứu “văn phòng trung tâm” (central office) là gì. Tại sao không cung cấp trực tiếp số máy chi nhánh trong IFERROR? Vì phần mở rộng có thể thay đổi vào một thời điểm trong tương lai. Nếu điều này xảy ra, bạn sẽ phải cập nhật dữ liệu của mình một lần trong bảng nguồn mà không cần lo lắng về việc cập nhật từng công thức VLOOKUP.

Cách thực hiện dãy hàm VLOOKUP trong Excel 

Trong tình huống khi bạn cần thực hiện cái gọi là VLOOKUP liên hoàn hoặc chuỗi trong Excel tùy thuộc vào việc tra cứu trước đó thành công hay thất bại, hãy chèn hai hay nhiều hàm IFERROR để chạy VLOOKUP từng cái một:

IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…),”Not found”)))

Công thức hoạt động logic như sau:

Nếu hàm VLOOKUP đầu tiên không tìm thấy gì, hàm IFERROR đầu tiên mắc lỗi và chạy một hàm VLOOKUP khác. Nếu làm VLOOKUP thứ hai thất bại , IFERROR thứ hai bắt lỗi và chạy hàm VLOOKUP thứ 3,… Nếu tất cả các VLOOKUP gặp sự cố, IFERROR cuối cùng sẽ trả về thông báo.

Công thức chuỗi IFERROR này đặc bijet hữu ích khi bạn phải VLOOKUP trên nhiều trang tính như thể hiện trong ví dụ dưới đây.

Giả sử, bạn có ba danh sách dữ liệu đồng nhất trong ba trang tính khác nhau (số văn phòng trong ví dụ này) và bạn muốn nhận phần mở rộng cho một số nhất định.

Giả sử giá trị tra cứu nằm trong ô A2 trong trang tính hiện tại và phạm vi tra cứu là A2:B5 trong 3 trang tính khác nhau (North, South và West), công thức sau hoạt động tốt:

=IFERROR(VLOOKUP(A2,North!$A$2:$B$5,2,FALSE), IFERROR(VLOOKUP(A2,South!$A$2:$B$5,2,FALSE),  IFERROR(VLOOKUP(A2,West!$A$2:$B$5,2,FALSE),”Not found”)))

vì vậy, công thức “chuỗi VLOOKUP” tìm kiếm trong cả ba trang tính theo thứ tự đã chèn chúng vào trong công thức và mang lại hiệu quả phù hợp đầu tiên mà nó tìm thấy:

Đây là cách sử dụng hàm IFERROR với hàm VLOOKUP trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi tới với những kiến thức bổ ích về Kế toán bằng Excel