MATCH và INDEX là hai hàm rất mạnh và cũng thường được sử dụng trong Excel để làm việc hoặc trong các phép tính khi giải các bài toán về Excel. Khi kết hợp với nhau, hai hàm này có thể giúp chúng ta xử lý các bài toán phức tạp một cách tương đối dễ dàng.
Nếu như bạn đang băn khoăn chưa biết về công thức cũng như cách sử dụng thì trong bài viết này chúng tôi sẽ chia sẻ về chi tiết cách sử dụng hai hàm là MATCH và INDEX và kèm ví dụ minh họa cụ thể trong phần nội dung dưới.
Xem thêm bài viết nói về cách sử dụng hàm Hlookup trong Excel mà mình chia sẻ rất chi tiết trong thời gian gần đây.
Cách sử dụng hàm MATCH và INDEX trong Excel
Sử dụng Hàm INDEX
Hàm INDEX có tác dụng trả về một giá trị hay một vùng dữ liệu tùy thuộc vào các tham số đầu vào. Hàm INDEX có hai cách sử dụng đó là dạng mảng (array) và dạng tham chiếu (reference).
1. INDEX dạng mảng
– Cú pháp:
=INDEX(array, row_num, column_num)
Trong đó:
- array: Là một dãy ô (VD: A1:E5) hoặc là một hằng mảng (VD: {1;3;”a”;”TXD”})
- row_num: số thứ tự dòng trong array để lấy giá trị trả về.
- column_num: số thứ tự cột trong array để lấy giá trị trả về.
Lưu ý:
- Nếu hai đối số column_num và row_num đều sử dụng, INDEX sẽ trả về giá trị của ô là giao điểm của column_num và row_num.
- Nếu row_num = 0, INDEX sẽ trả về một mảng các giá trị là toàn bộ cột. Ngược lại, nếu column_num = 0, INDEX sẽ trả về một mảng các giá trị là toàn bộ dòng.
- Nếu column_num và row_num không nằm trong phạm vi của mảng, hàm INDEX sẽ trả về lỗi #REF!
- Để hàm INDEX trả về kết quả dưới dạng mảng, các bạn cần điền công thức ở dạng mảng.
Ví dụ: Ví dụ dưới đây sẽ minh họa với các bạn cách sử dụng hàm INDEX dạng mảng.
2. INDEX dạng tham chiếu
– Cú pháp:
=INDEX(reference, row_num, column_num, area_num)
Trong đó
- reference: chứa các vùng tham chiếu. Nếu các vùng tham chiếu này rời rạc thì phải được để trong dấu ngoặc (). Ví dụ về reference: A1:F4; (A2:B3,T6:$X$5), ….
- row_num: Là số thứ tự dòng trong tham chiếu.
- column_num: Là số thứ tự cột trong tham chiếu.
- area_num: số thứ tự vùng tham chiếu (được đánh số bắt đầu là 1, 2, 3, …) để trả về giao điểm của column_num và row_num trong dãy đó. Nếu bỏ qua area_num, INDEX sẽ mặc định dùng vùng 1.
Ví dụ, nếu reference = (A2:B4, C1:F4, G1:J5), thì vùng 1 là dãy A2:B4, vùng 2 là dãy C1:F4, và vùng 3 là dãy G1:J5.
Lưu ý:
- Đối với tham chiếu chỉ gồm một dòng hoặc một cột, các bạn có thể sử dụng cú pháp:
INDEX(reference,,column_num); INDEX(reference,row_num,)
- Nếu row_num = 0, INDEX sẽ trả về một mảng các giá trị là toàn bộ cột. Ngược lại, nếu column_num = 0, INDEX sẽ trả về một mảng các giá trị là toàn bộ dòng.
- Nếu column_num và row_num không nằm trong phạm vi của mảng, hàm INDEX sẽ trả về lỗi #REF!
- Nếu bỏ qua column_num và row_num, INDEX sẽ trả về vùng trong tham chiếu được xác định bởi area_num.
- Để hàm INDEX trả về kết quả dưới dạng mảng, các bạn cần điền công thức ở dạng mảng.
Ví dụ: Bạn có thể xem ví dụ trong hình minh hoạ dưới đây.
Sử dụng hàm MATCH
– Hàm MATCH trả về vị trí tương đối của một giá trị trong một dãy giá trị.
– Cú pháp:
=MATCH(lookup_value, lookup_array, match_type)
Trong đó
- lookup_value: Giá trị dùng để tìm kiếm trong hàng đầu tiên của table_array. lookup_value có thể là một số, một chuỗi hoặc một tham chiếu. VD: “một”; 1; C4; …
- lookup_array: vùng tìm kiếm
- match_type: kiểu tìm kiếm.
Với match_type có thể là một trong các giá trị 1, 0 hoặc -1:
- -1: MATCH sẽ tìm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value. Với kiểu tìm kiếm này, lookup_array phải được xếp theo thứ tự giảm dần từ lớn đến nhỏ.
- 0: MATCH sẽ tìm chính xác giá trị lookup_value. Với kiểu tìm kiếm này, MATCH không phân biệt chữ HOA hay chữ thường.
- 1: (mặc định) MATCH sẽ tìm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value. Với kiểu tìm kiếm này, lookup_array phải được xếp theo thứ tự tăng dần từ nhỏ đến lớn.
Lưu ý:
- Nếu match_type = 0 và lookup_value kiểu text, bạn có thể dùng các ký tự đại diện cho lookup_value. Dấu * đại diện cho nhiều ký tự và dấu ? đại diện cho một ký tự.
- Nếu không tìm thấy lookup_value trong lookup_array, hàm MATCH sẽ báo lỗi #NA!
Ví dụ: Xem ví dụ trong hình minh hoạ dưới đây.
Ví dụ áp dụng kết hợp MATCH và INDEX
Trong ví dụ sau đây, chúng ta có danh sách các học sinh với điểm thi tương ứng từng môn thi. Mỗi học sinh này đều thuộc một lớp chuyên nào đó và chúng ta sẽ sử dụng hàm INDEX để xác định điểm môn chuyên tương ứng với từng học sinh.
Với mỗi học sinh:
– Mảng chứa giá trị điểm là dòng tương ứng với học sinh đó và từ cột D đến cột L. Ví dụ, với học sinh Vũ Thanh Khiết, mảng cần lấy giá trị (tham số đầu tiên của hàm INDEX) là vùng D10:L10.
– Ô chứa điểm môn chuyên trong các môn thi của từng học sinh tương ứng với vị trí của môn chuyên trong danh sách các môn chuyên ở vùng D5:L5. Do đó, chúng ta sử dụng hàm MATCH để lấy về cột sẽ chứa điểm môn chuyên cho mỗi học sinh. Ví dụ, với học sinh Vũ Thanh Khiết, chúng ta sử dụng công thức
MATCH(C10;$D$5:$L$5;0).
So sánh hàm INDEX + MATCH với VLOOKUP và VLOOKUP
– Hàm HLOOKUP và VLOOKUP chỉ sử dụng được nếu cột (dòng) chứa giá trị tìm kiếm nằm ở vị trí trước so với cột (dòng) chứa giá trị cần trả về. Nếu không, chúng ta sẽ cần phải sử dụng kết hợp với công thức mảng để lấy được giá trị. Tuy nhiên, như các bạn thấy với cặp MATCH + INDEX, chúng ta có thể lấy được giá trị cần tìm mà không cần quan tâm tới vị trí của vùng tìm kiếm và vùng kết quả như hai hàm VLOOKUP và HLOOKUP.
– VLOOKUP, HLOOKUP sẽ bị sai nếu chúng ta thêm hoặc bớt cột (dòng) ở vùng tham chiếu trong bảng tính. Bởi lẽ, khi thêm hoặc bớt 1 cột (hàng) sẽ làm cho khoảng cách từ cột (dòng) đầu tiên đến dòng chứa giá trị cần trả về bị thay đổi dẫn đến kết quả của hàm VLOOKUP, HLOOKUP không đúng nữa. Điều này hoàn toàn không ảnh hưởng tới MATCH + INDEX.
Ngoài ra, bạn có thể xem thêm một số hàm thông dụng khác mà chúng tôi đã từng chia sẻ như:
Tải các tập tin minh hoạ
Các bạn có thể tải ví dụ trong bài hướng dẫn thông qua các liên kết dưới đây:
Lời kết
Như vậy, chúng tôi đã giới thiệu với các bạn cách dử dụng hàm MATCH và INDEX trong Excel. Sự kết hợp giữa MATCH và INDEX luôn tạo ra những kết quả đơn giản đến bất ngờ. Vì vậy, các bạn cần đảm bảo nắm vững cách sử dụng hai hàm này để có thể sử dụng trong trường hợp cần thiết nhé!
Nếu có bất cứ thắc mắc nào liên quan đến việc sử dụng hàm Match và Index trong Excel thì đừng quên để lại bình luận của bạn trong phần dưới đây.
Chúc bạn thành công!
