Cách dùng INDEX MATCH với nhiều tiêu chí trong Excel

Cách đây vài năm, tôi làm phân tích chiến dịch marketing và phải so sánh hiệu suất bán hàng trên nhiều khu vực. Dữ liệu nằm rải rác ở nhiều trang tính Excel, và tôi cần kéo các số bán của từng sản phẩm về một báo cáo tổng hợp. Lúc đầu, tôi thử tìm kiếm và sao chép thủ công, nhưng không hề đơn giản như tôi nghĩ. Chỉ cần sai một hàng là cả báo cáo có thể hỏng.

Đó là lúc tôi tìm ra INDEX MATCH(). Tôi mất vài lần thử để viết đúng công thức, nhưng sau khi thấy nó dễ dàng định vị và kéo đúng con số mình cần, tôi liền dùng thường xuyên. Chỉ với hai hàm, tôi có thể lấy chính xác dữ liệu mình cần, bất kể chúng nằm rải rác ở đâu trong các bảng tính.

Trong bài viết này, tôi sẽ giải thích cách bạn có thể làm điều tương tự bằng các hàm INDEX() và MATCH(). Excel thì luôn còn nhiều điều để học hỏi. Nếu bạn là người mới bắt đầu, tôi rất khuyến nghị khóa học Introduction to Excel. Nếu bạn đã có kinh nghiệm hơn, hãy thử khóa Advanced Excel Functions của chúng tôi.

Ôn lại về INDEX MATCH

INDEX MATCH là cách viết tắt để chỉ sự kết hợp của hai hàm Excel phối hợp với nhau để thực hiện tra cứu nâng cao. Ta cũng có thể gọi là INDEX(MATCH()), nhưng trong bài này tôi sẽ dùng INDEX MATCH. Giờ hãy lần lượt xem từng hàm:

Hàm INDEX() trả về giá trị của một ô dựa trên vị trí của nó trong một phạm vi xác định. Cú pháp như sau:

=INDEX(array, row_num, [column_num])

Trong đó:

• array là phạm vi ô bạn muốn truy xuất giá trị.

• row_num là số hàng trong mảng mà từ đó trả về giá trị.

• column_num (tùy chọn) là số cột trong mảng mà từ đó trả về giá trị.

Hàm MATCH() xác định vị trí tương đối của một giá trị trong một phạm vi. Cú pháp là:

=MATCH(lookup_value, lookup_array, [match_type])

Trong đó:

• lookup_value là giá trị bạn muốn tìm.

• lookup_array là phạm vi nơi hàm tìm kiếm giá trị.

match_type là tùy chọn. 1 (mặc định) tìm giá trị nhỏ hơn hoặc bằng lookup_value (mảng phải sắp xếp tăng dần). 0 tìm khớp chính xác (mảng không cần sắp xếp). -1 tìm giá trị nhỏ nhất lớn hơn hoặc bằng lookup_value (mảng phải sắp xếp giảm dần).

Cách kết hợp INDEX() với MATCH()

Bằng cách lồng MATCH() trong INDEX(), ta có thể tạo một phép tra cứu động. Hãy hiểu qua ví dụ: Giả sử bạn muốn tìm vị trí của David Wilson trong tập dữ liệu. Thay vì mã hóa cứng số hàng trong INDEX(), hãy dùng MATCH() để xác định:

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

Trong công thức trên, MATCH("David Wilson", A2:A6, 0) trả về 4, là vị trí hàng. Và INDEX(C2:C6, 4) truy xuất giá trị ở hàng thứ 4 của phạm vi C2:C6, tức là Seattle.

Kết hợp INDEX() với MATCH(). Ảnh: Tác giả.

Để công thức linh hoạt hơn nữa, bạn có thể thay David Wilson được mã hóa cứng bằng tham chiếu ô. Như vậy, công thức sẽ tự động điều chỉnh theo giá trị trong D4:

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

Thay giá trị mã hóa cứng trong INDEX MATCH. Ảnh: Tác giả.

INDEX MATCH so với VLOOKUP()

Giờ bạn đã biết INDEX() và MATCH() hoạt động riêng lẻ thế nào và việc kết hợp khiến tra cứu linh hoạt ra sao, hãy xem vì sao INDEX MATCH là lựa chọn tốt hơn VLOOKUP().

• Khác với VLOOKUP(), yêu cầu cột tra cứu nằm bên trái, INDEX MATCH cho phép bạn lấy dữ liệu từ bất kỳ cột nào, bất kể vị trí.

• INDEX MATCH chỉ xử lý phạm vi ô cần thiết, trong khi VLOOKUP() quét cả bảng. 

• Công thức dùng VLOOKUP() có thể lỗi nếu chèn hoặc xóa cột, vì dựa vào chỉ số cột tĩnh. Ngược lại, INDEX MATCH tham chiếu các phạm vi động, giúp công thức vẫn nguyên vẹn dù cấu trúc dữ liệu thay đổi.

• Với INDEX MATCH, chúng ta không phải đếm số cột thủ công. Chỉ cần chỉ định cột tra cứu và cột trả về là xong. 

INDEX MATCH với nhiều tiêu chí

Tôi thường làm với các tập dữ liệu có mục trùng lặp, và việc tìm giá trị trong đó cực kỳ khó. Nhưng giờ tôi dùng INDEX MATCH vì nó xử lý những tình huống này rất dễ, không như các công thức tra cứu tiêu chuẩn khác. Tôi sẽ hướng dẫn bạn từng bước cách tôi dùng.

Chuẩn bị dữ liệu cho nhiều tiêu chí

Trước tiên, tạo tập dữ liệu và đảm bảo sắp xếp gọn trong một bảng với tiêu đề rõ ràng cho mỗi cột. Mỗi hàng nên đại diện cho một bản ghi duy nhất, và mỗi cột chứa một thuộc tính dữ liệu cụ thể.

Ví dụ, đây là một tập dữ liệu mẫu:

Tập dữ liệu cho INDEX MATCH nhiều tiêu chí. Ảnh: Tác giả.

Viết công thức cho nhiều tiêu chí

Sau khi dữ liệu được tổ chức đúng, đến lúc viết công thức. Công thức INDEX MATCH sẽ truy xuất giá trị từ cột khác bằng cách xác định một hàng thỏa mãn nhiều điều kiện. Điều này được thực hiện bằng cách kết hợp các phép kiểm tra logic trong hàm MATCH() và lồng nó trong hàm INDEX().

Cú pháp cơ bản như sau: 

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

Trong đó: 

• Return_range là phạm vi mà giá trị sẽ được trả về.

• Criteria1, Criteria2, … là các điều kiện cần thỏa mãn.

• Range1, Range2, … là các phạm vi tương ứng với điều kiện.

Giờ khi đã có tập dữ liệu, hãy xem kỹ hai phương pháp để trả lời câu hỏi: cách dùng INDEX MATCH với nhiều tiêu chí.

Dùng cột hỗ trợ cho tiêu chí phức tạp

Nếu tập dữ liệu có nhiều điều kiện, hãy dùng các cột hỗ trợ để đơn giản hóa. Chúng sẽ kết hợp tất cả điều kiện vào một cột để tra cứu dễ hơn. Ví dụ, tôi dùng cùng tập dữ liệu để tạo một cột hỗ trợ bằng cách ghép cột First Name và Salary:

=A2&B2

Tạo cột hỗ trợ. Ảnh: Tác giả.

Cột hỗ trợ này giúp đơn giản công thức INDEX MATCH. Thay vì viết công thức mảng phức tạp với nhiều điều kiện, tôi tham chiếu đến cột hỗ trợ trong công thức để có cách tiếp cận gọn hơn:

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))

INDEX MATCH với cột hỗ trợ. Ảnh: Tác giả.

Kết hợp nhiều tiêu chí bằng công thức mảng

Nếu bạn không thích cột hỗ trợ, có thể dùng công thức mảng để đạt kết quả tương tự. Chúng cho phép bạn đánh giá nhiều tiêu chí trực tiếp trong hàm MATCH(). Ví dụ, đây là cách tôi tìm lương của Alice tại phòng HR:

Bước 1: Viết hàm MATCH() với các điều kiện logic:

 MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0)

Trong công thức này, 1 đảm bảo MATCH() tìm các hàng mà mọi điều kiện đều đúng. (F4=A2:A11) kiểm tra giá trị trong F4 có khớp với giá trị nào trong phạm vi A2:A11 hay không. (F5=B2:B11) kiểm tra giá trị trong F5 có khớp với giá trị nào trong phạm vi B2:B11 hay không. Toán tử * đóng vai trò logic AND, đảm bảo mọi điều kiện đều được đáp ứng.

Bước 2: Bọc hàm MATCH() này trong hàm INDEX():

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))

Bước 3: Hoàn tất công thức. Nếu bạn dùng phiên bản Excel cũ, nhấn Ctrl+Shift+Enter để biến nó thành công thức mảng. Ở phiên bản mới, nhấn Enter.

INDEX MATCH dạng mảng với nhiều tiêu chí. Ảnh: Tác giả.

Ứng dụng nâng cao cho INDEX MATCH với nhiều tiêu chí

Bạn có thể làm được nhiều hơn nữa với INDEX MATCH. Hãy xem nhé: 

Dùng INDEX MATCH với phạm vi có tên và phạm vi động

Tôi dùng các phạm vi có tên trong Excel để đặt các tên có ý nghĩa như results hoặc totalSales thay vì tham chiếu tiêu chuẩn như A1:A10. Cách này giúp quản lý công thức trên nhiều trang tính dễ hơn. 

Để đặt tên phạm vi, chọn các ô và nhấn Ctrl + F3 (Windows) hoặc Cmd + F3 (Mac) để mở Name Manager. Sau đó, bấm New, nhập tên và bấm OK.

Đặt tên phạm vi. Ảnh: Tác giả.

Điểm khác biệt giữa phạm vi có tên và phạm vi động là phạm vi có tên tham chiếu một nhóm ô cố định, còn phạm vi động tự động điều chỉnh khi thêm hoặc bớt dữ liệu.

Để đặt phạm vi động, chọn các ô. Trên tab Formulas, bấm Name Manager hoặc nhấn Ctrl + F3 để mở Name Manager của Excel và bấm New. Hộp thoại New Name sẽ hiện ra. Bây giờ, trong trường Name nhập tên mong muốn. Sau đó, trong trường Refers to nhập công thức cho phạm vi động. 

Thiết lập phạm vi động. Ảnh: Tác giả. 

Giờ hãy xem ví dụ: Tôi định nghĩa hai phạm vi động và một phạm vi tĩnh:

• total_amount: =$F$2:INDEX($F:$F, COUNTA($F:$F))

• items_list: =$A$2:INDEX($A:$A, COUNTA($A:$A))

• lookup_value: =$I$3

Giờ, tôi dùng các phạm vi này trong công thức INDEX MATCH:

=INDEX(total_amount,MATCH(lookup_value,items_list,0))

Bạn có thể thấy công thức dễ hiểu hơn nhiều khi dùng tên rõ ràng.

Dùng phạm vi động và có tên với INDEX MATCH. Ảnh: Tác giả.

Lồng INDEX MATCH cho tra cứu phức tạp

Ngoài công việc cơ bản, bạn có thể dùng các hàm INDEX MATCH lồng nhau để xử lý tra cứu phức tạp. Ví dụ, tôi có tập dữ liệu thể hiện doanh số theo danh mục sản phẩm trên các khu vực. 

Dữ liệu thô. Ảnh: Tác giả.

Tôi muốn tìm doanh số đồ nội thất ở khu vực East. Nhưng để làm vậy, tôi phải khớp cả danh mục sản phẩm (hàng) và khu vực (cột), điều mà INDEX MATCH cơ bản không làm được. Vì thế tôi dùng công thức INDEX MATCH lồng nhau sau:

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))

Cách hoạt động: INDEX() kéo một giá trị từ phạm vi B2:D4, nhưng nó cần số hàng và số cột để biết chính xác vị trí. Vậy nên, MATCH(D6, A2:A4, 0) đầu tiên xác định số hàng. Nếu D6 chứa Furniture, nó tìm ở cột A2:A4 và thấy ở hàng thứ hai.

Tiếp theo, MATCH(D7, B1:D1, 0) xác định số cột. Nếu D7 là East, nó dò trên hàng B1:D1 và thấy ở cột thứ hai.

Khi INDEX() biết số hàng và cột, nó hiển thị giá trị đầu ra. Trong trường hợp này, doanh số cho Furniture tại khu vực East là 450.

Dùng INDEX MATCH lồng nhau. Ảnh: Tác giả.

Tôi dùng công thức này thay vì dò tìm thủ công theo hàng và cột vì nó xử lý mọi thứ chính xác. 

Thách thức thường gặp và mẹo khắc phục

Khi mới bắt đầu dùng INDEX MATCH, tôi gặp vài vấn đề và tôi không muốn bạn cũng bối rối như vậy. Vì thế, tôi sẽ điểm qua các lỗi phổ biến và cách khắc phục.

Xử lý lỗi trong công thức INDEX MATCH

Các lỗi như #N/A và #VALUE! có thể gây ức chế lúc đầu, nhưng khá dễ sửa. Hãy xem cách xác định nguyên nhân và các bước đơn giản để xử lý.

Lỗi #N/A xảy ra khi hàm MATCH() không tìm thấy giá trị. Nguyên nhân có thể do giá trị tra cứu không tồn tại trong mảng tìm kiếm, hoặc dữ liệu có khoảng trắng ẩn. Ví dụ, có lần tôi tham chiếu nhầm cột khi kéo Employee Names:

=INDEX(B2:B6,MATCH(E3,C2:C6,0))

Lỗi #N/A trong INDEX MATCH. Ảnh: Tác giả.

Để sửa, hãy kiểm tra giá trị tra cứu có tồn tại trong mảng không và dùng hàm TRIM() để loại khoảng trắng:

=TRIM(INDEX(B2:B6,MATCH(E3,A2:A6,0)))

Đã sửa lỗi #N/A trong INDEX MATCH. Ảnh: Tác giả.

#VALUE! xuất hiện khi công thức chưa được đặt thành công thức mảng. Ví dụ, nếu tôi dùng hàm MATCH() và bao gồm nhiều hơn một phạm vi, Excel sẽ coi đó là công thức mảng. Tuy nhiên, nếu không thiết lập đúng, Excel sẽ ném lỗi #VALUE!.

=INDEX(C2:C6,MATCH(D4&E4,A2:A6&B2:B6,0))

Lỗi #Value trong INDEX MATCH. Ảnh: Tác giả.

Để khắc phục, nhấn Ctrl + Shift + Enter sau khi nhập công thức. Khi đó, Excel sẽ bọc công thức trong dấu ngoặc nhọn {}, cho biết đây là công thức mảng. Đừng gõ ngoặc nhọn thủ công vì sẽ làm hỏng công thức.

Đã sửa lỗi #Value trong INDEX MATCH. Ảnh: Tác giả.

Tối ưu hiệu năng với tập dữ liệu lớn

Với tập dữ liệu lớn, đôi lúc công thức của tôi chạy chậm, khiến tôi phải chờ cập nhật tính toán. Nếu bạn cũng gặp vấn đề tương tự, hãy thử các mẹo sau: 

• Giới hạn phạm vi tra cứu: Chỉ chọn phạm vi cần thiết. Ví dụ, thay vì A:A, hãy dùng A1:A100 để giảm thời gian tính toán.

• Dùng cột hỗ trợ: Tính trước các tiêu chí phức tạp bằng cột hỗ trợ. Việc này sẽ giảm tải tính toán của công thức mảng.

• Bật chế độ tính thủ công: Chuyển Excel sang chế độ tính thủ công để tránh tính lại liên tục. Sau khi thay đổi, nhấn F9 để cập nhật công thức thủ công.

• Tránh hàm dễ biến động: Hạn chế dùng các hàm biến động như NOW(), RAND() và TODAY() kết hợp với INDEX MATCH. Những hàm này kích hoạt tính toán lại mỗi khi sổ làm việc cập nhật.

Lời kết 

INDEX MATCH giúp tiết kiệm thời gian và đơn giản hóa phân tích dữ liệu phức tạp. Nếu bạn làm với tập dữ liệu lớn, đáng để thử. Nhưng cách tốt nhất để nắm vững là thực hành. Vì vậy, tôi khuyên bạn hãy xử lý vài tập dữ liệu và thử nghiệm với những gì đã học. Tôi cũng rèn kỹ năng của mình như vậy. 

Để đào sâu kiến thức, hãy xem khóa Advanced Excel Functions để làm chủ nhiều công cụ mạnh mẽ hơn. Còn nếu bạn muốn xây dựng chuyên môn toàn diện về phân tích dữ liệu trong Excel, tôi khuyên dùng khóa Data Analysis in Excel. Khóa học bao quát từ chuẩn bị dữ liệu đến trực quan hóa.