Cách kết hợp hàm index và match trong excel nhiều điều kiện, cách kết hợp hàm index và match trong excel để dò

Hàm Index Match là gì?
Hàm Match
Hàm Index
Cách kết hợp hàm Index cùng Match vào Excel
Cách phối kết hợp hàm Index cùng Match nhiều điều kiện
Cách kết hợp hàm Index Match và Vlookup
Hướng dẫn thực hiện hàm Index Match phối hợp If
Những sai trái thường chạm mặt khi sử dụng hàm Index Match vào Excel
Tại sao đề xuất ưu tiên hàm Index Match thay bởi Vlookup với Hlookup?
Điểm chốt
Hàm Index Match là sự phối kết hợp linh hoạt thân INDEX và MATCH vào Excel, mang về nhiều tác dụng quan trọng. Mặc mặc dù cho là hai hàm không giống nhau, dẫu vậy khi thực hiện chung, chúng tạo nên cách kết quả để tra cứu kiếm dữ liệu. Đọc tiếp nhằm hiểu cụ thể về cách sử dụng hàm này.
Bạn đang xem: Cách kết hợp hàm index và match

Hàm Index Match là gì?

Hàm Index Match là sự phối kết hợp thông minh giữa hai hàm INDEX và MATCH để thực hiện việc tìm kiếm kiếm dữ liệu một cách bao gồm xác. Hãy tìm hiểu cụ thể về cách áp dụng này:

Hàm Match

Hàm Match được thực hiện để xác định vị trí của một cực hiếm trong một phạm vi ô. Nó trả về vị trí kha khá của quý hiếm đó trong phạm vi. Phương pháp cú pháp của hàm là:

=MATCH(Giá_trị_tìm_kiếm,Dãy_Giá_trị,).

Trong đó:

Giá_trị_tìm_kiếm: Là giá bán trị phải tìm tìm trong Dãy_Giá_trị. Hoàn toàn có thể là số, văn bản, giá bán trị logic hoặc tham chiếu mang lại một ô chứa dữ liệu. Dãy_Giá_trị: Là mảng hoặc phạm vi ô chứa tài liệu được tìm kiếm. Loại_tìm_kiếm: Là các loại tìm kiếm đang rất được thực hiện.

Hàm Index với hàm Match là bộ đôi hoàn hảo kết hợp trong Excel

Hàm Index

Hàm Index trả về giá trị của ô phía trong một mảng, được xác định bởi số hàng cùng số cột. Bí quyết cú pháp:

=INDEX(Mảng,Hàng_số,)

Trong đó:

Mảng: Là khu vực ô hoặc sản phẩm số mảng đang được tìm kiếm. Hàng_số: chọn hàng trong mảng để trả về giá chỉ trị. Cột_số: chọn cột vào mảng nhằm trả về giá chỉ trị.

Công thức hàm INDEX tham chiếu gồm dạng như sau:

=INDEX(Tham_chiếu,Hàng_số,,)

Trong đó:

Tham_chiếu: Là khu vực tham chiếu phải được xét đến. Hàng_số: Là chỉ số hàng tìm kiếm từ kia trả về một tham chiếu. Cột_số: Là chỉ số cột tìm kiếm từ kia trả về một tham chiếu. Khu_vực_số: Là số của vùng ô dữ liệu sẽ trả về giá trị trong Tham_chiếu. Trường hợp Khu_vực_số được làm lơ thì hàm Index sẽ dùng vùng 1.

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

Để rất có thể sử dụng hàm Index Match trong Excel, bạn có thể thực hiện theo ví dụ như sau để làm rõ hơn:

Ví dụ: thực hiện hàm Index cùng hàm Match vào Excel nhằm tìm showroom theo tên trong bảng dữ liệu. Công việc làm:

Bước 1: vào bảng tài liệu Excel, nhập phương pháp =INDEX(C3:C8,MATCH(C10,B3:B8,0)). Phương pháp này sẽ tiến hành 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: sau khoản thời gian nhập công thức, nhấn Enter nhằm hiển thị công dụng tính. Đơn giản như vậy, bạn đã sử dụng phối kết hợp 2 hàm để tìm tác dụng dữ liệu.

Nhấn Enter nhằm hiển thị tác dụng cần search kiếm

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

Đối với trường hợp vận dụng hàm Index Match các điều kiện, dưới đó là ví dụ minh họa cố thể:

Ví dụ: áp dụng hàm Index với hàm Match để tính 1-1 giá sản phẩm và hãng sản xuất. Trong công thức, hãy sử dụng $ để cố định giá trị những phạm vi. đưa sử theo đối chọi giá sản phẩm và đơn vị phân phối trong bảng 2 (B15:E18), bạn cần điền đối chọi giá vào bảng 1 (B3:D12).

Ví dụ minh họa với tài liệu bảng 1 với bảng 2

Dùng kết hợp 2 hàm Index với Match nhằm dò tìm giá bán trị mang lại ô D4 như sau:

Bước 1: sử dụng hàm Match để xác định vị trí hàng tương xứng với thành phầm CDRom trong phạm vi B15:B18 của bảng 2, công thức:

=MATCH(B4,$B$15:$B$18,0). Kết quả là “4,” tương xứng với hàng số 4 trong bảng 2.

Nhập phương pháp hàm Match =MATCH(B4,$B$15:$B$18,0)

Bước 2: Tiếp theo, áp dụng hàm Match nhằm xác xác định trí cột tương xứng với nhà phân phối Samsung. Phạm vi xét đến là dữ liệu trong tầm B15:E15. Bí quyết nhập vào là:

=MATCH(C4,$B$15:$E$15,0). Công dụng là quý hiếm “2,” tương xứng với cột số 2 vào bảng 2.

Nhập công thức hàm Match =MATCH(C4,$B$15:$E$15,0)

Bước 3: Tiếp theo, áp dụng hàm Index kết phù hợp với 2 hàm Match đã tính sinh sống trên để tìm cực hiếm theo hàng cùng cột vào 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ới hàm match

Kết trái trả về giá trị tương ứng với mặt hàng 4 (hàng Mouse) và cột 2 (Samsung) vào bảng 2 là 5.

Bước 4: Cuối cùng, copy phương pháp vừa tính cho các ô không giống để kết thúc bảng dữ liệu cần xét.

Copy bí quyết để hiển thị tất cả kết quả dữ liệu

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

Để kết hợp hàm Index Match Vlookup, sử dụng công thức như sau:

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

Trong đó:

INDEX(tham_chiếu; ; ): giá bán trị sử dụng để dò tìm điều kiện. Table_array: Bảng chứa giá trị cần dò kiếm tìm dữ liệu. Col_index_num: địa điểm cột cất giá trị ao ước dò tìm. : Phạm vi tài liệu đang tìm kiếm kiếm. 0 là kiếm tìm tương đối, một là tìm xuất xắc đối.

Ví dụ minh họa:

Dùng bảng tài liệu để xác minh điểm vùng của học sinh theo quần thể vực.

Bảng dữ liệu minh họa lấy ví dụ

Sử dụng công thức:

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

Trong đó:

INDEX(A2:F9;1;6): chọn giá trị từ khu vực ô A2:F9 ở mặt hàng 1 cột 6. $I$2:$J$9: Dải ô từ I2:J9 chứa giá trị đề nghị tìm kiếm. 2: địa chỉ cột mang lại điểm vùng. 0: kiểu dáng tìm tìm tương đối.

Bảng kết quả sau khi tính toán

Hướng dẫn thực hiện hàm Index Match kết hợp If

Để sử dụng phối hợp hàm Index Match và hàm If, chúng ta cũng có thể sử dụng cách làm 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 ô hoặc một sản phẩm số mảng nào đó bắt buộc. Khóa_tìm_kiếm: giá trị đề nghị tìm tìm dữ liệu. Dải_ô: quanh vùng vùng dữ liệu bạn muốn xác định vị trí của khóa_tìm_kiếm. Chỉ hoàn toàn có thể là một hàng hoặc một cột. Loại_tìm_kiếm: cách tiến hành tìm kiếm với 3 dạng chính: 0 cho kết quả khớp chính xác, một là giá trị mặc định, -1 là search kiếm dải ô được thu xếp theo thứ tự giảm dần. Số_cột: Số cột của tài liệu trả về. Giá_trị_nếu_đúng: giá trị trả về nếu đk đúng. Giá_trị_nếu_sai: quý giá trả về nếu điều kiện sai.

Ví dụ minh họa:

Bảng tài liệu để khẳng định sản phẩm bánh quy đã phân phối đạt KPI hay không. Để đạt KPI, số lượng bánh xuất kho phải lớn hơn 10 sản phẩm.

Xem thêm: Causes And Effects Of Human Trafficking In Persons, Health Consequences Of Trafficking

Bảng minh họa lấy ví dụ như

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 xác định địa chỉ hàng của ô chứa giá trị bánh quy. Hàm Index trả về số lượng bán của bánh quy. đối chiếu xem có lớn hơn 10 sản phẩm hay không. Kết quả: “Đạt” nếu mập hơn, “Không đạt” nếu như không.

Kết quả của bảng tính

Những sai lầm thường gặp mặt khi sử dụng hàm Index Match trong Excel

Khi phối hợp hàm Index với hàm Match, bạn có thể gặp phải những lỗi như sau:

Lỗi #REF

Lỗi #REF thường xuất hiện khi phạm vi trả về của Index khác với phạm vi tra cứu giúp của Match.

Nếu không tìm kiếm thấy dữ liệu đã cho, hàm Index với Match trả về #REF. Để khắc phục lỗi #REF, biến hóa phạm vi xét cho của hàm Index.

Lỗi #N/A

Lỗi #N/A xuất hiện khi không kiếm thấy cực hiếm trong bảng dữ liệu, hệt như lỗi thường chạm chán ở hàm Vlookup.

Tại sao đề xuất ưu tiên hàm Index Match thay vì Vlookup cùng Hlookup?

Kết phù hợp hàm Index với hàm Match mang đến nhiều tác dụng hơn:

Hàm Vlookup cùng Hlookup chỉ tra cứu tài liệu theo một chiều, khó áp dụng cho dữ liệu 2 chiều. Sử dụng Vlookup cùng Hlookup dễ khiến cho sai sót khi thêm/bớt cột trong bảng tính.

Điểm chốt

Hy vọng thông tin đã khiến cho bạn hiểu rõ hàm Index Match với cách phối hợp 2 hàm này. Giả dụ có vướng mắc hoặc muốn update thêm thông tin, ghẹ thăm Mytour tức thì nhé.

Hàm Index với Match là hàm dễ ợt giúp cho tất cả những người sử dụng Excel có thể dò tìm dữ liệu một cách dễ dàng và linh hoạt. Thay bởi vì sử dụng những hàm bao gồm điều kiện tinh vi như VLookup tuyệt Hlookup thì bạn có thể sử dụng những hàm này trong vô số nhiều trường thích hợp khác nhau. Nhờ vậy, thao tác Excel sẽ nhanh chóng và dễ ợt hơn. Vậy, Hàm Index, Match gồm ách sử dụng như vậy nào.Sau đây sẽ là giải thuật đáp một cách chi tiết nhất giành cho bạn.

Bất tiện lúc sử dụng những hàm Vlookup với hàm Hlookup

Hàm Vlookup cùng Hlookup là 1 hàm phổ biến đối với những ai thường sử dụng Excel. Nhị hàm này mọi có công dụng hỗ trợ dò tìm với tra cứu tài liệu tương ứng. Trong trường hợp các dữ liệu xử lý nằm tại vị trí hai bảng khác nhau trong cùng một Sheet Excel. Hàm Hlookup đang dò tìm còn chỉ trả về giá chỉ trị mang lại cột ở ở dưới hàm. Tương tự, hàm Vlookup dò tìm và chỉ trả xác định giá trị đúng vào lúc nằm nghỉ ngơi cột bên yêu cầu của hàm. Chắc chắn rằng là với biện pháp dò search này, người tiêu dùng Excel sẽ gặp phải nhiều hạn chế.

Đối với các hàm này, câu hỏi bạn chèn hay xóa cốt sẽ tác động đến công dụng tìm kiếm. Trong nhiều trường hợp giá trị dò tìm sẽ ảnh hưởng lỗi. ở kề bên đó, hàm Vlookup và Hlookup công cụ giá trị nên tìm kiếm không được vượt quá 255 ký tự. Nếu vi phạm luật hàm đã trả về lỗi #VALUE!. Sát bên đó, các hàm này thường không cân xứng với hầu như bảng tính lâu năm và cách làm phức tạp. Chúng tương đối dễ gây ra sai sót cùng nhầm trong quy trình xử lý dữ liệu. Núm vào đó, hàm Index cùng Match lại sở hữu sự hoạt bát và giải pháp sử dụng đơn giản dễ dàng hơn nhiều.

Cú pháp hàm Index trong Excel

Hàm Index cách xử lý và đã cho ra giá trị tự phạm vi bảng tính mà người tiêu dùng nhập vào cú pháp. Hàm này khá dễ dàng và đơn giản với cú pháp như sau:

=INDEX(array, row_num, )

Trong đó, các đối số có ý nghĩa sâu sắc như sau:

array là phạm vi dò tìm giá trị mà bạn có nhu cầu trả về kết quả row_num là số sản phẩm tự của hàng cơ mà giá trị công dụng trả về. Nếu vứt trống, thì column_num là bắt buộc.column_num là số thiết bị tự của cột cơ mà giá trị kết quả được trả về. Nếu vứt trống, thì row_num là bắt buộc.

Nếu trong công thức có cả row_num và column_num thì kết quả của hàm vẫn trả về quý giá ở ô là giao điểm của hàng với cột được xác định. Bọn họ xem xét ví dụ tiếp sau đây để hiểu hơn về cách sử dụng hàm Index này:

VD: =INDEX(A1:C10,2,3). Có nghĩa là, công thức đang tiến hành dò tra cứu ở mảng ô A1:C10. Sau đó, giá bán trị kiếm được sẽ trả về hàng máy hai cùng cột trang bị ba, tức là ô C2. Mặc dù nhiên, bọn họ không thường sử dụng hàm Index một cách 1-1 độc. Cụ vào đó, bọn họ cần phối kết hợp nó cùng với hàm Match.

Số sản phẩm tự của cột hay/và hàng chính là tất cả các gì bạn cần để nhập vào câu lệnh row_num hay/và column_num của. Tác dụng sẽ trả về cực hiếm ở giao điểm của hàng với cột xác định này. Tuy nhiên, ko thể khẳng định một cách thiết yếu xác bạn có nhu cầu cột nào mặt hàng nào cùng với hàm Index. Vì đó, họ cần sử dụng hàm Match.

Cú pháp hàm Match trong Excel

Hàm Match sẽ tiến hành tìm kiếm cực hiếm trong phạm vi dải ô xác định. Sau đó, hàm sẽ cách xử lý và trả về vị trí tương đối của ô kia trong dải ô. Hàm này còn có cú pháp như sau:

=Match(lookup_value, lookup_array, )

Trong đó những đối số có chân thành và ý nghĩa là:

lookup_value là chữ số xuất xắc chuỗi ký kết tự đề nghị tìm kiếm. lookup_array là chỉ dải ô được tìm kiếm.match_type là thống sô ra quyết định sự tương xứng tuyệt đối giỏi sự phù hợp tương đối. Nếu khách hàng điền 1 hoặc quăng quật trống, giá chỉ trị tạo ra sẽ không có sự phù hợp tuyệt so với giá trị buộc phải tìm. Trong những khi đó 0 chỉ kết quả phải bằng giá trị đề xuất tìm, giỏi là sự cân xứng tuyệt đối. Khi phối kết hợp hàm Index cùng Match, câu lệnh thứ tía của hàm Match luôn luôn luôn sẽ là “0” vị hàm đồi hỏi sự chính xác tuyệt đối. Còn “-1” chỉ khả năng đồng ý giá trị nhỏ dại nhất to hơn hay bằng giá trị nên tìm.

Để hiểu hơn về hàm này, bạn có thể xét lấy ví dụ sau đây. Nếu dải ô B1:B3 chứa các giá trị “Viet
Nam”, “Mexico”, “Venice”, rồi bí quyết =Match(“Viet
Nam”,B1:B3,0) trả về số 1 bởi vì đó là mục thứ nhất trong dải ô. Tuy nhiên, có thể thấy trường hợp chỉ sử dụng hàm Match riêng rẽ thì nó cũng không có quá những ý nghĩa.

Lợi ích khi phối kết hợp hàm Index cùng Match vào Excel

Khách với hàm Vlookup, khi phối kết hợp hàm Index và Match, chúng ta cũng có thể dò kiếm tìm cả quý giá ở bên buộc phải và trái của hàm. Chứ không hề nhất thiết là bên đề nghị như hàm Vlookup. Với hàm Vlookup, bất cứ cột làm sao được chèn tuyệt xóa cũng có thể có thể thay đổi kết trái trả về. Tuy nhiên, so với hàm Index và Match thì biến đổi này sẽ không có tác động gì đến công dụng của hàm. Lúc sử dụng những chuỗi dữ liệu lớn cùng phức tạp. Nhờ vậy tránh được những không đúng sót khó phát hiện.

Hơn cố nữa, khi phối kết hợp hai hàm này, nó sẽ giới hạn max cho kích thước dữ liệu bắt buộc tìm. Trong những lúc đó hàm Vlookup yêu mong tổng chiều dài của tiêu chuẩn chỉnh tìm kiếm tránh việc vượt vượt 255 cam kết tự. Do vậy, ví như chuỗi dò tìm quá to thì chúng ta nên sử dụng phối kết hợp hai hàm này. Sự phối kết hợp của nhị hàm này cũng chất nhận được tốc độ cách xử trí nhanh hơn. Với phần lớn chuỗi dữ liệu lớn đến rất cao thì hiệu quả xử lý sẽ nhanh hơn 15% đối với thông thường.

Hướng dẫn phối hợp và áp dụng hàm Index và Match vào Excel

Để hiểu hơn về cách phối hợp hàm Index và Match vào Excel, chúng ta xét ví dụ sau đây. Ta có bảng về tên sản phẩm và đơn vị sản xuất (B15:E18). Dựa vào bảng này, người dùng cần điền giá tương ứng lên bảng sống ô (B3:D12). Bọn họ sẽ làm bí quyết cho ô D4 và tiếp nối copy cho những ô còn lại.

Ví dụ về hàm Index với Match vào Excel
Bước 1: bọn họ sử dụng hàm Match với mục tiêu trả về địa điểm hàng tương xứng của thành phầm CDRom trong phạm vi bảng (B15:B18)

Sử dụng hàm Match để xác xác định trí hàng
Bước 2: tiếp tục sử dụng hàm Match xác định vị trí của cột khớp ứng của nhà phân phối Samsung vào phạm vi (B15:E15).

Sử dụng hàm Match để xác minh ví trí của cột
Bước 3: kết hợp hàm Index cùng với 2 hàm MATCH ở trên. Tác dụng trả về sẽ là quý giá dựa theo hàng cùng cột ở bảng ô (B3:D12). Tác dụng tương ứng đang là 5.

Kết thích hợp hàm Index với Match

Và trên đó là những hướng dẫn cụ thể về sử dụng và kết hợp hàm Index với Match trong Excel. Mong muốn những tin tức trên đây sẽ giúp đỡ ích mang lại bạn. Từ học nhiều hơn nữa về Excel trên cachseo.com nhé!