Công thức chung
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Tóm lược
Để kiểm tra một ô cho một trong một số chuỗi và trả về kết quả tùy chỉnh cho kết quả phù hợp đầu tiên được tìm thấy, bạn có thể sử dụng công thức INDEX / MATCH dựa trên hàm TÌM KIẾM. Trong ví dụ được hiển thị, công thức trong C5 là:
(=INDEX(results,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
trong đó các thứ (E5: E8) và kết quả (F5: F8) là các phạm vi được đặt tên.
Đây là công thức mảng và phải được nhập bằng Control + Shift + Enter.
Giải trình
Công thức này sử dụng hai phạm vi được đặt tên: thứ và kết quả . Nếu bạn đang chuyển trực tiếp công thức này, hãy đảm bảo sử dụng các dải ô được đặt tên có cùng tên (được xác định dựa trên dữ liệu của bạn). Nếu bạn không muốn sử dụng phạm vi đã đặt tên, hãy sử dụng tham chiếu tuyệt đối để thay thế.
Cốt lõi của công thức này là đoạn mã này:
ISNUMBER(SEARCH(things,B5)
Điều này dựa trên một công thức khác (được giải thích chi tiết ở đây) để kiểm tra một ô cho một chuỗi con. Nếu ô chứa chuỗi con, công thức trả về TRUE. Nếu không, công thức trả về FALSE.
Bởi vì chúng tôi đang đưa ra các chức năng TÌM KIẾM nhiều hơn một điều cần xem xét cho, trong phạm vi tên điều , nó sẽ cung cấp cho chúng tôi biết thêm một trong những kết quả, trong một mảng mà trông như sau:
(#VALUE!;9;#VALUE!;#VALUE!)
Các con số đại diện cho sự trùng khớp trong sự vật , lỗi đại diện cho các vật phẩm không được tìm thấy.
Để đơn giản hóa mảng, chúng tôi sử dụng hàm ISNUMBER để chuyển đổi tất cả các mục trong mảng thành TRUE hoặc FALSE. Mọi số hợp lệ đều trở thành TRUE và bất kỳ lỗi nào (tức là không tìm thấy thứ) sẽ trở thành FALSE. Kết quả là một mảng như thế này:
(FALSE;TRUE;FALSE;FALSE)
đi vào hàm MATCH dưới dạng đối số lookup_array, với giá trị lookup_value là TRUE:
MATCH(TRUE,(FALSE;TRUE;FALSE;FALSE),0) // returns 2
MATCH sau đó trả về vị trí của TRUE đầu tiên được tìm thấy, 2 trong trường hợp này.
Cuối cùng, chúng tôi sử dụng hàm INDEX để lấy kết quả từ các kết quả phạm vi đã đặt tên tại cùng vị trí đó:
=INDEX(results,2) // returns "found red"
Bạn có thể tùy chỉnh phạm vi kết quả với bất kỳ giá trị nào phù hợp trong trường hợp sử dụng của mình.
Ngăn chặn kết quả phù hợp sai
Một vấn đề với phương pháp này với phương pháp ISNUMBER + TÌM KIẾM là bạn có thể nhận được kết quả phù hợp sai từ các kết quả phù hợp từng phần bên trong các từ dài hơn. Ví dụ: nếu bạn cố gắng kết hợp "dr", bạn cũng có thể tìm thấy "Andrea", "đã uống", "nhỏ giọt", v.v. vì "dr" xuất hiện bên trong những từ này. Điều này xảy ra bởi vì TÌM KIẾM tự động thực hiện đối sánh "loại chứa".
Để khắc phục nhanh, bạn có thể bọc các từ tìm kiếm trong các ký tự khoảng trắng (tức là "dr" hoặc "dr") để tránh tìm thấy "dr" trong một từ khác. Nhưng điều này sẽ không thành công nếu "dr" xuất hiện đầu tiên hoặc cuối cùng trong một ô.
Nếu bạn cần một giải pháp mạnh mẽ hơn, một tùy chọn là chuẩn hóa văn bản trước trong cột trợ giúp và thêm khoảng trắng ở đầu và cuối. Sau đó, sử dụng công thức trên trang này trên văn bản trong cột trợ giúp, thay vì văn bản gốc.
