Hàm Index và Match là hai hàm rất đặc biệt trong Excel mà lại hoàn toàn rất có thể thay thế được cho hàm VLOOKUP. Để nắm vững hơn giải pháp sử dụng cũng như các ứng dụng của nhì hàm này, chúng ta đã cùng luyện tập cách thực hiện hàm INDEX cùng MATCH thông qua các bài xích tập kèm theo giải thuật nhé.

Bạn đang xem: Hướng dẫn sử dụng hàm index và match trong excel


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

Cho bảng tính sau:
*
Nếu mong mỏi biết tin tức của người mang tên “Lê Thị Thủy” thì chúng ta phai làm ráng nào? chắc hẳn việc đầu tiên bạn phải xác định xem người đó nằm ở địa chỉ 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 bao gồm công thức sau:=MATCH(“Lê Thị Thủy”, cột chúng ta tên, số 0)

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

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

Vậy việc tìm và đào bới ra số 3 thì có ý nghĩa gì? Hay nói cách khác biết người đó ở vị trí cái thứ 3 vào bảng thì có chức năng gì?
Để trả lời câu hỏi này, bọn họ xem tiếp yêu mong dưới đây

Bài tập 2: Kết phù hợp hàm INDEX cùng với MATCH tìm email theo tên

Khi quan sát vào bảng trên, ta thấy trên vị trí mẫu 3, trong cột thư điện tử sẽ lộ diện Email cần tìm. Như vậy hoàn toàn có thể hiểu: chỉ cần dóng theo cột Email, tại vị trí chiếc 3 vào bảng là họ có công dụng cần tìm.Điều đó đó là cách phối kết hợp hàm INDEX cùng với MATCH:
*
Trong công thức 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 có nhiệm vụ: chỉ ra rằng cột cất giá trị nên tìm là cột E, vùng E3:E8 Chỉ ra loại chứa giá trị yêu cầu tìm, cái này đã xác định do hàm MATCH ở trên
Giao điểm giữa dòng và cột vào hàm INDEX tìm đó là kết quả e-mail cần tìm.

Tham khảo: 6 tính năng hiệu quả nhất của hàm INDEX trong Excel

Bài tập 3: Dò tìm kiếm từ buộc phải qua trái cùng với hàm INDEX kết 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. Nhưng mà nếu nên tìm từ bắt buộc qua Trái (ngược lại cùng với thông thường) thì làm thế nào?
Ví dụ như sau: tìm tên phụ thuộc thông tin Email?
*
Cột email là cột lắp thêm 3, nằm cạnh sát phải cột chúng ta tên (cột vật dụng 1).Như vậy để dò tìm chúng ta tên dựa vào Email tức là chúng ta đang tra cứu từ nên qua trái.Quá trình tiến hành như sau: sử dụng hàm MATCH để tìm vị trí mẫu của email trong cột email =MATCH(Email, vùng đựng Email, 0) Kết phù hợp với hàm INDEX trong vấn đề chỉ ra địa chỉ cột chứa tác dụng cần tìm, là cột họ tên (cột C), vùng C3:C8

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

Rất đơn giản cần không nào.

Bài tập 4: Xác định đơn giá chỉ của sản phẩm theo nhiều điều kiện

Ví dụ ta có bảng đơn giá bán như sau:
*
Làm biện pháp nào xác định được đơn giá của 1 mã sản phẩm bất kỳ, vào 1 công ty 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
Để triển khai yêu cầu này, bọn họ sẽ sử dụng hàm Index kết hợp hàm Match như sau:Bước 1: Xác định địa điểm Mã mặt hàng nằm trên cột đồ vật mấy vào bảng
*
Tham chiếu quý hiếm Mã sản phẩm trong mẫu 3 (dòng chứa tin tức mã hàng làm 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 sản phẩm công nghệ 2

=MATCH(H2,A3:E3,0)

Bước 2: Xác định vị trí Mã công ty nằm trên mẫu thứ mấy trong bảng
*
Tham chiếu quý giá Mã doanh nghiệp 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ề hiệu quả 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 bao gồm là ô B5 (bởi bảng bắt đầu tính từ dòng 3 trở đi, yêu cầu dòng 5 ứng cùng với vị trí loại thứ 3 của bảng đối chọi giá)Bước 3: Xác định kết quả đơn giá đề xuất tìm với hàm INDEXViệc còn lại là dùng hàm INDEX để kết nối giữa cái và Cột đã xác định được ở trên.
*
Trong hàm INDEX này ta có: Thay vày tham chiếu tới 1 cột cố định tại tham số đầu tiên, họ tham chiếu cho cả vùng bảng A3:E7. Vào bảng này, số mẫu xác định bởi vì hàm MATCH tại ô I3, số cột xác định bởi hàm MATCH tại ô I2. Công dụng của hàm INDEX là giao điểm thân số chiếc và số cột đã xác định được.

=INDEX(A3:E7,I3,I2)

Kết luận

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

Vì sao sử dụng INDEX cùng MATCH giỏi hơn dùng VLOOKUP vào Excel

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


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

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

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

Mặc mặc dù hàm INDEX rất có thể được sử dụng riêng lẻ, tuy nhiên khi kết phù hợp với hàm MATCH vào công thức tạo cho nó hữu ích hơn. Hàm MATCH search kiếm một mục được hướng đẫn trong một phạm vi ô và tiếp đến trả về vị trí tương đối của mục kia trong phạm vi. Ví dụ, nó có thể được áp dụng để xác minh 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 đó 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à bí quyết sẽ sử dụng. Nó có thể là một hoặc các hàng cùng cột. row_num (đối số bắt buộc): Là sản phẩm trong mảng nhưng mà từ đó trả về một giá bán trị. column_num (đối số tùy chọn): Là cột vào mảng nhưng mà từ đó 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 vào lookup_array. Nó rất có thể là một số, văn bạn dạng hoặc giá trị xúc tích và ngắn gọn được nhập theo cách thủ công bằng tay hoặc được tham chiếu qua tham chiếu ô. Lookup_array (đối số bắt buộc): Là phạm vi ô phải xem qua. Nó có thể là một sản phẩm hoặc một cột. match_type có thể là -1, 0 hoặc 1. Nó chỉ định bí quyết lookup_value được so khớp với những giá trị vào lookup_array. Một là giá trị mang định giả dụ đối số này bị quăng quật qua. Thực hiện 1 hoặc -1 mang lại những thời gian bạn cần chạy tra cứu vãn gần vừa lòng thang điểm, như khi xử lý các con số với khi tính gần và đúng là ổn. Cơ mà hãy nhớ rằng nếu như bạn không chỉ định match_type, 1 đang là mặc định, điều này rất có thể làm sai lệch kết quả nếu bạn thực sự muốn một tác dụng khớp bao gồm xác.

Các ví dụ về hàm INDEX và MATCH

Trước lúc chứng kiến tận mắt xét cách phối 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 biện pháp mà mỗi hàm này sẽ thao tác làm việc trong Excel.

Ví dụ về hàm INDEX


*

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

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

Tìm tham chiếu ô trong bảng

*

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

MATCH(F1;B2:B5): Hàm MATCH sẽ tìm kiếm giá trị F1 (8795) trong vùng tài liệu B2:B5. Như bọn họ thấy trong bảng, địa điểm của cực 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ì họ đang search kiếm quý hiếm trong cột đó. Hàm INDEX hiện nay có thể được viết lại thành INDEX (C2:C5;2;) vì 2 là hàm MATCH vẫn tìm ra. Column_num là tùy chọn, chúng ta có thể loại dồn phần đó và cách làm rút gọn lại thành công thức INDEX (C2: C5;2).

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

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

*

Trong lấy ví dụ này về MATCH cùng INDEX, chúng ta sẽ triển khai tra cứu giúp hai chiều. Mục tiêu là để khám nghiệm 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ư ví dụ trên nhưng là một công thức MATCH bổ sung được lồng vào INDEX.

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

Xem thêm:

Có nghĩa là hàm INDEX đang tìm kiếm trong toàn bộ bảng B2:E13 cho hàng trang bị sáu với cột thứ tía để trả về công dụng là 41 như trong bảng trên.

Lưu ý khi thực hiện hàm MATCH với INDEX

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