Ketoanmvb - Dịch vụ kế toán, quyết toán thuế uy tín tại Hà Nội: Excel IFERROR & VLOOKUP – bẫy # N / A lỗi và thực hiện các lượt xem liên tiếp

Trong hướng dẫn này, chúng ta sẽ xem xét cách sử dụng các hàm IFERROR và VLOOKUP để bẫy và xử lý các lỗi khác nhau. Ngoài ra, bạn sẽ tìm hiểu cách thực hiện các lần xem liên tiếp trong Excel bằng cách lồng nhiều hàm IFERROR vào nhau.

Excel VLOOKUP và IFERROR – hai hàm này có thể khá khó hiểu một cách riêng biệt, hãy để một mình khi chúng được kết hợp. Trong bài viết này, bạn sẽ tìm thấy một vài ví dụ dễ làm theo địa chỉ sử dụng phổ biến và minh họa rõ ràng logic của công thức.

Nếu bạn không có nhiều kinh nghiệm với IFERROR và VLOOKUP chức năng, có thể là một ý tưởng tốt để sửa đổi cơ bản của họ trước bằng cách theo các liên kết ở trên.

Công thức IFOOKROR 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ẽ đưa ra lỗi # N / A, như sau:

Tùy thuộc vào nhu cầu kinh doanh của bạn, bạn có thể muốn ngụy trang lỗi bằng văn bản của riêng bạn, số không hoặc một ô 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 của riêng bạn

Nếu bạn muốn thay thế ký hiệu lỗi tiêu chuẩn bằng văn bản tùy chỉnh của mình, hãy bọc công thức VLOOKUP của bạn trong IFERROR và nhập bất kỳ văn bản nào bạn muốn vào 2^thứ tranh luận (value_if_error), ví dụ “Không tìm thấy”:

IFERROR (VLOOKUP (Giáo dục),”Không tìm thấy”)

Với giá trị tra cứu bằng 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 như sau:

=IFERROR(VLOOKUP(B2,'Lookup table'!$A$2:$B$5, 2, FALSE), "Not found")

Ảnh chụp màn hình bên dưới hiển thị công thức Excel IFERROR VLOOKUP của chúng tôi đang hoạt động:
Iferror Vlookup công thức để thay thế lỗi bằng văn bản của riêng bạn.

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

Theo cách tương tự, bạn có thể sử dụng CHỈ SỐ INDEX 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 của chính bạn khi không tìm thấy gì.

Ví dụ 2. IFERROR với 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 cứ điều gì khi không tìm thấy giá trị tra cứu, hãy để IFERROR hiển thị chuỗi rỗng (“”):

IFERROR (VLOOKUP (Giáo dục), “”)

Trong ví dụ của chúng tôi, công thức như sau:

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

Như bạn có thể 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ị bằng không, đặt 0 trong đối số cuối cùng:

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

Lời cảnh báo! Hàm IFERROR của Excel bắt tất cả các loại lỗi, không chỉ # N / A. Nó 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 dấu tất cả các lỗi có thể xảy ra, IFERROR Vlookup là cách tốt nhất. Nhưng nó có thể là một kỹ thuật không khôn ngoan trong nhiều tình huống.

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

Điểm mấu chốt là: rất cẩn thận khi chọn bạn đồng hành cho công thức VLOOKUP của bạn 🙂

Nest IFERROR trong VLOOKUP để luôn tìm thấy thứ gì đó

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

Lấy ví dụ của chúng tôi hơn nữa, hãy tạo một số loại bảng điều khiển cho người dùng của chúng tôi sẽ hiển thị cho họ số mở rộng của một văn phòng cụ thể. Một cái gì đó như thế này:
dữ liệu nguồn cho VLOOKUP với IFERROR lồng nhau

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 thông thường 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 của bạn nhập số hợp lệ trong 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 nhúng công thức trên vào giá trị đối số của IFERROR và đặt một Vlookup khác vào value_if_error tranh luận.

Công thức hoàn chỉnh hơi dài, nhưng nó hoạt động hoàn hảo:

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

Nếu tìm thấy số văn phòng, người dùng sẽ nhận được số máy lẻ tương ứng:
Công thức IFERROR với hai vlookups

Nếu không tìm thấy số văn phòng, phần mở rộng văn phòng trung tâm được hiển thị:
Nếu vlookup đầu tiên không tìm thấy gì, vlookup thứ hai sẽ có một giá trị chung.

Để làm cho công thức nhỏ gọn hơn một chút, 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 không (xin lưu ý rằng chúng tôi đã đặt col_index_num đến 1 cho công thức tra cứu và trả về giá trị từ cột A): VLOOKUP (D2, $ A $ 2: $ B $ 7.1, FALSE)

Nếu không tìm thấy số văn phòng đã chỉ định, thì chúng tôi sẽ tìm chuỗi “văn phòng trung tâm”, 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 bọc VLOOKUP đầu tiên trong IFERROR và lồng toàn bộ kết hợp này 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)

Vâng, một công thức hơi khác nhau, cùng một kết quả:
Vlookup với chức năng IFERROR lồng nhau

Nhưng lý do để tìm kiếm “văn phòng trung tâm” là gì, bạn có thể hỏi tôi. Tại sao không cung cấp số mở rộng trực tiếp trong IFERROR? Bởi vì phần mở rộng có thể thay đổi tại một số đ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 chỉ một lần trong bảng nguồn, mà không phải lo lắng về việc cập nhật từng công thức VLOOKUP của bạn.

Cách thực hiện các VLOOKUP tuần tự trong Excel

Trong tình huống khi bạn cần thực hiện cái gọi là tuần tự hoặc là xích Vlookups trong Excel tùy thuộc vào việc tra cứu trước thành công hay thất bại, lồng hai hoặc nhiều hàm IFERROR để chạy Vlookups của bạn từng cái một:

IFERROR (VLOOKUP (Giáo dục), IFERROR (VLOOKUP (Giáo dục), IFERROR (VLOOKUP (Giáo dục),”Không tìm thấy”)))

Công thức hoạt động với logic sau:

Nếu VLOOKUP đầu tiên không tìm thấy gì, IFERROR đầu tiên bẫy lỗi và chạy một VLOOKUP khác. Nếu VLOOKUP thứ hai không thành công, IFERROR thứ hai sẽ gặp lỗi và chạy VLOOKUP thứ ba, v.v. Nếu tất cả các Vlookups vấp ngã, IFERROR cuối cùng trả về tin nhắn của bạn.

Công thức IFERROR lồng nhau này đặc biệt hữu ích khi bạn phải xem qua Vlookup nhiều tờ 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 bảng tính khác nhau (số văn phòng trong ví dụ này) và bạn muốn nhận được một 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 bảng tính khác nhau (Bắc, Nam và Tây), công thức sau đây có tác dụng:

=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 “xem chuỗi” của chúng tôi tìm kiếm trong cả ba trang theo thứ tự chúng tôi lồng chúng vào công thức và mang đến kết quả khớp đầu tiên mà nó tìm thấy:
Các hàm IFERROR lồng nhau để thực hiện các lần xem liên tiếp