Hướng Dẫn Chi Tiết Sử Dụng Hàm INDEX và MATCH Trong Excel

Bất tiện khi sử dụng các hàm Vlookup và hàm Hlookup

Hàm Vlookup và Hlookup là những công cụ quen thuộc trong Excel, hỗ trợ đắc lực cho việc dò tìm và tra cứu dữ liệu. Tuy nhiên, chúng lại bộc lộ nhiều hạn chế trong các tình huống phức tạp. Chẳng hạn, khi dữ liệu cần xử lý nằm ở hai bảng khác nhau trên cùng một Sheet, hàm Hlookup chỉ có thể trả về giá trị từ các cột nằm bên dưới nó. Tương tự, Vlookup chỉ trả về giá trị khi nó nằm ở cột bên phải của hàm. Sự ràng buộc này gây ra nhiều bất tiện. Việc chèn hoặc xóa cột trong bảng tính có thể làm sai lệch kết quả tìm kiếm. Hơn nữa, các hàm này còn có giới hạn về độ dài ký tự tìm kiếm (tối đa, vượt quá giới hạn này sẽ trả về lỗi #VALUE!. Đối với các bảng tính có dung lượng lớn hoặc công thức phức tạp, Vlookup và Hlookup dễ dẫn đến sai sót và nhầm lẫn trong quá trình xử lý.

Cú pháp và cách dùng hàm INDEX trong Excel

Hàm INDEX là một công cụ mạnh mẽ, cho phép bạn truy xuất giá trị từ một phạm vi bảng tính dựa trên số thứ tự hàng và cột được chỉ định. Cú pháp cơ bản của hàm INDEX như sau:

=INDEX(array, row_num, [column_num])

Trong đó:

• array: Là phạm vi chứa dữ liệu bạn muốn truy xuất.
• row_num: Số thứ tự của hàng trong phạm vi chứa giá trị kết quả. Nếu bỏ trống đối số này, đối số column_num là bắt buộc.
• column_num: Số thứ tự của cột trong phạm vi chứa giá trị kết quả. Nếu bỏ trống đối số này, đối số row_num là bắt buộc.

Khi cả row_num và column_num đều được cung cấp, hàm INDEX sẽ trả về giá trị tại ô là giao điểm của hàng và cột được xác định.

Ví dụ: =INDEX(A1:C10, 2, 3) sẽ trả về giá trị tại ô C2, là giao điểm của hàng thứ 2 và cột thứ 3 trong phạm vi A1:C10.

Cách sử dụng hàm MATCH để tìm vị trí dữ liệu

Hàm MATCH có chức năng tìm kiếm một giá trị xác định trong một phạm vi (một hàng hoặc một cột) và trả về vị trí tương đối của giá trị đó trong phạm vi. Cú pháp của hàm MATCH như sau:

=MATCH(lookup_value, lookup_array, [match_type])

Trong đó:

• lookup_value: Giá trị bạn muốn tìm kiếm.
• lookup_array: Phạm vi (một hàng hoặc một cột) mà bạn muốn tìm kiếm.
• match_type: (Tùy chọn) Xác định loại khớp. 0 để khớp chính xác, 1 để khớp nhỏ hơn hoặc bằng (mặc định), -1 để khớp lớn hơn hoặc bằng.

Bài tập 1: Tìm vị trí theo dòng với hàm MATCH

Giả sử chúng ta có bảng dữ liệu sau và muốn tìm thông tin của người có tên “Lê Thị Thủy”. Trước tiên, ta cần xác định xem người này nằm ở vị trí thứ mấy trong bảng. Hàm MATCH sẽ giúp chúng ta làm điều này:

=MATCH(“Lê Thị Thủy”, C3:C8, 0)

Tại ô D12, nhập tên cần tìm là “Lê Thị Thủy”. Cột “Họ tên” là vùng C3:C8. Công thức này sẽ trả về kết quả là số 3, cho biết “Lê Thị Thủy” nằm ở dòng thứ 3 trong phạm vi dữ liệu.

Kết hợp hàm INDEX và MATCH để dò tìm dữ liệu linh hoạt

Sức mạnh thực sự của INDEX và MATCH nằm ở khả năng kết hợp chúng với nhau để tạo ra các công thức dò tìm dữ liệu mạnh mẽ, vượt trội hơn VLOOKUP ở nhiều khía cạnh.

Bài tập 2: Tìm Email theo tên

Dựa vào bảng dữ liệu ở Bài tập 1, chúng ta muốn tìm địa chỉ Email của “Lê Thị Thủy”. Chúng ta biết rằng “Lê Thị Thủy” nằm ở dòng thứ 3 (đã tìm bằng MATCH). Địa chỉ Email nằm ở cột E. Công thức kết hợp sẽ là:

=INDEX(E3:E8, MATCH(D12, C3:C8, 0))

Trong công thức này:

• Hàm MATCH(D12, C3:C8, 0) sẽ trả về số 3 (vị trí dòng của “Lê Thị Thủy”).
• Hàm INDEX(E3:E8, 3) sẽ truy xuất giá trị tại dòng thứ 3 trong cột E (vùng E3:E8), đó chính là địa chỉ Email cần tìm.

Bài tập 3: Dò tìm từ phải qua trái

Thông thường, chúng ta hay dò tìm dữ liệu từ trái sang phải. Tuy nhiên, với INDEX và MATCH, việc dò tìm từ phải sang trái (ví dụ: tìm tên dựa trên Email) trở nên đơn giản.

Để tìm tên dựa vào Email, ta thực hiện như sau:

1. Sử dụng MATCH để tìm vị trí dòng của Email trong cột E: MATCH(D12, E3:E8, 0)
2. Sử dụng INDEX để chỉ định cột chứa kết quả cần tìm là cột Họ tên (cột C): INDEX(C3:C8, ...)

Công thức hoàn chỉnh:

=INDEX(C3:C8, MATCH(D12, E3:E8, 0))

Công thức này sẽ tìm Email trong cột E và trả về tên tương ứng trong cột C, dù cột C nằm bên trái cột E.

Bài tập 4: Xác định đơn giá theo nhiều điều kiện

Khi cần dò tìm dữ liệu dựa trên nhiều điều kiện, INDEX và MATCH lại càng tỏ ra vượt trội. Ví dụ, chúng ta muốn xác định đơn giá của một mặt hàng trong một công ty cụ thể.

Giả sử ta có bảng đơn giá với các cột Mã hàng, Tên hàng, Đơn giá và các hàng là Mã công ty. Để xác định đơn giá của mã hàng H2 trong công ty H3, ta cần xác định đồng thời vị trí hàng và cột.

1. Xác định vị trí cột Mã hàng: Dùng MATCH để tìm vị trí của mã hàng trong dòng tiêu đề (dòng 3). Ví dụ: MATCH(H2, A3:E3, 0) sẽ trả về vị trí cột của mã hàng.
2. Xác định vị trí dòng Mã công ty: Dùng MATCH để tìm vị trí của mã công ty trong cột A (cột chứa mã công ty). Ví dụ: MATCH(H3, A3:A7, 0) sẽ trả về vị trí dòng của mã công ty.

Sau khi có vị trí hàng và cột, ta dùng INDEX để truy xuất giá trị tại giao điểm đó. Giả sử bảng dữ liệu đơn giá nằm trong vùng B4:E7, với tiêu đề ở dòng 3 và mã công ty ở cột A. Công thức tìm đơn giá sẽ như sau:

=INDEX(B4:E7, MATCH(H3, A4:A7, 0), MATCH(H2, B3:E3, 0))

Trong đó:

• MATCH(H3, A4:A7, 0) xác định vị trí dòng (ví dụ: trả về 3 nếu H3 là công ty thứ 3 trong danh sách).
• MATCH(H2, B3:E3, 0) xác định vị trí cột (ví dụ: trả về 2 nếu H2 là mã hàng thứ 2 trong dòng tiêu đề).
• INDEX(B4:E7, 3, 2) sẽ trả về giá trị tại hàng 3, cột 2 trong vùng dữ liệu B4:E7.

Ưu điểm vượt trội của hàm INDEX và MATCH so với VLOOKUP/HLOOKUP

Việc kết hợp hàm INDEX và MATCH mang lại nhiều lợi ích đáng kể so với việc chỉ sử dụng VLOOKUP hay HLOOKUP:

• Linh hoạt trong dò tìm: Có thể dò tìm dữ liệu từ trái sang phải, từ phải sang trái, hoặc theo cả hai chiều cùng lúc.
• Xử lý nhiều điều kiện: Dễ dàng kết hợp để dò tìm dựa trên hai hoặc nhiều tiêu chí, điều mà VLOOKUP/HLOOKUP gặp khó khăn.
• Không bị ảnh hưởng bởi việc chèn/xóa cột/hàng: Kết quả dò tìm dựa trên vị trí tương đối, nên việc thay đổi cấu trúc bảng tính ít ảnh hưởng đến công thức.
• Hiệu suất tốt hơn trên bảng tính lớn: Thường hoạt động nhanh hơn VLOOKUP/HLOOKUP khi xử lý lượng dữ liệu khổng lồ.
• Không bị giới hạn: Có thể dò tìm với chuỗi ký tự dài mà không gặp lỗi.

Các trường hợp ứng dụng thực tế của INDEX MATCH

Bộ đôi hàm INDEX và MATCH có thể áp dụng trong vô số tình huống thực tế như:

• Tạo báo cáo bán hàng động, tự động cập nhật theo mã sản phẩm hoặc khu vực.
• Xây dựng hệ thống quản lý nhân sự, tra cứu thông tin nhân viên theo mã ID hoặc tên.
• Thiết lập bảng giá tự động dựa trên nhiều thuộc tính của sản phẩm.
• Xử lý dữ liệu từ nhiều nguồn khác nhau và tổng hợp vào một báo cáo duy nhất.

Lưu ý khi sử dụng hàm INDEX và MATCH

Để sử dụng hàm INDEX và MATCH một cách hiệu quả, bạn cần lưu ý một số điểm sau:

• Đảm bảo match_type trong hàm MATCH được đặt là 0 nếu bạn cần khớp chính xác hoàn toàn giá trị.
• Kiểm tra kỹ phạm vi array trong INDEX và lookup_array trong MATCH để tránh sai lệch.
• Nếu bảng dữ liệu có nhiều cột hoặc hàng, hãy cân nhắc việc đặt tên cho các phạm vi để công thức dễ đọc và quản lý hơn.
• Hiểu rõ cấu trúc dữ liệu của bạn để thiết lập công thức phù hợp, tránh các lỗi phổ biến.

Tổng kết

Hàm INDEX và MATCH là bộ đôi quyền lực trong Excel, mang đến sự linh hoạt và khả năng xử lý dữ liệu vượt trội so với các hàm dò tìm truyền thống như VLOOKUP hay HLOOKUP. Bằng cách nắm vững cú pháp và thực hành qua các bài tập, bạn có thể ứng dụng chúng vào vô số tác vụ, từ những yêu cầu dò tìm đơn giản đến các bài toán phức tạp đòi hỏi xử lý đa điều kiện. Hãy bắt đầu áp dụng ngay hôm nay để nâng cao hiệu suất làm việc với Excel của bạn.