Nhận ô phù hợp cuối cùng chứa trong excel
Công thức chung
= LOOKUP ( 2 , 1 / TÌM KIẾM ( mọi thứ , A1 ), mọi thứ )
Mời bạn xem thêm:
>> tra cứu mã bảo hiểm xã hội
>> cách tính thuế ngược
>> mẫu đơn xin nghỉ chữa bệnh dài ngày
Giải trình
Để kiểm tra một ô cho một trong nhiều thứ và trả về kết quả 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ả, công thức sẽ trả lại kết quả cuối cùng từ danh sách “sự vật”.
Trong ví dụ được hiển thị, công thức trong C5 là:
= LOOKUP ( 2 , 1 / TÌM KIẾM ( mọi thứ , B5 ), mọi thứ )
Cách thức hoạt động của công thức này
Bối cảnh: bạn có một danh sách các thứ trong phạm vi được đặt tên là “mọi thứ” (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:
TÌM KIẾM ( mọi thứ , 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ả khớ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 cho SEARH nhiều thứ để tìm kiếm, nó sẽ trả lại nhiều kết quả. Trong ví dụ được hiển thị, SEARCH trả về một mảng các kết quả như sau:
{ 8 ; 24 ; #GIÁ TRỊ! ; #GIÁ TRỊ! }
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 được 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 giống như sau:
{ 0,125 ; 0,0416666666666667 ; #GIÁ TRỊ! ; #GIÁ TRỊ! }
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à vector kết quả là phạm vi được đặt tên là “vật”. Đâ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, hàm 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 “điều” cuối cùng được tìm thấy bởi SEARCH.
Cuối cùng, bằng cách sử dụng phạm vi được đặt tên là “những thứ” được cung cấp dưới dạng vector kết quả, hàm LOOKUP trả về điều cuối cùng được tìm thấy.
Với các giá trị mã hóa cứng
Sử dụng phạm vi như “mọi thứ” giúp dễ dàng sửa đổi danh sách cụm từ tìm kiếm (và thêm cụm từ tìm kiếm khác), nhưng không phải là yêu cầu. 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 / TÌM KIẾM ( { “đỏ” , “xanh dương” , “xanh lục” } , B5 ), { “đỏ” , “xanh dương” , “xanh lục” } )