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

Hướng dẫn cho thấy cách tra cứu với nhiều tiêu chí trong Excel bằng INDEX và MATCH và một vài cách khác.

Mặc dù Microsoft Excel cung cấp các chức năng đặc biệt để tra cứu dọc và ngang, người dùng chuyên gia thường thay thế chúng bằng INDEX MATCH, vượt trội so với VLOOKUP và HLOOKUP theo nhiều cách. Trong số những thứ khác, nó có thể tra cứu hai hoặc nhiều tiêu chí trong cột và hàng. Hướng dẫn này giải thích cú pháp và cơ học bên trong một cách chi tiết để bạn có thể dễ dàng điều chỉnh công thức cho các nhu cầu cụ thể của mình. Để làm cho các ví dụ dễ theo dõi hơn, bạn có thể tải xuống của chúng tôi sách bài tập mẫu.

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

Khi làm việc với cơ sở dữ liệu lớn, đôi khi bạn có thể thấy mình trong tình huống khi bạn cần tìm thứ gì đó nhưng không có số nhận dạng duy nhất cho tìm kiếm. Trong trường hợp này, tra cứu với một số điều kiện là giải pháp duy nhất.

Để tìm kiếm một giá trị dựa trên nhiều tiêu chí trong các cột riêng biệt, hãy sử dụng công thức chung này:

{= INDEX (return_range, MATCH (1, (tiêu chí1= =phạm vi1) * (tiêu chí2= =phạm vi 2) * (Hoài), 0))}

Ở đâu:

• Trả về là phạm vi mà từ đó trả về một giá trị.
• Tiêu chí1, tiêu chí2, Lọ là những điều kiện cần được đáp ứng.
• Phạm vi1, phạm vi 2, Lọ là các phạm vi mà các tiêu chí tương ứng cần được kiểm tra.

Công thức là một phiên bản nâng cao của biểu tượng CHỈ SỐ INDEX trả về một trận đấu dựa trên một tiêu chí duy nhất. Để đánh giá nhiều tiêu chí, chúng tôi sử dụng thao tác nhân làm việc như Toán tử AND trong công thức mảng. Dưới đây, bạn sẽ tìm thấy một ví dụ thực tế và giải thích chi tiết về logic.

INDEX MATCH với một số tiêu chí – ví dụ về công thức

Trong ví dụ này, chúng tôi sẽ sử dụng một bảng ở định dạng được gọi là “tệp phẳng” với mỗi kết hợp tiêu chí riêng biệt (mục theo tháng trong trường hợp của chúng tôi) trên hàng riêng của nó. Mục tiêu của chúng tôi là lấy lại số liệu bán hàng cho một mặt hàng nhất định trong một khu vực và tháng cụ thể.

Với dữ liệu nguồn và tiêu chí trong các ô sau:

• Trả về (bán hàng) – Đ2: D13
• Tiêu chí1 (vùng mục tiêu) – G1
• Tiêu chí2 (tháng mục tiêu) – G2
• Tiêu chí3 (mục tiêu) – G3
• Phạm vi1 (khu vực) – A2: A13
• Phạm vi 2 (tháng) – B2: B13
• Phạm vi 3 (mục) – C2: C13

Công thức có hình dạng sau:

=INDEX(D2:D13, MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Nhập công thức, giả sử trong G4, hoàn thành nó bằng cách nhấn Ctrl + Shift + Enter và bạn sẽ nhận được kết quả sau:

Công thức này hoạt động như thế nào

Phần khó nhất là hàm MATCH, vì vậy hãy tìm hiểu trước:

MATCH(1, (G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0))

Như bạn có thể nhớ, MATCH (lookup_value, lookup_array, [match_type]) tìm kiếm giá trị tra cứu trong mảng tra cứu và trả về vị trí tương đối của giá trị đó trong mảng.

Trong công thức của chúng tôi, các đối số như sau:

• Tra cứu_value: 1
• Tra cứu_array: (G1 = A2: A13) * (G2 = B2: B13) * (G3 = C2: C13)
• Loại so khớp: 0

1^thứ đối số là rõ ràng – hàm tìm kiếm số 1. 3^{lần thứ} đối số được đặt thành 0 có nghĩa là “khớp chính xác”, nghĩa là công thức trả về giá trị tìm thấy đầu tiên chính xác bằng giá trị tra cứu.

Câu hỏi là – tại sao chúng ta tìm kiếm “1”? Để có câu trả lời, chúng ta hãy xem xét kỹ hơn về mảng tra cứu nơi chúng ta so sánh từng tiêu chí với phạm vi tương ứng: vùng mục tiêu trong G1 so với tất cả các vùng (A2: A13), tháng mục tiêu trong G2 so với tất cả các tháng (B2: B13 ) và mục đích trong G3 so với tất cả các mục (C2: C13). Một kết quả trung gian là 3 mảng TRUE và FALSE trong đó TRUE đại diện cho các giá trị đáp ứng điều kiện được kiểm tra. Để hình dung điều này, bạn có thể chọn các biểu thức riêng lẻ trong công thức và nhấn nút Phím F9 để xem những gì mỗi biểu thức ước tính:

Hoạt động nhân biến đổi các giá trị TRUE và FALSE thành 1 và 0 tương ứng:

{1;1;1;1;1;1;0;0;0;0;0;0} * {0;0;1;1;0;0;0;0;1;1;0;0} * {1;0;1;0;1;0;1;0;1;0;1;0}

Và bởi vì nhân với 0 luôn cho 0, mảng kết quả chỉ có 1 trong các hàng đáp ứng tất cả các tiêu chí:

{0;0;1;0;0;0;0;0;0;0;0;0}

Mảng trên đi đến tra cứu_array lập luận của MATCH. Với tra cứu_ giá trị của 1, hàm trả về vị trí tương đối của hàng mà tất cả các tiêu chí là TRUE (hàng 3 trong trường hợp của chúng tôi). Nếu có một số 1 trong mảng, vị trí của cái đầu tiên được trả về.

Số được MATCH trả về sẽ chuyển trực tiếp đến row_num đối số của INDEX (mảng, row_num, [column_num]) chức năng:

=INDEX(D2:D13, 3)

Và nó mang lại kết quả là $ 115, đó là 3^{lần thứ} giá trị trong mảng D2: D13.

Công thức INDEX MATCH không mảng với nhiều tiêu chí

Công thức mảng được thảo luận trong ví dụ trước hoạt động tốt cho người dùng có kinh nghiệm. Nhưng nếu bạn đang xây dựng một công thức cho người khác và ai đó không biết các hàm mảng, họ có thể vô tình phá vỡ nó. Ví dụ: người dùng có thể nhấp vào công thức của bạn để kiểm tra nó, sau đó nhấn Đi vào thay vì Ctrl + Shift + Enter. Trong những trường hợp như vậy, sẽ là khôn ngoan khi tránh các mảng và sử dụng một công thức thông thường có khả năng chống đạn cao hơn:

MỤC LỤC(return_range, MATCH (1, INDEX ((tiêu chí1= =phạm vi1) * (tiêu chí2= =phạm vi 2) * (..), 0, 1), 0))

Đối với tập dữ liệu mẫu của chúng tôi, công thức như sau:

=INDEX(D2:D13, MATCH(1, INDEX((G1=A2:A13) * (G2=B2:B13) * (G3=C2:C13), 0, 1), 0))

Công thức này hoạt động như thế nào

Vì hàm INDEX có thể xử lý các mảng một cách tự nhiên, chúng tôi thêm một INDEX khác để xử lý mảng 1 và 0 được tạo bằng cách nhân hai hoặc nhiều mảng TRUE / FALSE. INDEX thứ hai được cấu hình bằng 0 row_num đối số cho công thức trả về toàn bộ mảng cột thay vì một giá trị. Vì dù sao nó cũng là mảng một cột, nên chúng tôi có thể cung cấp 1 cho một cách an toàn cột_num:

INDEX({0;0;1;0;0;0;0;0;0;0;0;0}, 0, 1) returns {0;0;1;0;0;0;0;0;0;0;0;0}

Mảng này được truyền cho hàm MATCH:

MATCH(1, {0;0;1;0;0;0;0;0;0;0;0;0}, 0)

MATCH tìm số hàng mà tất cả các tiêu chí là TRUE (chính xác hơn là vị trí tương đối của hàng đó trong mảng đã chỉ định) và chuyển số đó cho row_num đối số của INDEX đầu tiên:

=INDEX(D2:D13, 3)

INDEX MATCH với nhiều tiêu chí trong hàng và cột

Ví dụ này cho thấy cách thực hiện tra cứu bằng cách kiểm tra hai hoặc nhiều tiêu chí trong các hàng và cột. Trên thực tế, đó là một trường hợp phức tạp hơn của cái gọi là “tra cứu ma trận” hoặc “tra cứu hai chiều” với nhiều hơn một hàng tiêu đề.

Đây là công thức INDEX MATCH chung với nhiều tiêu chí trong các hàng và cột:

{= INDEX (bảng_array, TRẬN ĐẤU(vlookup_value, tra cứu, 0), MATCH (hlookup_value1 & hlookup_value2, tra cứu_row1 & tra cứu_row2, 0))}

Ở đâu:

Bảng_array – bản đồ hoặc khu vực để tìm kiếm bên trong, tức là tất cả các giá trị dữ liệu không bao gồm các tiêu đề cột và hàng.

Vlookup_value – giá trị bạn đang tìm kiếm theo chiều dọc trong một cột.

Tra cứu_column – phạm vi cột để tìm kiếm, thường là các tiêu đề hàng.

Hlookup_value1, hlookup_value2, tầm – các giá trị bạn đang tìm kiếm theo chiều ngang trong các hàng.

Tra cứu_row1, lookup_row2, Hoài – phạm vi hàng để tìm kiếm, thường là các tiêu đề cột.

Lưu ý quan trọng! Để công thức hoạt động chính xác, nó phải được nhập dưới dạng công thức mảng với Ctrl + Shift + Enter.

Nó là một biến thể của cổ điển tra cứu hai chiều công thức tìm kiếm một giá trị tại giao điểm của một hàng và cột nhất định. Sự khác biệt là bạn kết hợp một số giá trị và phạm vi hlookup để đánh giá nhiều tiêu đề cột. Để hiểu rõ hơn về logic, vui lòng xem xét ví dụ sau.

Tra cứu ma trận với nhiều tiêu chí – ví dụ về công thức

Trong bảng mẫu bên dưới, chúng tôi sẽ tìm kiếm một giá trị dựa trên các tiêu đề hàng (Mục) và 2 tiêu đề cột (Khu vực và nhà cung cấp). Để làm cho công thức dễ xây dựng hơn, trước tiên hãy xác định tất cả các tiêu chí và phạm vi:

• Bảng_array – B3: E4
• Vlookup_value (mục tiêu) – H1
• Tra cứu_column (Tiêu đề hàng: vật phẩm) – A3: A4
• Hlookup_value1 (vùng mục tiêu) – H2
• Hlookup_value2 (nhà cung cấp mục tiêu) – H3
• Tra cứu_row1 (Tiêu đề cột 1: vùng) – B1: E1
• Tra cứu_row2 (Tiêu đề cột 2: nhà cung cấp) – B2: E2

Và bây giờ, cung cấp các đối số vào công thức chung được giải thích ở trên và bạn sẽ nhận được kết quả này:

=INDEX(B3:E5, MATCH(H1,A3:A5,0), MATCH(H2&H3,B1:E1&B2:E2,0))

Nhớ hoàn thành công thức bằng cách nhấn Ctrl + Shift + Enter phím tắt và tra cứu ma trận của bạn với nhiều tiêu chí sẽ được thực hiện thành công:

Công thức này hoạt động như thế nào

Khi chúng tôi đang tìm kiếm theo chiều dọc và chiều ngang, chúng tôi cần cung cấp cả số hàng và số cột cho hàm INDEX (mảng, row_num, cột_num).

Row_num được phân phối bởi MATCH (H1, A3: A5, 0) so sánh mục tiêu (Táo) trong H1 so với các tiêu đề hàng trong A3: A5. Điều này cho kết quả là 1 vì “Táo” là mục đầu tiên trong phạm vi được chỉ định.

Cột_num được thực hiện bằng cách ghép 2 giá trị tra cứu và 2 mảng tra cứu: MATCH (H2 & H3, B1: E1 & B2: E2, 0))

Yếu tố chính để thành công là các giá trị tra cứu phải khớp chính xác với các tiêu đề cột và được nối theo cùng một thứ tự. Để hình dung điều này, chọn hai đối số đầu tiên trong công thức MATCH, nhấn F9và bạn sẽ thấy mỗi đối số ước tính như thế nào:

MATCH("NorthVendor 2", {"NorthVendor 1", "NorthVendor 2", "SouthVendor 1", "SouthVendor 2"}, 0)

Vì “NorthVendor 2” là phần tử thứ hai trong mảng, nên hàm trả về 2.

Tại thời điểm này, công thức INDEX MATCH hai chiều dài của chúng tôi chuyển đổi thành công thức đơn giản này:

=INDEX(B3:E5, 1, 2)

Và trả về một giá trị tại giao điểm của hàng thứ 1 và cột thứ 2 trong phạm vi B3: E5, là giá trị trong ô C3.

Đó là cách tìm kiếm nhiều tiêu chí trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Tải xuống có sẵn

Xin vui lòng tải về Excel INDEX MATCH nhiều tiêu chí sách bài tập mẫu với tất cả các công thức được thảo luận trong hướng dẫn này.

Các cách khác để tìm kiếm với một số điều kiện trong Excel

VLOOKUP với nhiều tiêu chí – điều này đòi hỏi phải thêm một cột trợ giúp ở bên trái bảng của bạn và nối tất cả các tiêu chí trong cột đó. Đó không phải là một giải pháp rất thanh lịch, nhưng bạn có thể muốn biết tất cả các tùy chọn có sẵn.

XLOOKUP với nhiều tiêu chí – phần bổ sung gần đây này cho nhóm chức năng tra cứu và tham chiếu của Excel (hiện chỉ có sẵn cho người đăng ký Office 365) xử lý các mảng theo thiết kế. Có nghĩa, nó hoạt động như một công thức thông thường, không phải là một công thức mảng!

Tra cứu nhiều tiêu chí và trả về nhiều kết quả – kết hợp 5 hàm khác nhau trong một công thức để đánh giá một số tiêu chí và trả về tất cả các kết quả khớp trong một cột hoặc hàng.

Hợp nhất bảng thuật sĩ – cách trực quan để Vlookup trong Excel với một hoặc nhiều tiêu chí.