Hàm Index là một trong những hàm được sử dụng nhiều nhất của Excel để truy tìm dữ liệu trong một phạm vi. Do đó, bài viết sau đây của Mua Bán sẽ giúp bạn hiểu hơn về cách dùng hàm Index, cách áp dụng với hàm Match và một số vấn đề khác có liên quan.
I. Hàm Index và hàm Match
Khi tìm hiểu về hàm Index, nhiều người thường nhầm lẫn giữa hàm Index và hàm Match. Thực tế, hai hàm này có sự khác nhau về cả ý nghĩa lẫn cấu trúc sử dụng. Tuy nhiên, chúng lại thường được áp dụng đồng thời để tìm kiếm và nhập dữ liệu vào một phạm vi đã xác định.
1. Cấu trúc của hàm Index
Hàm Index được dùng để trả về giá trị của vị trí được cung cấp trong phạm vi hoặc một mảng. Cấu trúc của hàm Index như sau:
=INDEX(array;row_num;column_num).
Trong đó:
- “array” là một phạm vi vùng ô hoặc một hằng số mảng;
- “row_num” là hàng trong mảng cần truy xuất dữ liệu;
- “column_num” là cột trong mảng cần truy xuất dữ liệu.
Tham khảo thêm: [VIDEO] 3 cách giãn dòng trong Excel chi tiết nhất
2. Cấu trúc của hàm Match
Khác với cách dùng hàm Index trong Excel, hàm Match được dùng để trả về vị trí tương đối của một giá trị trong một phạm vi nào đó. Cấu trúc của hàm Match như sau:
=MATCH(lookup_value,lookup_array,match_type).
Trong đó:
- “lookup_value” là giá trị cần tìm kiếm nằm trong mảng “lookup_array”;
- “lookup_array” là vùng mảng (còn được gọi là phạm vi) tìm kiếm;
- “match_type” là kiểu tìm kiếm.
II. Sự khác nhau giữa hàm Index, Match với hàm Vlookup, Hlookup
Các cặp hàm Index + Match và Vlookup + Hlookup thường được sử dụng để dò tìm dữ liệu. Tuy nhiên, hàm Index + Match có khả năng dò tìm dữ liệu linh hoạt hơn. Bởi vì đối với hàm Vlookup, giá trị trả về phải nằm ở cột bên phải so với giá trị dò tìm. Đối với hàm Hlookup, giá trị trả về phải nằm ở hàng bên dưới so với giá trị dò tìm.
Do đó, cách dùng hàm Index thay cho Vlookup được nhiều người lựa chọn. Ngoài ra, hàm Index còn có các lợi ích khác sau đây:
III. Lợi ích khi dùng hàm Index và hàm Match
Ngoài khả năng tìm kiếm dữ liệu linh hoạt hơn, hàm Index + Match còn có những ưu điểm khác bao gồm:
- Có thể xóa hoặc chèn cột mà không ảnh hưởng đến giá trị trả về;
- Giá trị cần tìm kiếm không có giới hạn về kích thước, trong khi đó giá trị cần tìm kiếm trong hàm Vlookup + Hlookup bị giới hạn không quá 255 ký tự;
- Tốc độ xử lý nhanh hơn khoảng 10 – 15% trong một bảng tính lớn chứa nhiều giá trị.
Tham khảo thêm: Cách làm mục lục trong word tự động cho tất cả phiên bản
IV. Ví dụ cách dùng hàm Index kết hợp hàm Match
Để giúp bạn áp dụng cách dùng hàm Index kết hợp hàm Match dễ dàng hơn, sau đây là hướng dẫn chi tiết về cách dùng trong các trường hợp phổ biến nhất:
1. Cách dùng hàm INDEX với MATCH tìm Email theo tên
Hướng dẫn nhanh:
Xác định giá trị cần tìm và vùng tìm kiếm → xác định vị trí theo dòng/cột của dữ liệu cần tìm bằng hàm Match → dùng hàm Index và Match để trả ra giá trị của vị trí vừa tìm được.
Hướng dẫn chi tiết:
Bước 1: Xác định giá trị cần tìm (tên khách hàng) và vùng tìm kiếm (bảng 1);
Bước 2: Xác định vị trí theo dòng/cột của dữ liệu cần tìm bằng hàm Match, công thức =match(B13,A3:A10,0);
Bước 3: Kết hợp hàm Index và Match để trả ra giá trị của vị trí vừa tìm được với công thức =index(C3:C10,match(B13, A3:A10,0)).
2. Cách dùng hàm INDEX với MATCH dò tìm từ phải qua trái
Trường hợp này cho thấy rõ ưu điểm của cách dùng hàm Index kết hợp với hàm Match. Bạn có thể dò tìm dữ liệu từ phải qua trái, từ trên xuống dưới mà không bị giới hạn về vị trí của dữ liệu.
Hướng dẫn nhanh:
Xác định giá trị cần tìm và vùng tìm kiếm → xác định vị trí theo dòng/cột của dữ liệu cần tìm bằng hàm Match → dùng hàm Index và Match để trả ra giá trị của vị trí vừa tìm được.
Hướng dẫn chi tiết:
Bước 1: Xác định giá trị cần tìm (email liên hệ) và vùng tìm kiếm (cột email liên hệ và cột tên khách hàng);
Bước 2: Xác định vị trí theo dòng/cột của dữ liệu cần tìm bằng hàm Match với công thức =match(B19,C3:C10,0)
Bước 3: Kết hợp hàm Index và Match để trả ra giá trị của vị trí vừa tìm được với công thức =index(A3:A10,match(B19,C3:C10,0)).
3. Cách dùng hàm INDEX để xác định đơn giá của mặt hàng theo nhiều điều kiện
Trong trường hợp này, cách dùng hàm Index và hàm Match cần phải xác định được vị trí tương đối của dữ liệu đang nằm ở hàng và cột nào. Cách thực hiện như sau:
Hướng dẫn nhanh:
Xác định vị trí hàng của giá trị cần tìm bằng hàm Match → xác định vị trí cột của giá trị cần tìm bằng hàm Match → xác định giá trị khi kết hợp vị trí hàng và cột vừa tìm bằng hàm Match và hàm Index.
Hướng dẫn chi tiết:
Bước 1: Xác định vị trí hàng của giá trị cần tìm bằng hàm Match với công thức =match(I5,A4:F4,0);
Bước 2: Xác định vị trí cột của giá trị cần tìm bằng hàm Match bằng công thức =match(I6,A4:A9,0);
Bước 3: Xác định giá trị khi kết hợp vị trí hàng và cột vừa tìm bằng hàm Index và hàm Match với công thức =index(A3:F9,K6,K5).
| Xem thêm một số tin đăng tuyển dụng việc làm công sở ngay tại website Muaban.net |
V. Một số lưu ý khi sử dụng hàm Index
Cách dùng hàm Index và hàm Match cho thấy nhiều ưu điểm vượt trội hơn so với hàm Vlookup và Hlookup. Do đó, bạn có thể sử dụng hoàn toàn hàm Index + Match để tìm kiếm giá trị trong hầu hết các trường hợp.
VI. Nguyên nhân gây ra lỗi khi sử dụng hàm Index kết hợp hàm Match
Lỗi #NA và lỗi #VALUE là hai lỗi thường gặp nhất đối với người mới biết cách dùng hàm Index và hàm Match. Vì vậy, chúng ta cần tìm hiểu nguyên nhân và cách khắc phục khi gặp hai lỗi này.
1. Lỗi #NA
- Hàm Match không tìm thấy giá trị cần tìm trong phạm vi, do đó, bạn cần kiểm tra lại vùng tìm kiếm hoặc giá trị cần tìm có bị sai sót hay không. Một số lỗi thường gặp gồm có: dư dấu khoảng cách ẩn, định dạng của ô đang bị sai, vùng dữ liệu có hàng/cột bị trùng lặp,…
- Bạn đang dùng hàm Index và hàm Match đang ở dạng tham chiếu nhưng lại nhập dưới dạng công thức mảng. Vì vậy, bạn cần chuyển các hàm này sang dạng mảng bằng lệnh Ctrl+Shift+Enter.
- Sự sai sót trong đối số match_type và thứ tự của giá trị cần tìm. Ví dụ:
- Nếu match_type là 1 hoặc không được chỉ định thì giá trị trong lookup_array phải được sắp xếp theo thứ tự tăng dần.
- Nếu match_type là -1 thì giá trị trong lookup_array phải được sắp xếp theo thứ tự giảm dần.
2. Lỗi #VALUE
Lỗi #VALUE xuất hiện khi bạn đang để công thức Index dưới dạng tham chiếu. Vì vậy, bạn cần chuyển các hàm này sang dạng mảng bằng lệnh Ctrl+Shift+Enter.
Mặt khác, lỗi này cũng xuất hiện nếu như giá trị bạn nhập không đúng định dạng của hàm. Ví dụ, trong column_num cần giá trị là số nhưng nếu bạn đưa dữ liệu dạng chữ thì sẽ bị lỗi #VALUE.
VII. Tổng kết
Cách dùng hàm Index và Match đơn giản và có nhiều ưu điểm. Do đó, nhiều người đã sử dụng kết hợp các hàm này trong việc tìm kiếm và xác định dữ liệu cần tìm. Muaban.net mong rằng bạn có thể áp dụng hiệu quả các hàm trên trong công việc của mình. Ngoài ra Muaban.net là website rao vặt uy tín về bất động sản, việc làm… Hãy truy cập ngay website để biết thêm nhiều thông tin nhé.
Xem thêm:
- Pivot table là gì? Hướng dẫn sử dụng pivot table chi tiết
- 3 cách tạo bảng trong Excel dễ làm và cực hiệu quả
- 6 cách đánh số trang trong Excel 2003, 2007, 2010, 2016 đơn giản, nhanh chóng nhất
