21/06/2018, 15:01
Cách sử dụng 2 hàm INDEX, MATCH trong Excel - kết hợp hàm INDEX và MATCH
Index và Match là hai hàm thú vị trong Excel. Tại sao vậy? Vì hiểu được 2 hàm này, ta có thể kết hợp chúng để xử lý những nhiệm vụ phức tạp mà Vlookup không làm được. Bài viết sau sẽ cung cấp cho các bạn những kiến thức cơ bản nhất về hàm Index, Match và cách kết hợp chúng trong Excel. A. Hàm ...
Index và Match là hai hàm thú vị trong Excel. Tại sao vậy? Vì hiểu được 2 hàm này, ta có thể kết hợp chúng để xử lý những nhiệm vụ phức tạp mà Vlookup không làm được. Bài viết sau sẽ cung cấp cho các bạn những kiến thức cơ bản nhất về hàm Index, Match và cách kết hợp chúng trong Excel.
A. Hàm INDEX:
1. Nội dung:
Hàm Index trả về một giá trị hoặc tham chiếu tới một giá trị từ trong một bảng hoặc phạm vi.
2. Cú pháp: Hàm Index có 2 dạng:
a. Dạng mảng:
=INDEX(array, row_num, [column_num])
Trong đó:
- Array: Là phạm vi ô hoặc một hằng số mảng, array là đối số bắt buộc:
- Nếu array chứa một hàng hoặc một cột thì đối số row_num hoặc column_num tương ứng là tùy chọn.
- Nếu array chứa nhiều hàng và nhiều cột:
- Nếu sử dụng cả hai đối số row_num và column_num, thì hàm sẽ trả về giá trị trong ô giao điểm của row_num và column_num.
- Nếu nhập chỉ số row_num hoặc column_num là 0 thì hàm sẽ trả về mảng giá trị cho toàn bộ cột hoặc hàng tương ứng.
- Row_num: Số thứ tự của hàng trong mảng.
- Column_num: Số thứ tự của cột trong mảng.
* Ví dụ:
- Trong bảng dữ liệu dưới đây, ta nhập công thức: =INDEX(B3:F7,2,5)
Tức là tìm giá trị tại giao điểm của dòng thứ 2 và cột thứ 5 của mảng B3:B7
Kết quả trả về giá trị tại ô F4 là 0.1
Vẫn bảng dữ liệu trên, nếu ta bôi đen 5 ô theo hàng ngang rồi nhập công thức: =INDEX(B3:F7,3,0)
Sau đó nhấn tổ hợp <Ctrl + Shift + Enter>, kết quả sẽ trả về một mảng gồm các giá trị của toàn bộ dòng thứ 3 như sau:
b. Dạng tham chiếu:
=INDEX(Reference,Row_num,[Column_num],[Area_num])
Trong Đó:
- Reference: Là vùng tham chiếu (1 hoặc nhiều vùng)
- Nếu có nhiều vùng tham chiếu thì bạn phải đặt các vùng vào trong một dấu ngoặc đơn, cách nhau bởi dấu phẩy.
- Row_num: Là số hàng trong tham chiếu.
- Column_num: Là số cột trong tham chiếu.
- Area_num: Là số của vùng ô sẽ trả về giá trị trong reference. Nếu Area_num được bỏ qua thì hàm INDEX mặc định lấy vùng 1.
* Ví dụ:
Trong bảng dữ liệu sau, nhập công thức: =INDEX((B3:C4,B6:F7),2,3,2)
Nghĩa là: Tìm giá trị ở hàng 2, cột 3 trong vùng thứ 2 (tức là vùng B6:F7).
Kết quả trả về giá trị trong ô D7 là Yếu.
B. Hàm MATCH:
1. Nội dung:
Hàm Match tìm một giá trị được chỉ định trong phạm vi của ô, sau đó trả về vị trí tương đối của giá trị đó trong phạm vi này.
2. Cú pháp:
=MATCH(Lookup_value,Lookup_array,[Match_type])
Trong đó:
- Lookup_value: Là giá trị tìm kiếm (số, văn bản, giá trị logic, hoặc 1 tham chiếu ô)
- Lookup_array: Là dải ô tìm kiếm (1 hàng hoặc 1 cột)
- Match_type: Là kiểu tìm kiếm. Có 3 kiểu:
- “1” hoặc bỏ qua: Tìm kiếm giá trị lớn nhất mà giá trị đó nhỏ hơn hoặc bằng giá trị tìm kiếm. Giá trị trong Lookup_array phải được sắp theo thứ tự tăng dần (-2, -1, 0, 1, 2, ...; A-Z; FALSE, TRUE;…)
- “0”: Tìm kiếm giá trị thứ nhất bằng chính xác Lookup_value. Các giá trị trong Lookup_array có thể được sắp theo bất kỳ trật tự nào
- “-1”: Tìm kiếm giá trị nhỏ nhất mà giá trị đó lớn hơn hoặc bằng Lookup_value. Các giá trị trong Lookup_array phải được sắp theo thứ tự giảm dần (TRUE, FALSE; Z-A; 2, 1, 0, -1, -2,…)
3. Ví dụ:
Ở bảng dữ liệu trên, ta nhập công thức: =MATCH(B9,B3:B7,0)
Nghĩa là: Tìm giá trị của ô B9 (Nguyễn Ngọc Dung) trong dải ô từ B3:B7, sau đó trả về số thứ tự của giá trị đó trong dải ô.
Kết quả là 4 vì trong dải ô B3:B7 thì Nguyễn Ngọc Dung nằm ở hàng thứ 4.
C. Kết hợp hàm INDEX và MATCH:
Hàm MATCH xác định vị trí tương đối của giá trị cần tìm trong dải ô xác định. Từ đó, hàm INDEX sử dụng con số đó, rồi trả về giá trị ở ô tương ứng.
Vẫn ví dụ như trên, giả sử ta cần tìm điểm trung bình của học sinh Nguyễn Ngọc Dung, ta nhập công thức như sau:
=INDEX(B3:C7,MATCH(B9,B3:B7,0),2)
Trước hết, công thức MATCH(B9,B3:B7,0) sẽ trả về số thứ tự của Nguyễn Ngọc Dung trong dải ô B3:B7 là 4.
Khi đó hàm trở về dạng đơn giản: =INDEX(B3:C7,4,2)
Giao điểm của hàng 4 và cột 2 trong mảng B3:C7 là ô C6. Vì vậy hàm trả về kết quả trong ô C6 là 6.5
Như vậy, bài viết này đã cung cấp cho các bạn cái nhìn cơ bản nhất về 2 hàm Index, Match và cách kết hợp 2 hàm. Nắm được những vấn đề cơ bản này, các bạn sẽ hiểu được các công thức phức tạp hơn liên quan đến kết hợp 2 hàm. Chúc các bạn thành công!
