Cách xử lý lỗi N/A, NAME, VALUE của hàm VLOOKUP

Sửa lỗi #N/A của VLOOKUP trong Excel

Trong công thức Vlookup, thông tin lỗi # N / A ( có nghĩa là “ không khả dụng ” ) được hiển thị khi Excel không hề tìm thấy giá trị tra cứu. Có vài nguyên do khiến điều đó hoàn toàn có thể xảy ra .

1. Lỗi đánh máy hoặc sắp xếp nhầm trong giá trị tra cứu

Lỗi xếp nhầm chữ thường xảy ra khi bạn làm việc với lượng dữ liệu lớn bao gồm hàng ngàn hàng hoặc khi giá trị tra cứu được gõ trực tiếp vào công thức.

2. Lỗi # N/A trong tìm kiếm gần đúng bằng hàm VLOOKUP

Nếu bạn đang sử dụng một công thức với đối sánh tương quan gần đúng ( đối số range_lookup là TRUE hoặc bỏ lỡ ), công thức Vlookup của bạn hoàn toàn có thể trả về lỗi # N / A trong hai trường hợp :

• Nếu giá trị tra cứu nhỏ hơn giá trị nhỏ nhất trong mảng tra cứu.
• Nếu cột tra cứu không được sắp xếp theo thứ tự tăng dần.

3. Lỗi # N/A trong tìm kiếm chính xác hàm VLOOKUP

Nếu bạn đang tìm kiếm với đối sánh tương quan đúng chuẩn ( đối số range_lookup đặt thành FALSE ) và không tìm thấy giá trị đúng mực, cũng sẽ Open lỗi # N / A

4. Cột tra cứu không phải là cột bên trái của bảng dữ liệu

Có thể bạn đã biết, một trong những hạn chế đáng kể nhất của hàm VLOOKUP là nó không hề dò tìm phía bên trái cột tra cứu, do đó cột tra cứu luôn luôn là cột bên trái ngoài cùng trong bảng dò tìm. Trong trong thực tiễn, tất cả chúng ta thường quên đi điều này và dẫn đến việc hàm VLOOKUP không hoạt động giải trí vì lỗi N / A .

Giải pháp: Nếu không thể tái cấu trúc dữ liệu để cột tra cứu là cột bên trái, bạn có thể sử dụng kết hợp các hàm INDEX và MATCH để thay thế linh hoạt cho hàm VLOOKUP. Bạn sẽ tìm thấy thông tin chi tiết và ví dụ về công thức trong hướng dẫn – Cách dùng hàm INDEX / MATCH để tra cứu các giá trị bên trái.

Xem thêm: Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel

5. Số được định dạng dưới dạng văn bản

Một lỗi N / A khác của hàm VLOOKUP là do những số lượng được định dạng dưới dạng văn bản, trong cột dò tìm hoặc vùng tra cứu .
Điều này thường xảy ra khi bạn nhập tài liệu từ một cơ sở tài liệu bên ngoài hoặc do bạn đã gõ dấu nháy đơn trước số .

Các số này cũng hoàn toàn có thể được tàng trữ ở định dạng chung. Trong trường hợp này, có một tín hiệu đáng quan tâm – số được canh lề trái của ô .

Giải pháp: Nếu đây chỉ là một số duy nhất, chỉ cần nhấp vào biểu tượng lỗi và chọn “Convert To Number” từ danh sách tùy chọn.

Nếu nhiều số bị ảnh hưởng, hãy chọn tất cả, nhấp chuột phải vào vùng chọn, sau đó chọn Format Cells>Number tab>Number và nhấp OK.

Xem ngay : Học Excel nâng cao ở đâu

6. Các khoảng trống lớn hoặc dấu đầu dòng

Đây là nguyên do rõ ràng nhất của lỗi Vlookup N / A vì mắt người không hề nhìn thấy những khoảng trống đặc biệt quan trọng, nhất là khi thao tác với những bảng lớn, nơi hầu hết những mục nằm dưới thanh cuộn .

Trường hợp 1: Khoảng trắng trong cột dò tìm (với công thức VLOOKUP)

Nếu những khoảng trống dư thừa Open trong bảng chính của bạn, bạn hoàn toàn có thể bảo vệ công thức Vlookup bằng cách gói đối số lookup_value với hàm TRIM :

= VLOOKUP (TRIM ($F2), $A$2: $C$10,3, FALSE)

Trường hợp 2: Khoảng trắng trong cột tra cứu

Nếu có khoảng chừng trắng Open trong cột tra cứu, không có cách nào để tránh được lỗi VLOOKUP # N / A. Thay vì dùng hàm VLOOKUP, bạn hoàn toàn có thể sử dụng một công thức mảng với sự tích hợp những hàm INDEX / MATCH và TRIM :

= INDEX ($C$2: $C$10, MATCH (TRUE, TRIM ($A$2: $A$10) = TRIM ($F$2), 0))

Vì đây là một công thức mảng, đừng quên nhấn Ctrl + Shift + Enter chứ không phải là phím Enter để hoàn thành nó:

Lỗi # NAME hàm VLOOKUP

Đây là trường hợp dễ xảy ra nhất – lỗi # NAME Open nếu bạn vô tình đánh sai tên hàm. Giải pháp hiển nhiên là – kiểm tra chính tả 🙂

Hàm VLOOKUP không hoạt động (giới hạn của hàm, vấn đề và giải pháp)

Ngoài việc có một cú pháp khá phức tạp, VLOOKUP có nhiều hạn chế đáng kể so với bất kể hàm Excel khác. Do những hạn chế này, bạn có vẻ như khó để tạo đúng chuẩn công thức Vlookup. Dưới đây bạn sẽ tìm thấy những giải pháp cho một vài trường hợp thông dụng khi VLOOKUP không thành công xuất sắc .

1. VLOOKUP không phân biệt chữ hoa chữ thường

Hàm VLOOKUP không phân biệt chữ hoa và chữ thường. Vì vậy, nếu bảng của bạn có nhiều mục tựa như chỉ khác nhau chữ hoa, chữ thường, công thức Vlookup sẽ trả lại giá trị tiên phong được tìm thấy .

Giải pháp: Sử dụng một hàm Excel khác có thể thực hiện tra cứu theo chiều dọc (LOOKUP, SUMPRODUCT, INDEX / MATCH) kết hợp với hàm EXACT để có thể chọn đúng trường hợp. Bạn có thể tìm thấy các giải thích chi tiết và các ví dụ công thức trong hướng dẫn – 4 cách để làm một vlookup phân biệt chữ thường và chữ hoa trong Excel.

Thủ thuật Excel nâng cao hay nhất 2018

2. VLOOKUP trả về giá trị tìm thấy đầu tiên

Như bạn đã biết, hàm VLOOKUP trả về giá trị tiên phong nó tìm thấy trong cột trả về tương thích với giá trị tra cứu. Tuy nhiên, bạn hoàn toàn có thể buộc nó trả về cái thứ 2, 3, 4 hoặc bất kể trường hợp nào mà bạn muốn. Nếu bạn cần phải nhận được tổng thể những giá trị, bạn sẽ phải sử dụng tích hợp những hàm INDEX, SMALL và ROW.

Giải pháp: Các ví dụ công thức có sẵn để tải về tại đây:

• Nhận biết những lần Open thứ 2, 3, 4, v.v …
• Nhận toàn bộ những lần Open trùng lặp của giá trị tra cứu

3. Một cột mới được chèn vào hoặc gỡ bỏ khỏi bảng

Đáng tiếc là những công thức VLOOKUP ngừng hoạt động giải trí mỗi khi một cột mới bị xóa hoặc được thêm vào bảng tra cứu. Điều này xảy ra chính do cú pháp của hàm VLOOKUP nhu yếu bạn cung ứng hàng loạt bảng cũng như 1 số ít nhất định cho biết cột nào bạn muốn trả lại tài liệu. Đương nhiên, cả bảng và số cột trả lại đổi khác khi bạn xóa một cột hiện có hoặc chèn một cột mới .

Giải pháp: INDEX / MATCH lại được dùng lần nữa để giải quyết vấn đề này. Trong hàm INDEX & MATCH, bạn chỉ định các cột tra cứu và cột trả kết quả một cách riêng biệt, kết quả là bạn có thể xóa hoặc chèn nhiều cột như bạn muốn mà không cần lo lắng về việc cập nhật mọi công thức vlookup liên quan .

4. Các ô tham chiếu thay đổi khi sao chép công thức đến ô khác

Giải pháp: Luôn sử dụng tham chiếu ô tuyệt đối (với dấu $) trong vùng chọn, ví dụ: $A$2: $C$100 hoặc $A:$C. Trong thanh công thức, bạn có thể nhanh chóng chuyển đổi giữa các loại tham chiếu khác nhau bằng cách nhấn F4.

Hàm VLOOKUP với hàm IFERROR / ISERROR

Nếu bạn không muốn để người dùng thấy toàn bộ những thông tin lỗi N / A, VALUE hoặc NAME, bạn hoàn toàn có thể trả lại ô trống hoặc hiển thị thông điệp của riêng bạn. Bạn hoàn toàn có thể làm điều này bằng cách lồng công thức VLOOKUP của bạn trong hàm IFERROR trong Excel 2013, 2010 và 2007 hoặc hàm IF / ISERROR trong những phiên bản Excel trước đó .

Sử dụng VLOOKUP với IFERROR

Cú pháp của hàm IFERROR rất đơn thuần

IFERROR (value, value_if_error)

Bạn nhập giá trị để kiểm tra lỗi trong đối số thứ nhất, và trong đối số thứ 2 bạn chỉ định giá trị trả về nếu lỗi xảy ra. Ví dụ, công thức IFERROR / VLOOKUP sau trả về một ô trống khi không tìm thấy giá trị tra cứu :

= IFERROR (VLOOKUP ($F$2, $B$2: $C$10,2, FALSE), “”)

Nếu bạn thích hiển thị thông điệp của mình thay vì những bộc lộ lỗi thường thì, hãy đánh chúng vào dấu ngoặc kép như thế này :

=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),”Oops, no match is found. Please try again!”)

Dùng hàm VLOOKUP với ISERROR

Hàm IFERROR có ở phiên bản Excel 2007, trong những phiên bản Excel trước bạn sẽ phải sử dụng phối hợp hàm IF và ISERROR như thế này :

=IF(ISERROR(VLOOKUP formula), “Your message if any“, VLOOKUP formula)

Ví dụ, đây là công thức IF / ISERROR / VLOOKUP tương tự như công thức IFERROR / VLOOKUP ở trên:

=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))

Để hoàn toàn có thể biết thêm nhiều hàm nâng cao trong Excel cũng như sử dụng tốt hơn những công cụ của Excel, những bạn hãy tham gia ngay khóa học EX101 – Excel từ cơ bản tới nâng cao của Học Excel Online. Đây là khóa học chi tiết cụ thể và khá đầy đủ nhất, giúp bạn tăng trưởng kỹ năng và kiến thức một cách không thiếu, có mạng lưới hệ thống. Đặc biệt khóa học này không số lượng giới hạn thời hạn học tập, nên bạn hoàn toàn có thể học tự do và xem lại bất kể khi nào .
Hiện nay mạng lưới hệ thống đang có nhiều tặng thêm khi bạn ĐK khóa học này. Chi tiết xem tại :

Source: https://bem2.vn
Category: TỔNG HỢP