Công thức Excel: Tra cứu và chuyển đổi nhiều tiêu chí -

Mục lục

Công thức chung

(=INDEX(rng1,MATCH(1,($A1=rng2)*(B$1=rng3),0)))

Tóm lược

Để thực hiện tra cứu nhiều tiêu chí và chuyển kết quả thành một bảng, bạn có thể sử dụng công thức mảng dựa trên INDEX và MATCH. Trong ví dụ được hiển thị, công thức trong G5 là:

(=INDEX(amount,MATCH(1,($F5=location)*(G$4=date),0)))

Lưu ý rằng công thức này là công thức mảng và phải được nhập bằng control + shift + enter.

Công thức này cũng sử dụng ba phạm vi được đặt tên: vị trí = B5: B13, số tiền = D5: D13, ngày = C5: C13

Giải trình

Cốt lõi của công thức này là INDEX, đang truy xuất một giá trị từ phạm vi được đặt tên "số tiền" (B5: B13):

=INDEX(amount,row_num)

nơi row_num được thực hiện với hàm MATCH và một số logic boolean:

MATCH(1,($F5=location)*(G$4=date),0)

Trong đoạn mã này, vị trí trong F5 được so sánh với tất cả các vị trí và ngày trong G4 được so sánh với tất cả các ngày. Kết quả trong mỗi trường hợp là một mảng các giá trị TRUE và FALSE. Khi các mảng này được nhân với nhau, phép toán sẽ ép các giá trị TRUE và FALSE thành một và các số không, để mảng tra cứu đi vào MATCH trông giống như sau:

(1;0;0;0;0;0;0;0;0)

MATCH được thiết lập để khớp 1 dưới dạng khớp chính xác và trả về vị trí INDEX dưới dạng số hàng. Số 1 hoạt động cho giá trị tra cứu vì mảng bây giờ chỉ chứa 1 và 0, như được hiển thị ở trên.

F5 và G4 được nhập dưới dạng tham chiếu hỗn hợp để công thức có thể được sao chép qua bảng mà không cần sửa đổi.