Cách Sử Dụng Excel Index Match Với Nhiều Điều Kiện (multiple Criteria) – Hướng Dẫn Chi Tiết Từ A Đến Z

Có thể bạn quan tâm: Excel In Ra Trang Trắng: Nguyên Nhân, Giải Pháp Và Cách Khắc Phục Hiệu Quả

Giới thiệu chung

Trong môi trường làm việc với dữ liệu, Excel luôn là công cụ không thể thiếu của các nhà phân tích, kế toán, nhân sự và hầu hết các chuyên gia kinh doanh. Một trong những nhu cầu phổ biến nhất là truy xuất dữ liệu dựa trên nhiều tiêu chí (multiple criteria). Mặc dù hàm VLOOKUP (HOẶC XLOOKUP trong các phiên bản mới) được sử dụng rộng rãi, nhưng INDEX + MATCH lại mang lại sự linh hoạt và hiệu suất cao hơn, đặc biệt khi chúng ta cần áp dụng nhiều điều kiện đồng thời.

Bài viết này sẽ:

1. Giải thích nguyên lý hoạt động của INDEX và MATCH.
2. Trình bày chi tiết cách kết hợp chúng để thực hiện tra cứu với nhiều tiêu chí.
3. Cung cấp các ví dụ thực tế từ đơn giản đến phức tạp, bao gồm cả việc xử lý dữ liệu lớn và các trường hợp đặc biệt (như dữ liệu văn bản, ngày tháng, giá trị trống).
4. So sánh với các phương pháp khác (SUMIFS, FILTER, XLOOKUP, Power Query).
5. Đưa ra các mẹo tối ưu tốc độ và cách debug khi công thức không trả về kết quả mong muốn.

Lưu ý: Bài viết được viết bằng tiếng Việt, dành cho người dùng có mức độ trung cấp trở lên, nhưng các bước sẽ được giải thích chi tiết để người mới bắt đầu cũng có thể theo dõi.

Có thể bạn quan tâm: Cách Sử Dụng Hàm If Kết Hợp Với Kiểm Tra Giá Trị Trong Một Dải Ô (range) Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1. Tổng quan về INDEX và MATCH

1.1. Hàm INDEX

=INDEX(array, row_num, column_num)

• array: vùng dữ liệu mà chúng ta muốn trả về một giá trị.
• row_num: vị trí dòng trong array.
• column_num (tùy chọn): vị trí cột trong array (nếu array là 2 chiều).

Ví dụ: =INDEX(A1:C10, 5, 2) trả về giá trị nằm ở giao điểm dòng 5, cột 2 của vùng A1:C10 (tức là B5).

1.2. Hàm MATCH

=MATCH(lookup_value, lookup_array, match_type)

• lookup_value: giá trị cần tìm.
• lookup_array: dải ô chứa giá trị cần so sánh.
• match_type: 0 (chính xác), 1 (lớn hơn hoặc bằng), -1 (nhỏ hơn hoặc bằng). Thông thường chúng ta dùng 0.

Ví dụ: =MATCH("Nam", D1:D10, 0) trả về vị trí (số thứ tự) của từ “Nam” trong cột D.

1.3. Tại sao kết hợp INDEX + MATCH?

• Không giới hạn cột tìm kiếm: VLOOKUP chỉ tìm từ cột bên trái sang phải, trong khi INDEX+MATCH có thể tra cứu bất kỳ cột nào.
• Hiệu suất: Khi làm việc với bảng dữ liệu lớn, INDEX+MATCH thường nhanh hơn VLOOKUP.
• Khả năng mở rộng: Dễ dàng mở rộng để hỗ trợ nhiều tiêu chí bằng cách sử dụng các hàm mảng (array formulas) hoặc hàm FILTER (Excel 365/2026).

Có thể bạn quan tâm: Excel If Function Multiple Conditions: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2. Cơ bản: Tra cứu một tiêu chí bằng INDEX MATCH

Trước khi vào phần multiple criteria, chúng ta cùng ôn lại cách dùng cơ bản.

Giả sử bảng dữ liệu Bảng 1 (A1:C6) như sau:

Muốn lấy Giá của SP003:

=INDEX(C2:C6, MATCH("SP003", A2:A6, 0))

Kết quả: 3,500.

3. Tra cứu với nhiều tiêu chí – Khái niệm

Khi có hai hoặc nhiều cột cần so sánh đồng thời, chúng ta không thể dùng MATCH đơn thuần. Cách tiếp cận thường gặp:

1. Kết hợp các tiêu chí thành một “chuỗi duy nhất” (concatenation) và dùng MATCH trên chuỗi này.
2. Sử dụng hàm mảng (array formula) với (phép nhân logic) hoặc + (phép cộng logic) để tạo mảng boolean.
3. Xây dựng công thức động với SUMPRODUCT hoặc FILTER.

Trong phần này, chúng ta sẽ đi qua từng cách, phân tích ưu nhược điểm và cung cấp ví dụ thực tế.

4. Phương pháp 1: Concatenation (Kết hợp chuỗi)

4.1. Ý tưởng

• Tạo một cột “Key” (khóa) bằng cách ghép các giá trị tiêu chí lại với nhau (ví dụ: Mã SP & "_" & Đợt).
• Tương tự, tạo một chuỗi “Key” cho giá trị cần tra cứu.
• Dùng MATCH trên cột “Key”.

4.2. Thực hiện

Bảng dữ liệu mẫu (Bảng 2) – Bán hàng theo tháng

Mục tiêu: Lấy doanh thu của SP001 ở khu vực Bắc vào ngày 2026-01-02.

Bước 1: Tạo cột “Key”

Ở cột E (ô E2) nhập công thức:

=A2 & "|" & B2 & "|" & C2

Kéo công thức xuống hết bảng. Kết quả sẽ là:

• 2026-01-01|SP001|Bắc
• 2026-01-02|SP001|Bắc
• …

Bước 2: Tạo công thức tra cứu

=INDEX(D2:D1000, MATCH("2026-01-02|SP001|Bắc", E2:E1000, 0))

Kết quả: 12,000.

4.3. Ưu điểm & Nhược điểm

5. Phương pháp 2: Công thức mảng (Array Formula) với (phép nhân logic)

5.1. Nguyên lý

Trong Excel, các biểu thức so sánh trả về một mảng Boolean (TRUE/FALSE). Khi chúng ta nhân các mảng này (), TRUE được coi là 1, FALSE là 0. Nhân các mảng sẽ cho ra 1 chỉ khi tất cả điều kiện đều đúng, ngược lại 0.

Sau đó, dùng MATCH(1, array, 0) để tìm vị trí đầu tiên mà tất cả điều kiện thỏa mãn.

5.2. Cú pháp cơ bản

=INDEX(return_range, MATCH(1, (criteria_range1=criteria1) (criteria_range2=criteria2) (criteria_range3=criteria3), 0))

Lưu ý: Đây là công thức mảng. Trong Excel 2026 trở xuống, người dùng phải ấn Ctrl+Shift+Enter. Từ Excel 365/2026 trở lên, công thức sẽ tự động là công thức động (dynamic array) – không cần CSE.

5.3. Ví dụ thực tế

Sử dụng lại Bảng 2 (đã có cột ngày, mã SP, khu vực, doanh thu). Mục tiêu: Lấy doanh thu của SP001, khu vực Bắc, ngày 2026-01-02.

=INDEX(D2:D1000, MATCH(1, (A2:A1000=DATE(2026,1,2)) (B2:B1000="SP001") (C2:C1000="Bắc"), 0))

Kết quả: 12,000.

Giải thích từng phần:

• (A2:A1000=DATE(2026,1,2)) trả về mảng {FALSE; FALSE; TRUE; FALSE; …}
• (B2:B1000="SP001") trả về mảng {TRUE; FALSE; TRUE; TRUE; …}
• (C2:C1000="Bắc") trả về mảng {TRUE; FALSE; TRUE; FALSE; …}
• Nhân ba mảng lại → mảng {0;0;1;0;…}
• MATCH(1, …, 0) trả về vị trí 3 (hàng 4 trong sheet), và INDEX trả về giá trị tương ứng ở cột D.

5.4. Xử lý trường hợp nhiều kết quả (cùng tiêu chí)

Nếu có nhiều hàng thỏa mãn cùng một bộ tiêu chí, MATCH sẽ chỉ trả về đầu tiên. Để lấy tất cả kết quả, chúng ta có thể dùng:

• FILTER (Excel 365/2026) – ví dụ: =FILTER(D2:D1000, (A2:A1000=...) (B2:B1000=...) (C2:C1000=...))
• Hoặc INDEX + SMALL kết hợp để trả về danh sách (công thức mảng phức tạp hơn).

5.5. Ưu điểm & Nhược điểm

6. Phương pháp 3: Sử dụng SUMPRODUCT

SUMPRODUCT là hàm mạnh mẽ, cho phép thực hiện tổng các tích của mảng, nhưng cũng có thể dùng để tìm vị trí hoặc trả về giá trị khi kết hợp với INDEX.

6.1. Công thức cơ bản

=INDEX(return_range, SUMPRODUCT( (criteria_range1=criteria1) (criteria_range2=criteria2) (criteria_range3=criteria3) ROW(criteria_range1) // trả về số dòng ) - MIN(ROW(criteria_range1)) + 1)

Giải thích: SUMPRODUCT sẽ nhân các mảng boolean và trả về số dòng của hàng thỏa điều kiện (vì chỉ một hàng thỏa, kết quả sẽ là số dòng tương ứng). Sau đó, trừ đi MIN(ROW(...)) - 1 để đưa về vị trí tương đối cho INDEX.

6.2. Ví dụ

=INDEX(D2:D1000, SUMPRODUCT( (A2:A1000=DATE(2026,1,2)) (B2:B1000="SP001") (C2:C1000="Bắc") ROW(A2:A1000) ) - ROW(A2) + 1)

Kết quả: 12,000.

6.3. Khi có nhiều kết quả

Có thể bạn quan tâm: Excel In What I Do: Bí Quyết Thành Công Và Nâng Cao Hiệu Suất Công Việc

Nếu có nhiều hàng thỏa, SUMPRODUCT sẽ trả về tổng các số dòng, không phải vị trí. Vì vậy, phương pháp này thích hợp khi chỉ có duy nhất một bản ghi cho mỗi bộ tiêu chí (thường là trường hợp khóa chính).

6.4. Ưu điểm & Nhược điểm

7. Phương pháp 4: XLOOKUP (Excel 365/2026) với Nhiều Tiêu Chí

XLOOKUP là hàm mới, thay thế VLOOKUP và HLOOKUP. Tuy nhiên, XLOOKUP không hỗ trợ nhiều tiêu chí trực tiếp. Chúng ta vẫn phải dùng công thức mảng để tạo “Key” ảo hoặc dùng FILTER.

7.1. Sử dụng “Key” ảo (không cần cột phụ)

=XLOOKUP( 1, (A2:A1000=DATE(2026,1,2)) (B2:B1000="SP001") (C2:C1000="Bắc"), D2:D1000, "Không tìm thấy", 0)

• Ở đây, lookup_value là 1 (các mảng boolean nhân sẽ cho 1 khi thỏa).
• lookup_array là mảng logic (1/0).
• return_array là cột cần trả về.

7.2. Sử dụng FILTER (trả về nhiều kết quả)

=FILTER(D2:D1000, (A2:A1000=DATE(2026,1,2)) (B2:B1000="SP001") (C2:C1000="Bắc"), "Không có dữ liệu")

Kết quả sẽ là một mảng (có thể có nhiều giá trị). Khi dùng trong Excel 365, chúng sẽ “tràn” ra các ô liền kề.

7.3. Ưu điểm & Nhược điểm

8. So sánh nhanh các phương pháp

9. Trường hợp thực tế nâng cao

9.1. Tra cứu với điều kiện “hoặc” (OR)

Giả sử muốn lấy doanh thu của SP001 ở khu vực Bắc hoặc Nam vào ngày 2026‑01‑02.

=INDEX(D2:D1000, MATCH(1, (A2:A1000=DATE(2026,1,2)) (B2:B1000="SP001") ((C2:C1000="Bắc") + (C2:C1000="Nam")), 0))

Ở đây, dấu + hoạt động như OR (TRUE+FALSE = 1, TRUE+TRUE = 2, nhưng vẫn >=1). Để chắc chắn chỉ nhận 1, chúng ta có thể dùng >0:

=INDEX(D2:D1000, MATCH(1, (A2:A1000=DATE(2026,1,2)) (B2:B1000="SP001") ((C2:C1000="Bắc") + (C2:C1000="Nam") > 0), 0))

9.2. Tra cứu với điều kiện “lớn hơn / nhỏ hơn”

Muốn tìm giá bán lớn hơn 10,000 cho SP002:

=INDEX(C2:C1000, MATCH(1, (B2:B1000="SP002") (C2:C1000>10000), 0))

9.3. Xử lý giá trị trống và lỗi #N/A

Khi một trong các tiêu chí có thể để trống (nghĩa là “không quan tâm”), chúng ta có thể dùng công thức:

=INDEX(D2:D1000, MATCH(1, ( (A2:A1000=IF($G$1="",A2:A1000,$G$1)) ) ( (B2:B1000=IF($G$2="",B2:B1000,$G$2)) ) ( (C2:C1000=IF($G$3="",C2:C1000,$G$3)) ), 0))

Trong đó $G$1, $G$2, $G$3 là ô nhập tiêu chí. Nếu ô trống, công thức sẽ so sánh cùng với chính nó, luôn trả về TRUE.

9.4. Kết hợp với định dạng ngày và định dạng văn bản

Ngày trong Excel là số nguyên. Khi so sánh ngày cần đảm bảo cùng định dạng:

=INDEX(D2:D1000, MATCH(1, (INT(A2:A1000)=INT($G$1)) (B2:B1000=$G$2), 0))

INT loại bỏ phần thời gian (nếu có). Đối với văn bản, có thể dùng TRIM để loại bỏ khoảng trắng thừa:

=INDEX(D2:D1000, MATCH(1, (TRIM(B2:B1000)=TRIM($G$2)) (C2:C1000=$G$3), 0))

10. Tối ưu hiệu suất cho dữ liệu lớn

1. Giảm kích thước mảng: Chỉ dùng phạm vi thực tế (VD: A2:A50000 thay vì A:A).
2. Sử dụng bảng (Table): Khi dùng Table, Excel tự động quản lý phạm vi và cải thiện tốc độ tính toán.
3. Tắt tính năng “Automatic Calculation” khi nhập công thức phức tạp, sau đó bật lại.
4. Sử dụng hàm LET (Excel 365) để lưu các mảng trung gian, tránh tính lại nhiều lần:

=LET( d, A2:A1000, s, B2:B1000, r, C2:C1000, v, D2:D1000, idx, MATCH(1, (d=DATE(2026,1,2)) (s="SP001") (r="Bắc"), 0), INDEX(v, idx)
)

1. Kết hợp Power Query: Đối với hàng triệu bản ghi, Power Query (Get & Transform) thực hiện lọc và tra cứu nhanh hơn, sau đó chỉ tải kết quả cần thiết về sheet.

11. Debug – Khi công thức trả về lỗi hoặc #N/A

Ví dụ kiểm tra từng tiêu chí:

=COUNTIFS(A2:A1000, DATE(2026,1,2), B2:B1000, "SP001", C2:C1000, "Bắc")

Nếu kết quả là 0 → không có dữ liệu thỏa, cần kiểm tra nhập liệu (khoảng trắng, định dạng ngày).

12. Khi nào nên dùng Power Query thay vì công thức

• Dữ liệu > 100.000 hàng: Công thức mảng có thể làm Excel chậm, trong khi Power Query xử lý ngoài bộ nhớ.
• Nhiều nguồn dữ liệu (CSV, SQL, Web): Power Query tích hợp ETL, dễ làm sạch và ghép bảng.
• Cần tự động cập nhật: Với Power Query, chỉ cần “Refresh” để lấy dữ liệu mới, không cần sao chép công thức.
• Thực hiện các phép biến đổi phức tạp (pivot, unpivot, merge, group by).

Quy trình nhanh:

1. Data → Get Data → From Table/Range (chuyển dữ liệu thành Table).
2. Trong Power Query Editor, dùng Merge Queries để thực hiện join dựa trên nhiều cột (các tiêu chí).
3. Chọn Left Outer Join (hoặc tùy yêu cầu).
4. Kéo cột cần lấy (ví dụ: Doanh thu) vào bảng gốc.
5. Close & Load → bảng kết quả sẽ tự động cập nhật khi dữ liệu gốc thay đổi.

13. Tổng hợp công thức “cực chuẩn” cho 3 tiêu chí (Ngày, Mã SP, Khu vực)

13.1. Công thức mảng (Excel 365)

=LET( ngày, A2:A1000, mã, B2:B1000, khu, C2:C1000, giá, D2:D1000, tiêu1, DATE(2026,1,2), tiêu2, "SP001", tiêu3, "Bắc", idx, MATCH(1, (ngày=tiêu1)(mã=tiêu2)(khu=tiêu3), 0), INDEX(giá, idx)
)

13.2. Công thức mảng với CSE (Excel 2026/2026)

=INDEX($D$2:$D$1000, MATCH(1, ($A$2:$A$1000=DATE(2026,1,2)) ($B$2:$B$1000="SP001") ($C$2:$C$1000="Bắc"), 0))

(Nhấn Ctrl+Shift+Enter)

13.3. Công thức XLOOKUP (Excel 365)

=XLOOKUP(1, ($A$2:$A$1000=DATE(2026,1,2)) ($B$2:$B$1000="SP001") ($C$2:$C$1000="Bắc"), $D$2:$D$1000, "Không tìm thấy")

13.4. Công thức FILTER (để lấy danh sách)

=FILTER($D$2:$D$1000, ($A$2:$A$1000=DATE(2026,1,2)) ($B$2:$B$1000="SP001") ($C$2:$C$1000="Bắc"), "Không có kết quả")

14. Kết luận

Việc tra cứu dữ liệu với nhiều tiêu chí trong Excel không còn là vấn đề khó khăn nếu chúng ta nắm vững các công cụ:

• INDEX + MATCH (công thức mảng) – linh hoạt, không cần cột phụ.
• SUMPRODUCT – phù hợp với các phiên bản cũ, không cần CSE.
• XLOOKUP và FILTER – mạnh mẽ trên Excel 365, hỗ trợ trả về nhiều kết quả.
• Power Query – giải pháp tối ưu cho dữ liệu lớn và quy trình ETL.

Hãy lựa chọn phương pháp phù hợp với môi trường làm việc (phiên bản Excel, kích thước dữ liệu, yêu cầu bảo trì). Khi áp dụng đúng, bạn sẽ giảm đáng kể thời gian xử lý, tăng độ chính xác và tạo ra các báo cáo tự động, chuyên nghiệp.

Tip cuối cùng: Khi gặp bất kỳ lỗi nào, hãy luôn bắt đầu với COUNTIFS để kiểm tra có thực sự tồn tại bản ghi thỏa mãn hay không, sau đó dần dần xây dựng công thức phức tạp hơn. Chúc bạn thành công trong việc khai thác dữ liệu bằng Excel!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.