Công thức Excel: Ví dụ đối sánh ký tự đại diện XLOOKUP
Công thức Excel: Ví dụ đối sánh ký tự đại diện XLOOKUP

Công thức chung
=XLOOKUP(value,lookup,return,"not found",2)
Tóm lược

Để thực hiện đối sánh từng phần với XLOOKUP, hãy cung cấp 2 cho đối số chế độ đối sánh để cho phép ký tự đại diện. Trong ví dụ được hiển thị, công thức trong H7 là:

=TRANSPOSE((XLOOKUP(H4,D5:D15,B5:E15,"Not found",2)))

thực hiện khớp ký tự đại diện với giá trị trong H4 và trả về kết quả là tất cả 4 trường. Hàm TRANSPOSE là tùy chọn và chỉ được sử dụng ở đây để chuyển đổi kết quả từ XLOOKUP sang mảng dọc.

Giải trình

Làm việc từ trong ra ngoài, XLOOKUP được cấu hình để tìm giá trị trong H4 trong cột Họ và trả về tất cả các trường. Để hỗ trợ ký tự đại diện, match_mode được cung cấp dưới dạng 2:

XLOOKUP(H4,D5:D15,B5:E15,2) // match Last, return all fields
  • Các lookup_value đến từ ô H4
  • Các lookup_array là phạm vi D5: D15, chứa Họ
  • Các return_array là B5: E15, chứa tất cả các trường
  • Các không tìm thấy đối số được đặt thành “Không tìm thấy”
  • Các match_mode là 2, để cho phép các ký tự đại diện
  • Các search_mode không được cung cấp và mặc định là 1 (đầu tiên đến cuối cùng)

Vì H4 chứa “corr *”, XLOOKUP tìm Họ đầu tiên bắt đầu bằng “corr” và trả về tất cả bốn trường theo chiều ngang mảng:

{648,"Sharyn","Corriveau","Support"}

Kết quả này được trả về trực tiếp cho hàm TRANSPOSE:

=TRANSPOSE({648,"Sharyn","Corriveau","Support"})

Các Hàm TRANSPOSE thay đổi mảng từ ngang sang dọc:

{648;"Sharyn";"Corriveau";"Support"} // vertical array

và các giá trị mảng tràn vào dãy H7: H10.

Có ký tự đại diện ngầm định

Trong ví dụ trên, ký tự đại diện dấu hoa thị

=TRANSPOSE((XLOOKUP(H4&"*",D5:D15,B5:E15,"Not found",2)))

được nhập rõ ràng vào giá trị tra cứu. Để chuyển ký tự đại diện một cách ngầm định, bạn có thể điều chỉnh công thức như sau: Trên đây, chúng tôi ghép lại dấu hoa thị ký tự đại diện

đến giá trị của H4 trong chính công thức. Thao tác này sẽ thêm dấu hoa thị vào bất kỳ giá trị nào được nhập trong H4 và XLOOKUP sẽ thực hiện tra cứu ký tự đại diện.

https://exceljet.net/formula/xlookup-wildcard-match-example