Cách kết hợp giữa hàm INDEX và MATCH

INDEX và MATCH là các hàm tra cứu trong Excel. Mặc dù chúng là hai hàm hoàn toàn riêng biệt có thể được sử dụng riêng nhưng 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 giá trị hoặc tham chiếu đến một giá trị từ bên trong một lựa chọn cụ thể. Ví dụ nó có thể được sử dụng để tìm giá trị trong hàng thứ hai của tập dữ liệu hoặc trong hàng thứ năm và cột thứ ba.

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

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

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

=INDEX[array, row_num, [column_num]]

Trong đó:

• Array [đối số bắt buộc]: Là phạm vi ô mà công thức sẽ sử dụng. Nó có thể là một hoặc nhiều hàng và cột.
• row_num [đối số bắt buộc]: Là hàng trong mảng mà từ đó trả về một giá trị.
• column_num [đối số tùy chọn]: Là cột trong mảng mà từ đó trả về một giá trị.

=MATCH[lookup_value, lookup_array, [match_type]]

Trong đó:

• Lookup_value [đối số bắt buộc]: Là giá trị bạn muốn so khớp trong lookup_array. Nó có thể là một số, văn bản hoặc giá trị logic được nhập theo cách thủ công hoặc được tham chiếu qua tham chiếu ô. 
• Lookup_array [đối số bắt buộc]: Là phạm vi ô cần xem qua. Nó có thể là một hàng hoặc một cột.
• match_type 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ị trong lookup_array. 1 là giá trị mặc định nếu đối số này bị bỏ qua.
• Sử dụng 1 hoặc -1 cho những thời điểm bạn cần chạy tra cứu gần đúng theo thang điểm, như khi xử lý các con số và khi tính gần đúng là ổn. Nhưng hãy nhớ rằng nếu bạn không chỉ định match_type, 1 sẽ là mặc định, điều này có thể làm sai lệch kết quả nếu bạn thực sự muốn một kết quả khớp chính xác.

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

Trước khi xem xét cách 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 cách mà mỗi hàm này sẽ làm việc trong Excel.

Ví dụ về hàm INDEX

=INDEX[A1:B4;2;2]=INDEX[A1:B1;1]=INDEX[2:2;1]

=INDEX[B1:B2;1]

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

• =INDEX[A1:B4;2;2]: Hàm sẽ kiểm tra mảng A1:B4 để tìm giá trị trong cột thứ hai và hàng thứ hai, đó là Vũ Hồng Ngọc.
• =INDEX[A1:B1;1]: Hàm sẽ kiểm tra mảng A1:B1 để tìm giá trị trong cột đầu tiên, đó là Lê Thanh Khương.
• =INDEX[2:2;1]: Hàm sẽ kiểm tra mọi thứ trong hàng thứ hai để tìm giá trị trong cột đầu tiên, đó là Nguyễn Gia Viễn.
• =INDEX[B1:B2;1]: Hàm sẽ kiểm tra mảng B1:B2 để tìm giá trị trong hàng đầu tiên, đó là Bùi Thanh Tuyên.

Ví dụ về hàm MATCH

Chúng ta có thể tìm hiểu về cách 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 về hàm MATCH:

=MATCH["Vũ Hồng Ngọc";A2:D2;0]=MATCH[16;D1:D3]=MATCH[16;D1:D3;-1]

=MATCH[13;A1:D1;0]

Trong đó: 

• =MATCH["Vũ Hồng Ngọc";A2:D2;0]: Hàm MATCH sẽ tìm kiế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 sẽ tìm kiếm 16 trong phạm vi D1:D3. Nhưng vì 16 không có trong phạm vi tìm kiếm nên MATCH sẽ tìm giá trị lớn nhất tiếp theo nhỏ hơn hoặc bằng 14, trong trường hợp này là 13 , nằm ở vị trí 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 chúng ta thấy thông báo lỗi #N/A.
• =MATCH[13;A1:D1;0]: Hàm MATCH sẽ tìm kiếm 13 trong hàng đầu 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 đây là hai ví dụ mà chúng ta có thể kết hợp INDEX và MATCH trong một công thức:

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

=INDEX[C2:C5;MATCH[F1;B2:B5]]

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

• MATCH[F1;B2:B5]: Hàm MATCH sẽ tìm kiếm giá trị F1 [8795] trong vùng dữ liệu B2:B5. Như chúng ta thấy trong bảng, vị trí của giá trị F1 trong vùng dữ liệu này là 2 và đó là kết quả mà hàm MATCH đã tìm ra.
• Mảng INDEX là C2:C5 vì chúng ta đang tìm kiếm giá trị trong cột đó.
• Hàm INDEX bây giờ có thể được viết lại thành INDEX [C2:C5;2;[column_num]] vì 2 là hàm MATCH đã tìm ra.
• Column_num là tùy chọn, chúng ta có thể loại bỏ phần đó và công thức rút gọn 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 đầu sẽ giống như một công thức với hàm INDEX bình thường và giá trị của mục thứ hai trong phạm vi C2:C5 là Bút nước Thiên Long.

Tra cứu theo tiêu đề hàng và cột

=INDEX[B2:E13;MATCH[G1;A2:A13;0];MATCH[G2;B1:E1;0]]

Trong ví dụ này về MATCH và INDEX, chúng ta sẽ thực hiện tra cứu hai chiều. Mục đích là để kiểm tra xem loại Bút tre đã bán được bao nhiêu chiếc trong tháng 5. Đó là cách tương tự như ví dụ trên nhưng là một công thức MATCH bổ sung được lồng trong INDEX.

• MATCH[G1;A2:A13;0]: Hàm MATCH sẽ tìm kiếm G1 [số 5] trong phạm vi A2:A13 để nhận một giá trị cụ thể. 
• MATCH[G2;B1:E1;0]: Là công thức MATCH thứ hai và giống với công thức đầu tiên nhưng thay vào đó là tìm kiếm G2 [từ "Bút tre"] trong tiêu đề cột từ B1:E1. Như trong hình đó là cột thứ 3.
• Cuối cùng công thức với hàm INDEX có thể được viết lại ngắn gọn hơn để chúng dễ hiểu là: =INDEX[B2:E13;6;3]. Có nghĩa là hàm INDEX sẽ tìm kiếm trong toàn bộ bảng B2:E13 cho hàng thứ sáu và cột thứ ba để trả về kết quả là 41 như trong bảng trên.

Lưu ý khi sử dụng hàm MATCH và INDEX

• MATCH không phân biệt chữ hoa và chữ thường, vì vậy chữ hoa và chữ thường được xử lý giống nhau khi khớp các giá trị văn bản.
• MATCH trả về lỗi #N/A vì nhiều lý do như: match_type là 0 và lookup_value không được tìm thấy; match_type là -1 và lookup_array không theo thứ tự giảm dần; match_type là 1 và lookup_array không theo thứ tự tăng dần và lookup_array không phải là một hàng hoặc một cột.
• Bạn có thể sử dụng một ký tự đại diện trong đối số lookup_value nếu match_type là 0 và lookup_value là một chuỗi văn bản. Dấu chấm hỏi khớp với bất kỳ ký tự đơn nào và dấu hoa thị sẽ khớp với bất kỳ chuỗi ký tự nào. 
• INDEX sẽ trả về lỗi #REF! nếu row_num và column_num không trỏ đến một ô trong mảng.

Xem thêm:

Với trường hợp để có thể lấy các giá trị ở các hàng và các cột và dễ dàng thực hiện hơn để thay thế cho hàm tìm kiếm Vlookup hay Hlookup thì  chúng ta nên kết hợp giữa hàm Index và Match. Hàm Vlookup hay hàm Hlookup trong Excel sẽ chỉ tìm giá trị theo hàng hoặc cột, theo 1 chiều mà không tìm kiếm giá trị trên cả hàng và cột và chúng thuộc nhóm hàm tìm kiếm cơ bản trong Excel. Trong Excel có rất nhiều lựa chọn khác nhau để chúng ta có thể tìm được giá trị mình cần trong bảng. Bài viết dưới đây của mình sẽ hướng dẫn một cách cụ thể, chi tiết cách kết hợp 2 hàm Index và Match trong Excel. Bạn có thể tham khảo bài viết của mình sau đây nhé.

Hàm Index trong Excel có 2 dạng đó là hàm Index dạng mảng và Index dạng tham chiếu.

Hàm Index dạng mảng có ý nghĩa là trả về giá trị của một ô dữ liệu có chỉ số hàng và chỉ số cột mà chúng ta đang tìm kiếm. 

Cú pháp, cấu trúc của hàm Index dạng mảng là 

Index [Array, Row_num,[column_num]]. 

Trong đó các tham số có ý nghĩa là:

• Array: Mảng dữ liệu tham chiếu.

• Row_num: Hàng chứa giá trị cần lấy.

• Column_num: Cột chứa giá trị cần lấy.

Hàm Index dạng tham chiếu có ý nghĩa là trả về giá trị của một ô có chỉ số hàng và cột đang tìm kiếm. 

Cú pháp, cấu trúc của hàm Index dạng tham chiếu là 

INDEX [Reference, Row_num, [Column_num], [Area_num]. 

Trong đó các tham số có ý nghĩa như sau: 

• Reference: Vùng tham chiếu chứa giá trị cần tìm.

• Row_num: Chỉ số hàng chứa giá trị cần tìm.

• Column_num: Chỉ số cột chứa giá trị cần tìm.

• Area_num: Số vùng trả về, để trống mặc định là 1.

2.Hàm match trong excel

Hàm Match trong Excel có ý nghĩa là trả về số thứ tự của giá trị cần tìm trong bảng. 

Cấu trúc, cú pháp của hàm Match trong Excel là

 =MATCH [Lookup_Value, Lookup_array, [Match_type]]. 

Trong đó các tham số có ý nghĩa như sau:

• Lookup_Value: Giá trị cần tìm kiếm.

• Lookup_array: Mảng chứa giá trị cần tìm.

• Match_type: Kiểu tìm kiếm.

•  Có 3 kiểu tìm kiếm:

  • Tìm kiếm giá trị nhỏ hơn giá trị cần tìm kiếm khi match_type =1.

  • Tìm kiếm giá trị bằng giá trị cần tìm kiếm khi match_type =0.

  • Tìm kiếm giá trị lớn hơn giá trị cần tìm kiếm khi match_type = -1.

Khi bỏ qua không nhập gì thì hàm MATCH mặc định là 1.

3.Hướng dẫn sử dụng kết hợp hàm match với hàm index

Như chúng ta đã tìm hiểu ở trên, hàm index trả về cho chúng ta giá trị tại ô tham chiếu, nhưng khi tiến hành sao chép công thức, kết quả trả về cho chúng ta khi truy xuất lại không chính xác vì khi dùng index để truy xuất dữ liệu, tham số về hàng và cột luôn được thay đổi khi sao chép công thức. Bởi vậy chúng ta cần kết hợp match trong index để truy xuất dữ liệu chính xác đến hàng/ cột cần tham chiếu

Công thức kết hợp hàm Index với hàm Match là 

=INDEX[ cột cần tra cứu giá trị, [MATCH [giá trị dùng để tra cứu, cột chứa giá trị, 0]].

4. Ví dụ minh họa về hàm index và match

Cho bảng dưới đây, hãy tìm kiếm khu vực của sản phẩm truyện tranh trong bảng

Theo bảng chúng ta thấy vị trí của truyện tranh nằm ở số thứ tự 4 và dóng sang sẽ thấy khu vực của sản phẩm truyện tranh là khu vực C. Như vậy chúng ta sẽ nhìn vào cột khu vực và tìm tới dòng số 5 sẽ ra được kết quả.

Bước 1:

Trước hết chúng ta sẽ tìm sản phẩm truyện tranh nằm ở vị trí nào trong bảng.

Nhập công thức

=MATCH ["Truyện tranh",B1:B7,0]

rồi nhấn Enter. 

Trong đó:

• Truyện tranh: Là giá trị cần tìm đúng vị trí, khu vực.

• B1:B7: Vùng tìm kiếm cho giá trị và ở đây là cột sản phẩm.

• 0: Tìm giá trị chính xác.

Kết quả sẽ ra 5, nghĩa là sản phẩm truyện tranh ở vị trí dòng thứ 5 trong bảng

Bước 2:

Bây giờ chúng ta sẽ tìm giá trị tại cột khu vực ứng với giá trị tại dòng số 5, sẽ ra được khu vực cho sản phẩm truyện tranh

Áp dụng vào ví dụ này thì có thể thay cả cụm MATCH [giá trị dùng để tra cứu, cột chứa giá trị, 0]] = 5, thứ tự của giá trị dùng để tra cứu.

Nhập công thức là

=INDEX[C1:C7,5]

sau đó nhấn Enter. Trong đó C1:C7 là cột chứa giá trị cần tra cứu.

Kết quả sẽ ra Khu vực C

Công thức tổng quát kết hợp hàm Index với hàm Match khi áp dụng vào ví dụ minh họa này là 

=INDEX[C1:C7,MATCH["Truyện tranh",B1:B7,0]]

 rồi nhấn Enter.

Kết quả cũng cho ra khu vực C ứng với sản phẩm truyện tranh.

Như vậy, qua bài viết trên đây chắc hẳn các bạn  đã biết cách sử dụng kết hợp hàm Index và hàm Match trong bảng tính Excel để lấy giá trị ở dòng và cột ở các bảng khác nhau và điền giá trị chính xác vào bảng dữ liệu. Một lưu ý là bạn có thể sử dụng cách làm này trên các phiên bản Office 2010, Office 2007,...Việc kết hợp hàm index và hàm match với nhau có lẽ đã mang đến chức năng lấy giá trị ở các hàng và các cột, đem lại một kết quả tìm kiếm tốt hơn, hiệu quả hơn, tiết kiệm thời gian hơn và chúng có thể giúp bạn dễ dàng thay thế cho hàm tìm kiếm cơ bản trong Excel như hàm Vlookup và hàm Hlookup.