Cách kết hợp hàm INDEX và MATCH trong Excel
Để thực hiện tra cứu phức tạp với nhiều tiêu chí, ta không thể sử dụng hàm VLOOKUP. Khi đó, chúng ta phải sử dụng hàm INDEX kết hợp với hàm MATCH để dò tìm nhiều điều kiện nâng cao. Cụ thể cách thực hiện như thế nào, các bạn hãy cùng tham khảo bài viết hướng dẫn cách kết hợp hàm Index và Match dưới đây nhé.
1. Chức năng của hàm INDEX khi kết hợp với hàm MATCH
Hàm INDEX trong Excel là hàm trả về một giá trị từ một vị trí cụ thể hoặc mảng giá trị cụ thể trong danh sách bởi các chỉ số hàng cột.
Cú pháp của hàm INDEX dạng tham chiếu:
=INDEX(Reference,Row_num,[Column_num],[Area_num])
Trong đó:
- Reference: Vùng tham chiếu, bắt buộc.
- Row_num: Chỉ số hàng từ đó trả về một tham chiếu, bắt buộc.
- Column_num: Chỉ số cột từ đó trả về một tham chiếu, tùy chọn.
- Area_num: Số của vùng ô sẽ trả về giá trị trong reference. Nếu Area_num được bỏ qua thì hàm INDEX dùng vùng 1, tùy chọn.
Cú pháp của hàm INDEX dạng mảng:
=INDEX(Array,Row_num,[Column_num])
Trong đó:
- Array: Phạm vi ô hoặc một hằng số mảng, bắt buộc
- Row_num: Chọn hàng trong mảng mà từ đó trả về một giá trị
- Column_num: Chọn cột trong mảng mà từ đó trả về một giá trị.
Hàm MATCH là hàm có thể tìm vị trí của một giá trị nào đó trong phạm vi mảng hoặc ô. Nó sẽ trả về đúng vị trí của ô đó hoặc trong mảng đó.
Cú pháp hàm MATCH:
=MATCH(Lookup_value,Lookup_array,[Match_type]).
Trong đó:
- Lookup_value: giá trị tìm kiếm trong mảng Lookup_array. Giá trị này có thể là số, văn bản, giá trị logic hoặc một tham chiếu ô đến một số, văn bản hay giá trị logic, bắt buộc phải có.
- Lookup_array: mảng hay phạm vị ô được tìm kiếm, bắt buộc có.
- Match_type: kiểu tìm kiếm, không nhất thiết phải có.
Khi hàm Index và Match này kết hợp với nhau, chúng sẽ tạo ra công thức dò tìm hàm index match nhiều điều kiện trong Excel một cách nhanh chóng và linh hoạt hơn.
2. Sự kết hợp hàm INDEX và MATCH
Trước khi sử dụng hàm INDEX kết hợp với hàm MATCH dựa trên nhiều tiêu chí, chúng ta hãy xem chúng hoạt động với nhau như thế nào thông qua một công thức đơn giản dưới đây:
Trong ví dụ dưới đây, ta sử dụng công thức kết hợp hàm Index Match 2 điều kiện như sau:
=INDEX($C$2:$C$4,MATCH(A7,$B$2:$B$4,0))
Yêu cầu của ví dụ này là chúng ta cần tìm ra chiếc áo len cổ điển trong cột B của bảng giá và lấy gía của nó từ cột C.
Trên mặt hàng này, chúng tôi cần giá được nhập vào ô A7 - Áo len
Công thức INDEX và MATCH được nhập vào ô C7, để lấy giá cho mặt hàng đó
Ví dụ về sự kết hợp của hàm Index với hàm Match. Hình 1
>> Xem thêm: Cách dùng hàm Count để đếm số lượng ô trong Excel
2.1. Hàm INDEX và MATCH được sử dụng phù hợp với nhiều tiêu chí
Ở ví dụ trước, trận đấu được dựa trên một tiêu chí - tên Vật phẩm. Đối với lần tra cứu tiếp theo, có 2 tiêu chí là: Tên sản phẩm và mã sản phẩm.
Trong hình dưới đây, mỗi mục được liệt kê 3 lần trong bảng tra cứu giá. Để có được mức giá phù hợp, bạn sẽ cần chỉ định cả tên mặt hàng và kích thước. Chúng tôi muốn tìm giá cho một chiếc áo khoác lớn.
Ví dụ về cách kết hợp của hàm Index và Match. Hình 2
2.2. Hàm MATCH trả về kết quả đúng hay sai?
Trong công thức tra cứu, chúng ta cần hàm MATCH để kiểm tra cả cột Mục và Kích thước .
Để hiển thị cách hoạt động, tôi sẽ thêm các cột tạm thời trên bảng tính, để kiểm tra các mục và kích thước cột - có phải là mục Áo khoác và kích thước có phải là Lớn hơn không?
Ta tiến hành nhập công thức: = C2= $C$13 vào địa chỉ ô F2
Nếu mục trong cột C là Áo khoác, kết quả trong cột E là TRUE. Nếu không, kết quả trả về là FALSE
Hàm Match trả về kết quả đúng hay sai?
Tiếp tục nhập công thức: =D2 = $D$13 vào địa chỉ ô G2
Nếu kích thước trong cột D lớn. Kết quả trong cột F sẽ là TRUE. Nếu không sẽ là FALSE.
2.3. Hàm MATCH trả về kết quả đúng
Hàm Match trả về kết quả đúng
Chúng tôi cần giá từ hàng trong đó cả hai kết quả là TRUE. Chúng tôi sẽ sử dụng một công thức để tính toán:
- Tại địa chỉ ô H2 nhập công thức: =F2*G2 sau đó sao chép xuống H10
- Trong quá trình học Excel, TRUE bằng 1 và FALSE bằng 0. Khi bạn nhân các giá trị
- Nếu một trong hai giá trị là FALSE (0), kết quả bằng 0
- Nếu cả hai giá trị là TRUE (1), kết quả là 1
- Chỉ hàng thứ 8 trong danh sách các mục của chúng tôi có 1, vì cả hai giá trị là TRUE trong hàng đó.
3. Tra cứu với nhiều tiêu chí
- Chúng ta có thể sử dụng công thức MATCH để tìm vị trí của cột 1 trong cột G, trong ảnh ở trên. Hàng dữ liệu thứ 8 (hàng bảng tính 9), có 1 và hàng đó sẽ cung cấp cho chúng tôi giá chính xác cho một chiếc áo khoác lớn.
- Nhưng, thay vì thêm các cột bổ sung vào bảng tính, chúng ta sẽ sử dụng công thức hàm INDEX và MATCH nhiều điều kiện được nhập mảng để thực hiện tất cả công việc. Áp sụng công thức nhập mảng vào ô E13 để có được giá chính xác.
- Nhấn Ctrl + Shift + Enter, thay vì chỉ nhấn Enter. Điều đó sẽ tự động thêm dấu ngoặc nhọn xung quanh công thức.
Cách kết hợp của hàm Index và Match trong Excel
4. Lỗi thường gặp khi sử dụng hàm index và match
Khi sử dụng hàm INDEX và hàm MATCH nhiều điều kiện, bạn có thể bắt gặp lỗi #NA và lỗi #VALUE.
Nguyên nhân lỗi #NA:
- Lỗi #NA xảy ra khi hàm MATCH không tìm thấy giá trị trong phạm vi dò tìm.
- Khi bạn sử dụng một phạm vi trong INDEX, MATCH thay vì một giá trị, bạn cần nhấn Ctrl+Shift+Enter để chuyển về công thức mảng.
- Khi bạn sử dụng MATCH, cần có sự nhất quán giữa giá trị trong đối số match_type và thứ tự sắp xếp các giá trị trong phạm vi dò tìm, nếu không bạn sẽ gặp giá trị #N/A.
Nguyên nhân lỗi #VALUE:
- Lỗi #VALUE xuất hiện khi công thức bạn đang sử dụng không được chuyển thành công thức mảng. Để khắc phục điều này, bạn cần nhấn phím Ctrl+Shift+Enter để tạo ra kết quả chính xác nhất.
Trên đây là hướng dẫn về cách kết hợp của hàm INDEX và MATCH trong Excel để dò tìm thỏa mãn nhiều điều kiện. Bạn có thể vừa tham khảo bài viết vừa thực hành ngay để nắm được cách thức thực hiện cũng như hiểu được bản chất của hai hàm INDEX và hàm MATCH.
Chúc các bạn thành công!
Xem thêm:
Tags: Excel
_m_1687340871.jpg)
