Cách kết hợp hàm INDEX và MATCH trong Excel với ví dụ dễ hiểu

Cách kết hợp hàm INDEX và MATCH trong Excel giúp dò tìm dữ liệu linh hoạt và chính xác hơn. MATCH giúp xác định vị trí cần tra cứu, còn INDEX trả về giá trị tương ứng. Nhờ đó, việc truy xuất thông tin trở nên nhanh và phù hợp với nhiều bảng tính phức tạp. Bài viết này sẽ hướng dẫn bạn áp dụng dễ hiểu và chính xác.

Cách kết hợp hàm INDEX và MATCH trong Excel

Trước khi tìm hiểu cách kết hợp, bạn cần nắm rõ cách hoạt động của hai hàm này để ứng dụng chính xác trong các bảng dữ liệu.

Hàm MATCH dùng để xác định vị trí của một giá trị trong một phạm vi dữ liệu, trả về số thứ tự tương ứng.

Cú pháp: =MATCH(lookup_value; lookup_array; match_type)

Trong đó:

• Lookup_value: Giá trị cần tìm.
• Lookup_array: Vùng chứa dữ liệu được dùng để đối chiếu.
• Match_type: Tùy chọn kiểu tìm kiếm theo điều kiện xác định.

Cú pháp hàm MATCH trong Excel

Hàm INDEX dùng để lấy dữ liệu dựa trên vị trí hàng và cột trong một bảng hoặc phạm vi bất kỳ.

Cú pháp: =INDEX(array; row_num; column_num)

Trong đó:

• array: Vùng dữ liệu cần truy xuất.
• row_num: Số thứ tự hàng cần lấy giá trị.
• column_num: Số thứ tự cột chứa giá trị cần trả về.

Cú pháp hàm INDEX trong Excel

Kết hợp hàm INDEX và MATCH trong Excel đặc biệt hữu ích khi bạn cần tra cứu dữ liệu trong bảng có nhiều hàng và cột, hoặc khi cấu trúc bảng thường xuyên thay đổi. Hai hàm này cho phép tìm kiếm theo cả chiều ngang lẫn chiều dọc, mang lại sự linh hoạt cao trong xử lý dữ liệu.

Bộ đôi hàm này hoạt động hiệu quả với các bảng tính lớn và những yêu cầu tra cứu theo nhiều tiêu chí. Nhờ đó, công thức vẫn giữ được sự ổn định và đưa ra kết quả chính xác, ngay cả khi bạn làm việc với dữ liệu phức tạp.

Kết hợp hàm INDEX và MATCH khi nào?

Dưới đây là một số cách kết hợp hàm INDEX và MATCH thường gặp kèm ví dụ minh họa chi tiết:

Ví dụ: Bạn dùng hàm INDEX và hàm MATCH để tính đơn giá sản phẩm và hãng sản xuất. Trong công thức bạn sẽ cần phải sử dụng $ (F4) để cố định giá trị phạm vi. Giả sử theo đơn giá sản phẩm và hãng sản xuất trong bảng 2 (B16:E19) thì bạn cần điền đơn giá vào bảng 1 (B4:D13).

Dùng hàm INDEX và hàm MATCH để tính đơn giá sản phẩm và hãng sản xuất

Kết hợp 2 hàm INDEX hàm MATCH để dò tìm kiếm quả giá trị cho ô D5 như sau:

Bước 1: Xác định vị trí hàng (Row) của sản phẩm

Sử dụng hàm MATCH để tìm xem tên sản phẩm (B5) nằm ở vị trí thứ mấy trong danh sách sản phẩm của Bảng 2 (B16:B19).

Công thức: =MATCH(B5,$B$16:$B$19,0)

Giải thích:

• B5: Giá trị cần tìm (ví dụ: "Case").
• $B$16:$B$19: Phạm vi tìm kiếm (cố định bằng $).
• 0: Yêu cầu tìm kiếm khớp chính xác.

Kết quả giả định: Giả sử kết quả trả về là 4, nghĩa là "Case" nằm ở hàng thứ 4 trong phạm vi tìm kiếm B16:B19.

Dùng hàm MATCH để tìm tên sản phẩm

Bước 2: Xác định vị trí cột (Column) của hãng sản xuất

Sử dụng hàm MATCH thứ hai để tìm xem hãng sản xuất ($C5$) nằm ở cột thứ mấy trong hàng tiêu đề của Bảng 2 (B16:E16).

Công thức: =MATCH(C5,$B$16:$E$16,0)

Giải thích:

• C5: Giá trị cần tìm (ví dụ: "Dell").
• $B$16:$E$16: Phạm vi tìm kiếm (cố định bằng $).

Kết quả giả định: Giả sử kết quả trả về là 2, nghĩa là "Dell" nằm ở cột thứ 2 trong phạm vi tìm kiếm B16:E16.

Dùng hàm MATCH tìm Hãng sản xuất

Bước 3: Kết hợp INDEX và MATCH để lấy đơn giá

Sử dụng hàm INDEX với toàn bộ dữ liệu Bảng 2 ($B16:E19$) và sử dụng kết quả của hai hàm MATCH trên làm đối số vị trí hàng và vị trí cột.

Công thức chung (nhập vào ô D5):

=INDEX($B$16:$E$19,MATCH(B5,$B$16:$B$19,),MATCH(C5,$B$16:$E$16,0))

Giải thích:

• $B$16:$E$19: Đây là mảng kết quả chính (toàn bộ Bảng 2), nơi hàm INDEX sẽ tìm kiếm giá trị. Phạm vi này phải được cố định tuyệt đối bằng dấu $.
• Hàm MATCH thứ nhất (Hàng) cung cấp số hàng (ví dụ: 4).
• Hàm MATCH thứ hai (Cột) cung cấp số cột (ví dụ: 2).

Kết quả giả định: Hàm sẽ tìm giá trị nằm ở hàng thứ 4 và cột thứ 2 trong mảng $B16:E19$. Giả sử giá trị này là 4 (Đơn giá).

Kết hợp hàm INDEX và hàm MATCH để lấy Đơn giá

Bước 4: Sao chép Công thức

Sau khi công thức đã được nhập đúng tại ô $D5$, bạn chỉ cần kéo (copy) công thức xuống các ô còn lại trong cột đơn giá ($D6$ đến $D13$). Nhờ việc sử dụng dấu $ (F4) để cố định phạm vi Bảng 2, công thức sẽ hoạt động chính xác cho mọi sản phẩm trong Bảng 1.

Copy công thức kết hợp cho các dòng còn lại

Khi cần sử dụng đồng thời INDEX, MATCH và VLOOKUP, bạn có thể áp dụng công thức sau:

=VLOOKUP(INDEX(vùng_tham_chiếu; hàng; cột); table_array; col_index_num; [range_lookup])

Giải thích các thành phần trong công thức:

• INDEX(vùng_tham_chiếu; hàng; cột): Trả về giá trị làm tiêu chí tìm kiếm.
• Table_array: Phạm vi dữ liệu chứa thông tin cần truy xuất.
• Col_index_num: Thứ tự cột trong bảng dữ liệu mà bạn muốn lấy kết quả.
• [Range_lookup]: Xác định kiểu dò tìm. Giá trị 0 dùng cho tìm kiếm chính xác, giá trị 1 ứng với tìm kiếm gần đúng.

Ví dụ minh họa:

Giả sử bạn có một bảng dữ liệu dùng để xác định mức điểm ưu tiên theo vùng của từng học sinh dựa trên khu vực sinh sống.

Kết hợp hàm INDEX MATCH và VLOOKUP để xác định mức điểm ưu tiên

Công thức sử dụng:

=VLOOKUP(INDEX(A2:F9;1;6); $I$2:$J$9; 2; 0)

Giải thích các thành phần:

• INDEX(A2:F9;1;6): Lấy giá trị tại dòng 1, cột 6 trong phạm vi A2:F9 để làm điều kiện tra cứu.
• $I$2:$J$9: Là vùng dữ liệu từ I2 đến J9, nơi chứa thông tin cần đối chiếu.
• 2: Chỉ định cột thứ hai trong bảng dò chứa kết quả điểm vùng.
• 0: Yêu cầu VLOOKUP tìm kiếm chính xác (dò tìm đúng giá trị).

Kết quả mức điểm ưu tiên theo từng vùng của học sinh

Để kết hợp hàm INDEX, MATCH và IF, bạn có thể áp dụng công thức tổng quát như sau:

=IF(INDEX(mảng_dữ_liệu; MATCH(khóa_tìm_kiếm; dải_ô; loại_tìm_kiếm); số_cột) + điều_kiện; giá_trị_nếu_đúng; giá_trị_nếu_sai)

Giải thích các thành phần trong công thức:

• Mảng_dữ_liệu: Vùng dữ liệu hoặc một dãy ô cố định mà hàm INDEX sẽ lấy kết quả.
• Khóa_tìm_kiếm: Giá trị dùng để tra cứu vị trí trong dữ liệu.
• Dải_ô: Phạm vi chứa danh sách giá trị tham chiếu, thường chỉ gồm một hàng hoặc một cột.
• Loại_tìm_kiếm: Xác định cách MATCH tìm kiếm:
• 0: Tìm khớp chính xác.
• 1: Tìm kiếm gần đúng (giá trị nhỏ nhất nhưng vẫn ≤ khóa tìm kiếm).
• -1: Tìm kiếm gần đúng theo thứ tự giảm dần (giá trị lớn nhất nhưng vẫn ≥ khóa tìm kiếm).
• Số_cột: Thể hiện vị trí cột trong mảng mà bạn muốn lấy dữ liệu.
• Giá_trị_nếu_đúng: Kết quả trả về khi điều kiện trong hàm IF thỏa mãn.
• Giá_trị_nếu_sai: Kết quả trả về khi điều kiện không đúng.

Cú pháp Kết hợp hàm INDEX MATCH và hàm IF

Ví dụ minh họa:

Giả sử bạn có một bảng dữ liệu dùng để kiểm tra xem mặt hàng Bim Bim có đạt chỉ tiêu KPI hay không. Điều kiện đặt ra là số lượng bán phải vượt mức 10 sản phẩm mới được xem là hoàn thành KPI.

Bảng dữ liệu để kiểm tra các mặt hàng có đạt chỉ tiêu KPI hay không

Công thức sử dụng:

=IF(INDEX(A2:F5,MATCH("Bim Bim",A2:A5,0),3)>10,"Đạt","Không đạt")

Giải thích cách hoạt động:

• MATCH tìm vị trí của dòng chứa sản phẩm bánh quy trong danh sách A2:A5.
• INDEX dựa vào dòng tìm được để lấy số lượng đã bán tại cột thứ 3 trong vùng A2:F5.
• Hàm IF so sánh số lượng đó với ngưỡng 10:
  • Nếu lớn hơn 10 → kết quả trả về là "Đạt".
  • Nếu không đạt mức này → trả về "Không đạt"

Kết quả kiểm tra mặt hàng đạt KPI

Khi áp dụng hàm INDEX và MATCH trong cùng một công thức, người dùng dễ gặp một số lỗi phổ biến sau:

Nguyên nhân:

Lỗi này xảy ra khi phạm vi mà hàm INDEX trả về không đồng nhất với vùng tra cứu của MATCH. Một trường hợp khác là MATCH không tìm thấy vị trí phù hợp, khiến INDEX không thể trả về giá trị hợp lệ.

Cách khắc phục:

• Kiểm tra lại vùng tham chiếu của INDEX, bảo đảm rằng phạm vi này nằm trong giới hạn dữ liệu hợp lệ.
• Soát lại dải ô MATCH đang dò tìm để chắc chắn rằng giá trị tìm kiếm thực sự tồn tại.
• Đảm bảo hai hàm tham chiếu cùng cấu trúc dữ liệu (số hàng/cột tương đồng).

Lỗi #REF khi kết hợp hàm INDEX và MATCH

Nguyên nhân:

Lỗi #N/A xuất hiện khi giá trị cần tra cứu không có trong dải dữ liệu. Lỗi này tương tự như khi sử dụng VLOOKUP và không tìm thấy giá trị khớp chính xác.

Cách khắc phục:

• Kiểm tra lại dữ liệu đầu vào xem có lỗi chính tả, thừa dấu cách hoặc sai định dạng không.
• Đảm bảo dải ô tra cứu chứa đúng giá trị cần tìm.

Lỗi #N/A khi kết hợp hàm INDEX và MATCH

Với những hướng dẫn trên, bạn có thể dễ dàng hiểu cách kết hợp hai hàm INDEX và MATCH trong Excel cũng như áp dụng linh hoạt trong nhiều tình huống xử lý dữ liệu. Hãy theo dõi chuyên mục Kinh nghiệm tại Siêu Thị Điện Máy - Nội Thất Chợ Lớn để học thêm nhiều bí quyết Excel hữu dụng mỗi ngày.

Nếu bạn đang tìm thiết bị hỗ trợ học tập và làm việc, các mẫu điện thoại và máy tính bảng cấu hình mạnh, hiển thị sắc nét đang giảm giá tại Siêu Thị Điện Máy - Nội Thất Chợ Lớn sẽ là lựa chọn đáng cân nhắc.