Nài viết này miêu ta điểm tốt chính của hàm INDEX và hàm MATCH trong Excel – các ưu điểm khiến nó phù hợp hơn so với hàm VLOOKUP. bạn sẽ tìm thấy nhiều VD bí quyết – những VD giúp cho bạn dễ dàng xử lý các nhiệm vụ phức tạp khi hàm VLOOKUP không làm được. Cùng Bảng Xếp Hạng tham khảo bài viết dưới đây nhé.
Bạn đang coi bài viết: Hàm Index và Match được coi là hàm thay thế vlookup tốt nhất 2022
Điểm khác nhau giữa hàm INDEX + MATCH và hàm VLOOKUP, HLOOKUP
Hàm VLOOKUP, HLOOKUP là 2 hàm dò tìm dữ liệu và phổ biến với phần đông người. mặc dù vậy, hàm VLOOKUP, HLOOKUP có giới hạn đó là thành quả trả về phải nằm ở cột bên phải so với giá trị dò tìm với hàm VLOOKUP và nằm ở cột phía dưới với hàm HLOOKUP.
Ở VD bên dưới, ta có khả năng sử dụng hàm VLOOKUP và HLOOKUP để dò tìm thành quả cho cột Tên hãng và hàng Tên hãng trong 2 bảng màu vàng từ 2 bảng dữ liệu màu xanh.
nếu thứ tự bảng màu xanh đảo trái lại, bạn không thể sử dụng hàm VLOOKUP và HLOOKUP nữa.
Như vậy, nếu bạn phải cần dò tìm giá trị theo chiều ngược lại, hàm INDEX liên kết với hàm MATCH sẽ giúp ích cho bạn giải quyết vấn đề này.
ngoài ra, các lợi ích khác khi dùng hàm INDEX Kết hợp với hàm MATCH so với hàm VLOOKUP, HLOOKUP đó là:
- Chèn hay xóa cột một cách an toàn: Vì hàm MATCH giúp xác định rõ cột chứa thành quả cần tìm một cách trực tiếp
- Không đặt giới hạn cho kích thước dữ liệu cần tìm: Với hàm VLOOKUP và HLOOKUP thì thành quả cần Lựa chọn không nên vượt quá 255 ký tự.
- Tốc độ xử lý nhanh hơn: Việc dùng hàm INDEX tích hợp hàm MATCH sẽ tăng tốc độ xử lý nhanh hơn từ 10% đến 15% trong bảng tính lớn.
Xem thêm bài viết: Hướng dẫn sử dụng hàm if kết hợp vlookup và left
Những điều cơ bản hàm index và hàm match trong excel
Vì mục đích của bài content này là để nói về cách tìm kiếm thay thế trong Excel bằng việc tích hợp hàm INDEX với hàm MATCH, nên chúng ta sẽ không nói nhiều về cú pháp và cách sử dụng. con người sẽ chỉ nói đủ để hiểu ý tưởng chung rồi nói kỹ hơn về các VD công thức – các ví dụ cho thấy điểm mạnh của việc sử dụng hàm INDEX và hàm MATCH thay cho hàm VLOOKUP.
Cú pháp và cách dùng hàm Index
Hàm INDEX trong Excel trả về giá trị từ mảng dựa trên số thứ tự hàng và cột bạn nắm rõ ràng. Cú pháp hàm INDEX rất đơn giản:
INDEX(array, row_num, [column_num])
phía dưới là cách diễn giải dễ dàng cho mỗi thông số:
array – đây chính là dải ô địa điểm bạn muốn giá trị được trả về.
row_num – số thứ tự của hàng trong mảng mà bạn muốn giá trị được trả về. nếu như bỏ trống, thì colum_num là không thể không.
colum_num – số thứ tự của cột trong mảng mà bạn mong muốn thành quả được trả về. nếu như để trống, thì row_num là không thể không.
nếu cả chỉ số row_num và colum_num đều được sử dụng, thì hàm INDEX sẽ trả về thành quả ở ô là giao điểm của hàng và cột nắm rõ ràng.
Và đây chính là VD dễ dàng nhất của hàm INDEX:
=INDEX(A1:C10,2,3)
bí quyết tìm kiếm dải ô A1:C10 rồi trả về giá trị của ô ở hàng thứ hai và cột thứ ba, cụ thể là ô C2.
Rất dễ, đúng không? tuy nhiên, khi xử lý dữ liệu thực, bạn gần như không biết được mình mong muốn hàng nào cột nào, Đây là lý do bạn cần sự tạo điều kiện của hàm MATCH.
Cú pháp và cách dùng hàm Match
Hàm MATCH dùng để Lựa chọn vị trí có thể là tương đối hoặc tuyệt đối của 1 giá trị trong 1 dòng hoặc một cột của bảng tính Cú pháp của hàm MATCH như sau:
=MATCH(giá_trị_cần_tìm, mảng_tìm_kiếm, [kiểu_tìm_kiếm])
- giá_trị_cần_tìm – thành quả cần được chọn lựa vị trí trong mảng
- mảng_tìm_kiếm – mảng chứa giá trị cần Lựa chọn
- kiểu_tìm_kiếm – Lựa chọn giá trị chuẩn xác hay tìm kiếm thành quả gần nhất.
- 1 – hoặc bỏ qua: tìm giá trị khổng lồ nhất mà giá trị đó bằng hoặc nhỏ hơn giá_trị_cần_tìm. giá trị trong mảng_tìm_kiếm cần được phân bổ tăng dần
- 0 – thành quả bằng, đòi hỏi tìm kiếm chuẩn xác
- -1 – tìm giá trị nhỏ nhất mà giá trị đó lơn hơn hoặc bằng giá_trị_cần_tìm
ví dụ dùng hàm MATCH để chọn lựa vị trí của mã sản phẩm “S04N” tại cột A
=MATCH(“S04N”,A1:A10,0) Kết quả bằng 6.
nếu như chỉ dừng ở đây, bạn có thể thấy hàm MATCH không có nhiều giá trị trong app, tuy nhiên bạn có thể thấy nhiệm vụ của hàm MATCH ra sao khi sử dụng liên kết với hàm khác và trong trường hợp này con người kết hợp hàm MATCH cùng hàm INDEX.
Xem thêm bài viết : Cách kết hợp hàm Index và Vlookup trong Excel
Cách tích hợp hàm INDEX và hàm MATCH
Để hàm INDEX và hàm MATCH thực hiện công việc đồng thời, chúng tôi chỉ cần nói ra tham chiếu hàng của bên chúng tôi từ công thức hàm INDEX với công thức hàm MATCH.
Như bạn thấy, việc tạo ra bí quyết của bên chúng tôi thực sự không phải là quá phức tạp. Nó chỉ là vấn đề tổ chức hàm MATCH bên trong hàm INDEX và cam kết rằng cả hai phần tử đều được trang bị vào đúng các ô và dãy. Hiện giờ, bảng tính của chúng tôi được cài đặt theo cách này, chúng tôi có thể thay đổi cách thức được cài đặt mà không vi phạm bí quyết.
có khả năng mất nhiều thời gian hơn để thực hiện hàm INDEX và hàm MATCH so với việc sử dụng hàm VLOOKUP, tuy nhiên kết quả chúng ta sẽ có một tài liệu linh động hơn với ít lỗi hơn. nếu như bảng tính của bạn phụ thuộc vào một số chức năng tra cứu, Bạn có thể tiết kiệm cho mình một vài rắc rối sau này bằng việc bỏ hàm VLOOKUP và học cách dùng hàm INDEX và hàm MATCH hợp lý.
Lợi ích của hàm thay thế vlookup
- Lựa chọn từ phải sang phía trái. Bất cứ người sử dụng thông thạo nào cũng biết, hàm VLOOKUP trong Excel không thể chọn lựa phía bên trái, có nghĩa là giá trị cần tìm phải luôn nằm ở cột cận trái của dải ô cần tìm. Với hàm INDEX MATCH, cột cần tìm của Bạn có thể nằm ở phía bên phải của bảng.
- Chèn hay xóa cột một cách an toàn. công thức VLOOKUP gặp hư hại hay trả về kết quả không chuẩn xác khi xóa hay thêm cột mới vào bảng cần tìm. Với hàm VLOOKUP, bất cứ cột nào được chèn hay xóa cũng có thể khác biệt kết quả trả về bởi bí quyết bởi vì cú pháp hàm VLOOKUP yêu cầu định rõ tất cả mảng bảng và một số nhất định để chỉ bạn mong muốn lấy dữ liệu từ cột nào.
Ví dụ: nếu bạn có bảng A1:C10 và mong muốn trả về giá trị từ cột B, bạn có thể đặt số “2” ở chỉ số thứ ba (col_index_num) của công thức VLOOKUP, VD =VLOOKUP(“lookup value”, A1:C10, 2). nếu sau đấy, bạn chèn một cột mới giữa cột A và cột B, thì bạn phải chỉnh sửa “2” thành “3” trong công thức, nếu như không thì nó sẽ trả về giá trị từ cột mới được thêm vào.
- Với hàm INDEX MATCH, chúng ta có thể xóa hay chèn cột mới vào bảng cần tìm mà không chỉnh sửa kết quả bởi vì bạn định rõ cột chứa thành quả cần tìm một cách trực tiếp. Và Đây là một lợi ích rất lớn, nhất là khi giải quyết chuỗi dữ liệu lớn, vì bạn có thể chèn hay xóa mà chớ lo lắng về việc cập nhật mỗi bí quyết Lựa chọn liên quan.
- Không đặt giới hạn cho kích thước dữ liệu cần tìm. Khi sử dụng hàm VLOOKUP, hãy nhớ rằng tổng chiều dài của tiêu chuẩn chọn lựa không được vượt quá 255 ký tự, nếu không thì bạn có thể nhận được lỗi #VALUE!. vì thế, nếu chuỗi dữ liệu của bạn chứa một chuỗi dài, thì hàm INDEX MATCH là phương án khả thi duy nhất.
Giả sử, bạn dùng công thức VLOOKUP phía dưới để chọn lựa từ ô B5 đến ô D10 cho giá trị ở ô A2:
=VLOOKUP(A2,B5:D10,3,FALSE)
công thức sẽ không chạy nếu như giá trị cần tìm ở ô A2 vượt quá 255 ký tự. thay vào đó, bạn nên áp dụng hàm INDEX/MATCH tương tự:
=INDEX(D5:D10, MATCH(TRUE, INDEX(B5:B10=A2, 0), 0))
- Tốc độ xử lý nhanh hơn. nếu như bạn đang giải quyết một bảng tương đối nhỏ, thì điểm khác biệt trong việc giải quyết của Excel sẽ gần như không thể nhận ra, quan trọng là ở các phiên bản gần đây. tuy nhiên nếu như bạn dùng bảng tính lớn có hàng nghìn hàng và hàng nghìn bí quyết cần tìm, Excel sẽ làm việc nhanh hơn nhiều nếu bạn sử dụng hàm INDEX/MATCH thay vì hàm VLOOKUP. Nói chung, việc sử dụng công thức INDEX/MATCH sẽ tăng vận tốc xử lý của Excel lên 13 % đối với bí quyết VLOOKUP tương tự.
liên quan của hàm VLOOKUP lên việc giải quyết của Excel có thể sẽ rõ ràng hơn nếu sổ thực hiện công việc của chúng ta chứa hàng trăm công thức mảng khó hiểu như VLOOKUP và SUM. Điểm quan trọng nhất đấy là việc kiểm tra mỗi thành quả trong mảng đòi hỏi một hàm VLOOKUP khác. vì thế, mảng càng có nhiều giá trị và sổ thực hiện công việc của chúng ta càng có nhiều công thức mảng, thì Excel giải quyết càng chậm.
Mặt khác, với hàm INDEX/MATCH, Excel chỉ phải cân nhắc cột chọn lựa và cột trả về, nên nó xử lý bí quyết nhanh hơn nhiều.
Không phải điều dễ dàng luôn là tốt nhất!
Excel là phần mềm rất phức tạp và việc tham khảo sâu vào các chức năng thường khá “đáng sợ”. Có một điều cần nói đến để xử lý các vấn đề đơn giản, thường một công thức phức tạp hơn có khả năng Đem lại những lợi ích lớn về lâu về dài.
không có gì sai khi dùng hàm VLOOKUP để thực hiện việc hoàn thành công việc này. tuy nhiên, hàm INDEX và hàm MATCH làm giảm tác động mắc lỗi của người dùng và không yêu cầu chỉnh sửa thêm khi mà bạn thực hiện thay đổi cấu trúc cho bảng tính của mình. dùng chúng trong tình trạng hòa hợp chỉ là một kỹ thuật tiên tiến hơn một tí đối với việc thực hiện công dụng hàm VLOOKUP, tuy nhiên nó cung cấp cho người dùng một số lợi thế lớn.
Excel càng trở nên hữu ích, bạn càng học được nhiều về nó, do vậy luôn có lợi khi học cách tiếp xúc mới và hiểu những tính năng mới. Đừng ngủ quên trên chiến thắng! dành thời gian để mở rộng kiến thức của bạn.
Xem thêm bài viết: Sử dụng hàm Exp với excel đơn giản nhất 2022
Một số lỗi thường gặp
trong quá trình dùng hàm INDEX Kết hợp với hàm MATCH, bạn sẽ gặp lỗi #NA và lỗi #VALUE xuất hiện. Các nguyên nhân gây ra lỗi này thường là:
Lỗi #NA
- Khi hàm MATCH không tìm thấy thành quả trong phạm vi dò tìm, nó trả về thành quả #N/A.
- khi bạn sử dụng một phạm vi trong INDEX, MATCH thay vì một thành quả, bạn cần nhấn Ctrl+Shift+Enter để chuyển về công thức mảng.
- khi mà bạn sử dụng MATCH, cần có sự nhất quán giữa thành quả trong đối số match_type và tuần tự sắp xếp các giá trị trong phạm vi dò tìm, nếu không bạn có thể gặp giá trị #N/A.
Lỗi #VALUE
nếu như bạn đang dùng chỉ mục dưới dạng công thức mảng cùng với kết quả phù hợp để có thể dò tìm một giá trị, bạn có thể cần chuyển công thức của chúng ta thành công thức mảng bằng cách nhấn Ctrl+Shift+Enter, nếu như không thì bạn có thể thấy lỗi #VALUE! xảy ra.
Tổng kết
Bảng Xếp hạng hỳ vọng ít nhất có một bí quyết trong bài hướng dẫn này sẽ hữu ích đối với bạn. nếu như bạn gặp phải vai trò Lựa chọn khác mà bạn không tìm thấy phương án cho nhiệm vụ đấy ở đây.
