Sự kết hợp của hàm INDEX kết hợp với hàm MATCH sẽ mang đến sự linh hoạt rộng trong việc dò search so với các hàm không giống trong Excel. Điện lắp thêm XANH vẫn chỉ bạn cách áp dụng 2 hàm này kết hợp với nhau trong nội dung bài viết sau nhé.

Bạn đang xem: Hàm index


Ví dụ trong bài xích được triển khai trên phiên bản Excel 2016. Các bạn có thể áp dụng tương tự như trên những phiên phiên bản Excel khác như: 2007, 2010, 2013, 2017 với Microsoft Excel 365.

1Sự khác biệt giữa hàm INDEX + MATCH cùng hàm VLOOKUP, HLOOKUP

Hàm VLOOKUP, HLOOKUP là 2 hàm dò tìm dữ liệu và phổ biến với những người. Mặc dù nhiên, hàm VLOOKUP, HLOOKUP có số lượng giới hạn đó là cực hiếm trả về phải nằm tại vị trí cột bên phải so với cái giá trị dò tìm kiếm với hàm VLOOKUP và nằm tại vị trí cột bên dưới cùng với hàm HLOOKUP.

Ở ví dụ bên dưới, ta hoàn toàn có thể dùng hàm VLOOKUP với HLOOKUP để dò tìm giá trị cho cột tên hãng và hàng Tên hãng trong 2 bảng màu đá quý từ 2 bảng tài liệu màu xanh.

Nếu thứ từ bỏ bảng greed color đảo ngược lại, các bạn không thể áp dụng hàm VLOOKUP cùng HLOOKUP nữa.

Như vậy, nếu bạn cần dò tìm quý hiếm theo chiều ngược lại, hàm INDEX kết hợp với hàm MATCH để giúp đỡ bạn giải quyết và xử lý vấn đề này.

Ngoài ra, các công dụng khác khi sử dụng hàm INDEX kết phù hợp với hàm MATCH đối với hàm VLOOKUP, HLOOKUP kia là:

Chèn hay xóa cột một giải pháp an toàn: vị hàm MATCH giúp khẳng định rõ cột đựng giá trị buộc phải tìm một phương pháp trực tiếp Không đặt giới hạn cho kích cỡ dữ liệu phải tìm: với hàm VLOOKUP cùng HLOOKUP thì giá bán trị nên tìm kiếm ko được vượt quá 255 ký kết tự. Tốc độ cách xử trí nhanh hơn: Việc sử dụng hàm INDEX phối hợp hàm MATCH sẽ tăng vận tốc xử lý nhanh hơn trường đoản cú 10% mang đến 15% trong bảng tính lớn.

2Hàm INDEX và hàm MATCH

Công thức hàm INDEX

Hàm INDEX trả về cực hiếm theo vị trí của hàng cùng cột vào một bảng hoặc một phạm vi.

=INDEX(array;row_num;column_num)

Trong đó:

array: vùng ô hoặc một sản phẩm số mảng làm sao đó;row_num: chọn hàng trong mảng từ kia trả về một giá trị;column: lựa chọn cột vào mảng từ đó trả về một giá chỉ trị.Công thức hàm MATCH

Hàm MATCH trả về vị trí kha khá của một quý hiếm trong một phạm vi.

=MATCH(lookup_value,lookup_array,match_type)

Trong đó:

Lookup_value: quý hiếm tìm kiếm trong mảng Lookup_array.Lookup_array: mảng giỏi phạm vị ô được tìm kiếm kiếm.Match_type: hình dáng tìm kiếm.


3Ví dụ hàm INDEX phối hợp hàm MATCH


Bạn hoàn toàn có thể tải tệp tin Excel của ví dụ bên dưới tại đây. Trong các công thức, bạn cần sử dụng $ để cố định và thắt chặt giá trị những phạm vi.

Giả sử ta có đơn giá theo thành phầm và đơn vị sản xuất trong Bảng 2 (B15:E18). Dựa trên bảng 2, ta phải điền solo giá vào Bảng 1 (B3:D12).

Cách ta áp dụng hàm INDEX phối hợp hàm MATCH nhằm dò tìm giá chỉ trị mang lại ô D4 như sau:

1. Sử dụng hàm MATCH nhằm xác định vị trí hàng khớp ứng của thành phầm CDRom trong phạm vi B15:B18 của Bảng 2: 


=MATCH(B4,$B$15:$B$18,0)

Kết quả 4 tương xứng với hàng số 4 vào Bảng 2.

2. Sử dụng hàm MATCH nhằm xác định vị trí cột khớp ứng của đơn vị phân phối Samsung trong phạm vi B15:E15: 


=MATCH(C4,$B$15:$E$15,0)

Kết quả 2 tương xứng với cột số 2 vào Bảng 2.

3. Sử dụng hàm INDEX kết hợp với 2 hàm MATCH làm việc trên nhằm trả về giá trị dựa theo hàng cùng cột vào Bảng 2: 


=INDEX($B$15:$E$18,MATCH(B4,$B$15:$B$18,0),MATCH(C4,$B$15:$E$15,0))

Kết quả trả về giá bán trị tương xứng của sản phẩm 4 (Hàng Mouse), cột 2 (Cột Samsung) vào Bảng 2 là 5.

Sau đó, ta copy công thức cho những ô khác nhằm hoàn thành.

4Một số để ý khi sử dụng

Như đã đề cập ở trong phần 1, vày hàm VLOOKUP, HLOOKUP hạn chế chế của nó yêu cầu nếu quý giá trả về nằm ở vị trí cột phía trái hoặc cột sinh sống trên so với cái giá trị dò kiếm tìm thì 2 công thức sẽ không còn hoạt động. Hàm INDEX phối kết hợp hàm MATCH đã linh hoạt rộng rất nhiều lúc không thân thương về vị trí các cột giá trị trả về sinh sống đâu.

Trong ví dụ ở dưới, ta bao gồm bảng 1 liệt kê thành phố hà nội của các nước và buộc phải điền dữ liệu tương xứng ở bảng 2. Trường thích hợp này ta không thể áp dụng hàm VLOOKUP nhưng mà hàm INDEX kết hợp hàm MATCH sẽ xử lý được điều này.


=INDEX($B$3:$C$12,MATCH(E4,$C$3:$C$12,0),1)

5Một số lỗi thường gặp

Trong lúc áp 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 tại sao gây ra lỗi này thường là:

Lỗi #NA

Khi hàm MATCH không tìm kiếm thấy cực hiếm trong phạm vi dò tìm, nó trả về quý hiếm #N/A.Khi bạn sử dụng một phạm vi vào INDEX, MATCH thay bởi vì một giá trị, bạn cần nhấn Ctrl+Shift+Enter để chuyển về bí quyết mảng.Khi bạn sử dụng MATCH, cần có sự nhất quán giữa cực hiếm trong đối số match_type với thứ tự chuẩn bị xếp những giá trị trong phạm vi dò tìm, giả dụ không bạn sẽ gặp quý giá #N/A.

Lỗi #VALUE

Nếu bạn đang sử dụng chỉ mục bên dưới dạng cách làm mảng cùng với kết quả tương xứng để rất có thể dò tìm một giá bán trị, các bạn sẽ cần gửi công thức của doanh nghiệp thành bí quyết mảng bằng phương pháp nhấn Ctrl+Shift+Enter, nếu như không thì bạn sẽ thấy lỗi #VALUE! xuất hiện.

Hàm INDEX hoàn toàn có thể được thực hiện một mình, tuy thế khi lồng cùng với hàm MATCH tạo nên một tra cứu vãn nâng cao. Trong bài bác này, FPT cửa hàng sẽ hướng dẫn chúng ta cách thực hiện hàm INDEX cùng MATCH bên nhau một cách hiệu quả.


Hàm INDEX và hàm MATCH trong Excel là gì?

INDEX và MATCH là những hàm tra cứu vớt trong Excel. Mặc dù chúng là hai hàm hoàn toàn riêng lẻ có thể được áp dụng riêng cơ mà cũng rất có thể được phối kết hợp để tạo thành các bí quyết nâng cao.

Hàm INDEX trả về một quý giá hoặc tham chiếu mang lại một giá trị từ bên trong một lựa chọn vậy thể. Lấy ví dụ như nó hoàn toàn có thể được sử dụng để tìm giá trị trong hàng vật dụng hai của tập dữ liệu hoặc vào hàng trang bị năm với cột sản phẩm ba.

Mặc dù hàm INDEX có thể được thực hiện riêng lẻ, cơ mà khi kết hợp với hàm MATCH vào công thức khiến cho nó hữu dụng hơn. Hàm MATCH search kiếm một mục được hướng dẫn và chỉ định trong một phạm vi ô và tiếp nối trả về vị trí kha khá của mục kia trong phạm vi. Ví dụ, nó rất có thể được thực hiện để xác minh rằng một tên rõ ràng là mục thứ ba trong danh sách những tên.

Cú pháp với đối số của hàm INDEX và MATCH

Dưới đây là cú pháp và những đối số của nhị hàm này:


Trong đó:

Array (đối số bắt buộc): Là phạm vi ô mà công thức sẽ sử dụng. Nó hoàn toàn có thể là một hoặc nhiều hàng với cột. row_num (đối số bắt buộc): Là mặt hàng trong mảng nhưng từ kia trả về một giá chỉ trị. column_num (đối số tùy chọn): Là cột vào mảng nhưng mà từ kia trả về một giá bán trị.

Trong đó:

Lookup_value (đối số bắt buộc): Là giá bán trị bạn có nhu cầu so khớp trong lookup_array. Nó rất có thể là một số, văn phiên bản hoặc giá bán trị xúc tích và ngắn gọn được nhập theo cách thủ công hoặc được tham xuyên thẳng qua tham chiếu ô. Lookup_array (đối số bắt buộc): Là phạm vi ô nên xem qua. Nó có thể là một hàng hoặc một cột. match_type có thể là -1, 0 hoặc 1. Nó chỉ định cách lookup_value được so khớp với các giá trị vào lookup_array. Một là giá trị mang định giả dụ đối số này bị vứt qua. Sử dụng 1 hoặc -1 cho những thời khắc bạn đề xuất chạy tra cứu gần hòa hợp thang điểm, như lúc xử lý những con số cùng khi tính gần chính xác là ổn. Dẫu vậy hãy lưu giữ rằng nếu như bạn không chỉ có định match_type, 1 đã là khoác định, điều này rất có thể làm không nên lệch tác dụng nếu bạn thực sự ý muốn một hiệu quả khớp bao gồm xác.

Các lấy ví dụ như về hàm INDEX với MATCH

Trước khi chứng kiến tận mắt xét cách kết hợp giữa hàm INDEX và MATCH trong một công thức, chúng ta cần hiểu bí quyết mà từng hàm này sẽ thao tác trong Excel.

Ví dụ về hàm INDEX


*

Trong ví dụ trước tiên này, gồm bốn công thức áp dụng hàm INDEX mà chúng ta cũng có thể sử dụng để nhận những giá trị không giống nhau:

=INDEX(A1:B4;2;2): Hàm sẽ kiểm tra mảng A1:B4 nhằm tìm quý hiếm trong cột vật dụng hai và hàng trang bị hai, sẽ là Vũ Hồng Ngọc. =INDEX(A1:B1;1): Hàm sẽ kiểm tra mảng A1:B1 nhằm tìm cực hiếm trong cột đầu tiên, sẽ là Lê Thanh Khương. =INDEX(2:2;1): Hàm sẽ chất vấn mọi lắp thêm trong hàng thứ hai để tìm quý giá trong cột đầu tiên, đó là Nguyễn Gia Viễn. =INDEX(B1:B2;1): Hàm sẽ bình chọn mảng B1:B2 để tìm quý giá trong số 1 tiên, đó là Bùi Thanh Tuyên.

Ví dụ về hàm MATCH

Chúng ta hoàn toàn có thể tìm hiểu về phong thái hàm MATCH thao tác làm việc trong Excel trải qua 4 ví dụ đơn giản dễ dàng sau:

Dưới đó là bốn ví dụ đơn giản dễ dàng về hàm MATCH:


*

Trong đó:

=MATCH("Vũ Hồng Ngọc";A2:D2;0): Hàm MATCH đang tìm tìm Vũ Hồng Ngọc trong phạm vi A2:D2 và trả về tác dụng là 3. =MATCH(16;D1:D3): Hàm MATCH đã tìm tìm 16 vào phạm vi D1:D3. Nhưng vì 16 không có trong phạm vi tìm kiếm đề nghị MATCH vẫn tìm giá trị lớn nhất tiếp theo nhỏ tuổi hơn hoặc bởi 14, trong trường hòa hợp này là 13 , nằm tại phần 1 của lookup_array. =MATCH(16;D1:D3;-1): Tương từ như cách làm ở trên, nhưng do mảng không theo thứ tự bớt dần như -1 yêu cầu nên chúng ta thấy thông báo lỗi #N/A. =MATCH(13;A1:D1;0): Hàm MATCH vẫn tìm tìm 13 trong số 1 tiên của bảng dữ liệu, hiệu quả trả về là 4 vày nó là mục thứ tứ trong mảng này.

Ví dụ về hàm INDEX-MATCH

Dưới đó là hai ví dụ mà chúng ta cũng có thể kết phù hợp INDEX với MATCH trong một công thức:

Tìm tham chiếu ô vào bảng

*

Ví dụ này sẽ lồng cách làm của hàm MATCH trong bí quyết hàm INDEX. Mục tiêu là để khẳng định loại mặt hàng đó trải qua mã số được chỉ định. Ý nghĩa ví dụ của phương pháp trên sẽ thao tác làm việc theo biện pháp sau:

MATCH(F1;B2:B5): Hàm MATCH vẫn tìm kiếm quý hiếm F1 (8795) vào vùng tài liệu B2:B5. Như bọn họ thấy trong bảng, vị trí của quý hiếm F1 vào vùng tài liệu này là 2 với đó là hiệu quả mà hàm MATCH sẽ tìm ra. Mảng INDEX là C2:C5 vì chúng ta đang kiếm tìm kiếm quý hiếm trong cột đó. Hàm INDEX hiện thời có thể được viết lại thành INDEX (C2:C5;2;) vì 2 là hàm MATCH đang tìm ra. Column_num là tùy chọn, chúng ta có thể loại cho phần đó và bí quyết rút gọn lại thành công thức INDEX (C2: C5;2).

Như vậy sau khi rút gọn, công thức lúc đầu sẽ giống hệt như một phương pháp với hàm INDEX thông thường và cực hiếm của mục sản phẩm hai trong phạm vi C2:C5 là cây bút nước Thiên Long.

Tra cứu theo tiêu đề hàng và cột

*

Trong ví dụ này về MATCH và INDEX, chúng ta sẽ tiến hành tra cứu vớt hai chiều. Mục đích là để chất vấn xem loại cây viết tre đã bán được bao nhiêu chiếc trong tháng 5. Đó là cách tựa như như lấy ví dụ trên nhưng là 1 trong những công thức MATCH bổ sung cập nhật được lồng trong INDEX.

MATCH(G1;A2:A13;0): Hàm MATCH đã tìm tìm G1 (số 5) vào phạm vi A2:A13 để dấn một giá chỉ trị cầm cố thể. MATCH(G2;B1:E1;0): Là công thức MATCH sản phẩm hai cùng giống với công thức đầu tiên nhưng cố vào đó là tìm tìm G2 (từ "Bút tre") trong title cột từ bỏ B1:E1. Như trong hình sẽ là cột sản phẩm 3. Cuối cùng công thức cùng với hàm INDEX có thể được viết lại ngắn gọn hơn nhằm chúng dễ dàng nắm bắt là:=INDEX(B2:E13;6;3).

Xem thêm: 10 cách làm thiệp chúc mừng năm mới handmade đơn giản mà đẹp

Có tức là hàm INDEX sẽ tìm kiếm trong toàn cục bảng B2:E13 cho hàng sản phẩm sáu cùng cột thứ tía để trả về kết quả là 41 như trong bảng trên.

Lưu ý khi thực hiện hàm MATCH cùng INDEX

MATCH không rành mạch chữ hoa và chữ thường, bởi vì vậy chữ hoa cùng chữ hay được cách xử lý giống nhau khi khớp các giá trị văn bản. MATCH trả về lỗi #N/A vì chưng nhiều lý do như:match_type là 0 cùng lookup_value ko được tìm thấy;match_type là -1 với lookup_array không theo thứ tự bớt dần;match_type là 1 và lookup_array không tuân theo thứ tự tăng dần đều và lookup_array không phải là 1 hàng hoặc một cột. Bạn có thể sử dụng một cam kết tự thay mặt trong đối số lookup_value nếu match_type là 0 với lookup_value là một trong những chuỗi văn bản. Dấu chấm hỏi khớp với ngẫu nhiên ký tự đơn nào và dấu hoa thị sẽ khớp với bất kỳ chuỗi ký kết tự nào. INDEX sẽ trả về lỗi #REF! nếu row_num với column_num không trỏ mang lại một ô vào mảng.