Công thức chung
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,A1)),0)))
Tóm lược
Để kiểm tra một ô cho một trong số những thứ và trả về kết quả phù hợp đầu tiên được tìm thấy trong danh sách, bạn có thể sử dụng công thức INDEX / MATCH sử dụng SEARCH hoặc FIND để tìm một kết quả phù hợp. Trong ví dụ được hiển thị, công thức trong C5 là:
(=INDEX(things,MATCH(TRUE,ISNUMBER(SEARCH(things,B5)),0)))
trong đó "thứ" là phạm vi được đặt tên E5: E9.
Lưu ý: đây là công thức mảng và phải được nhập bằng Control + Shift + Enter.
Giải trình
Trong ví dụ này, chúng tôi có một danh sách các màu trong phạm vi được đặt tên là "thứ" (E5: E9). Chúng tôi muốn kiểm tra văn bản trong cột B để xem nó có chứa bất kỳ màu nào trong số này không. Nếu vậy, chúng tôi muốn trả về tên của màu đầu tiên được tìm thấy.
Làm việc từ trong ra ngoài, công thức này sử dụng hàm ISNUMBER và hàm TÌM KIẾM để tìm kiếm văn bản trong B5 cho từng màu được liệt kê trong "những thứ" như sau:
ISNUMBER(SEARCH(things,B5)
Biểu thức này dựa trên một công thức (được giải thích chi tiết ở đây) kiểm tra một ô để tìm một chuỗi con. Nếu ô chứa chuỗi con, biểu thức trả về TRUE. Nếu không, biểu thức trả về FALSE.
Khi chúng ta cung cấp cho TÌM KIẾM này một danh sách các thứ (thay vì một thứ) sẽ trả lại cho chúng ta một mảng kết quả. Mỗi màu được tìm thấy sẽ tạo ra một vị trí số và các màu không được tìm thấy sẽ tạo ra lỗi:
(#VALUE!;#VALUE!;20;#VALUE!;#VALUE!)
Sau đó, hàm ISNUMBER chuyển đổi kết quả thành giá trị TRUE / FALSE. Bất kỳ số nào trở thành TRUE và bất kỳ lỗi nào (không tìm thấy) trở thành FALSE. Kết quả là một mảng như thế này:
(FALSE;FALSE;TRUE;FALSE;FALSE)
Mảng này được trả về hàm MATCH dưới dạng đối số mảng. Giá trị tra cứu là TRUE và loại đối sánh được đặt thành 0 để bắt buộc đối sánh chính xác. Khi có một màu phù hợp, MATCH trả về vị trí TRUE được tìm thấy đầu tiên. Giá trị này được đưa vào hàm INDEX dưới dạng số hàng, với phạm vi được đặt tên là "thứ" được cung cấp dưới dạng mảng. Khi có ít nhất một kết quả phù hợp, INDEX trả về màu tại vị trí đó. Khi không tìm thấy kết quả phù hợp nào, công thức này trả về lỗi # N / A.
Với các giá trị được mã hóa cứng
Nếu bạn không muốn thiết lập một phạm vi được đặt tên bên ngoài như "things" trong ví dụ này, bạn có thể mã hóa các giá trị vào công thức dưới dạng "hằng số mảng" như sau:
(=INDEX(("red","green","blue"),MATCH(TRUE,ISNUMBER(SEARCH(("red","green","blue"),B5)),0)))
Nhận kết quả phù hợp đầu tiên trong ô
Ngôn ngữ ở đây khá khó hiểu, nhưng công thức trên sẽ trả về kết quả phù hợp đầu tiên được tìm thấy trong danh sách những thứ cần tìm. Thay vào đó, nếu bạn muốn trả về kết quả phù hợp đầu tiên được tìm thấy trong ô đang được kiểm tra, bạn có thể thử một công thức như sau:
=INDEX(things,MATCH(AGGREGATE(15,6,SEARCH(things,A1),1),SEARCH(things,A1),0))
Trong phiên bản này của công thức, hàm MATCH được thiết lập để tìm kiếm kết quả của đoạn mã này:
AGGREGATE(15,6,SEARCH(things,A1),1) // get min value
trong đó sử dụng hàm AGGREGATE để nhận giá trị nhỏ nhất trong kết quả được trả về bởi SEARCH. Chúng ta cần AGGREGATE ở đây, bởi vì mảng đến có thể sẽ chứa lỗi (được SEARCH trả về khi không tìm thấy thứ) và chúng ta cần một hàm sẽ bỏ qua những lỗi đó và vẫn cung cấp cho chúng ta giá trị số tối thiểu.
Kết quả từ AGGREGATE được trả về trực tiếp cho MATCH dưới dạng giá trị tra cứu, cùng với cùng một mảng được SEARCH trả về. Kết quả cuối cùng là kết quả đầu tiên được tìm thấy trong ô, không phải là kết quả đầu tiên được tìm thấy trong danh sách mọi thứ.
