Hướng Dẫn Sử Dụng Index Và Match Đúng Cách (Hơn Cả Vlookup)
Khi sử dụng excel chắc các bạn cũng biết đến hàm Vlookup và những ưu điểm tuyệt vời mà nó mang lại nhưng song hành với những ưu điểm đó thì hàm Vlookup cũng có các nhược điểm như Vlookup chỉ tìm được giá trị từ trái qua phải, Vlookup chỉ tìm được theo một điều kiện, Vlookup xử lý chậm với dữ liệu lớn. Vì thế trong bài viết này mình sẽ hướng dẫn các bạn về hàm Index + Match để thay thế cho hàm Vlookup và còn khắc phục được ba nhược điểm của hàm Vlookup.
Video hướng dẫn thực hiện
Index và Match cơ bản
Bạn có thể tải file thực hành tại đây.
Với hàm Index ta có ba thành phần thành phần một là vùng dữ liệu thành phần hai là dịch xuống bao nhiêu hàng và thành phần thứ ba là dịch sang phải bao nhiêu hàng.
Ví dụ trong file tài liệu này bạn muốn tìm số điện thoại ở hàng thành phố HCM thì bạn sẽ sử dụng hàm Index với cú pháp =Index(bôi đen toàn bộ 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 HCM 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). VD: =INDEX(B5:F10;3;3)
Hàm Match là hàm trả về thứ tự cần tìm kiế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, 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 điền 0 vào đây để tìm đúng giá trị. Bây giờ mình có thể tìm thử vị trí của 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à tìm đúng vị trí.
Bây giờ mình cho cột tên thành danh sách sổ xuống cho dễ kiểm tra. Kích vào ô Excel 2 chọn Data -> Data Validation -> trong Allow chọn List -> trong Source bôi đen phần dữ liệu họ tên -> ok.
Giờ bạn kích vào danh sách tên và đổi tên thì tự động hàm Match trả về vị trí của tên đó trong cột tên.
Để tích hợp hàm Match vào hàm Index bằng cách bạn kích vào ô chứa hàm Match trả về vị trí lúc nãy 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)
Bây giờ bạn có thể đổi tên thì thành phố tự động thay đổi theo họ tên. Và bạn nhứ lưu ý là vùng dữ liệu của Index và Match phải tương tự nhau về số hàng.
Index và Match nâng cao
Bây giờ mình sẽ tìm theo hai điều kiện trong bản là doanh thu thực thế của bạn Excel 1 mình sẽ làm theo ba cách, cách 1 là thêm dữ liệu phụ, cách 2 là công thức mảng và cách 3.
Mình bắt đầu với cách 1 bạn phải tạo thêm dữ liệu bằng cách là sẽ gộp hai ô kế hoạch và lợi nhuận, thực tế với doanh thu,... bằng cách bạn kích vào ô phía trên hàng kế hoạch và dùng cú pháp =kích vô ô kế hoạch & kích vô ô doanh thu -> enter sau đó kéo định dạng đó cho hết hàng của bản.
Bây giờ mình bắt đầu với =INDEX(bôi đen vùng dữ liệu;MATCH(kích 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(kích vô ô thực tế & 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 một cột.
Bây giờ bạ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 bạn chỉnh.
Cách 2 mình 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ế)&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 & bôi đen hàng doanh thu và lợi nhuận trong bản)+1) và bạn đừng bấm enter vội bạn phải dùng tổ hợp phím Ctrl+Shift+Enter vì công thức mảng định dạng như vậy.
VD: =INDEX(B16:F21;MATCH(C26;B16:B21;0);MATCH(D24&D25;C15:F15&C16:F16;0)+1)
Cách 3 là cách hay nhất mà bạn không cần phải thêm dữ liệu phụ hay dùng mảng đầu tiên bạn cũng =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ế)&kích vào ô điều kiện 2(ô doanh thu);INDEX(bôi đen hàng kế hoạch và thực tế trong bản & bôi đen hàng doanh thu và lợi nhuận trong bản;0))+1).
VD: =INDEX(B16:F21;MATCH(C32;B16:B21;0);MATCH(D30&D31;INDEX(C15:F15&C16:F16;0))+1)
Để xem cách hoạt động của một hàm trong Excel thì bạn kích vào hàm đó hay ô chứa hàm đó chọn Formulas -> Evaluate Formula -> bạn bấm vào Evaluate để xem từng bước mà hàm thực hiện.
CHO ĐIỂM BÀI VIẾT NÀY
