Để tiến hành tra cứu tinh vi với nhiều tiêu chí, ta không thể thực hiện hàm VLOOKUP. Khi đó, họ phải sử dụng hàm INDEX kết phù hợp với hàm MATCH nhằm dò search nhiều điều kiện nâng cao. Rõ ràng cách phối kết hợp hàm index và match trong excel như vậy nào, chúng ta hãy cùng tham khảo nội dung bài xích học Excel online bên dưới đâynhé.
Bạn đang xem: Cách dùng index kết hợp match
1. Hàm INDEX với hàm MATCH
Ở phần này, shop chúng tôi sẽ reviews về hàm INDEX cùng hàm MATCH trong excel.
1.1. Cách làm hàm INDEX
Hàm INDEX vào excel có chức năng trả về giá trị của một ô hoặc một phạm vi ô vào một bảng tính, dựa vào chỉ số hàng với cột được chỉ định. Công thức hàm INDEX bao gồm hai dạng:
- INDEX (array, row_num,
- INDEX (reference, row_num,
Trong đó:
- array: Là mảng hai chiều chứa những giá trị bắt buộc tìm.
- reference: là một trong những hoặc nhiều phạm vi tham chiếu chứa những giá trị đề xuất tìm.
- row_num: Là chỉ số mặt hàng của ô nên tìm trong mảng hoặc phạm vi tham chiếu.
- column_num: Là chỉ số cột của ô đề xuất tìm trong mảng hoặc phạm vi tham chiếu. Nếu vứt qua, hàm đã trả về quý giá của cột đầu tiên.
- area_num: Là chỉ số khu vực của phạm vi tham chiếu yêu cầu tìm. Nếu bỏ qua, hàm vẫn trả về quý giá của khoanh vùng đầu tiên.
Hàm INDEX vào excel có chức năng trả về quý giá của một ô hoặc một phạm vi ô trong một bảng tính, dựa vào chỉ số hàng và cột được chỉ định
1.2. Công thức hàm MATCH
Hàm MATCH vào excel có tác dụng trả về vị trí của một giá trị trong một mảng một chiều hoặc trong một phạm vi ô. Bí quyết hàm MATCH tất cả dạng:
MATCH (lookup_value, lookup_array,
Trong đó:
- lookup_value: Là giá bán trị bắt buộc tìm tìm trong mảng hoặc phạm vi ô.
- lookup_array: Là mảng một chiều hoặc phạm vi ô chứa các giá trị cần đối chiếu với lookup_value.
- match_type: Là vẻ bên ngoài khớp giá chỉ trị, có ba giá trị có thể là:
+ 1: Trả về địa chỉ của giá bán trị to nhất nhỏ tuổi hơn hoặc bởi lookup_value. Mảng hoặc phạm vi ô phải được sắp xếp theo thiết bị tự tăng dần.
+ 0: Trả về địa điểm của giá bán trị bởi lookup_value. Mảng hoặc phạm vi ô không rất cần được sắp xếp theo thiết bị tự nào.
+ -1: Trả về địa chỉ của giá bán trị nhỏ tuổi nhất to hơn hoặc bằng lookup_value. Mảng hoặc phạm vi ô cần được sắp xếp theo lắp thêm tự giảm dần.
Hàm MATCH trong excel có chức năng trả về địa chỉ của một quý hiếm trong một mảng một chiều hoặc trong một phạm vi ô
2. Bởi vì sao đề xuất dùng hàm Index Match nắm Vlookup với Hlookup?
Hàm Index Match là một trong thuật toán kết hợp hai hàm INDEX cùng MATCH nhằm tìm kiếm với lấy dữ liệu xuất phát từ một bảng tính theo đk cho trước. Hàm Index Match có tương đối nhiều ưu điểm so với hàm Vlookup với Hlookup như:
- Không phụ thuộc vào vào địa chỉ của cột hoặc hàng đựng giá trị nên tìm. Hàm Hlookup và Vlookup chỉ có thể tìm kiếm cực hiếm trong cột đầu tiên hoặc hàng đầu tiên của mảng hoặc phạm vi ô, trong lúc hàm Index Match rất có thể tìm kiếm quý hiếm ở bất kỳ vị trí nào.
- không bị tác động bởi vấn đề thêm hoặc xóa cột hoặc mặt hàng trong bảng tính. Hàm Vlookup với Hlookup cần phải chỉ định số thiết bị tự của cột hoặc hàng đựng giá trị yêu cầu lấy, yêu cầu nếu có sự biến hóa về số lượng cột hoặc hàng, công thức sẽ ảnh hưởng sai. Hàm Index Match không cần phải chỉ định số sản phẩm tự của cột hoặc hàng, mà chỉ cần dùng hàm MATCH nhằm tìm ra địa điểm của chúng.
- rất có thể tìm kiếm giá bán trị theo nhiều điều kiện. Hàm Vlookup cùng Hlookup chỉ hoàn toàn có thể tìm kiếm quý giá theo một điều kiện duy nhất, là giá trị của cột hoặc hàng chứa giá trị cần tìm. Hàm Index Match có thể kết phù hợp với các hàm khác ví như AND, OR, IF để tìm kiếm giá bán trị theo nhiều điều kiện không giống nhau.
Lý do đề xuất dùng hàm INDEX cùng MATCH
3. Ví dụ hàm INDEX kết hợp hàm MATCH
Ở phần này, cửa hàng chúng tôi sẽ reviews tới bạn 4 lấy ví dụ về hàm index và hàm match trong Excelđể bạn hiểu hơn về sự kết hợp của nhì hàm này.
3.1. Cách phối hợp hàm index và match vào excel
Để phối kết hợp hàm index cùng match trong excel, ta rất có thể sử dụng bí quyết sau:
- INDEX (array, MATCH (lookup_value, lookup_array, match_type),
- INDEX (reference, MATCH (lookup_value, lookup_array, match_type),
Ví dụ: mang lại bảng dữ liệu 1:
Yêu cầu: Tim điểm của Bình bằng cách dùng hàm index cùng match.
Cách kết hợp hàm index với match trong excel:
- Bước 1: Nhập bí quyết sau vào ô B9:
=INDEX(D3:D6;MATCH(B8;A3:A6;0)).
Nhập cách làm sau vào ô B9
Trong đó:
+ D3:D6 là mảng hai chiều chứa những giá trị bắt buộc tìm.
+ B8 là giá chỉ trị phải tìm kiếm trong mảng.
+ A3:A6 là mảng một chiều chứa những giá trị cần đối chiếu với B8.
+ 0 là đẳng cấp khớp giá chỉ trị bao gồm xác.
- bước 2: nhấn enter vẫn thu được tác dụng là 7 điểm.
Kết quả sau thời điểm nhập công thức
3.2. Cách phối hợp hàm index với match trong excelvớinhiều điều kiện
Bài tập hàm index cùng match: mang đến hai bảng tài liệu như sau:
Yêu cầu: dùng hàmindex cùng match nhằm tìm đối kháng giá mang đến từng thành phầm ở bảng 1.
Cách thực hiện hàm index match nhiều điều kiện:
- bước 1: Ở ô D4, chúng ta nhập công thức kết hợp của hai hàm index và match như sau:
=INDEX($B$15:$E$18,MATCH(B4,$B$15:$B$18,0),MATCH(C4,$B$15:$E$15,0)).
Nhập phương pháp vào ô
D4
- Bước 2: nhấn enter, kết quả trả về giá bán trị tương ứng với hàng 4 (hàng Mouse) và cột 2 (Samsung) trong bảng 2 là 5.
- bước 3: Copy phương pháp ở ô D4 xuống phía bên dưới sẽ thu được bảng hiệu quả như sau:
3.2. Cách phối hợp hàm Index Match với Vlookup
Hàm Index Match cùng Vlookup có thể kết hợp với nhau để tìm kiếm cùng lấy dữ liệu từ không ít bảng tính không giống nhau, dựa vào một giá trị chung.
Ví dụ: đến bảng lấy một ví dụ 3 với 4:
Yêu cầu: từ bảng ví dụ 4 hãy dùng Hàm Index Match cùng Vlookup nhằm tìm ra nút trợ cấp cho của từng fan ở bảng số 3.
Xem thêm: Cách Chèn Url Vào Excel
Cách thực hiện:
- Bước 1: Ở ô D18, bạn nhập bí quyết sau:
=VLOOKUP(INDEX(A19:C23;1;3);$F$19:$G$22;2;0).
Nhập bí quyết vào
D18
Trong đó:
- INDEX(A19:C23;1;3): nằm trong khu vực dải ô A19:C23 lôi ra giá trị ở mặt hàng 1 cột 3.
- $F$19:$G$22: Dải ô trải dài từ F19:G22 có chứa quý hiếm dò tìm.
- 2: Là vị trí cột điểm vùng.
- 0: Là đẳng cấp dò kiếm tìm tương đối.
- Bước 2: nhận enter sẽ thu được công dụng là 170000 VNĐ.
Kết quả là 170000 VNĐ
- Bước 3: Kéo thả loài chuột xuống phía dưới sẽ chiếm được bảng tác dụng là:
3.4. Hướng dẫn áp dụng hàm Index Match phối kết hợp If
Hàm Index Match phối kết hợp If rất có thể giúp ta search kiếm với lấy dữ liệu xuất phát điểm từ 1 bảng tính theo một điều kiện nào đó.
Ví dụ: đến bảng dữ liệu 5:
Yêu cầu: sử dụng hàm if kết hợp với Index Match để xác minh lượng cam đẩy ra có đạt yêu cầu hay không. Lưu giữ ý, lượng trái cây xuất kho cần lớn hơn 8kg bắt đầu được coi là đạt yêu cầu.
Cách thực hiện:
- Bước 1: Đặt nhỏ trở chuột vào ô B38, nhập công thức sau:
=IF(INDEX(A32:D35;MATCH("Cam";A32:A35;3);3)>8;"Đạt";"Không đạt").
Nhập bí quyết vào ô B37
- Bước 2: nhận enter đã thu được tác dụng là Đạt, tức là lượng cam xuất kho đã nhiều hơn 8kg.
Kết quả là Đạt, tức là lượng cam đẩy ra đã nhiều hơn thế nữa 8kg
4. Một số để ý khi thực hiệncách phối hợp hàm index với match trong excel
Khi áp dụng hàm Index Match, ta cần lưu ý một số điểm sau:
- Hàm Index Match rất có thể tìm kiếm quý giá theo chiều ngang hoặc chiều dọc, tùy nằm trong vào cách chọn mảng hoặc phạm vi ô. Nếu lựa chọn 1 hàng hoặc một cột, hàm đã tìm tìm theo chiều ngang hoặc theo hướng dọc tương ứng. Nếu lựa chọn 1 mảng hai chiều, hàm đã tìm tìm theo cả hai chiều.
- Hàm Index Match có thể tìm kiếm quý hiếm trong ngẫu nhiên vị trí nào của mảng hoặc phạm vi ô, không duy nhất thiết cần là cột thứ nhất hoặc số 1 tiên. Tuy nhiên, bạn cần chắc hẳn rằng rằng giá chỉ trị đề nghị tìm tìm là tốt nhất trong mảng hoặc phạm vi ô, nếu không hàm vẫn trả về vị trí của giá chỉ trị đầu tiên được tra cứu thấy.
- Hàm Index Match rất có thể tìm kiếm giá trị theo khá nhiều điều kiện khác nhau, bằng cách kết hợp với các hàm khác như AND, OR, IF. Mặc dù nhiên, chúng ta cần chăm chú đến lắp thêm tự ưu tiên của các toán tử với hàm khi viết công thức, để đảm bảo kết quả bao gồm xác.
Một số để ý khi sử dụng hàm Index cùng Match
5. Một trong những lỗi thường chạm chán khi dùng hàm Index Match
Khi áp dụng hàm Index Match, ta có thể gặp phải một trong những lỗi sau:
5.1. Lỗi #n/a trong hàm index
Lỗi #NA xuất hiện thêm khi hàm không tìm thấy giá bán trị buộc phải tìm tìm trong mảng hoặc phạm vi ô. Để hạn chế và khắc phục lỗi này, ta bắt buộc kiểm tra lại xem giá trị nên tìm kiếm có tồn trên trong mảng hoặc phạm vi ô hay không và xem tất cả bị sai chủ yếu tả giỏi không. Quanh đó ra, ta cũng cần được xem lại đẳng cấp khớp cực hiếm có tương xứng hay không, ví dụ nếu cần sử dụng kiểu khớp 1 hoặc -1, ta cần bố trí mảng hoặc phạm vi ô theo trang bị tự tăng ngày một nhiều hoặc sút dần.
Lỗi #NA xuất hiện khi hàm không kiếm thấy giá chỉ trị buộc phải tìm kiếm trong mảng hoặc phạm vi ô
5.2. Lỗi #VALUE
Lỗi #VALUE mở ra khi hàm bao gồm tham số không phù hợp lệ. Để khắc phục và hạn chế lỗi này, ta đề xuất kiểm tra lại xem những tham số của hàm tất cả đúng kiểu tài liệu hay không, ví dụ nếu cần sử dụng số thực thay do số nguyên hoặc cần sử dụng văn bạn dạng thay bởi vì số. Ko kể ra, ta cũng cần phải xem lại xem những mảng hoặc phạm vi ô có cùng size hay không, ví dụ như nếu dùng một hàng và một cột có con số ô khác nhau.
Tổng kết
Hàm Index Match là một trong kỹ thuật có lợi để tra cứu kiếm và lấy dữ liệu từ 1 bảng tính theo đk cho trước. Hàm Index Match có khá nhiều ưu điểm so với hàm Vlookup cùng Hlookup, như không dựa vào vào địa điểm của cột hoặc hàng, không bị tác động bởi câu hỏi thêm hoặc xóa cột hoặc hàng, và có thể tìm kiếm giá trị theo không ít điều kiện. Mặc dù nhiên, khi áp dụng hàm Index Match, chúng ta cần để ý một số điểm như giải pháp chọn mảng hoặc phạm vi ô, cách sắp xếp mảng hoặc phạm vi ô, cách viết công thức, và giải pháp khắc phục các lỗi thường gặp. Hy vọng bài viết này đã giúp đỡ bạn hiểu rõ hơn về cách phối hợp hàm index và match vào excel để dò nhiều điều kiện.Chúc các bạn thành công!
Hàm Index Match là gì?Hàm Match
Hàm Index
Cách phối hợp hàm Index cùng Match vào Excel
Cách phối hợp hàm Index với Match các điều kiện
Cách phối hợp hàm Index Match cùng Vlookup
Hướng dẫn sử dụng hàm Index Match phối kết hợp If
Những sai trái thường gặp khi thực hiện hàm Index Match vào Excel
Tại sao đề nghị ưu tiên hàm Index Match thay do Vlookup cùng Hlookup?
Điểm chốt
Hàm Index Match là sự phối hợp linh hoạt giữa INDEX với MATCH vào Excel, mang đến nhiều lợi ích quan trọng. Mặc mặc dù là hai hàm khác nhau, mà lại khi áp dụng chung, chúng tạo thành cách tác dụng để kiếm tìm kiếm dữ liệu. Đọc tiếp nhằm hiểu chi tiết về cách áp dụng hàm này.
Hàm Index Match là gì?
Hàm Index Match là sự phối hợp thông minh thân hai hàm INDEX cùng MATCH để tiến hành việc kiếm tìm kiếm dữ liệu một cách bao gồm xác. Hãy tìm hiểu chi tiết về cách ứng dụng này:
Hàm Match
Hàm Match được thực hiện để xác xác định trí của một quý hiếm trong một phạm vi ô. Nó trả về vị trí tương đối của giá trị đó vào phạm vi. Công thức 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á chỉ 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á trị logic hoặc tham chiếu mang đến một ô cất 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 kiếm. Loại_tìm_kiếm: Là nhiều loại tìm kiếm đang rất được thực hiện.
Hàm Index
Hàm Index trả về cực hiếm của ô bên trong một mảng, được xác định bởi số hàng và số cột. Công thức cú pháp:
=INDEX(Mảng,Hàng_số,
Trong đó:
Mảng: Là khoanh vùng ô hoặc hàng số mảng đang được tìm kiếm. Hàng_số: lựa chọn hàng trong mảng để trả về giá chỉ trị. Cột_số: chọn cột trong mảng để trả về giá chỉ trị.Công thức hàm INDEX tham chiếu bao gồm dạng như sau:
=INDEX(Tham_chiếu,Hàng_số,
Trong đó:
Tham_chiếu: Là khu vực tham chiếu đề nghị được xét đến. Hàng_số: Là chỉ số mặt hàng tìm kiếm từ kia trả về một tham chiếu. Cột_số: Là chỉ số cột search 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ề quý giá trong Tham_chiếu. Nếu Khu_vực_số được bỏ lỡ thì hàm Index sẽ cần sử dụng vùng 1.Cách phối hợp hàm Index và Match trong Excel
Để rất có thể sử dụng hàm Index Match trong Excel, chúng ta cũng có thể thực hiện tại theo ví dụ sau để nắm rõ hơn:
Ví dụ: áp dụng hàm Index cùng hàm Match vào Excel để tìm showroom theo thương hiệu trong bảng dữ liệu. Quá trình làm:
Bước 1: trong bảng tài liệu Excel, nhập cách làm =INDEX(C3:C8,MATCH(C10,B3:B8,0)). Bí quyết 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ả.
Bước 2: sau khoản thời gian nhập công thức, nhấn Enter để 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 nhằm tìm kết quả dữ liệu.
Cách kết hợp hàm Index với Match các đ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, dưới đó là ví dụ minh họa cụ thể:
Ví dụ: sử dụng hàm Index và hàm Match nhằm tính đơn giá thành phầm và hãng sản xuất. Trong công thức, hãy thực hiện $ để cố định giá trị những phạm vi. Mang sử theo solo giá sản phẩm và nhà phân phối trong bảng 2 (B15:E18), bạn phải điền đối kháng giá vào bảng 1 (B3:D12).
Dùng kết hợp 2 hàm Index cùng Match nhằm dò tìm giá trị mang đến ô D4 như sau:
Bước 1: sử dụng hàm Match để xác định vị trí hàng khớp ứng với thành phầm CDRom vào 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,” khớp ứng với sản phẩm số 4 trong bảng 2.
Bước 2: Tiếp theo, sử dụng hàm Match để xác xác định trí cột tương ứng với đơn vị phân phối Samsung. Phạm vi xét mang đến là dữ liệu trong tầm B15:E15. Công thức nhập vào là:
=MATCH(C4,$B$15:$E$15,0). Hiệu quả là quý hiếm “2,” tương xứng với cột số 2 trong bảng 2.
Bước 3: Tiếp theo, sử dụng hàm Index kết phù hợp với 2 hàm Match vẫn tính ngơi nghỉ trên để tìm giá trị theo hàng cùng 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))
Kết quả trả về giá bán 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 bí quyết vừa tính cho những ô khác để chấm dứt bảng dữ liệu cần xét.
Cách phối kết hợp hàm Index Match và Vlookup
Để phối kết hợp hàm Index Match Vlookup, thực hiện công thức như sau:
=VLOOKUP(INDEX(tham_chiếu;
Trong đó:
INDEX(tham_chiếu;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 khu vực.
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 cất giá trị đề nghị tìm kiếm. 2: địa chỉ cột đến điểm vùng. 0: hình dạng tìm kiếm tương đối.
Hướng dẫn thực hiện hàm Index Match phối hợp If
Để sử dụng phối kết hợp hàm Index Match và hàm If, chúng ta cũng có thể sử dụng phương pháp 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á bán trị phải tìm kiế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ỉ có thể là một sản phẩm 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 tác dụng khớp chủ yếu xác, 1 là giá trị khoác định, -1 là search kiếm dải ô được thu xếp theo sản phẩm công nghệ tự giảm dần. Số_cột: Số cột của dữ liệu trả về. Giá_trị_nếu_đúng: giá trị trả về nếu điều kiện đúng. Giá_trị_nếu_sai: giá trị trả về nếu điều kiện sai.Ví dụ minh họa:
Bảng tài liệu để xác định sản phẩm bánh quy đã buôn bán đạt KPI tuyệt không. Để đạt KPI, số lượng bánh xuất kho phải to hơn 10 sản phẩm.
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 ô đựng 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 thành phầm hay không. Kết quả: “Đạt” nếu phệ hơn, “Không đạt” nếu như không.
Những sai trái thường chạm chán khi sử dụng hàm Index Match vào Excel
Khi phối kết hợp hàm Index và hàm Match, chúng ta có thể gặp phải những lỗi như sau:
Lỗi #REF
Lỗi #REF thường mở ra 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 kiếm thấy dữ liệu đã cho, hàm Index với Match trả về #REF. Để hạn chế lỗi #REF, chuyển đổi phạm vi xét đến của hàm Index.
Lỗi #N/A
Lỗi #N/A xuất hiện khi không tìm kiếm thấy quý hiếm trong bảng dữ liệu, y như lỗi thường chạm mặt ở hàm Vlookup.
Tại sao nên ưu tiên hàm Index Match thay vì chưng Vlookup cùng Hlookup?
Kết thích hợp hàm Index và hàm Match đem đến nhiều tác dụng hơn:
Hàm Vlookup với Hlookup chỉ tra cứu tài liệu theo một chiều, khó sử dụng cho dữ liệu 2 chiều. Thực hiện Vlookup và Hlookup dễ gây sai sót lúc thêm/bớt cột vào bảng tính.Điểm chốt
Hy vọng tin tức đã giúp đỡ bạn hiểu rõ hàm Index Match cùng cách phối hợp 2 hàm này. Nếu có thắc mắc hoặc muốn cập nhật thêm thông tin, gạnh thăm cachseo.com ngay nhé.
