Cách sử dụng hàm Index Match trong Excel kèm ví dụ thực tế

Bạn đã biết cách kết hợp hàm Index Match chưa? Hãy cùng AntBook tìm hiểu cách sử dụng hàm INDEX và MATCH trong Excel để tra cứu dữ liệu linh hoạt, chính xác và chuyên nghiệp nhé!

1. Khái niệm hàm Index và Match trong Excel là gì?

Hàm INDEX MATCH thực chất là sự kết hợp giữa hai hàm riêng biệt: INDEX và MATCH, nhằm mục đích tìm kiếm và trích xuất dữ liệu một cách chính xác và linh hoạt hơn so với hàm VLOOKUP thông thường. Để hiểu rõ cách hoạt động, hãy cùng AntBook phân tích từng hàm một.

Khái niệm hàm Index và Match trong Excel

1.1 Hàm MATCH – Tìm vị trí của giá trị trong một dãy

Hàm MATCH có nhiệm vụ tìm kiếm một giá trị xác định trong một phạm vi (mảng), sau đó trả về vị trí tương đối (thứ tự) của giá trị đó trong phạm vi tìm kiếm.

Cú pháp:

=MATCH(lookup_value, lookup_array, [match_type])

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

• lookup_value: Giá trị cần tìm kiếm. Có thể là số, chữ, giá trị logic (TRUE/FALSE) hoặc một tham chiếu đến ô có chứa giá trị đó.
• lookup_array: Phạm vi (mảng) chứa giá trị cần tìm.
• match_type (tùy chọn): Kiểu tìm kiếm. Có 3 lựa chọn:
  • 1 (hoặc bỏ qua): Tìm giá trị lớn nhất nhỏ hơn hoặc bằng lookup_value – dữ liệu cần được sắp xếp tăng dần.
  • 0: Tìm chính xác giá trị bằng lookup_value.
  • -1: Tìm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value – dữ liệu cần được sắp xếp giảm dần.

1.2 Hàm INDEX – Trả về giá trị tại vị trí xác định

Hàm INDEX dùng để lấy giá trị tại một vị trí cụ thể, giao giữa dòng và cột trong một mảng dữ liệu.

Cú pháp dạng mảng:

=INDEX(array, row_num, [column_num])

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

• array: Vùng dữ liệu cần lấy giá trị.
• row_num: Chỉ số hàng trong vùng dữ liệu.
• column_num (tùy chọn): Chỉ số cột trong vùng dữ liệu.

Cú pháp dạng tham chiếu (dùng khi có nhiều vùng dữ liệu):

=INDEX(reference, row_num, [column_num], [area_num])

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

• reference: Một hoặc nhiều vùng dữ liệu.
• row_num: Số thứ tự hàng muốn lấy.
• column_num (tùy chọn): Số thứ tự cột muốn lấy.
• area_num (tùy chọn): Số thứ tự của vùng dữ liệu (nếu có nhiều vùng trong reference).

Khi kết hợp, hàm MATCH sẽ tìm vị trí của giá trị cần tra cứu, và sau đó hàm INDEX sẽ lấy giá trị tại vị trí đó. Sự kết hợp này giúp việc tìm kiếm linh hoạt hơn, không bị giới hạn như VLOOKUP (phải tìm từ trái qua phải) và hoạt động tốt với các bảng lớn.

Kết hợp hàm Index và hàm Match trong Excel

Ví dụ kết hợp:

=INDEX(B2:B10, MATCH(“Giá trị cần tìm”, A2:A10, 0))

Giải thích cú pháp:

• Hàm MATCH tìm vị trí của “Giá trị cần tìm” trong cột A.
• Hàm INDEX dùng vị trí đó để trả về giá trị tương ứng trong cột B.

2. Hướng dẫn cách kết hợp hàm Index Match trong Excel

Để hiểu cách dùng kết hợp hai hàm INDEX và MATCH, hãy cùng xem một ví dụ minh họa cụ thể:

Ví dụ: Giả sử bạn có một bảng dữ liệu gồm hai cột: cột Họ tên (B3:B8) và cột Địa chỉ (C3:C8). Mục tiêu là tìm địa chỉ tương ứng với một tên được nhập trong ô C10.

Bước 1: Trong ô nơi bạn muốn hiển thị kết quả (ví dụ ô D10), nhập công thức sau:

=INDEX(C3:C8,MATCH(C10,B3:B8,0))

Nhập công thức kết hợp hàm Index và hàm Match

• C3:C8: là vùng chứa địa chỉ bạn muốn lấy.
• C10: là ô chứa tên cần tìm.
• B3:B8: là vùng chứa tên để dò tìm vị trí.
• 0: yêu cầu MATCH tìm chính xác tên khớp.

Bước 2: Nhấn Enter, Excel sẽ trả về địa chỉ tương ứng với tên được nhập trong ô C10.

Như vậy, bạn đã sử dụng thành công kết hợp INDEX + MATCH để tìm dữ liệu chính xác theo tên trong bảng. Cách làm này rấ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.

Kết quả hiển thị

3. Kết hợp hàm Index Match nhiều điều kiện trong Excel

Khi bạn cần tra cứu dữ liệu theo nhiều điều kiện, chẳng hạn như tra giá sản phẩm theo tên sản phẩm và hãng sản xuất, bạn có thể kết hợp hàm INDEX và MATCH để thực hiện.

Ví dụ bạn có 2 bảng:

• Bảng 1 (B3:D12): cần điền đơn giá sản phẩm.
• Bảng 2 (B15:E18): chứa dữ liệu tra cứu gồm tên sản phẩm và đơn giá theo từng hãng.

Ví dụ về kết hợp hàm Index Match nhiều điều kiện

Giả sử bạn cần điền đơn giá cho ô D4, dựa trên tên sản phẩm ở ô B4 và hãng sản xuất ở ô C4.

Bước 1: Dùng MATCH để tìm số thứ tự hàng (tên sản phẩm)

Bạn cần xác định vị trí của sản phẩm trong cột tên sản phẩm ở bảng 2:

=MATCH(B4,$B$15:$B$18,0)

Dùng MATCH để tìm số thứ tự hàng

Nếu ô B4 là “CDRom”, kết quả trả về là 4, nghĩa là “CDRom” nằm ở dòng thứ 4 trong bảng 2 (tính trong vùng từ B15 đến B18).

Bước 2: Dùng MATCH để tìm số thứ tự cột (hãng sản xuất)

Tiếp theo, bạn xác định vị trí của hãng sản xuất trong hàng tiêu đề của bảng 2:

=MATCH(C4,$B$15:$E$15,0)

Dùng MATCH để tìm số thứ tự cột

Nếu ô C4 là “Samsung”, kết quả là 2, tức là “Samsung” nằm ở cột thứ 2 trong bảng 2 (tính từ B đến E).

Bước 3: Kết hợp INDEX và MATCH để tìm giá trị
Dựa vào kết quả của hai hàm MATCH trên, bạn sử dụng INDEX để lấy đúng đơn giá:

=INDEX($B$15:$E$18, MATCH(B4,$B$15:$B$18,0), MATCH(C4,$B$15:$E$15,0))

Công thức này sẽ trả về đơn giá tương ứng với tên sản phẩm và hãng sản xuất đã chọn.

Kết hợp INDEX và MATCH để tìm giá trị

Ví dụ: Nếu sản phẩm là “Mouse” và hãng là “Samsung”, kết quả trả về là 5 (lấy từ bảng 2).

Bước 4: Sao chép công thức
Cuối cùng, bạn chỉ cần copy công thức từ ô D4 xuống các ô còn lại trong cột, Excel sẽ tự động tính đơn giá tương ứng cho từng dòng dựa trên sản phẩm và hãng đã chọn.

Sao chép công thức

Việc dùng kết hợp INDEX + MATCH với nhiều điều kiện như trên giúp bạn tra cứu dữ liệu nhanh chóng, chính xác và linh hoạt hơn rất nhiều so với các hàm thông thường như VLOOKUP.

4. Cách sử dụng hàm INDEX MATCH và VLOOKUP

Trong một số trường hợp, bạn có thể kết hợp hàm INDEX với VLOOKUP để thực hiện việc tra cứu dữ liệu nâng cao. Cụ thể, bạn có thể dùng INDEX để lấy ra một giá trị, sau đó dùng VLOOKUP để tìm thông tin liên quan đến giá trị đó trong một bảng khác.

Công thức:

=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:

• INDEX(vùng_tham_chiếu, hàng, cột): Trích xuất giá trị cụ thể tại vị trí giao giữa hàng và cột trong vùng dữ liệu.
• table_array: Bảng dữ liệu mà bạn muốn tra cứu dựa trên giá trị từ INDEX.
• col_index_num: Số thứ tự cột trong table_array chứa giá trị cần lấy.
• [range_lookup]: Kiểu tra cứu:
  • 0 (hoặc FALSE): Tra cứu chính xác.
  • 1 (hoặc TRUE): Tra cứu tương đối.

Giả sử bạn có bảng điểm từ A2:F9 và muốn xác định “điểm ưu tiên theo khu vực” cho từng học sinh dựa vào cột khu vực, với dữ liệu khu vực – điểm ưu tiên nằm trong vùng I2:J9.

Ví dụ minh họa

Bạn nhập công thức:

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

Nhập công thức

Việc kết hợp INDEX và VLOOKUP rất hữu ích khi giá trị bạn cần tra không có sẵn, mà phải tính toán hoặc truy xuất trước bằng INDEX, sau đó mới dùng để dò tìm dữ liệu liên quan thông qua VLOOKUP. Cách làm này giúp công thức của bạn linh hoạt và mạnh mẽ hơn trong nhiều tình huống thực tế.

5. Chi tiết cách kết hợp hàm Index Match và hàm If

Bạn có thể kết hợp INDEX + MATCH với hàm IF để tra cứu dữ liệu và đưa ra điều kiện kiểm tra. Dạng công thức tổng quát như sau:

=IF(INDEX(vùng_dữ_liệu, MATCH(giá_trị_tìm, dải_tìm_kiếm, loại_tìm)) + điều_kiện, giá_trị_nếu_đúng, giá_trị_nếu_sai)

Ví dụ, bạn có bảng dữ liệu từ A2:F5 với danh sách các sản phẩm và số lượng đã bán. Mục tiêu là kiểm tra xem sản phẩm “Bánh quy” có bán được trên 10 chiếc để xem có đạt KPI hay không.

Nhập công thức:

=IF(INDEX(A2:F5, MATCH(“Bánh quy”, A2:A5, 0), 3) > 10, “Đạt”, “Không đạt”)

Giải thích:

• MATCH(“Bánh quy”, A2:A5, 0): Xác định vị trí hàng có “Bánh quy”.
• INDEX(A2:F5, …, 3): Lấy dữ liệu ở cột số 3 (giả sử là cột số lượng bán).
• So sánh giá trị đó với > 10:
  • Nếu lớn hơn 10 → trả về “Đạt”
  • Nếu không → trả về “Không đạt”

Nhập công thức

Sự kết hợp này rất hữu ích để tra cứu một giá trị cụ thể và đánh giá theo điều kiện. Bạn có thể áp dụng linh hoạt cho các báo cáo KPI, kiểm tra tồn kho, chấm điểm học sinh,… trong Excel một cách nhanh chóng và hiệu quả.

6. Khi nào nên kết hợp hàm Index Match trong Excel

Bạn nên sử dụng kết hợp hàm INDEX và MATCH khi cần tìm kiếm và trích xuất dữ liệu một cách chính xác trong các bảng dữ liệu lớn hoặc có cấu trúc phức tạp. Bộ đôi hàm này rất hiệu quả khi bạn cần tìm giá trị theo nhiều tiêu chí hoặc muốn linh hoạt trong việc tìm từ trái sang phải, từ phải sang trái, hoặc theo cả hàng và cột.

So với VLOOKUP hay HLOOKUP – vốn chỉ tìm theo một chiều cố định (từ trái sang phải hoặc từ trên xuống dưới) – thì INDEX MATCH có ưu điểm là không bị giới hạn vị trí cột/hàng và dễ dàng thích nghi khi dữ liệu thay đổi.

Nhờ đó, công thức của bạn sẽ chính xác hơn, linh hoạt hơn đồng thời cũng dễ dàng áp dụng cho các bảng dữ liệu lớn, phức tạp

Hàm INDEX kết hợp MATCH là lựa chọn mạnh mẽ cho những ai thường xuyên xử lý và phân tích dữ liệu trong Excel.

Hàm INDEX kết hợp MATCH được sử dụng phổ biến trong phân tích dữ liệu trong Excel

7. Một số lỗi thường gặp khi dùng hàm INDEX MATCH trong Excel

7.1 Lỗi #REF!

Lỗi #REF! thường xảy ra khi phạm vi dữ liệu của hàm INDEX không tương thích với kết quả mà MATCH trả về. Ví dụ, MATCH trả về vị trí dòng thứ 5, nhưng INDEX chỉ chứa 3 dòng thì sẽ gây lỗi.

Ngoài ra, lỗi này cũng có thể xuất hiện nếu không tìm thấy dữ liệu thỏa điều kiện, khiến kết quả trả về nằm ngoài phạm vi hợp lệ của INDEX.

Cách khắc phục: Kiểm tra lại phạm vi trong cả INDEX và MATCH. Đảm bảo vùng dữ liệu đủ lớn để chứa vị trí mà MATCH trả về.

7.2 Lỗi #N/A

Lỗi #N/A xuất hiện khi giá trị bạn đang tìm không tồn tại trong vùng tra cứu. Điều này tương tự như lỗi thường gặp khi sử dụng hàm VLOOKUP.

Nguyên nhân có thể do sai chính tả, dấu cách thừa, hoặc giá trị tìm kiếm không khớp hoàn toàn với dữ liệu gốc.

Cách khắc phục: Kiểm tra lại nội dung giá trị tìm kiếm và vùng dữ liệu. Bạn cũng có thể dùng hàm IFERROR hoặc IFNA để thay thế lỗi bằng một thông báo dễ hiểu.

8. Tại sao nên dùng hàm INDEX và MATCH thay cho VLOOKUP và HLOOKUP?

Việc kết hợp hàm INDEX và MATCH thường được xem là giải pháp linh hoạt và hiệu quả hơn so với chỉ dùng VLOOKUP hoặc HLOOKUP.

Hàm VLOOKUP và HLOOKUP chỉ hỗ trợ tra cứu một chiều – VLOOKUP theo cột, HLOOKUP theo hàng. Điều này khiến việc tìm dữ liệu theo nhiều điều kiện hoặc nhiều chiều trở nên khó khăn.

Ngược lại, khi kết hợp INDEX và MATCH, bạn có thể dễ dàng tra cứu theo cả chiều ngang và dọc, phù hợp với những bảng dữ liệu có cấu trúc phức tạp.

Một điểm hạn chế lớn của hàm VLOOKUP trong Excel là nếu bạn thêm hoặc xóa cột trong bảng, kết quả tra cứu có thể sai vì công thức sử dụng số thứ tự cột cố định.

Trong khi đó, INDEX + MATCH hoạt động dựa trên vị trí chính xác theo giá trị, nên sẽ không bị ảnh hưởng khi thêm, bớt cột hay thay đổi cấu trúc bảng.

Hàm INDEX MATCH giúp tra cứu chính xác, linh hoạt và hạn chế lỗi hơn so với VLOOKUP và HLOOKUP. Đó là lý do nhiều người dùng Excel chuyên nghiệp đều ưu tiên sử dụng bộ đôi hàm này.

Hàm INDEX và MATCH là lựa chọn tối ưu cho những ai muốn làm chủ việc tra cứu và phân tích dữ liệu trong Excel. Khi hiểu rõ cách sử dụng bộ đôi này, bạn không chỉ thao tác linh hoạt với các bảng dữ liệu phức tạp mà còn hạn chế được các lỗi thường gặp như #REF! hay #N/A.

Để nắm vững toàn bộ kỹ năng Excel từ cơ bản đến nâng cao, bao gồm cả các công thức, mẹo xử lý dữ liệu và phân tích chuyên sâu, bạn có thể tham khảo cuốn sách Bí quyết làm chủ Excel phân phối độc quyền tại AntBook. Sách in màu rõ nét, trình bày ví dụ trực quan, dễ hiểu, và đặc biệt có nhóm hỗ trợ học tập 24/7 cùng các chuyên gia Excel giúp bạn giải đáp mọi thắc mắc nhanh chóng. Mua ngay tại AntBook để từng bước làm chủ Excel và nâng cao hiệu suất làm việc!