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

Bạn đang xem: Sử dụng hàm index và match trong excel


Hàm INDEX cùng hàm MATCH trong Excel là gì?

INDEX cùng MATCH là những hàm tra cứu vớt trong Excel. Tuy vậy chúng là hai hàm trả toàn hiếm hoi có thể được sử dụng riêng nhưng lại cũng có thể được kết hợp để tạo ra các công thức nâng cao.

Hàm INDEX trả về một quý giá hoặc tham chiếu đến một cực hiếm từ phía bên trong một lựa chọn cố thể. Lấy ví dụ nó rất có thể được thực hiện để tìm quý hiếm trong hàng thiết bị hai của tập tài liệu hoặc trong hàng trang bị năm cùng cột lắp thêm ba.

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

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

Dưới đấy là cú pháp và các đối số của nhì hàm này:


Trong đó:

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

Trong đó:

Lookup_value (đối số bắt buộc): Là giá trị bạn muốn so khớp trong lookup_array. Nó hoàn toàn có thể là một số, văn bản hoặc giá trị xúc tích được nhập theo cách bằng tay 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 ô bắt buộc xem qua. Nó hoàn toàn có thể là một hàng hoặc một cột. match_type hoàn toàn 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. 1 là giá trị khoác định trường hợp đối số này bị quăng quật qua. áp dụng 1 hoặc -1 mang lại những thời khắc bạn đề nghị chạy tra cứu vớt 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 và đúng là ổn. Tuy vậy hãy ghi nhớ rằng ví như bạn không chỉ là định match_type, 1 sẽ là mang định, điều này có thể làm sai lệch hiệu quả nếu chúng ta thực sự hy vọng một tác dụng khớp thiết yếu xác.

Các lấy một ví dụ về hàm INDEX và MATCH

Trước khi xem xét cách phối hợp giữa hàm INDEX cùng MATCH vào một công thức, họ cần hiểu phương pháp mà mỗi hàm này sẽ thao tác trong Excel.

Ví dụ về hàm INDEX


*

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

=INDEX(A1:B4;2;2): Hàm sẽ kiểm soát mảng A1:B4 để tìm giá trị trong cột đồ vật hai với hàng thiết bị hai, sẽ là Vũ Hồng Ngọc. =INDEX(A1:B1;1): Hàm sẽ đánh giá mảng A1:B1 nhằm tìm cực hiếm trong cột đầu tiên, đó là Lê Thanh Khương. =INDEX(2:2;1): Hàm sẽ chất vấn mọi sản phẩ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ẽ soát sổ mảng B1:B2 để tìm giá trị trong số 1 tiên, sẽ 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 làm việc trong Excel thông qua 4 ví dụ đơn giản sau:

Dưới đấy 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ề kết quả là 3. =MATCH(16;D1:D3): Hàm MATCH đã tìm tìm 16 trong phạm vi D1:D3. Nhưng do 16 không tồn tại trong phạm vi tra cứu kiếm đề xuất MATCH sẽ tìm giá bán trị lớn nhất tiếp theo nhỏ dại hơn hoặc bởi 14, trong trường hòa hợp này là 13 , nằm tại đoạn 1 của lookup_array. =MATCH(16;D1:D3;-1): Tương trường đoản cú như cách làm ở trên, nhưng do mảng không theo thứ tự sút dần như -1 yêu mong nên chúng ta thấy thông báo lỗi #N/A. =MATCH(13;A1:D1;0): Hàm MATCH đã tìm tìm 13 trong số 1 tiên của bảng dữ liệu, kết quả trả về là 4 do nó là mục thứ bốn trong mảng này.

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

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

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

*

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

MATCH(F1;B2:B5): Hàm MATCH đang tìm kiếm cực hiếm F1 (8795) vào vùng tài liệu B2:B5. Như bọn họ thấy trong bảng, địa chỉ của giá trị F1 vào vùng dữ liệu này là 2 với đó là tác dụng mà hàm MATCH sẽ tìm ra. Mảng INDEX là C2:C5 vì chúng ta đang tìm kiếm quý hiếm trong cột đó. Hàm INDEX bây giờ 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, bạn có thể loại bỏ phần đó và phương pháp rút gọn gàng 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 ban sơ sẽ giống hệt như một phương pháp với hàm INDEX bình thường và giá trị của mục thiết bị hai trong phạm vi C2:C5 là cây viết nước Thiên Long.

Tra cứu giúp theo title hàng với cột

*

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

MATCH(G1;A2:A13;0): Hàm MATCH đang tìm tìm G1 (số 5) vào phạm vi A2:A13 để dìm một giá bán trị vắt thể. MATCH(G2;B1:E1;0): Là phương pháp MATCH đồ vật hai cùng giống cùng với công thức đầu tiên nhưng cầm vào chính là tìm kiếm G2 (từ "Bút tre") trong tiêu đề cột tự B1:E1. Như trong hình chính là cột vật dụng 3. Cuối cùng công thức cùng với hàm INDEX hoàn toàn có thể được viết lại ngắn gọn hơn để chúng dễ nắm bắt là:=INDEX(B2:E13;6;3).Có tức là hàm INDEX vẫn tìm tìm trong tổng thể bảng B2:E13 cho hàng thứ sáu và cột thứ bố để trả về công dụng là 41 như vào bảng trên.

Lưu ý khi áp dụng hàm MATCH và INDEX

MATCH không tách biệt chữ hoa và chữ thường, bởi vì vậy chữ hoa cùng chữ hay được xử lý giống nhau lúc khớp các giá trị văn bản. MATCH trả về lỗi #N/A vị nhiều vì sao như:match_type là 0 với lookup_value không được tra cứu thấy;match_type là -1 và lookup_array không tuân theo thứ tự sút dần;match_type là một và lookup_array không tuân theo thứ tự tăng ngày một nhiều và lookup_array ko phải là 1 hàng hoặc một cột. Chúng ta có thể sử dụng một ký kết tự thay mặt đại diện trong đối số lookup_value giả dụ match_type là 0 và lookup_value là 1 trong chuỗi văn bản. Dấu chấm hỏi khớp với bất kỳ ký tự solo 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! trường hợp row_num và column_num ko trỏ đến một ô vào mảng.

Hàm Index Match được áp dụng trong Excel tương đối nhiều và mang về nhiều lợi ích thiết thực cho tất cả những người dùng. Mặc mặc dù là hai hàm khác nhau nhưng khi kết phù hợp với nhau lại tạo nên nhiều quý giá tìm kiếm kết quả nhất. Vậy đây là hàm tính gì? Sử dụng kết hợp ra sao? Để rõ cụ thể hơn độc giả hãy cùng với Sforum tò mò xem ngay nội dung bài viết dưới trên đây nhé.


Hàm Index Match là gì? 

Hàm Index Match là 2 hàm đơn nhất kết phù hợp với nhau để sản xuất ra công dụng trong câu hỏi dò tra cứu dữ liệu. Để hiểu đấy là hàm gì các bạn hãy phân tích từng loại nhé. Cụ thể:

Hàm Match 

Hàm Match là một trong hàm search kiếm giá trị được khẳng định trước trong một phạm vi ô. Kế tiếp chúng đang trả về đúng vị trí tương đối của cực hiếm trong phạm vi đó. Cách làm cú pháp hàm là:

 =MATCH(Lookup_value,Lookup_array,).

Trong đó:

Lookup_value: Là khoảng giá trị search kiếm trong mảng Lookup_array. Giá trị tìm tìm đó rất có thể là số, văn bản, quý giá logic hay như là một tham chiếu ô đến một trong những nào đó. Đó cũng rất có thể là tham chiếu cho văn phiên bản hay giá trị lô ghích của dữ liệu.Lookup_array: Là mảng hay như là 1 phạm vị ô được tìm kiếm kiếm trên điều kiện.Match_type: Là mẫu mã tìm kiếm vẫn xét.
*
Hàm Index và hàm Match là 2 hàm tính đơn chiếc trong excel được kết hợp với nhau

Hàm Index

Hàm Index là một trong dạng hàm trả về mảng. Chúng giúp lấy những giá trị tại một ô nào kia giao giữa cột cùng dòng. Cách làm cú pháp hàm:

 =INDEX(Array,Row_num,)

Trong đó:

Array: Là vùng ô hoặc một mặt hàng số mảng làm sao đó bắt buộc đang tìm kiếm kiếm.Row_num: Là lựa chọn hàng vào mảng search kiếm từ kia trả về một giá bán trị.Column_num: Là chọn cột trong mảng tra cứu kiếm từ kia trả về một giá trị.

Cú pháp hàm INDEX dạng tham chiếu được xem như sau: 

=INDEX(Reference,Row_num,,)

Trong đó:

Reference: Là vùng tham chiếu phải được xét đến.Row_num: Là chỉ số hàng tìm tìm từ đó trả về một tham chiếu.Column_num: Là chỉ số cột tìm kiếm kiếm từ kia trả về một tham chiếu.Area_num: Là số của vùng ô tài liệu sẽ trả về cực hiếm trong Reference. Nếu Area_num được làm lơ thì hàm Index sẽ sử dụng vùng 1.

Cách phối hợp hàm Index và Match vào Excel

Để rất có thể sử dụng hàm Index Match trong Excel sẽ sở hữu được VD minh họa để các bạn dễ hiểu. Cụ thể như sau:

Ví dụ: thực hiện hàm Index với hàm Match vào Excel để tìm địa chỉ cửa hàng theo thương hiệu trong bảng dữ liệu. Công việc làm:

Bước 1: vào bảng tài liệu Excel chúng ta nhập công thức =INDEX(C3:C8,MATCH(C10,B3:B8,0)). Cách làm này sẽ được nhập vào ô tham chiếu mà bạn có nhu cầu hiển thị kết quả.

*
Nhập ô tham chiếu công thức =INDEX(C3:C8,MATCH(C10,B3:B8,0))

Bước 2: Nhập công thức dứt bạn dìm phím Enter để bọn chúng hiển thị công dụng tính. Bởi vậy là các bạn đã sử dụng phối hợp 2 hàm đó nhằm tìm kết quả dữ liệu rồi đó.

*
Nhấn Enter nhằm hiển thị kết quả cần tìm kiếm kiếm

Cách phối hợp hàm Index với Match những điều kiện

Đối với ngôi trường hợp vận dụng hàm Index Match những điều kiện sẽ sở hữu được ví dụ minh họa cụ thể như sau:

Ví dụ: chúng ta dùng hàm Index với hàm Match để tính đối chọi giá sản phẩm và hãng sản xuất sản xuất. Vào công thức bạn sẽ cần phải thực hiện $ để cố định giá trị các phạm vi. Mang sử theo đối chọi giá sản phẩm và đơn vị sản xuất trong bảng 2 (B15:E18) thì bạn cần điền đối chọi giá vào bảng 1 (B3:D12).

*
Ví dụ minh họa với dữ liệu bảng 1 cùng bảng 2

Các phối hợp 2 hàm Index cùng hàm Match nhằm dò tìm công dụng giá trị cho ô D4 như sau:

Bước 1: Bạn thực hiện hàm Match nhằm xác xác định trí của mặt hàng tương ứng thành phầm CDRom. Khoảng giá trị phạm vi B15:B18 của bảng 2, công thức:

=MATCH(B4,$B$15:$B$18,0). Hiệu quả “4” tương ứng với sản phẩm số 4 trong bảng 2 xét đến.

*
Nhập bí quyết tính hàm Match =MATCH(B4,$B$15:$B$18,0)

Bước 2: tiếp nối bạn thực hiện hàm Match nhằm xác định vị trí cột tương xứng của đơn vị sản xuất Samsung. Phạm vi xét đến dữ liệu trong tầm B15:E15. Bí quyết nhập:

=MATCH(C4,$B$15:$E$15,0). Kết quả cho quý hiếm “2” khớp ứng với cột số 2 trong bảng 2.

*
Nhập phương pháp tính hàm Match =MATCH(C4,$B$15:$E$15,0)

Bước 3: kế tiếp bạn sử dụng hàm Index và kết hợp với 2 hàm Match vẫn tính sinh sống trên. Lúc ấy sẽ kiếm được giá trị theo hàng và cột trong bảng 2. Công thức:

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

*
Nhập công thức phối hợp giữa hàm index và hàm match

Kết quả trả về giá chỉ trị khớp ứng với mặt hàng 4 (hàng Mouse) và cột 2 (Samsung) trong bảng 2 là 5.

Bước 4: sau cùng bạn copy phương pháp vừa tính cho những ô không giống để xong bảng tài liệu cần xét đến.

*
Copy cách làm để hiển thị toàn bộ kết quả dữ liệu

Cách phối kết hợp hàm Index Match cùng Vlookup

Để phối kết hợp hàm Index Match Vlookup thì sẽ có công thức tính riêng. Bí quyết như sau:

=VLOOKUP(INDEX(tham_chiếu;;);table_array;col_index_num;)

Trong đó:

INDEX(tham_chiếu; ; ): Là giá chỉ trị dùng để làm dò tìm kiếm điều kiện.Table_array: Là bảng cất giá trị mong muốn dò tra cứu dữ liệu.Col_index_num: Là địa chỉ của cột cất giá trị mà bạn có nhu cầu dò tìm.: Là phạm vi mà tài liệu đang tìm kiếm. Nếu có giá trị 0 là dò tìm tương đối hoặc 1 là dò tìm giỏi đối.

Ví dụ minh họa:

Có bảng dữ liệu dùng để xác định điểm vùng của từng học tập sinh nhờ vào khu vực.

*
Bảng tài liệu ví dụ minh họa

Công thức áp dụng:

=VLOOKUP(INDEX(A2:F9;1;6);$I$2:$J$9;2;0)

Trong đó: 

INDEX(A2:F9;1;6): nằm trong khu vực dải ô A2:F9 mang ra giá trị ở hàng 1 cột 6.$I$2:$J$9: Dải ô trải lâu năm từ I2:J9 có chứa quý giá dò tìm.2: Là vị trí cột điểm vùng.0: Là dạng hình dò kiếm tìm tương đối.
*
Bảng công dụng tính được

Hướng dẫn áp dụng hàm Index Match phối kết hợp If 

Để sử dụng kết hợp giữa hàm Index Match với hàm If sẽ có được công thức tính như sau:

=IF(INDEX(mảng_dữ_liệu;MATCH(khóa_tìm_kiếm,dải_ô,loại_tìm_kiếm);số_cột)+điều_kiện;giá_trị_nếu_đúng; giá_trị_nếu_sai)

Trong đó:

Mảng_dữ_liệu: Là khu vực vùng ô hoặc 1 hàng số mảng nào đó bắt buộc.Khóa_tìm_kiếm: Là cực hiếm mà bạn muốn tìm kiếm dữ liệu.Dải_ô: Là khu vực vùng dữ liệu bạn có nhu cầu xác xác định trí của khóa_tìm_kiếm. Dải ô này chỉ rất có thể có duy nhất là một trong những hàng hoặc 1 cột làm sao đó.Loại_tìm_kiếm: Là cách tiến hành tìm kiếm với 3 dạng chính. Gồm những: 0 tác dụng khớp bao gồm xác, 1 là mặc định giá trị. Dạng thứ tía -1 là dải ô được bố trí theo sản phẩm công nghệ tự giảm dần trả về giá chỉ trị nhỏ tuổi nhất to hơn hoặc bằng khóa kiếm tìm kiếm.Số_cột: giá trị trả về số cột của dữ liệu lấy ra.Giá_trị_nếu_đúng: Ở đây vẫn trả về quý giá nếu hàm INDEX + điều_kiện đúng.Giá_trị_nếu_sai: Ở đây đã trả về giá trị nếu hàm INDEX + điều_kiện sai.

Ví dụ minh họa: 

Bảng dữ liệu đưa ra xác định sản phẩm bánh quy đã chào bán đạt hay không đạt tiêu chí KPI. Để có thể đạt chỉ tiêu KPI con số bánh xuất kho phải lớn hơn 10 sản phẩm.

*
Bảng ví dụ minh họa

Công thức áp dụng: 

=IF(INDEX(A2:F5;MATCH(“Bánh quy”;A2:A5;0);3)>10;”Đạt”;”Không đạt”)

Trong đó: Hàm Match đã trả về địa chỉ hàng của ô chứa giá trị bánh quy. Hàm Index đã trả về con số bán của bánh quy. Đem đối chiếu xem lớn hơn 10 thành phầm hay không. Nếu đúng lớn hơn thì giá trị trả về “Đạt” còn không đúng thì cực hiếm trả về “không đạt”.

*
Kết quả bảng tính thừa nhận được

Một số lỗi thường chạm mặt khi sử dụng hàm Index Match trong Excel

Khi sử dụng phối kết hợp hàm Index và hàm Match vào Excel sẽ gặp mặt một số lỗi cơ phiên bản thường thấy như sau:

Lỗi #REF

Lỗi #REF này là giữa những lỗi phổ biến thường gặp gỡ nhất khi sử dụng kết hợp 2 hàm tính Index với Match. Thông thường, lỗi sẽ xảy ra khi phạm vi trả về của hàm Index khác với phạm vi tra cứu giúp của hàm Match. 

Đối với ngôi trường hợp không kiếm thấy tiêu chí dữ liệu đã mang đến thì áp dụng hàm Index và Match đang sẽ về #REF. Ao ước khắc phục được lỗi #REF này thì chiến thuật tốt tốt nhất là đổi khác phạm vi xét mang lại của hàm Index.

Lỗi #N/A

Lỗi #N/A nãy lộ diện khi không kiếm thấy quý giá xét đến của bảng dữ liệu. Lỗi này cũng xảy ra tương tự hệt như hàm Vlookup hay thấy.

Vì sao cần dùng hàm Index Match nỗ lực Vlookup cùng Hlookup?

Việc sử dụng phối kết hợp hàm Index cùng hàm Match sửa chữa cho hàm Vlookup, hàm Hlookup sẽ tạo ra những hiệu quả. Sự phối hợp hai hàm này sẽ tạo nên ra nhiều công dụng tốt rộng với lý do dễ dàng và đơn giản sau:

Hàm Vlookup, hàm Hlookup thưởng chúng chỉ hoàn toàn có thể ra cứu tài liệu theo một chiều. Đối cùng với những dữ liệu cần tra cứu 2 chiều rất khó để sử dụng.Sử dụng hàm Vlookup, hàm Hlookup sẽ dẫn mang lại sai sót nếu khách hàng thêm hoặc ngắn hơn cột sinh sống trong bảng tính.

Xem thêm: Top 100+ Cách Đặt Tên Cho Bé Gái 2021 Hay Và Hợp Tuổi Bố Mẹ Đặt Cho Con Yêu

Lời kết

Hy vọng với tổng thể thông tin tổng hòa hợp trên đang phần nào giúp bạn đọc nắm rõ hàm Index Match là gì rồi chứ. Ngoài ra còn biết được phương pháp sử dụng phối hợp 2 hàm này đơn giản nhất. Nếu gồm gì thắc mắc với thông tin trên hoặc cập nhập thêm nhiều sự việc khác các bạn hãy đến cùng với Sforum ngay lập tức nhé.