Công thức chung
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Tóm lược
Để tra cứu giá trị bằng cách so khớp trên nhiều cột, bạn có thể sử dụng công thức mảng dựa trên MMULT, TRANSPOSE, COLUMN và INDEX. Trong ví dụ được hiển thị, công thức trong H4 là:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
trong đó "tên" là dải ô được đặt tên C4: E7 và "nhóm" là dải ô được đặt tên B4: B7. Công thức trả về nhóm mà mỗi tên thuộc về.
Lưu ý: đây là công thức mảng và phải được nhập bằng phím enter.
Giải trình
Làm việc từ trong ra ngoài, tiêu chí logic được sử dụng trong công thức này là:
--(names=G4)
trong đó tên là dải ô được đặt tên C4: E7. Điều này tạo ra kết quả TRUE / FALSE cho mọi giá trị trong dữ liệu và âm kép buộc các giá trị TRUE FALSE thành 1 và 0 để mang lại một mảng như thế này:
(0,0,0;1,0,0;0,0,0;0,0,0)
Mảng này là 4 hàng x 3 cột, phù hợp với cấu trúc của "tên".
Mảng thứ hai được tạo với biểu thức này:
TRANSPOSE(COLUMN(names)^0))
Hàm COLUMN được sử dụng để tạo một mảng số có 3 cột và 1 hàng, và TRANSPOSE chuyển đổi mảng này thành 1 cột và 3 hàng. Nâng lên lũy thừa của 0 chỉ cần chuyển đổi tất cả các số trong mảng thành 1. Sau đó, hàm MMULT được sử dụng để thực hiện phép nhân ma trận:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
và kết quả đi vào hàm MATCH dưới dạng mảng, với 1 là giá trị tra cứu:
MATCH(1,(0;1;0;0),0)
Hàm MATCH trả về vị trí của kết quả phù hợp đầu tiên, tương ứng với hàng của hàng phù hợp đầu tiên đáp ứng tiêu chí đã cung cấp. Đây là nguồn cấp dữ liệu vào INDEX dưới dạng số hàng, với phạm vi được đặt tên là "nhóm" là mảng:
=INDEX(groups,2)
Cuối cùng, INDEX trả về "Bear", nhóm mà Adam thuộc về.
Literal chứa các tiêu chí
Để kiểm tra các giá trị văn bản cụ thể thay vì đối sánh chính xác, bạn có thể sử dụng hàm ISNUMBER và SEARCH cùng nhau. Ví dụ: để đối sánh các ô có chứa "apple", bạn có thể sử dụng:
=ISNUMBER(SEARCH("apple",data))
Công thức này được giải thích ở đây.
