Công thức chung
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Tóm lược
Để tra cứu giá trị bằng INDEX và MATCH, sử dụng nhiều tiêu chí, bạn có thể sử dụng công thức mảng. Trong ví dụ được hiển thị, công thức trong H8 là:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Lưu ý: đây là công thức mảng và phải được nhập bằng control + shift + enter, ngoại trừ trong Excel 365.
Giải trình
Đây là một công thức tiên tiến hơn. Để biết thông tin cơ bản, hãy xem Cách sử dụng INDEX và MATCH.
Thông thường, công thức INDEX MATCH được định cấu hình với bộ MATCH để xem qua phạm vi một cột và cung cấp kết quả phù hợp dựa trên tiêu chí đã cho. Nếu không nối các giá trị trong cột trợ giúp hoặc trong chính công thức, không có cách nào để cung cấp nhiều hơn một tiêu chí.
Công thức này khắc phục hạn chế này bằng cách sử dụng logic boolean để tạo một mảng gồm các giá trị đơn và số không để đại diện cho các hàng phù hợp với cả 3 tiêu chí, sau đó sử dụng MATCH để so khớp với 1 đầu tiên được tìm thấy. Mảng tạm thời gồm những cái và số không được tạo bằng đoạn mã này:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Ở đây chúng tôi so sánh mục ở H5 với tất cả các mục, kích thước ở H6 so với mọi kích cỡ và màu trong H7 so với tất cả các màu. Kết quả ban đầu là ba mảng kết quả TRUE / FALSE như sau:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Mẹo: sử dụng F9 để xem các kết quả này. Chỉ cần chọn một biểu thức trong thanh công thức và nhấn F9.
Phép toán (phép nhân) biến đổi các giá trị TRUE FALSE thành 1s và 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Sau khi nhân, chúng ta có một mảng như sau:
(0;0;1;0;0;0;0)
được đưa vào hàm MATCH dưới dạng mảng tra cứu, với giá trị tra cứu là 1:
MATCH(1,(0;0;1;0;0;0;0))
Tại thời điểm này, công thức là một công thức INDEX MATCH chuẩn. Hàm MATCH trả về 3 thành INDEX:
=INDEX(E5:E11,3)
và INDEX trả về kết quả cuối cùng là $ 17,00.
Hình ảnh hóa mảng
Các mảng được giải thích ở trên có thể khó hình dung. Hình ảnh dưới đây cho thấy ý tưởng cơ bản. Các cột B, C và D tương ứng với dữ liệu trong ví dụ. Cột F được tạo bằng cách nhân ba cột với nhau. Đây là mảng được giao cho MATCH.
Phiên bản không phải mảng
Có thể thêm một INDEX khác vào công thức này, tránh việc phải nhập dưới dạng công thức mảng với control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Hàm INDEX có thể xử lý các mảng một cách nguyên bản, vì vậy INDEX thứ hai chỉ được thêm vào để "bắt" mảng được tạo bằng thao tác logic boolean và trả lại cùng một mảng đó cho MATCH. Để thực hiện việc này, INDEX được định cấu hình không có hàng và một cột. Thủ thuật hàng không khiến INDEX trả về cột 1 từ mảng (dù sao cũng đã là một cột).
Tại sao bạn muốn phiên bản không phải mảng? Đôi khi, mọi người quên nhập công thức mảng với control + shift + enter và công thức trả về kết quả không chính xác. Vì vậy, một công thức không phải mảng là "chống đạn" hơn. Tuy nhiên, sự đánh đổi là một công thức phức tạp hơn.
Lưu ý: Trong Excel 365, không nhất thiết phải nhập công thức mảng theo cách đặc biệt.
