Hàm Index match nhiều điều kiện: Cách dùng cơ bản, nâng cao
Khi sử dụng Excel, hàm Vlookup là phổ biến nhất bởi những ưu điểm mà nó mang lại song cùng với những ưu điểm còn tồn tại yếu điểm nhất định là nó chỉ tìm được giá trị từ trái qua phải. Điều này đồng nghĩa với việc Vlookup xử lý dữ liệu chậm và chỉ tìm được một điều kiện. Chúng tôi đã tìm ra cách tìm dữ liệu một cách nâng cao hơn đó là sự kết hợp giữa hàm index match nhiều điều kiện.
Hàm Index là gì, sử dụng hàm Index Match như thế nào?
Cùng Máy Tính Vui tìm hiểu xem hàm Index là gì? Cách sử dụng hàm Index Match như thế nào? Ngay sau đây nhé!
Hàm Index là gì?
Hàm Index là một công thức hàm trả về giá trị một ô dữ liệu có chỉ số một và hàng mà người dùng tìm kiếm. Sử dụng hàm thành thạo sẽ là một thủ thuật excel hữu ích giúp bạn tiện hơn trong quá trình làm việc của mình.
Hàm Index trong Excel có 2 dạng là hàm Index dạng mảng và Index ở dạng tham chiếu. Hàm Index có hai dạng là dạng mảng và dạng tham chiếu.
Hàm Index dạng mảng: Index(Array, Row_num, [column_num])
Array: chứa dữ liệu được tham chiếuRow_num: là hàng chứa giá trị sẽ lấyColumn_num: là cột chứa giá trị sẽ lấy
Hàm Index ở dạng tham chiếu trả về giá trị của một ô có chỉ số hàng và số cột đang tìm kiếm. Công thức là INDEX (Reference, Rownum, [Column_num], [Area_num]. Trong đó có:
Reference: Vùng tham chiếu chứa những giá trị cần tìm.Row - num: Chỉ số hàng chứa giá trị cần tìm.Column - num: Chỉ số cột chứa giá trị cần tìm.Area - num: Số vùng trả về, để trống mặc định là 1.
Hàm Match, sử dụng hàm Match và sự kết hợp hàm Index Match
Index (bôi đen hết những dữ liệu từ ô thành phố đến ô cuối cùng của dữ liệu; số hàng dịch xuống, bạn tính từ hàng thành phố xuống hàng Hồ Chí Minh là ba hàng nên để số 3; số điện thoại bạn tính từ cột thành phố qua là ba cột đến cột điện thoại nên để số 3).
Còn hàm Match là hàm mà để trả về lại thứ tự cần tìm thành phần thứ nhất của hàm Match là giá trị mình cần tìm, thành phần thứ hai là vùng dữ liệu để tìm kiếm và thành phần thứ ba nếu bạn điền 1 thì nó sẽ tìm kiếm lớn hơn nên bạn cần điền 0 vào đây để tìm đúng giá trị.
Công thức hàm Match =Match(Lookup_value,Lookup_array,[Match_type]).
Lookup_value: giá trị tìm kiếm trong mảng Lookup_array.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ó.
Chúng ta hãy tìm thử vị trí của thành phần Excel 2 với cú pháp =MATCH(H6;C5:C10;0) trong đó H6 là ô dữ liệu cần tìm C5 đến C10 là cột dữ liệu cần tìm 0 là bạn đã tìm đến đúng vị trí cần tìm.
Mình sẽ đặt cột tên thành danh sách xổ bên dưới xuống cho dễ kiểm tra.
Chọn vào ô Excel 2 chọn Data >Data Validation > Allow chọn List > Source bôi đen phần dữ liệu họ tên > Chọn OK. Chọn vào danh sách tên và đổi tên thì hàm Match sẽ tự động cho kết quả về lại vị trí của tên đó ở trong cột tên.
Để kết hợp hàm index và hàm match bằng cách bạn chọn vào ô chứa hàm Match trả về vị trí lúc nãy. Sau đó copy và mở hàm Index thay cho thành phần số hai của hàm Index.
=INDEX(B5:F10;MATCH(H6;C5:C10;0);1).
Hàm index match trong excel cơ bản đã được giải quyết.
Sử dụng hàm Index Match nâng cao – Index Match nhiều điều kiện
Bằng cách mới, chúng ta tìm theo hai điều kiện trong bản là doanh thu thực tế trong Excel 1 chúng ta sẽ làm theo hai cách, cách 1 là thêm dữ liệu phụ, cách 2 sử dụng công thức mảng.
Để bắt đầu với cách 1 bạn cần tạo thêm dữ liệu mới và thêm dữ liệu vào bằng cách gộp hai ô thực tế với doanh thu, kế hoạch và lợi nhuận,…
Bằng cách bạn chọn vào ô phía trên hàng kế hoạch và dùng cú pháp =chọn vào ô kế hoạch và chọn vào ô doanh thu > Enter sau đó kéo định dạng đó cho hết hàng.
Sau đó bắt đầu với =INDEX(bôi đen vùng dữ liệu ;MATCH(chọn vô ô Excel 1 ở họ tên cần tìm;bôi đen vùng dữ liệu tìm kiếm là cột họ tên trong bản ;0);MATCH (chọn vô ô thực tế và kích vô ô doanh thu;bôi đen phần cột phụ mình đã thêm phía trên bản lúc nãy ;0)+1)
Trong đó: cộng 1 ở cuối tại vì vùng dữ liệu của phần MATCH cuối nhỏ hơn vùng dữ liệu trong INDEX của một cột.
Bạn còn có thể thay đổi họ tên hoặc kế hoạch doanh thu thì phần doanh thu tự động thay đổi theo nội dung mà bạn chỉnh. Và đó là kết thúc hàm index và match nhiều điều kiện nâng cao.
Còn với cách hai hàm index match nâng cao, chúng ta cũng bắt đầu với
=INDEX(bôi đen vùng dữ liệu;MATCH(kích vào ô điều kiện cần tìm mà cụ thể là ô tên;bôi đen cột tên trong bản;0);MATCH(kích vào ô điều kiện 1(ô thực tế) và kích vào ô điều kiện 2(ô doanh thu);bôi đen hàng kế hoạch và thực tế trong bản và bôi đen hàng doanh thu và lợi nhuận trong bản)+1).
Lúc này bạn đừng bấm Enter vội mà bạn cần phải dùng tổ hợp phím Ctrl+Shift+Enter vì sử dụng hàm Index Match là vậy.
Và bên trên đó là những thủ thuật excel hữu ích và cách dùng hàm index match thành thạo nhất và mang tính ứng dụng cao nhất, việc sử dụng kết hợp sẽ giúp cho công việc của bạn trở nên hiệu quả và tiết kiệm thời gian hơn.
Bên cạnh đó, Máy Tính Vui mời bạn xem thêm các bài viết chủ đề thủ thuật máy tính hay trên Window để có thêm những mẹo hay về cách sử dụng, tuỳ biến trên hệ điều hành Window hay Macbook các bạn nhé.
