Excel mang đến sanrong.vn 365 Excel đến sanrong.vn 365 dành mang đến máy Mac Excel mang đến web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel năm 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel for Windows Phone 10 coi thêm...Ít hơn

Chủ đề này thể hiện các vì sao VLOOKUP thịnh hành nhất mang lại một kết quả sai vào hàm và cung cấp các lời khuyên để ráng thế bằng phương pháp sử dụng INDEXvà MATCH.

Bạn đang xem: Hàm vlookup bị 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ự cụ VLOOKUP trình bày các vì sao phổ biến hóa #NA rứa trong một tệp PDF thuận tiện. Bạn có thể chia sẻ tệp PDF với những người khác hoặc in để tham khảo riêng.


Sự cố: quý giá tra cứu vãn không nằm ở cột thứ nhất trong tham table_array đã chọn

Một buộc ràng của hàm VLOOKUP là nó chỉ có thể tìm các giá trị trên cột ngoại trừ cùng bên trái trong mảng bảng. Nếu cực hiếm tra cứu của người tiêu dùng không nằm trong cột đầu tiên của mảng, bạn sẽ gặp lỗi #N/A.

Trong bảng sau đây, shop chúng tôi muốn truy nã xuất số đơn vị chức năng đã xuất bán cho Kale.

*

Lỗi #N/A hoàn toàn có thể xảy ra bởi giá trị tra cứu vãn "Kale" xuất hiện thêm trong cột sản phẩm 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 vào cột A, chứ không hẳn cột B.

Giảipháp : chúng ta cũng có thể thử hạn chế và 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 ấy không thể, hãy thử di chuyển các cột của bạn. Điều kia cũng hoàn toàn có thể rất cạnh tranh thực thi, nếu như bạn có bảng tính béo hoặc phức tạp trong đó quý hiếm ô là hiệu quả của các phép tính khác—hoặc rất có thể có các lý do lô-gic khác vì sao tại sao chúng ta chỉ đơn giản là không thể dịch rời các cột. Giải pháp là sử dụng phối hợp hàm INDEX và MATCH, hàm này hoàn toàn có thể tra cứu quý giá trong một cột bất kỳ vị trí của chính nó trong bảng tra cứu. Coi phần tiếp theo.

Cân nhắc việc áp dụng INDEX/MATCH để vắt thế

Hàm INDEX và MATCH là rất nhiều tùy chọn xuất sắc cho nhiều trường hợp, trong các số ấy VLOOKUP không đáp ứng nhu ước của bạn. Ưu điểm chủ yếu của index/MATCH là chúng ta cũng có thể tra cứu quý hiếm trong một cột ở bất kỳ vị trí làm sao trong bảng tra cứu. Index trả về quý giá từ bảng/phạm vi đang xác định—theo địa điểm của nó. Match trả về vị trí tương đối của một giá trị trong bảng/phạm vi. Sử dụng index với MATCH cùng nhau trong cách làm để tìm kiếm quý hiếm trong bảng/mảng bằng phương pháp xác định vị trí kha khá của cực hiếm trong bảng/mảng.

Có một số công dụng của việc thực hiện INDEX/MATCH thay vày VLOOKUP:

Với index và MATCH, giá trị trả về không cần phải ở trong cùng một cột cùng với cột tra cứu. quý hiếm này khác với hàm VLOOKUP, trong số ấy giá trị trả về bắt buộc nằm trong phạm vi vẫn xác định. Điều này đặc biệt như núm nào? với hàm VLOOKUP, bạn phải ghi nhận số cột tất cả chứa quý giá trả về. Tuy vậy việc này có vẻ không khó khăn khăn, nhưng bạn cũng có thể rườm rà soát khi gồm một bảng bự và đề nghị đếm số cột. Ko kể ra, nếu như khách hàng thêm/loại quăng quật cột trong bảng, các bạn phải tính lại và update đối col_index_num số. Cùng với hàm INDEX cùng MATCH, ko yêu ước đếm vị 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 xác định cả hai. Điều này có nghĩa là chúng ta cũng có thể tra cứu các giá trị cả theo chiều dọc củ và chiều ngang.

Có thể sử dụng index với MATCH nhằm tra cứu cực hiếm trong bất kỳ cột nào. Không giống hệt như hàm VLOOKUP— trong những số ấy bạn chỉ rất có thể tra cứu cực hiếm ở cột đầu tiên của bảng —hàm INDEX với MATCH sẽ vận động nếu giá trị tra cứu của người sử dụng nằm trong cột đầu tiên, cột cuối cùng hoặc ngẫu nhiên đâu sinh hoạt giữa.

Hàm INDEX cùng MATCH đưa về khả năng hoạt bát khi sản xuất tham chiếu động mang lại cột đựng giá trị trả về. Điều này có nghĩa là chúng ta có thể thêm cột vào bảng của chính mình mà không phá vỡ vạc index và MATCH. Khía cạnh khác, hàm VLOOKUP đang ngắt nếu như bạn cần thêm 1 cột vào bảng — bởi nó tạo ra tham chiếu tĩnh cho bảng.

Index với MATCH có thể chấp nhận được khả năng khớp linh hoạt rộng với khớp. Index và MATCH có thể tìm thấy kết quả khớp chính xác hoặc giá trị lớn hơn hoặc nhỏ hơn cực hiếm tra cứu. VLOOKUP vẫn chỉ tra cứu kiếm tác dụng khớp sát nhất với giá trị (theo mang định) hoặc giá trị thiết yếu xác. Theo mang định, VLOOKUP cũng đưa định rằng cột đầu tiên trong mảng bảng được bố trí theo bảng chữ cái và mang sử bảng của người tiêu dùng không được tùy chỉnh thiết lập theo phương pháp đó, hàm VLOOKUP sẽ trả về kết quả khớp đầu tiên trong bảng, cơ mà đó hoàn toàn có thể không đề xuất là dữ liệu mà nhiều người đang tìm kiếm.

Cú pháp

Để thành lập cú pháp cho hàm INDEX/MATCH, bạn cần sử dụng đối số mảng/tham chiếu từ hàm INDEX cùng lồng cú pháp MATCH vào mặt 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ừ ví dụ sinh sống trên. Cú pháp đang 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, tiếng Anh có nghĩa là:

=INDEX(trả về một quý hiếm từ C2:C10, cơ mà sẽ MATCH(Kale, nằm chỗ nào đó vào mảng B2:B10, trong số đó giá trị trả về là giá bán trị trước tiên tương ứng với Kale))

*

Công thức search kiếm giá trị thứ nhất trong C2:C10 tương xứng với Kale (trong B7) với trả về quý hiếm trong C7 (100), là giá chỉ trị đầu tiên khớp cùng với Kale.

Sự cố: không tìm kiếm thấy tác dụng khớp chính xác

Khi tham range_lookup là FALSE — với hàm VLOOKUP tất yêu tìm thấy kết quả khớp chính xác trong tài liệu của bạn—nó trả về lỗi #N/A.

Giảipháp: nếu như bạn chắc chắn là rằng dữ liệu tương quan tồn trên trong bảng tính của bản thân mình và hàm VLOOKUP không cố kỉnh được, hãy dành thời gian để xác minh rằng những ô được tham chiếu không có khoảng white ẩn hoặc ký kết tự ko in. Bên cạnh ra, hãy bảo vệ rằng các ô tuân theo như đúng kiểu dữ liệu. Ví dụ: ô gồm số cần được format là Số , chứkhông phải 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 mát dữ liệu trong các ô.

Sự cố: giá trị tra cứu nhỏ hơn giá chỉ trị bé dại nhất vào mảng

Nếu đối range_lookup được đặt thành TRUE—và cực hiếm tra cứu nhỏ dại hơn giá trị nhỏ dại nhất trong mảng—bạn sẽ chạm chán lỗi #N/A. TRUE tìm kiếm kiếm kết quả khớp tương đối trong mảng với trả về giá bán trị nhỏ nhất gần nhất so với mức giá trị tra cứu.

Trong ví dụ như sau đây, giá trị tra cứu là 100, nhưng không có giá trị làm sao trong phạm vi B2:C10 nhỏ tuổi hơn 100; cho nên có lỗi.

*

Giải pháp:

Sửa giá trị tra cứu vãn nếu cần.

Nếu chúng ta không thể biến hóa giá trị tra cứu vãn và nên sự linh hoạt cao hơn nữa với các giá trị khớp, hãy cân nhắc sử dụng INDEX/MATCH cố cho VLOOKUP—hãy coi mục ngơi nghỉ trên trong bài viết này. Cùng với index/MATCH, bạn cũng có thể tra cứu các giá trị lớn hơn, nhỏ dại hơn hoặc bằng giá trị tra cứu. Để biết thêm thông tin về cách 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 vãn không được sắp xếp theo trang bị tự tăng dần

Nếu tham range_lookup được để thành TRUE— và một trong những cột tra cứu của khách hàng không được bố trí theo thiết bị tự tăng dần (A-Z) — các bạn sẽ gặp lỗi #N/A.

Giải pháp:

Thay thay đổi hàm VLOOKUP nhằm tìm kết quả khớp thiết yếu xác. Để làm cho điều đó, hãy để đối range_lookup thành FALSE. Không cần phải sắp xếp cho FALSE.

Dùng hàm INDEX/MATCH để tra cứu giá trị trong bảng không được xếp loại.

Sự cố: quý giá là một số điểm trôi nổi lớn

Nếu bạn có giá trị thời gian hoặc số thập phân lớn trong số ô, Excel trả về lỗi #N/A vày độ đúng đắn của điểm trôi nổi. Số dấu điểm trôi nổi là phần nhiều số theo sau lốt thập phân. (Ví Excel giữ trữ các giá trị thời gian 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, bởi vậy để hàm chuyển động đúng cách, số dấu hiển thị trôi nổi sẽ cần được thiết kế tròn cho 5 địa chỉ thập phân.

Giảipháp: rút ngắn số bằng phương pháp làm tròn số lên tới mức năm vị trí thập phân cùng với hàm ROUND.

Xem thêm: Thực Hư Khoai Tây Xào Cà Chua Xào Khoai Tây Xào Cà Chua Lạ Miệng Đổi Vị Bữa Ăn

Bạn đề xuất thêm trợ giúp?

Bạn luôn có thể hỏi một chuyên gia trong xã hội Kỹ thuật Excel hoặc thừa nhận sự cung ứng trongCộng đồng trả lời.