Cách sử dụng hàm Index kết hợp với hàm Match trong Excel

Hàm Index là một trong những hàm phổ biến nhất trong Excel dùng để tìm kiếm dữ liệu trong một dải ô. Do đó, bài viết dưới đây từ Mytour sẽ giúp bạn hiểu rõ hơn về cách sử dụng hàm Index, cách áp dụng với hàm Match và một số vấn đề liên quan khác.

I. Giới thiệu về hàm Index và hàm Match

Khi nghiên cứu về hàm Index, nhiều người thường nhầm lẫn giữa hàm Index và hàm Match. Trên thực tế, hai hàm này khác nhau cả về ý nghĩa lẫn cấu trúc. Tuy nhiên, chúng thường được sử dụng song song để tìm kiếm và nhập dữ liệu vào một dải ô đã xác định.

1. Cấu trúc của hàm Index

Hàm Index dùng để trả về giá trị tại vị trí được chỉ định trong một phạm vi hoặc mảng. Cấu trúc của hàm Index như sau:

=INDEX(array;row_num;column_num).

Trong đó:

• “array” là một vùng ô hoặc một mảng hằng số;
• “row_num” là chỉ số hàng trong mảng cần lấy dữ liệu;
• “column_num” là chỉ số cột trong mảng cần lấy dữ liệu.

2. Cấu trúc của hàm Match

Khác với hàm Index trong Excel, hàm Match được sử dụng để trả về vị trí tương đối của một giá trị trong một phạm vi nhất định. 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 nằm trong mảng “lookup_array”;
• “lookup_array” là mảng (hay còn gọi là vùng tìm kiếm) được sử dụng để dò tìm;
• “match_type” là kiểu tìm kiếm được áp dụng.

II. Sự khác biệt giữa hàm Index, Match với hàm Vlookup, Hlookup

Hàm Index + Match và Vlookup + Hlookup thường được sử dụng để tìm kiếm dữ liệu. Tuy nhiên, hàm Index + Match cung cấp sự linh hoạt hơn trong việc tìm kiếm. Đối với hàm Vlookup, giá trị trả về cần nằm ở cột bên phải so với giá trị tìm kiếm, trong khi hàm Hlookup yêu cầu giá trị trả về nằm ở hàng bên dưới giá trị dò tìm.

Vì vậy, nhiều người chọn cách sử dụng hàm Index thay cho Vlookup. Bên cạnh đó, hàm Index còn mang lại những lợi ích khác như sau:

III. Lợi ích của việc sử dụng hàm Index và hàm Match

Ngoài việc tìm kiếm dữ liệu một cách linh hoạt hơn, hàm Index + Match còn sở hữu những ưu điểm khác như:

• Có thể xóa hoặc thêm cột mà không làm ảnh hưởng đến giá trị trả về;
• Giá trị cần tìm không bị giới hạn kích thước, trong khi Vlookup + Hlookup chỉ cho phép tối đa 255 ký tự;
• Tốc độ xử lý nhanh hơn từ 10 – 15% trong bảng tính lớn chứa nhiều dữ liệu.

IV. Ví dụ về cách sử dụng hàm Index kết hợp với hàm Match

Để bạn có thể dễ dàng áp dụng hàm Index kết hợp với hàm Match, dưới đây là hướng dẫn chi tiết cho những trường hợp phổ biến nhất:

1. Hướng dẫn sử dụng hàm INDEX với MATCH để tìm Email theo tên

Hướng dẫn ngắn gọn:

Xác định giá trị cần tìm kiếm và vùng tìm kiếm → tìm vị trí theo hàng/cột của dữ liệu cần tìm bằng hàm Match → sử dụng hàm Index và Match để lấy giá trị tại vị trí đã xác định.

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í của dữ liệu cần tìm bằng hàm Match, với công thức =match(B13,A3:A10,0);

Bước 3: Kết hợp hàm Index và Match để lấy giá trị tại vị trí vừa tìm thấy bằng công thức =index(C3:C10,match(B13, A3:A10,0)).

2. Cách sử dụng hàm INDEX kết hợp với MATCH để dò tìm từ phải sang trái

Trường hợp này làm nổi bật ưu điểm của việc kết hợp hàm Index với hàm Match. Bạn có thể tìm kiếm dữ liệu từ phải sang trái, từ trên xuống dưới mà không bị hạn chế về vị trí dữ liệu.

Hướng dẫn tóm tắt:

Xác định giá trị cần tìm và vùng tìm kiếm → xác định vị trí của dữ liệu cần tìm theo dòng/cột bằng hàm Match → sử dụng hàm Index và Match để lấy giá trị tại vị trí vừa tìm thấy.

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 chứa email liên hệ và cột tên khách hàng);

Bước 2: Sử dụng hàm Match để xác định vị trí theo dòng/cột của dữ liệu cần tìm với công thức =match(B19,C3:C10,0).

Bước 3: Kết hợp hàm Index và Match để lấy giá trị tại vị trí vừa tìm được bằng công thức =index(A3:A10,match(B19,C3:C10,0)).

3. Cách sử dụng hàm INDEX để xác định giá của mặt hàng theo nhiều điều kiện

Trong trường hợp này, để sử dụng hàm Index và hàm Match, bạn cần xác định vị trí chính xác của dữ liệu đang nằm ở hàng và cột nào. Các bước 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 thông qua 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ị bằng cách kết hợp vị trí hàng và cột vừa tìm qua hàm Match và hàm Index.

Hướng dẫn chi tiết:

Bước 1: Sử dụng hàm Match để xác định vị trí hàng của giá trị cần tìm với công thức =match(I5,A4:F4,0);

Bước 2: Sử dụng hàm Match để xác định vị trí cột của giá trị cần tìm với công thức =match(I6,A4:A9,0);

Bước 3: Kết hợp hàm Index và hàm Match để xác định giá trị bằng công thức =index(A3:F9,K6,K5).

V. Một số điểm cần chú ý khi sử dụng hàm Index

Việc sử dụng hàm Index kết hợp với hàm Match mang lại nhiều lợi ích vượt trội so với hàm Vlookup và Hlookup. Do đó, bạn hoàn toàn có thể áp dụng hàm Index + Match để tìm kiếm giá trị trong hầu hết các tình huống.

VI. Nguyên nhân gây ra lỗi khi sử dụng hàm Index kết hợp hàm Match

Hai lỗi thường gặp nhất với người mới sử dụng hàm Index và hàm Match là lỗi #NA và lỗi #VALUE. Vì vậy, cần hiểu nguyên nhân và cách khắc phục khi gặp phải hai lỗi này.

1. Lỗi #NA

• Hàm Match không tìm thấy giá trị trong phạm vi, bạn nên kiểm tra lại vùng tìm kiếm hoặc giá trị cần tìm có sai sót không. Một số lỗi thường gặp có thể là: có khoảng trắng ẩn, định dạng ô sai, hoặc vùng dữ liệu có hàng/cột bị trùng lặp,…
• Bạn đang sử dụng hàm Index và hàm Match ở dạng tham chiếu nhưng lại nhập dưới dạng công thức mảng. Do đó, bạn cần chuyển đổi các hàm này sang dạng mảng bằng tổ hợp phím Ctrl+Shift+Enter.
• Sự sai sót trong đối số match_type và thứ tự giá trị cần tìm. Ví dụ:
  • Nếu match_type là 1 hoặc không 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 xảy ra khi bạn đang sử dụng công thức Index dưới dạng tham chiếu. Do đó, bạn cần chuyển các hàm này thành dạng mảng bằng tổ hợp phím Ctrl+Shift+Enter.

Ngoài ra, lỗi này cũng có thể xuất hiện nếu giá trị bạn nhập không đúng định dạng yêu cầu của hàm. Ví dụ, trong column_num cần nhập số, nếu bạn cung cấp dữ liệu dưới dạng chữ thì sẽ dẫn đến lỗi #VALUE.