Cách sử dụng hàm INDEX kết hợp hàm MATCH để dò tìm “đúng một phát ăn ngay”

Cách sử dụng hàm INDEX kết hợp hàm MATCH để dò tìm dữ liệu là một kỹ thuật mạnh mẽ trong Excel, giúp người dùng thực hiện các tác vụ tra cứu phức tạp một cách linh hoạt và hiệu quả hơn so với các hàm dò tìm truyền thống như VLOOKUP hay HLOOKUP. Bài viết này sẽ hướng dẫn chi tiết về cách thức hoạt động, cú pháp, và các ứng dụng nâng cao của bộ đôi hàm này, giúp bạn làm chủ kỹ năng dò tìm dữ liệu trong Excel.

1. Tại sao nên dùng hàm INDEX kết hợp hàm MATCH thay vì VLOOKUP?

Trong Excel, cách sử dụng hàm INDEX để dò tìm và cách sử dụng hàm MATCH để dò tìm là hai hàm riêng biệt nhưng khi kết hợp lại, chúng tạo thành một công cụ dò tìm dữ liệu vô cùng mạnh mẽ.

• Hàm INDEX được dùng để trả về giá trị tại một vị trí cụ thể trong một vùng dữ liệu (phạm vi hàng, cột hoặc cả bảng).
• Hàm MATCH giúp tìm kiếm một giá trị trong một hàng hoặc cột và trả về vị trí tương đối của giá trị đó trong vùng tìm kiếm.

So với hàm VLOOKUP, sự kết hợp này mang lại nhiều ưu điểm vượt trội:

• Linh hoạt hơn: VLOOKUP chỉ có thể dò tìm từ trái sang phải, tức là giá trị dò tìm phải nằm ở cột đầu tiên của bảng dữ liệu. Hàm INDEX kết hợp hàm MATCH không có giới hạn này, bạn có thể dò tìm ở bất kỳ cột nào trong bảng, dù cột chứa giá trị cần tìm nằm bên trái hay bên phải cột chứa giá trị dò tìm.
• Không bị giới hạn cột bên trái: Đây là lợi thế lớn nhất. Nếu giá trị bạn muốn trả về nằm ở cột bên trái của giá trị dò tìm, VLOOKUP sẽ không thể thực hiện được, nhưng INDEX + MATCH thì có.
• Chuyên nghiệp hơn: Cung cấp giải pháp dò tìm chính xác và đáng tin cậy hơn, đặc biệt với các bảng dữ liệu lớn và phức tạp.

2. Cách hoạt động của hàm INDEX và MATCH

Để hiểu rõ cách sử dụng hàm INDEX kết hợp hàm MATCH để dò tìm, trước tiên cần nắm vững cách thức hoạt động của từng hàm:

Hàm INDEX:

• Mục đích: Trả về giá trị của một ô tại giao điểm của một hàng và một cột cụ thể trong một bảng hoặc vùng dữ liệu.
• Cú pháp: =INDEX(array, row_num, [column_num])
• array: Vùng dữ liệu chứa giá trị bạn muốn trả về.
• row_num: Số thứ tự của hàng trong array mà bạn muốn lấy giá trị.
• [column_num]: (Tùy chọn) Số thứ tự của cột trong array mà bạn muốn lấy giá trị. Nếu array chỉ là một hàng hoặc một cột, bạn không cần dùng tham số này.
• Ví dụ: =INDEX(A1:C5, 3, 2) sẽ trả về giá trị tại ô B3 (hàng 3, cột 2 trong vùng A1:C5).

Hàm MATCH:

• Mục đích: Xác định vị trí tương đối của một giá trị trong một hàng hoặc cột.
• Cú pháp: =MATCH(lookup_value, lookup_array, [match_type])
• lookup_value: Giá trị bạn muốn tìm.
• lookup_array: Phạm vi một hàng hoặc một cột mà bạn muốn tìm lookup_value trong đó.
• [match_type]: (Tùy chọn) Kiểu đối chiếu.
• 0: Tìm kiếm chính xác (giá trị bằng nhau). Đây là loại thường dùng nhất.
• 1: Tìm kiếm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value (yêu cầu lookup_array phải được sắp xếp tăng dần).
• -1: Tìm kiếm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value (yêu cầu lookup_array phải được sắp xếp giảm dần).
• Ví dụ: =MATCH("Áo sơ mi", A1:A10, 0) sẽ trả về số thứ tự của ô chứa "Áo sơ mi" trong vùng A1:A10. Nếu "Áo sơ mi" ở ô A5, hàm sẽ trả về 5.

3. Cách kết hợp hàm INDEX + MATCH để dò tìm dữ liệu đơn giản

Đây là ứng dụng phổ biến nhất của cách sử dụng hàm INDEX kết hợp hàm MATCH để dò tìm. Hàm MATCH sẽ cung cấp số thứ tự hàng (hoặc cột) cho hàm INDEX.

Cú pháp kết hợp: =INDEX(range, MATCH(lookup_value, lookup_array, 0))

range: Vùng dữ liệu chứa các giá trị mà bạn muốn trả về (có thể là một cột hoặc nhiều cột).

lookup_value: Giá trị bạn muốn tìm.

lookup_array: Vùng dữ liệu chứa giá trị lookup_value (phải là một cột duy nhất).

0: Đảm bảo tìm kiếm chính xác.

Ví dụ dò tìm:

• Dò tìm tên nhân viên theo mã: Giả sử bạn có bảng dữ liệu từ A1:C10 với Mã nhân viên ở cột A, Tên nhân viên ở cột B. Để tìm tên nhân viên có mã "NV005" (ô E1), bạn nhập: =INDEX(B1:B10, MATCH(E1, A1:A10, 0)) Hàm MATCH(E1, A1:A10, 0) sẽ tìm vị trí của "NV005" trong cột Mã nhân viên. Giả sử nó ở hàng thứ 5, hàm MATCH sẽ trả về 5. Sau đó, INDEX(B1:B10, 5) sẽ trả về giá trị ở hàng thứ 5 của cột B, tức là tên của nhân viên có mã "NV005".
• Dò tìm điểm thi theo tên: Tương tự, nếu bạn có danh sách tên và điểm, bạn có thể áp dụng công thức tương tự.

Lưu ý: Luôn sử dụng match_type là 0 để đảm bảo tìm kiếm chính xác giá trị. Nếu không tìm thấy, hàm MATCH sẽ trả về lỗi #N/A.

4. Ứng dụng nâng cao: Dò tìm theo cả hàng và cột

Một trong những ưu điểm vượt trội của cách sử dụng hàm INDEX kết hợp hàm MATCH để dò tìm là khả năng dò tìm theo hai chiều (dò tìm theo hàng và cột).

• Sử dụng INDEX với 2 MATCH: Bạn có thể lồng hai hàm MATCH vào trong hàm INDEX: một MATCH để xác định vị trí hàng và một MATCH khác để xác định vị trí cột.
• Cú pháp mở rộng: =INDEX(array, MATCH(x, range1, 0), MATCH(y, range2, 0))
  • array: Toàn bộ bảng dữ liệu mà bạn muốn dò tìm.
  • x: Giá trị bạn muốn tìm trong hàng (ví dụ: tên học sinh).
  • range1: Vùng dữ liệu chứa x (ví dụ: cột chứa tên học sinh).
  • y: Giá trị bạn muốn tìm trong cột (ví dụ: tên môn học).
  • range2: Vùng dữ liệu chứa y (ví dụ: hàng chứa tên các môn học).
  • 0: Đảm bảo tìm kiếm chính xác cho cả hai hàm MATCH.

Ví dụ: Tra điểm theo tên học sinh và môn học: Giả sử bạn có bảng điểm từ A1:E10, với tên học sinh ở cột A (A2:A10) và tên các môn học ở hàng 1 (B1:E1). Để tìm điểm môn "Toán" của học sinh "Nguyễn Văn A" (ô G1 là tên học sinh, ô G2 là tên môn học), bạn nhập: =INDEX(A1:E10, MATCH(G1, A1:A10, 0), MATCH(G2, A1:E1, 0)) Hàm MATCH đầu tiên sẽ tìm vị trí của "Nguyễn Văn A" trong cột A. Hàm MATCH thứ hai sẽ tìm vị trí của "Toán" trong hàng 1. Hàm INDEX sau đó sẽ trả về giá trị tại giao điểm của hàng và cột tìm được.

5. Mẹo xử lý lỗi thường gặp khi dùng INDEX + MATCH

Khi thực hiện cách sử dụng hàm INDEX kết hợp hàm MATCH để dò tìm, bạn có thể gặp một số lỗi phổ biến:

Lỗi #N/A:

• Lý do: Thường xảy ra khi hàm MATCH không tìm thấy lookup_value trong lookup_array.
• Khắc phục: Kiểm tra lại chính tả lookup_value (có thể bị sai ký tự, dấu cách thừa), đảm bảo lookup_value tồn tại trong lookup_array, và kiểu dữ liệu của lookup_value khớp với kiểu dữ liệu trong lookup_array (ví dụ: số với số, văn bản với văn bản).

Lỗi #REF!:

• Lý do: Xảy ra khi một trong các tham chiếu trong công thức bị lỗi hoặc không hợp lệ (ví dụ: vùng dữ liệu bị xóa, hoặc tham chiếu ra ngoài phạm vi cho phép).
• Khắc phục: Kiểm tra lại tất cả các vùng array, lookup_array trong công thức để đảm bảo chúng vẫn hợp lệ.

Lưu ý vùng dữ liệu: độ dài phải khớp: Đảm bảo rằng range trong hàm INDEX và lookup_array trong hàm MATCH (khi dò tìm theo cột) có cùng số lượng hàng. Tương tự, khi dò tìm theo cột, range và lookup_array (khi dò tìm theo hàng) phải có cùng số lượng cột.

Nắm vững cách sử dụng hàm INDEX kết hợp hàm MATCH để dò tìm dữ liệu là một kỹ năng vô cùng giá trị trong Excel, giúp bạn thực hiện các tác vụ dò tìm một cách linh hoạt, mạnh mẽ và chuyên nghiệp hơn rất nhiều so với việc chỉ dùng VLOOKUP. Từ việc dò tìm đơn giản một giá trị cho đến việc tra cứu theo hai tiêu chí, sự kết hợp của hai hàm này sẽ mở ra nhiều khả năng mới trong việc quản lý và phân tích dữ liệu. Hãy thực hành thường xuyên để làm chủ bộ đôi quyền năng này nhé!