Hàm Index với Match là nhị hàm rất đặc biệt trong Excel mà lại hoàn toàn có thể thay thế được mang đến hàm VLOOKUP. Để nắm rõ hơn bí quyết sử dụng cũng giống như các áp dụng của nhị hàm này, chúng ta sẽ cùng rèn luyện cách áp dụng hàm INDEX với MATCH trải qua các bài bác tập kèm theo giải thuật nhé.

Bạn đang xem: Hàm match và index trong excel


Bài tập 1: sử dụng hàm MATCH tìm địa chỉ theo dòng, cột

Cho bảng tính sau:
*
Nếu mong muốn biết tin tức của người mang tên “Lê Thị Thủy” thì các bạn phai làm cụ nào? có lẽ rằng việc đầu tiên bạn phải xác định coi người đó nằm ở vị trí thứ mấy trong bảng tính này.Bằng cách thực hiện hàm MATCH, bạn sẽ biết được 1 người nào đó nằm ở dòng thứ mấy vào bảng.Ta có công thức sau:=MATCH(“Lê Thị Thủy”, cột họ tên, số 0)

*
Tại ô D12 nhập tên Lê Thị Thủy cột họ tên là vùng C3:C8Như vậy hàm MATCH cho kết quả là số 3, tương ứng với cái thứ 3 trong bảng tài liệu trên.

Tham khảo: Cách áp dụng hàm MATCH trong Excel

Vậy việc tìm ra số 3 thì có ý nghĩa gì? Hay có thể nói rằng biết người đó ở vị trí mẫu thứ 3 trong bảng thì có công dụng gì?Để trả lời thắc mắc này, bọn họ xem tiếp yêu cầu dưới đây

Bài tập 2: Kết thích hợp hàm INDEX cùng với MATCH tìm thư điện tử theo tên

Khi quan sát vào bảng trên, ta thấy tại vị trí cái 3, vào cột e-mail sẽ lộ diện Email buộc phải tìm. Như vậy rất có thể hiểu: chỉ cần dóng theo cột Email, trên vị trí dòng 3 trong bảng là họ có hiệu quả cần tìm.Điều đó chính là cách phối kết hợp hàm INDEX với MATCH:
*
Trong phương pháp tại ô D15 kiếm tìm Email, chúng ta thấy:

=INDEX(E3:E8,MATCH(D12,C3:C8,0))

Công thức này, hàm INDEX gồm nhiệm vụ: đã cho thấy cột cất giá trị yêu cầu tìm là cột E, vùng E3:E8 Chỉ ra dòng chứa giá trị nên tìm, chiếc này đã xác định vày hàm MATCH ở trên
Giao điểm giữa dòng và cột trong hàm INDEX tìm đó là kết quả e-mail cần tìm.

Tham khảo: 6 tính năng công dụng nhất của hàm INDEX vào Excel

Bài tập 3: Dò search từ đề xuất qua trái cùng với hàm INDEX phối hợp MATCH

Thông thường bọn họ hay dò tìm kiếm theo chiều trường đoản cú Trái qua Phải. Tuy nhiên nếu yêu cầu tìm từ yêu cầu qua Trái (ngược lại cùng với thông thường) thì làm nỗ lực nào?
Ví dụ như sau: search tên dựa vào thông tin Email?
*
Cột thư điện tử là cột máy 3, nằm cạnh sát phải cột bọn họ tên (cột thứ 1).Như vậy để dò tìm họ tên phụ thuộc vào Email có nghĩa là chúng ta đang search từ bắt buộc qua trái.Quá trình thực hiện như sau: sử dụng hàm MATCH để tìm vị trí dòng của email trong cột e-mail =MATCH(Email, vùng đựng Email, 0) Kết phù hợp với hàm INDEX trong bài toán chỉ ra địa điểm cột chứa công dụng cần tìm, là cột chúng ta tên (cột C), vùng C3:C8

=INDEX(C3:C8,MATCH(D12,E3:E8,0))

Rất đơn giản đề xuất không nào.

Bài tập 4: Xác định đơn giá của mặt hàng theo nhiều điều kiện

Ví dụ ta có bảng đơn giá chỉ như sau:
*
Làm giải pháp nào xác định được đơn giá của 1 mã sản phẩm bất kỳ, vào 1 doanh nghiệp bất kỳ?Điều này tương đương việc bọn họ phải xét đồng thời 2 điều kiện: Mã hàng với Mã công tyĐể tiến hành yêu cầu này, chúng ta sẽ sử dụng hàm Index phối kết hợp hàm Match như sau:Bước 1: Xác định vị trí Mã sản phẩm nằm trên cột máy mấy trong bảng
*
Tham chiếu quý giá Mã hàng trong loại 3 (dòng chứa thông tin mã hàng có tác dụng căn cứ đối chiếu) Hàm MATCH trả về tác dụng là 2, tương ứng cùng với cột thứ 2

=MATCH(H2,A3:E3,0)

Bước 2: Xác định vị trí Mã công ty nằm trên dòng thứ mấy vào bảng
*
Tham chiếu cực hiếm Mã công ty trong cột A (cột chứa thông tin mã doanh nghiệp làm căn cứ đối chiếu) Hàm MATCH trả về công dụng là 3, tương ứng với mẫu thứ 3

=MATCH(H3,A3:A7,0)



Như vậy giao điểm của loại 3, cột 2 thiết yếu là ô B5 (bởi bảng bắt đầu tính từ loại 3 trở đi, đề xuất dòng 5 ứng với vị trí loại thứ 3 của bảng solo giá)Bước 3: Xác định kết quả đơn giá cần tìm cùng với hàm INDEXViệc sót lại là dùng hàm INDEX để kết nối giữa mẫu và Cột đã xác định được ở trên.
*
Trong hàm INDEX này ta có: Thay vì chưng tham chiếu tới 1 cột cố định trên tham số đầu tiên, chúng ta tham chiếu cho tất cả vùng bảng A3:E7. Trong bảng này, số chiếc xác định vị hàm MATCH tại ô I3, số cột xác định vày hàm MATCH tại ô I2. Tác dụng của hàm INDEX là giao điểm giữa số mẫu và số cột đã xác định được.

=INDEX(A3:E7,I3,I2)

Kết luận

Như vậy trải qua 4 bài tập ví dụ, chúng ta đã hoàn toàn có thể hình dung được cách áp dụng hàm INDEX phối kết hợp hàm MATCH để thực hiện các yêu cầu dò tìm, tham chiếu, kiếm tìm kiếm trong Excel. Khi ứng dụng xuất sắc cách làm cho này, bạn hoàn toàn có thể thực hiện được hầu như yêu cầu tìm kiếm tinh vi mà hàm VLOOKUP khó đáp ứng được.Thay vì phải đắn đo sử dụng hàm VLOOKUP tốt HLOOKUP, làm công thức dài chiếc khi phải phối hợp VLOOKUP với hàm MATCH, HLOOKUP cùng với hàm MATCH, thì chúng ta chỉ việc dùng INDEX kết hợp MATCH là xong.Ngoài ra chúng ta có thể đọc thêm 1 số nội dung bài viết thú vị khác:

Vì sao cần sử dụng INDEX với MATCH tốt hơn dùng VLOOKUP vào Excel

Excel mang lại cdvhnghean.edu.vn 365 Excel đến cdvhnghean.edu.vn 365 d&#x
E0;nh cho m&#x
E1;y Mac Excel đến web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel năm 2016 Excel năm 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Excel Starter 2010 xem th&#x
EA;m...&#x
CD;t hơn
*

Có hai phương pháp để sử dụng hàm INDEX:

Dạng mảng

Mô tả

Trả về quý hiếm của một thành phần nằm trong bảng hoặc mảng, được chọn bởi vì chỉ mục số hàng với cột.

Dùng dạng mảng nếu đối số thứ nhất của hàm INDEX là một hằng số mảng.

Cú pháp

INDEX(array, row_num, )

Dạng mảng của hàm INDEX có các đối số sau đây:

Mảng Bắt buộc. Một phạm vi ô hoặc một hằng số mảng.

Nếu mảng chỉ cất một hàng hoặc cột thì đối số row_num hoặc đối column_num tương xứng là tùy chọn.

Nếu mảng có rất nhiều hàng và những cột với chỉ rất có thể dùng row_num hoặc column_num, thì hàm INDEX trả về mảng chứa tổng thể hàng hoặc cột vào mảng.

row_num Bắt buộc, trừ lúc column_num bao gồm mặt. Chọn hàng trong mảng mà từ đó trả về một giá trị. Nếu row_num được vứt qua, column_num bắt buộc.

column_num Tùy chọn. Chọn cột vào mảng mà từ đó trả về một giá trị. Trường hợp column_num được vứt qua, các bạn row_num bắt buộc.

Chú thích

Nếu cả nhị row_num cùng column_num được sử dụng, hàm INDEX trả về giá trị trong ô nghỉ ngơi giao điểm của row_num và column_num.

row_num cùng column_num yêu cầu trỏ tới một ô vào mảng; trường hợp không, hàm INDEX trả về giá bán #REF! lỗi.

Ví dụ

Ví dụ 1

Những ví dụ này dùng hàm INDEX để tìm giá trị trong ô giao cắt giữa hàng và cột.

Sao chép tài liệu ví dụ trong bảng sau đây và ốp lại ô A1 của một bảng tính Excel mới. Để cách làm hiển thị kết quả, hãy chọn công thức, dấn F2 rồi thừa nhận Enter.

Dữ liệu

Dữ liệu

Táo

Chanh

Chuối

Công thức

Mô tả

Kết quả

=INDEX(A2:B3,2,2)

Giá trị trên giao điểm của hàng 2 cùng cột 2 trong vòng A2:B3.

=INDEX(A2:B3,2,1)

Giá trị trên giao điểm của mặt hàng 2 cùng cột 1 trong những khoảng A2:B3.

Chuối

Ví dụ 2

Ví dụ này dùng hàm INDEX trong một công thức mảng để tìm các giá trị trong hai ô được chỉ rõ vào mảng 2x2.


Lưu ý: Nếu chúng ta có phiên bản hiện trên của cdvhnghean.edu.vn 365 thì bạn cũng có thể nhập bí quyết vào ô bên trên cùng phía bên trái của phạm vi đầu ra, rồi nhận ENTER để xác thực công thức kia là phương pháp mảng động. Ví như không, công thức đề xuất được nhập bên dưới dạng cách làm mảng thừa tự bằng phương pháp trước tiên lựa chọn hai ô trống, nhập cách làm vào ô bên trên cùng phía trái của dải ô đầu ra, rồi thừa nhận CTRL+SHIFT+ENTER nhằm xác nhận. Excel chèn lốt ngoặc nhọn sinh hoạt đầu với cuối phương pháp giúp bạn. Để biết thêm tin tức về phương pháp mảng, hãy coi mục lý giải và ví dụ về công thức mảng.


Công thức

Mô tả

Kết quả

=INDEX(1,2;3,4,0,2)

Giá trị được search thấy trong mặt hàng 1, cột 2 trong mảng. Mảng đựng 1 cùng 2 trong mặt hàng 1 và 3 với 4 trong sản phẩm 2.

2

Giá trị được search thấy trong hàng 2, cột 2 vào mảng (mảng y như bên trên).

4

Đầu trang

Biểu mẫu mã tham chiếu

Mô tả

Trả về tham chiếu của ô nằm ở giao cắt của một hàng và cột cụ thể. Trường hợp tham chiếu được chế tạo thành từ các vùng chọn không tức khắc kề, chúng ta có thể chọn vùng lựa chọn để search trong đó.

Cú pháp

INDEX(tham chiếu, số_hàng, , )

Dạng tham chiếu của hàm INDEX có các đối số sau đây:

Tham khảo Bắt buộc. Tham chiếu tới một hoặc nhiều phạm vi ô.

Nếu bạn nhập một phạm vi không gần kề cho tham chiếu, hãy để tham chiếu trong dấu ngoặc đơn.

Nếu từng vùng vào tham chiếu chỉ chứa một mặt hàng hoặc cột thì tham đối row_num hoặc column_num tương ứng, là tùy chọn. Ví dụ, đối với tham chiếu chỉ có một hàng, hãy dùng INDEX (tham chiếu,,số_hàng).

row_num Bắt buộc. Số hàng trong tham chiếu từ đó trả về một tham chiếu.

column_num Tùy chọn. Số cột trong tham chiếu từ đó trả về một tham chiếu.

area_num ko bắt buộc. Lựa chọn một phạm vi trong tham chiếu nhưng từ kia trả về giao điểm của row_num với column_num. Khu vực đầu tiên được chọn hoặc nhập được đánh số 1, khu vực thứ nhì là 2, và v.v.. Ví như area_num bỏ qua, hàm INDEX cần sử dụng vùng 1. Các quanh vùng liệt kê tại đây đều đề nghị được để trên một trang tính. Nếu như khách hàng chỉ định khu vực không trên cùng trang tính với nhau, thì nó trả về lỗi #VALUE! . Nếu như bạn cần sử dụng các phạm vi nằm trên các trang tính khác nhau, bạn nên thực hiện dạng mảng của hàm INDEX và thực hiện một hàm khác để tính toán phạm vi chế tạo ra thành mảng. Ví dụ: chúng ta cũng có thể sử dụng hàm CHOOSE để đo lường phạm vi sẽ được sử dụng.

Ví dụ, giả dụ Reference tế bào tả những ô (A1:B4,D1:E4,G1:H4), area_num 1 là phạm vi A1:B4, area_num 2 là phạm vi D1:E4 và area_num 3 là phạm vi G1:H4.

Chú thích

Sau khi tham chiếu cùng area_num đã lựa chọn 1 phạm vi thay thể, row_num và column_num lựa chọn một ô nuốm thể: row_num 1 là hàng trước tiên trong phạm vi, column_num một là cột đầu tiên, v.v. Tham chiếu mà lại hàm INDEX trả về là giao điểm của row_num với column_num.

Nếu các bạn đặt row_num hoặc column_num thành 0 (không), hàm INDEX trả về tham chiếu cho toàn thể cột hoặc mặt hàng tương ứng.

row_num, column_num, cùng area_num buộc phải trỏ tới một ô vào tham chiếu; giả dụ không, hàm INDEX trả về giá chỉ #REF! lỗi. Nếu quăng quật row_num và column_num, hàm INDEX trả về vùng vào tham chiếu được xác định bởi hàm area_num.

Kết quả của hàm INDEX là một tham chiếu và nó được các công thức khác hiểu như vậy. Tùy thuộc vào công thức, giá trị mà hàm INDEX trả về có thể được dùng như một tham chiếu hoặc một giá trị. Ví dụ, công thức CELL("độ rộng",INDEX(A1:B2,1,2)) tương tự với CELL("độ rộng",B1). Hàm CELL sử dụng giá trị mà hàm INDEX trả về làm tham chiếu ô. Mặt khác, một công thức như 2*INDEX(A1:B2,1,2) chuyển giá trị mà hàm INDEX trả về thành số vào ô B1.

Ví dụ

Sao chép dữ liệu ví dụ vào bảng sau đây và ốp lại ô A1 của một bảng tính Excel mới. Để những công thức hiển thị kết quả, hãy lựa chọn chúng, nhấn F2 và tiếp nối nhấn Enter.

Hoa quả

Giá

Tổng số

Táo

0,69 $

40

Chuối

0,34 $

38

Chanh

0,55 $

15

Cam

0,25 $

25

0,59 $

40

Hạnh nhân

2,80 $

10

Hạt điều

3,55 $

16

Đậu phộng

1,25 $

20

Óc chó

1,75 $

12

Công thức

Mô tả

Kết quả

=INDEX(A2:C6, 2, 3)

Giao điểm của mặt hàng 2 và cột 3 trong vòng A2:C6, là phần ngôn từ của ô C3.

38

=INDEX((A1:C6, A8:C11), 2, 2, 2)

Giao điểm của sản phẩm 2 với cột 2 vào vùng thứ hai của A8:C11, chính là nội dung của ô B9.

1,25

=SUM(INDEX(A1:C11, 0, 3, 1))

Tổng của cột 3 trong vùng trước tiên thuộc khoảng chừng A1:C11, đó là tổng của C1:C11.

Xem thêm: Cách Tra Cứu Hóa Đơn Điện Tử Internet Viettel Chính Xác, Tra Cứu Hóa Đơn

216

=SUM(B2:INDEX(A2:C6, 5, 2))

Tổng của khoảng bắt đầu ở B2 và dứt tại giao điểm của mặt hàng 5 và cột 2 của khoảng chừng A2:A6, đó là tổng của B2:B6.