Cách sử dụng hàm INDEX và MATCH cùng nhau trong Excel

Hàm INDEX có thể được sử dụng một mình, nhưng khi lồng với hàm MATCH tạo ra một tra cứu nâng cao. Trong bài này, Blogkienthuc.edu.vn sẽ hướng dẫn các bạn cách sử dụng hàm INDEX và MATCH cùng nhau một cách hiệu quả.

Hàm INDEX và hàm MATCH trong Excel là gì?

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.

Bạn đang đọc: Cách sử dụng hàm INDEX và MATCH cùng nhau trong Excel

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))

 

Tìm hiểu thêm: Cách tìm phim thuyết minh trên Netflix cực dễ bằng máy tính, điện thoại

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))

>>>>>Xem thêm: Hướng dẫn chi tiết từ A-Z cách tải và sử dụng Snap camera nhanh chóng dễ dàng nhất 

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:

• Hàm MID trong Excel là gì? Cách sử dụng hàm MID trong thực tế
• Hướng dẫn cụ thể hàm COUNTIF và hàm COUNTIFS trong Excel
• Hàm Vlookup trong Excel là gì? Hướng dẫn cách sử dụng hàm Vlookup đơn giản, dễ hiểu