Chủ đề này diễn tả các tại sao VLOOKUP phổ biến nhất mang lại một công dụng sai vào hàm và hỗ trợ các khuyến cáo để gắng thế bằng phương pháp sử dụng INDEXvà MATCH.
Bạn đang xem: Tự chọn : các phép tính trong tập hợp n
Mẹo: Ngoài ra, hãy xem thêm Thẻ tham khảo Nhanh: Mẹo khắc chế sự núm VLOOKUP trình bày các tại sao phổ trở nên #NA nuốm trong một tệp PDF thuận tiện. Bạn cũng có thể chia sẻ tệp PDF với những người khác hoặc in để xem thêm riêng.
Sự cố: giá trị tra cứu không nằm tại cột thứ nhất trong tham table_array vẫn chọn
Một ràng buộc của hàm VLOOKUP là nó chỉ rất có thể tìm những giá trị trên cột kế bên cùng phía trái trong mảng bảng. Nếu giá trị tra cứu của khách hàng không phía trong cột thứ nhất của mảng, các bạn sẽ gặp lỗi #N/A.
Trong bảng sau đây, công ty 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 có thể xảy ra bởi vì giá trị tra cứu "Kale" xuất hiện trong cột máy hai (Nông sản) của đối table_array A2:C10. Vào trường hợp này, Excel bạn đang tìm vào cột A, chứ chưa phải cột B.
Giảipháp : chúng ta cũng có thể thử khắc phục và hạn chế sự thế này bằng cách đ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 rời các cột của bạn. Điều đó cũng rất có thể rất cạnh tranh thực thi, nếu khách hàng có bảng tính béo hoặc tinh vi trong đó giá trị ô là tác dụng của các phép tính khác—hoặc hoàn toàn có thể có các tại sao lô-gic khác vì sao tại sao chúng ta chỉ đơn giản và dễ dàng là không thể dịch chuyển các cột. Phương án là sử dụng kết hợp hàm INDEX với 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 chính nó trong bảng tra cứu. Coi phần tiếp theo.
Cân đề cập việc sử dụng INDEX/MATCH để chũm thế
Hàm INDEX cùng MATCH là đa số tùy chọn tốt cho các trường hợp, trong số ấy VLOOKUP không đáp ứng nhu cầu nhu cầu của bạn. Ưu điểm thiết yếu của index/MATCH là chúng ta 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ề quý giá 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 quý giá trong bảng/phạm vi. Cần sử dụng index cùng MATCH với mọi người trong nhà trong bí quyết để tra cứu kiếm quý giá trong bảng/mảng bằng phương pháp xác xác định trí kha khá của giá trị trong bảng/mảng.
Có một số tác dụng của việc sử dụng INDEX/MATCH thay vì chưng VLOOKUP:
Với index và MATCH, quý giá trả về không cần thiết phải ở trong cùng một cột với cột tra cứu. quý giá này khác với hàm VLOOKUP, trong những số ấy giá trị trả về yêu cầu nằm trong phạm vi sẽ xác định. Điều này quan trọng đặc biệt như nắm nào? với hàm VLOOKUP, bạn phải biết số cột có chứa quý hiếm trả về. Mặc dù việc này còn có vẻ không khó khăn, nhưng chúng ta có thể rườm kiểm tra khi gồm một bảng phệ và cần đếm số cột. Ngoại trừ ra, nếu khách hàng thêm/loại quăng quật cột vào bảng, chúng ta phải tính lại và cập nhật đối col_index_num số. Cùng với hàm INDEX với MATCH, ko yêu cầu đếm vì chưng cột tra cứu khác với cột có giá trị trả về.
Với index cùng MATCH, chúng ta có thể xác định một sản phẩm 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à bạn cũng có thể tra cứu các giá trị cả theo theo hướng dọc và chiều ngang.
Có thể cần sử dụng index cùng MATCH để 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 đó bạn chỉ hoàn toàn có thể tra cứu giá trị ở cột trước tiên của bảng —hàm INDEX và MATCH sẽ vận động nếu quý hiếm tra cứu của người tiêu dùng nằm trong cột đầu tiên, cột ở đầu cuối hoặc ngẫu nhiên đâu ở giữa.
Hàm INDEX và MATCH đem về khả năng hoạt bát khi tạo tham chiếu động mang đến cột cất 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 bản thân mà ko phá đổ vỡ index cùng MATCH. Phương diện khác, hàm VLOOKUP đã ngắt nếu bạn cần thêm một cột vào bảng — do nó tạo thành tham chiếu tĩnh đến bảng.
Index và MATCH được cho phép khả năng khớp linh hoạt hơn với khớp. Index với MATCH rất có thể tìm thấy kết quả khớp đúng chuẩn hoặc giá trị lớn hơn hoặc bé dại hơn cực hiếm tra cứu. VLOOKUP sẽ chỉ kiếm tìm kiếm hiệu quả khớp ngay gần nhất với giá trị (theo mang định) hoặc giá trị chính xác. Theo mặc định, VLOOKUP cũng mang định rằng cột thứ nhất trong mảng bảng được bố trí theo bảng chữ cái và trả sử bảng của người sử dụng không được thiết lập cấu hình theo phương pháp đó, hàm VLOOKUP đã trả về công dụng khớp đầu tiên trong bảng, mà lại đó rất có thể không nên là tài liệu mà bạn đang tìm kiếm.
Cú pháp
Để xây đắp 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 cùng 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 để thay thế hàm VLOOKUP trường đoản cú ví dụ sinh sống trên. Cú pháp đang có bề ngoài như sau:
=INDEX(C2:C10,MATCH(B13,B2:B10,0))
Nói một cách đối chọi giản, giờ Anh bao gồm nghĩa là:
=INDEX(trả về một quý giá từ C2:C10, cơ mà sẽ MATCH(Kale, nằm ở chỗ nào đó trong mảng B2:B10, trong những số ấy giá trị trả về là giá bán trị trước tiên tương ứng cùng với Kale))
Công thức tra cứu kiếm giá bán trị đầu tiên trong C2:C10 tương xứng với Kale (trong B7) và trả về cực hiếm trong C7 (100), là giá chỉ trị đầu tiên khớp với Kale.
Sự cố: không tìm thấy công dụng khớp bao gồm xác
Khi tham range_lookup là FALSE — cùng hàm VLOOKUP bắt buộc tìm thấy hiệu quả khớp đúng đắn trong tài 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 tương quan tồn trên trong bảng tính của bản thân và hàm VLOOKUP không rứa được, hãy dành thời gian để xác minh rằng những ô được tham chiếu không tồn tại khoảng trắng ẩn hoặc cam kết tự ko in. Quanh đó ra, hãy bảo đảm rằng các ô tuân theo như đúng kiểu dữ liệu. Ví dụ: ô có số đề nghị được định dạng là Số , chứkhông cần Văn bản.
Ngoài ra, hãy lưu ý đến sử dụng hàm CLEAN hoặc TRIM để dọn không bẩn dữ liệu trong những ô.
Sự cố: giá trị tra cứu bé dại hơn giá chỉ trị nhỏ tuổi nhất trong 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á trị nhỏ nhất sớm nhất so với 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 bé dại hơn 100; vì thế có lỗi.
Giải pháp:
Sửa cực hiếm tra cứu vớt nếu cần.
Nếu các bạn không thể biến đổi giá trị tra cứu giúp và phải sự linh hoạt cao hơn với những giá trị khớp, hãy quan tâm đến sử dụng INDEX/MATCH nuốm cho VLOOKUP—hãy xem mục sinh sống trên trong nội dung bài viết này. Cùng với index/MATCH, chúng ta có thể tra cứu các giá trị bự hơn, bé dại hơn hoặc bởi giá trị tra cứu. Để hiểu biết thêm thông tin về cách sử dụng hàm INDEX/MATCH thay vày hàm VLOOKUP, hãy xem thêm mục trước trong chủ đề này.
Sự cố: Cột tra cứu vớt không được sắp xếp theo lắp thêm tự tăng dần
Nếu tham range_lookup được đặt thành TRUE— cùng một trong những cột tra cứu của bạn không được bố trí theo máy tự tăng cao (A-Z) — bạn sẽ gặp lỗi #N/A.
Giải pháp:
Thay đổi hàm VLOOKUP nhằm tìm hiệu quả khớp chủ yếu xác. Để làm điều đó, hãy đặt đối range_lookup thành FALSE. Không cần phải sắp xếp mang lại FALSE.
Dùng hàm INDEX/MATCH để tra cứu giá trị trong bảng không được xếp loại.
Sự cố: giá trị là một số điểm trôi nổi lớn
Nếu bạn có mức giá trị thời gian hoặc số thập phân lớn trong các ô, Excel trả về lỗi #N/A vì chưng độ chính xác của điểm trôi nổi. Số dấu điểm trôi nổi là đa số số theo sau vệt thập phân. (Ví Excel lưu trữ các 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ó các điểm trôi nổi rất lớn, bởi vì vậy nhằm hàm vận động đúng cách, số dấu hiển thị trôi nổi đã cần được thiết kế tròn mang lại 5 vị trí thập phân.
Giảipháp: rút ngắn số bằng cách làm tròn số lên đến năm địa chỉ thập phân với hàm ROUND.
Xem thêm: Hậu Cung Chân Hoàn Truyện Tập 5, Hậu Cung Chân Hoàn Truyện: Tập 5
Bạn nên thêm trợ giúp?
Bạn luôn rất có thể hỏi một chuyên viên trong cộng đồng Kỹ thuật Excel hoặc thừa nhận sự hỗ trợ trongCộng đồng trả lời.