E0;nh cho m&#x
E1;y Mac Excel mang lại web Excel 2021 Excel 2021 mang đến Mac Excel 2019 Excel 2019 cho Mac Excel 2016 Excel 2016 cho Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel mang đến Windows Phone 10 xem th&#x
EA;m...Ẩn bớt
Chủ đề này trình bày các tại sao VLOOKUP thông dụng nhất cho một công dụng sai vào hàm và cung ứng các khuyến nghị để thế thế bằng phương pháp sử dụng INDEXvà MATCH.
Bạn đang xem: Hàm vlookup lỗi n/a
Mẹo: Ngoài ra, hãy xem thêm Thẻ tìm hiểu thêm Nhanh: Mẹo khắc phục và hạn chế sự gắng VLOOKUP trình bày các lý do phổ biến đổi #NA cố trong một tệp PDF thuận tiện. Chúng ta có thể chia sẻ tệp PDF với người khác hoặc in để tham khảo riêng.
Sự cố: giá trị tra cứu vãn không nằm tại cột thứ nhất trong tham table_array đang chọn
Một ràng buộc của hàm VLOOKUP là nó chỉ có thể tìm những giá trị bên trên cột ngoài cùng phía trái trong mảng bảng. Nếu quý giá tra cứu của chúng ta không phía trong cột đầu tiên của mảng, bạn sẽ gặp lỗi #N/A.
Trong bảng sau đây, cửa hàng chúng tôi muốn tróc nã xuất số đơn vị chức năng đã xuất bán cho Kale.
Lỗi #N/A rất có thể xảy ra vì giá trị tra cứu "Kale" xuất hiện thêm trong cột máy hai (Nông sản) của đối table_array A2:C10. Vào trường đúng theo này, Excel nhiều người đang tìm trong cột A, chứ chưa phải cột B.
Giảipháp : bạn cũng có thể thử khắc phục sự nạm này bằng phương pháp điều chỉnh hàm VLOOKUP để tham chiếu cột đúng. Nếu điều này không thể, hãy thử dịch chuyển các cột của bạn. Điều kia cũng rất có thể rất khó khăn thực thi, nếu như khách hàng có bảng tính to hoặc tinh vi trong đó quý hiếm ô là hiệu quả của các phép tính khác—hoặc hoàn toàn có thể có các nguyên nhân lô-gic khác tại sao tại sao chúng ta chỉ đơn giản và dễ dàng là ko thể dịch rời các cột. Phương án là sử dụng phối kết hợp hàm INDEX và MATCH, hàm này hoàn toàn có thể tra cứu giá trị trong một cột bất cứ vị trí của nó trong bảng tra cứu. Coi phần tiếp theo.
Cân đề cập việc áp dụng INDEX/MATCH để cố gắng thế
Hàm INDEX với MATCH là phần đa tùy chọn tốt cho các trường hợp, trong số đó VLOOKUP không thỏa mãn nhu cầu nhu mong của bạn. Ưu điểm chủ yếu của index/MATCH là bạn cũng có thể tra cứu giá trị trong một cột ở ngẫu nhiên vị trí nào trong bảng tra cứu. Index trả về cực hiếm từ bảng/phạm vi vẫn xác định—theo vị trí của nó. Match trả về vị trí kha khá của một giá trị trong bảng/phạm vi. Sử dụng index và MATCH cùng nhau trong bí quyết để tìm kiếm quý giá trong bảng/mảng bằng cách xác xác định trí tương đối của cực hiếm trong bảng/mảng.
Có một số tác dụng của việc áp dụng INDEX/MATCH thay vì chưng VLOOKUP:
Với index với MATCH, giá trị trả về không nhất thiết phải ở trong cùng một cột với cột tra cứu. giá trị này khác với hàm VLOOKUP, trong các số đó giá trị trả về phải nằm vào phạm vi sẽ xác định. Điều này đặc biệt quan trọng như gắng nào? cùng với hàm VLOOKUP, bạn phải ghi nhận số cột tất cả chứa cực hiếm trả về. Tuy nhiên việc này có vẻ không cạnh tranh khăn, nhưng bạn có thể rườm kiểm tra khi bao gồm một bảng khủng và cần đếm số cột. Bên cạnh ra, nếu khách hàng thêm/loại quăng quật cột trong bảng, chúng ta phải tính lại và update đối col_index_num số. Cùng với hàm INDEX và MATCH, ko yêu cầu đếm vày cột tra cứu khác cùng với cột có mức giá trị trả về.
Với index cùng MATCH, chúng ta có thể xác định một hàng hoặc một cột trong một mảng—hoặc khẳng định cả hai. Điều này còn có nghĩa là chúng ta cũng có thể tra cứu những giá trị cả theo theo hướng dọc và chiều ngang.
Có thể cần sử dụng index cùng MATCH nhằm tra cứu giá trị trong ngẫu nhiên cột nào. Không giống như hàm VLOOKUP— trong các số ấy bạn chỉ rất có thể tra cứu giá trị ở cột thứ nhất của bảng —hàm INDEX và MATCH sẽ hoạt động nếu quý giá tra cứu của công ty nằm vào cột đầu tiên, cột sau cuối hoặc ngẫu nhiên đâu sống giữa.
Hàm INDEX cùng MATCH đem lại khả năng linh hoạt khi tạo nên tham chiếu động mang đến cột chứa giá trị trả về. Điều này có nghĩa là bạn có thể thêm cột vào bảng của chính mình mà ko phá tan vỡ index với MATCH. Mặt khác, hàm VLOOKUP đã ngắt nếu như bạn cần thêm 1 cột vào bảng — vị nó sản xuất tham chiếu tĩnh mang đến bảng.
Index và MATCH chất nhận được khả năng khớp linh hoạt rộng với khớp. Index cùng MATCH hoàn toàn có thể tìm thấy hiệu quả khớp chính xác hoặc giá chỉ trị to hơn hoặc nhỏ dại hơn giá trị tra cứu. VLOOKUP đang chỉ kiếm tìm kiếm hiệu quả khớp ngay gần nhất với giá trị (theo khoác định) hoặc giá trị bao gồm xác. Theo khoác định, VLOOKUP cũng đưa định rằng cột thứ nhất trong mảng bảng được thu xếp theo bảng vần âm và giả sử bảng của người sử dụng không được tùy chỉnh theo cách đó, hàm VLOOKUP sẽ trả về công dụng khớp trước tiên trong bảng, mà lại đó rất có thể không cần là tài liệu mà bạn đang tìm kiếm.
Cú pháp
Để sản xuất cú pháp mang đến hàm INDEX/MATCH, bạn cần sử dụng đối số mảng/tham chiếu tự hàm INDEX và lồng cú pháp MATCH vào bên trong. Cách này còn có dạng:
=INDEX(array or reference, MATCH(lookup_value,lookup_array,
Chúng ta hãy sử dụng hàm INDEX/MATCH để sửa chữa hàm VLOOKUP từ bỏ ví dụ sinh sống trên. Cú pháp sẽ có hiệ tượng như sau:
=INDEX(C2:C10,MATCH(B13,B2:B10,0))
Nói một cách đối chọi giản, giờ đồng hồ Anh bao gồm nghĩa là:
=INDEX(trả về một quý giá từ C2:C10, mà lại sẽ MATCH(Kale, nằm ở đâu đó vào mảng B2:B10, trong số ấy giá trị trả về là giá trị đầu tiên tương ứng cùng với Kale))
Công thức search kiếm giá trị đầu tiên trong C2:C10 khớp ứng với Kale (trong B7) với trả về giá trị trong C7 (100), là giá bán trị đầu tiên khớp cùng với Kale.
Sự cố: không kiếm thấy hiệu quả khớp chính xác
Khi tham range_lookup là FALSE — cùng hàm VLOOKUP không thể tìm thấy hiệu quả khớp đúng chuẩn trong dữ liệu của bạn—nó trả về lỗi #N/A.
Giảipháp: trường hợp bạn chắc hẳn rằng rằng dữ liệu liên quan tồn tại trong bảng tính của bản thân mình và hàm VLOOKUP không nắm được, hãy dành thời hạn để xác minh rằng các ô được tham chiếu không có khoảng trắng ẩn hoặc ký tự không in. Quanh đó ra, hãy bảo đảm rằng những ô tuân theo đúng kiểu dữ liệu. Ví dụ: ô gồm số nên được định dạng là Số , chứkhông đề xuất Văn bản.
Ngoài ra, hãy quan tâm đến sử dụng hàm CLEAN hoặc TRIM nhằm dọn sạch dữ liệu trong những ô.
Sự cố: giá trị tra cứu nhỏ hơn giá bán trị nhỏ dại nhất vào mảng
Nếu đối range_lookup được đặt thành TRUE—và quý hiếm tra cứu nhỏ dại hơn giá bán trị nhỏ nhất trong mảng—bạn sẽ chạm mặt lỗi #N/A. TRUE search kiếm hiệu quả khớp tương đối trong mảng với trả về giá chỉ trị nhỏ dại nhất gần nhất so với giá trị tra cứu.
Trong lấy ví dụ sau đây, giá trị tra cứu vãn là 100, nhưng không tồn tại giá trị nào trong phạm vi B2:C10 nhỏ tuổi hơn 100; do đó có lỗi.
Giải pháp:
Sửa quý giá tra cứu vớt nếu cần.
Nếu chúng ta không thể biến đổi giá trị tra cứu và cần sự linh hoạt cao hơn với những giá trị khớp, hãy lưu ý đến sử dụng INDEX/MATCH vậy cho VLOOKUP—hãy coi mục ở trên trong bài viết này. Với index/MATCH, chúng ta có thể tra cứu những giá trị bự hơn, nhỏ dại hơn hoặc bởi giá trị tra cứu. Để biết thêm thông tin về phong thái sử dụng hàm INDEX/MATCH thay vì hàm VLOOKUP, hãy tham khảo mục trước trong chủ thể này.
Sự cố: Cột tra cứu giúp không được thu xếp theo sản phẩm công nghệ tự tăng dần
Nếu tham range_lookup được đặt thành TRUE— và một trong những cột tra cứu của người tiêu dùng không được sắp xếp theo thiết bị tự tăng ngày một nhiều (A-Z) — các bạn sẽ gặp lỗi #N/A.
Giải pháp:
Thay thay đổi hàm VLOOKUP để tìm công dụng khớp chính xác. Để làm điều đó, hãy đặt đối range_lookup thành FALSE. Không nhất thiết phải sắp xếp cho FALSE.
Dùng hàm INDEX/MATCH để tra cứu cực hiếm trong bảng không được xếp loại.
Sự cố: quý giá là một trong những điểm trôi nổi lớn
Nếu bạn có mức giá trị thời hạn hoặc số thập phân lớn trong các ô, Excel trả về lỗi #N/A bởi vì độ đúng đắn của điểm trôi nổi. Số lốt điểm trôi nổi là số đông số theo sau dấu thập phân. (Ví Excel lưu giữ trữ những giá trị thời hạn dưới dạng số vết 0 trôi nổi.) Excel thể lưu trữ số có những điểm trôi nổi rất lớn, vày vậy để hàm vận động đúng cách, số lốt hiển thị trôi nổi đang cần được làm tròn đến 5 vị trí thập phân.
Giảipháp: tinh giảm số bằng cách làm tròn số lên tới mức năm địa điểm thập phân cùng với hàm ROUND.
Bạn đề xuất thêm trợ giúp?
Bạn luôn rất có thể hỏi một chuyên gia trong xã hội Kỹ thuật Excel hoặc thừa nhận sự hỗ trợ trong
Cộng đồng trả lời.
Trong vượt trình thao tác làm việc với hàm Vlookup, hết sức nhiều các bạn gặp bắt buộc trường hợp hiệu quả trả về là kết quả lỗi. Có tương đối nhiều lỗi trong làm việc với hàm này nhưng mà thường xuyên chạm mặt phải nhất đó là lỗi #N/A hàm Vlookup, vì chưng vậy hôm nay mailinhschool.edu.vn sẽ giới thiệu đến chúng ta những nguyên nhân dẫn cho lỗi #N/A và hướng dẫn phương pháp để sửa đều lỗi đó gấp rút nhất. Mời chúng ta tham khảo chi tiết trong bài viết phía dưới.
NGUYÊN NHÂN GÂY LỖI #N/A! khi DÙNG HÀM VLOOKUP VÀ CÁCH KHẮC PHỤC
Để hoàn toàn có thể sửa cụ thể các lỗi, bọn chúng mình sẽ gửi ra đông đảo ví dụ ví dụ với từng phần nhằm giúp các bạn dễ hình dung nhé.
Lỗi #N/A! hàm Vlookup vị vùng tìm kiếm kiếm không đầy đủ
Nguyên nhân chính gây ra lỗi này là vì excel bắt buộc tìm thấy giá chỉ trị. Chúng mình gồm ví dụ sau: Tìm sản phẩm Xoài vào bảng dữ liệu:
Bước 1: Nhập hàm =VLOOKUP("XOÀI",A2:D6,4,0) vào bảng tính Excel.
Bước 2: dìm phím Enter để tìm tìm kết quả. Vị bảng tài liệu không mang tên cần tìm là XOÀI đề nghị Excel đã trả về hiệu quả là lỗi #N/A! (Như vào hình).
Cách xung khắc phục: kiểm soát dữ liệu đo lường của hàm và chỉnh sửa lại. Ở phía trên ta thay sản phẩm tìm kiếm từ XOÀI thanh lịch SẦU RIÊNG với phương pháp =VLOOKUP("Sầu Riêng",B2:D6,2,0), và Excel trả tác dụng như hình.
Lỗi #N/A Vlookup vì không thắt chặt và cố định vùng tìm kiếm khi copy công thức
Nguyên nhân thiết yếu dẫn đến lỗi này đó là các bạn không cố định vùng search kiếm, và nếu bạn không ráng định, excel sẽ mặc định phương pháp đó với copy không còn xuống bảng tài liệu của bạn. Đây là ví dụ khiến cho bạn hình dung: trong minh họa đã tìm sản phẩm Sầu Riêng mà lại không cố định và thắt chặt vùng dữ liệu. Khi tiến hành copy xuống dưới thì Excel vẫn báo lỗi #N/A.
Cách tự khắc phục: Bạn chỉ việc sửa lại công thức bằng cách cố định vùng dữ liệu. Phương pháp làm như sau
Bước 1: trong công thức, triển khai bôi đen ô B2:D6 như hình.
Bước 2: tiếp đến bạn hãy ấn phím F4 trên keyboard và excel sẽ tự động hóa cố định vùng dữ liệu được chọn mang lại bạn. Bởi vậy là bạn đã sửa hoàn thành lỗi rồi đó.
Lỗi #N/A! khi thực hiện hàm Vlookup, Hlookup kết hợp với MID, LEFT, RIGHT
Hàm Vlookup kết hợp với hàm MIDNguyên nhân thiết yếu đó là lúc định dạng không trùng khớp giữa công dụng hiển thị và tài liệu tra cứu. Ta có ví dụ dùng hàm Vlookup để tìm điểm khu vực dựa cùng số báo danh của sinh viên.
Bước 1: trên ô tài liệu nhập hàm =VLOOKUP(MID(A3,4,1),$C$9:$F$10,2,0) như hình.
Trong đó:
MID(A3,4,1): Lấy 1 kí tự đồ vật 4 làm việc ô A3 (cụ thể là số 1).$C$9:$F$10: Bảng chứa khu vực thi cùng điểm khu vực.2: Lấy điểm khu vực vực.0: Lấy giá chỉ trị đúng mực tuyệt đối.
Bước 2: Khi nhấn phím enter excel sẽ trả về hiệu quả lỗi như hình.
Cách khắc phục: Có 2 giải pháp khắc phục lỗi vào ví dụ kia là:
Cách thứ nhất, chuyển kết quả của hàm Mid về dạng số bởi hàm Value. Đối với phương pháp này các bạn hãy thêm hàm Value vào như sau: =VLOOKUP(VALUE(MID(A3,4,1)),$C$9:$F$10,2,0). Kế tiếp nhấn Enter nhằm hoàn tất.
Ta gồm ví dụ sau: Điền tên thương hiệu vào mã lô sản phẩm tương ứng.
Nhập vào bảng tính Excel công thức hàm Vlookup: =VLOOKUP(LEFT(B3,3),E8:F11,2,0) như hình.
Trong đó:
VLOOKUP: là tên hàm đo lường và tính toán dò tìm kiếm dữ liệu.LEFT(A3,3): Là lệnh hàm tìm tài liệu ô A3 với 3 cam kết tự.E8:F11: Là vùng tài liệu tìm kiếm.
Bước 2: bạn ấn phím Enter. Excel vẫn hiển thị tác dụng như hình.
Cách khắc phục: Để khắc chế lỗi này chúng ta kiểm tra với chỉnh lại giá trị trong hàm LEFT vì chưng ô bảng tính chỉ chứa 2 cam kết tự mà ở đây mình mang giá trị bởi 3 như sau: =VLOOKUP(LEFT(A3,2),HANGSANXUAT,2,0). Khi đang sửa xong excel vẫn trả về tác dụng như các bạn muốn.
Và hàm RIGHT bạn cũng tiến hành cách là tương tự như như cùng với hàm LEFT.
KẾT THÚC
Qua bài bác viết, hy vọng bạn đã có những kỹ năng và kiến thức cơ bản để rất có thể khắc phục được lỗi #N/A khi dùng hàm Vlookup vào Excel . Và đừng quên ghé thăm blog của bọn chúng mình thường xuyên để học được nhiều kiến thức hữu ích bạn nhé. Cảm ơn bạn đã đọc nội dung bài viết và chúc bạn áp dụng thành công.
