Công thức chung
=LOOKUP(2,1/SEARCH(things,A1),things)
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 cuối cùng được tìm thấy trong danh sách, bạn có thể sử dụng công thức dựa trên hàm LOOKUP và SEARCH. Trong trường hợp tìm thấy nhiều kết quả phù hợp, công thức sẽ trả về kết quả phù hợp cuối cùng từ danh sách "thứ".
Trong ví dụ được hiển thị, công thức trong C5 là:
=LOOKUP(2,1/SEARCH(things,B5),things)
Giải trình
Bối cảnh: bạn có một danh sách những thứ trong phạm vi được đặt tên là "things" (E5: E8) và bạn muốn kiểm tra các ô trong cột B để xem chúng có chứa những thứ này hay không. Nếu vậy, bạn muốn trả lại mục cuối cùng từ "những thứ" đã được tìm thấy.
Trong công thức này, hàm SEARCH được sử dụng để tìm kiếm các ô trong cột B như sau:
SEARCH(things,B5)
Khi SEARCH tìm thấy một kết quả phù hợp, nó sẽ trả về vị trí của kết quả phù hợp trong ô đang được tìm kiếm. Khi tìm kiếm không thể tìm thấy kết quả phù hợp, nó sẽ trả về lỗi #VALUE. Bởi vì chúng tôi đang cung cấp cho SEARH nhiều hơn một thứ cần tìm, nó sẽ trả về nhiều hơn một kết quả. Trong ví dụ được hiển thị, TÌM KIẾM trả về một mảng kết quả như sau:
(8;24;#VALUE!;#VALUE!)
Mảng này sau đó được sử dụng như một ước số cho số 1. Kết quả là một mảng bao gồm các lỗi và giá trị thập phân. Các lỗi đại diện cho những thứ không tìm thấy và các giá trị thập phân đại diện cho những thứ được tìm thấy. Trong ví dụ được hiển thị, mảng trông như thế này:
(0.125;0.0416666666666667;#VALUE!;#VALUE!)
Mảng này đóng vai trò là "lookup_vector" cho hàm LOOKUP. Giá trị tra cứu được cung cấp dưới dạng số 2 và vectơ kết quả là phạm vi được đặt tên là "thứ". Đây là phần thông minh.
Công thức được xây dựng theo cách sao cho vectơ tra cứu sẽ không bao giờ chứa giá trị lớn hơn 1, trong khi giá trị tra cứu là 2. Điều này có nghĩa là giá trị tra cứu sẽ không bao giờ được tìm thấy. Trong trường hợp này, LOOKUP sẽ khớp với giá trị số cuối cùng được tìm thấy trong mảng, tương ứng với "thứ" cuối cùng được tìm thấy bởi TÌM KIẾM.
Cuối cùng, sử dụng phạm vi được đặt tên là "things" được cung cấp làm vectơ kết quả, LOOKUP trả về điều cuối cùng được tìm thấy.
Với các giá trị được mã hóa cứng
Sử dụng một phạm vi như "things" giúp bạn dễ dàng sửa đổi danh sách các cụm từ tìm kiếm (và thêm các cụm từ tìm kiếm khác), nhưng nó không phải là một yêu cầu bắt buộc. Bạn cũng có thể mã hóa các giá trị trực tiếp vào công thức như sau:
=LOOKUP(2,1/SEARCH(("red","blue","green"),B5),("red","blue","green"))
