Công thức chung
=XLOOKUP("*"&value&"*",lookup,results,,2)
Tóm lược
Để sử dụng XLOOKUP để khớp các giá trị có chứa văn bản cụ thể, bạn có thể sử dụng ký tự đại diện và nối. Trong ví dụ được hiển thị, công thức trong F5 là:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
trong đó mã (B5: B15) và số lượng (C5: C15) là các phạm vi được đặt tên.
Giải trình
Hàm XLOOKUP có hỗ trợ tích hợp cho các ký tự đại diện, nhưng tính năng này phải được bật rõ ràng bằng cách đặt chế độ đối sánh thành số 2.
Trong ví dụ được hiển thị, XLOOKUP được định cấu hình để khớp với giá trị được nhập vào ô E5, giá trị này có thể xuất hiện ở bất kỳ đâu trong các giá trị tra cứu trong B5: B15. Công thức trong F5 là:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, với dấu hoa thị (*) được nối trước và sau
- lookup_array - mã dải ô được đặt tên (B5: B15)
- return_array - số lượng phạm vi được đặt tên (C5: C15)
- if_not_found - chuỗi "không khớp"
- match_mode - được cung cấp dưới dạng 2 (đối sánh ký tự đại diện)
- search_mode - không được cung cấp. Mặc định là 1 (đầu tiên đến cuối cùng)
Để tự động khớp loại "chứa", dấu hoa thị ký tự đại diện (*) vừa được thêm vào trước vừa được nối vào giá trị trong ô E5 bằng cách nối:
"*"&E5&"*"
Sau khi nối, công thức trở thành:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP xác định vị trí khớp đầu tiên có chứa "BCC" (050-BCC-123 trong hàng 10) và trả về giá trị tương ứng từ mảng trả về, 50.
Lưu ý rằng XLOOKUP không phân biệt chữ hoa chữ thường, nhập "bcc" vào E5 sẽ trả về kết quả tương tự:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Xem bên dưới để biết tùy chọn định cấu hình XLOOKUP cho kết quả phân biệt chữ hoa chữ thường.
Tùy chọn VLOOKUP
Công thức VLOOKUP cũng hỗ trợ các ký tự đại diện khi được đặt thành đối sánh chính xác. Công thức VLOOKUP tương đương cho ví dụ này là:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Giải thích đầy đủ ở đây.
Với SEARCH và FIND
Cũng có thể sử dụng các hàm SEARCH và FIND để thực hiện đối sánh kiểu "chứa" với XLOOKUP. Đối với đối sánh không phân biệt chữ hoa chữ thường (như ví dụ ở trên), bạn có thể sử dụng TÌM KIẾM như sau:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Đối với kết hợp phân biệt chữ hoa chữ thường, bạn có thể sử dụng FIND để thay thế:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Cả hai tùy chọn trên giúp dễ dàng mở rộng tiêu chí để bao gồm các điều kiện khác bằng cách sử dụng logic boolean.
Logic cho ISNUMBER + SEARCH được giải thích ở đây.
Nhiều trận đấu
Nếu bạn cần nhiều kết quả phù hợp, hãy xem chức năng FILTER.
