Công thức chung
=INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0))
Tóm lược
Để truy xuất kết quả phù hợp đầu tiên trong hai phạm vi giá trị, bạn có thể sử dụng công thức dựa trên các hàm INDEX, MATCH và COUNTIF. Trong ví dụ được hiển thị, công thức trong G5 là:
=INDEX(range2,MATCH(TRUE,COUNTIF(range1,range2)>0,0))
trong đó "range1" là dải ô được đặt tên B5: B8, "range2" là dải ô được đặt tên D5: D7.
Giải trình
Trong ví dụ này, phạm vi được đặt tên "range1" tham chiếu đến các ô B5: B8 và phạm vi được đặt tên "range2" tham chiếu đến D5: D7. Chúng tôi đang sử dụng phạm vi được đặt tên chỉ để thuận tiện và dễ đọc; công thức cũng hoạt động tốt với các tham chiếu ô thông thường.
Cốt lõi của công thức này là INDEX và MATCH. Hàm INDEX lấy một giá trị từ range2 đại diện cho giá trị đầu tiên trong range2 được tìm thấy trong range1. Hàm INDEX yêu cầu một chỉ mục (số hàng) và chúng tôi tạo giá trị này bằng cách sử dụng hàm MATCH, được đặt để khớp với giá trị TRUE trong phần này của công thức:
MATCH(TRUE,COUNTIF(range1,range2)>0,0)
Ở đây, giá trị đối sánh là TRUE và mảng tra cứu được tạo bằng COUNTIF tại đây:
COUNTIF(range1,range2)>0
COUNTIF trả về tổng số các giá trị range2 xuất hiện trong range1. Vì range2 chứa nhiều giá trị, nên COUNTIF sẽ trả về nhiều kết quả giống như sau:
(0;0;1)
Chúng tôi sử dụng "> 0" để buộc tất cả các kết quả phải TRUE hoặc FALSE:
(FALSE;FALSE;TRUE)
Sau đó MATCH thực hiện công việc của nó và trả về vị trí của TRUE đầu tiên (nếu có) xuất hiện, trong trường hợp này là số 3.
Cuối cùng, INDEX trả về giá trị tại vị trí đó, "Màu đỏ".
