Trong Excel, ngoài các hàm như HLOOKUP hay VLOOKUP ra. Còn có các hàm khác như INDEX hay MATCH cũng có chức năng tương tự như hàm HLOOKUP. Vậy hàm INDEX và hàm MATCH là gì? Cách sử dụng hàm INDEX và MATCH như thế nào? Hãy theo dõi bài viết của Mindovermetal dưới đây.
Mục lục nội dung
Cú pháp của hàm INDEX và hàm MATCH trong Excel
Cú pháp hàm INDEX trong Excel
Hàm INDEX có chức năng trả về một giá trị ô dựa trên số thứ tự hàng và cột được khai báo. Đồng thời hàm này có hai công thức đó là công thức ở dạng mảng và dạng tham chiếu.
Hàm INDEX ở dạng mảng
Ở dạng mảng INDEX trả về giá trị của một phần tử hoặc một mảng dựa trên số hàng và số cột mà bạn yêu cầu.
Công thức như sau: =INDEX(array, row_num, [column_num])
Trong đó:
- Array: Là giá trị dò bao gồm một dải các ô.
- Row_num: Là hàng thứ mấy trong bảng bạn muốn tham chiếu giá trị
- Column_num: Là cột thứ mấy trong bảng bạn muốn tham chiếu giá trị.
Hàm INDEX ở dạng tham chiếu
Ở dạng tham chiếu, INDEX trả về ô tham chiếu của hàng và cột đã xác định.
Công thức của dạng này là: =INDEX(reference, row_num, column_num, area_num)
Trong đó:
- Reference: Là một hay nhiều bảng dò.
- Row_num: Là hàng thứ mấy trong bảng dò bạn muốn tham chiếu giá trị.
- Column_num: Là cột thứ mấy trong bảng dò bạn muốn tham chiếu giá trị.
- Area_num: Tham số tùy chọn, chỉ ra bảng mà bạn muốn tham chiếu giá trị.
Ta sẽ xem 2 ví dụ dưới đây để phân biệt sự khác nhau giữa 2 công thức này:
Ở trên là ví dụ về hàm INDEX ở dạng mảng. Có thể thấy kết quả trả về là giao của hàng thứ 2 và cột thứ 3 trong bảng dữ liệu.
Ví dụ trên là INDEX ở dạng tham chiếu. Kết quả trả về là giao giữa hàng 3 và cột 2 trong bảng dữ liệu 2.
Như vậy, sự khác nhau cơ bản của 2 dạng trên đó là vùng dữ liệu. Đối với dạng mảng, chỉ có thể chọn 1 vùng dữ liệu duy nhất. Trong khi với dạng tham chiếu, có thể chọn nhiều vùng dữ liệu khác nhau và quyết định sẽ chọn vùng nào để truy xuất dữ liệu.
Cú pháp của hàm MATCH trong Excel
Hàm MATCH có chức năng tìm kiếm và trả về vị trí của một giá trị cụ thể trong một dãy các ô.
Cú pháp của hàm này là: =MATCH(lookup_value, lookup_array, match_type)
Trong đó:
- Lookup_value: Là giá trị cần phải tìm kiếm vị trí.
- Lookup_array: Dãy các ô chứa giá trị.
- Match_type: Loại tìm kiếm (-1, 0, 1)
Lưu ý:
- Với Match_type là -1, tìm giá trị nhỏ nhất sao cho giá trị đó lớn hơn giá trị cần tìm. Dãy giá trị phải được sắp xếp theo chiều tăng dần.
- Với match_type bằng 0, tìm giá trị chính xác với giá trị cần tìm.
- Với match_type bằng 1, tìm giá trị lớn nhất sao cho giá trị đó nhỏ hơn giá trị cần tìm. Dãy giá trị phải được sắp xếp theo chiều giảm dần.
Nhìn vào hình trên ta sẽ thấy, cú pháp =MATCH(D4, C4:E4, 0) đã tìm và trả về kết quả là 2. Đây là vị trí của 120.000 trong bảng dữ liệu từ C4 đến E4.
Trên đây là cách sử dụng hàm INDEX và MATCH trong Excel. Hai hàm này nếu sử dụng riêng biệt sẽ có những hạn chế riêng. Tuy nhiên, nếu kết hợp lại với nhau, ta sẽ có một hàm tìm kiếm vô cùng mạnh mẽ.
Tìm kiếm một giá trị bất kì trong bảng dữ liệu với hàm INDEX/MATCH
INDEX/MATCH là hàm tìm kiếm mạnh mẽ nhất trong Excel. Hàm này có thể tìm kiếm một giá trị bất kì ở bất cứ đâu trong bảng dữ liệu. Để có thể hiểu rõ hàm này, ta sẽ đi qua một ví dụ sau đây.
Hãy điền đơn giá cho bảng dữ liệu trên. Biết rằng, đơn giá của một mặt hàng phụ thuộc vào tên hàng và mã hàng ở bảng đơn giá các mặt hàng.
Hiểu hàm INDEX/MATCH qua ví dụ
Với ví dụ trên, ta không thể dùng hàm VLOOKUP hay HLOOKUP. Vì thế trong trường hợp này ra sẽ dùng INDEX/MATCH. Công thức như sau:
=INDEX($L$9:$N$11, MATCH(C5,$K$9:$K$11,0), MATCH(B5,$L$8:$N$8,0))
Cách thức hoạt động của công thức trên là:
Trước tiên, hàm MATCH(C5,$K$9:$K$11,0) sẽ truy xuất vị trí của đơn giá theo hàng và MATCH(B5,$L$8:$N$8,0) sẽ truy xuất vị trí của đơn giá theo cột.
Sau khi truy xuất vị trí đơn giá theo hàng và cột xong. Lúc này hàm INDEX sẽ tìm kiếm và trả về đơn giá là giao giữa hàng và cột vừa tìm được.
Nhập công thức đã cho vào ô F5, ta sẽ có đơn giá của mặt hàng đầu tiên.
Tiếp đó, di chuột về phía góc dưới bên phải của ô A5 cho đến khi xuất hiện dấu “+”. Nhấp chuột và kéo thả cho đến sản phẩm cuối cùng. Ta sẽ có đơn giá của toàn bộ mặt hàng.
Lưu ý:
Đối với những bảng giá trị bắt buộc phải để tham chiếu tuyệt đối (dấu $). Để khi sao chép công thức, vị trí của bảng giá trị sẽ không thay đổi.
Kết quả được đúc kết
Từ ví dụ trên ta có thể rút ra công thức chung của hàm INDEX/MATCH như sau:
=INDEX(array, MATCH(lookup_value1, lookup_array1, 0), MATCH(lookup_value2, lookup_array2, 0)
Trong đó:
- Array: Là bảng giá trị chứa ô giá trị cần tìm, là tham chiếu tuyệt đối.
- Lookup_value1, lookup_array1: là giá trị cần tìm vị trí theo hàng và bảng giá trị chứa giá trị cần tìm, bảng giá trị là tham chiếu tuyệt đối.
- Lookup_value2, lookup_array2: là giá trị cần tìm vị trí theo cột và bảng giá trị chứa giá trị cần tìm, bảng giá trị là tham chiếu tuyệt đối.
Các bạn không cần thiết phải nhớ công thức trên một cách máy móc. Tốt nhất là nên nhớ nó theo bản chất của từng hàm để có thể linh hoạt sử dụng và kết hợp với các hàm khác như MIN, MAX hay AVERAGE.
Trên đây là cách sử dụng hàm INDEX và MATCH. Sự kết hợp của hai hàm này đã tạo ra một công thức tìm kiếm hết sức linh hoạt. Hiểu và biết ứng dụng hai hàm này sẽ giúp bạn nâng cao hiệu suất trong công việc. Nếu quan tâm và muốn tìm hiểu về Excel. Hãy theo dõi và đọc những bài viết khác tại Mindovermetal.
