Formula generik
=XLOOKUP("*"&value&"*",lookup,results,,2)
Ringkasan
Untuk menggunakan XLOOKUP untuk memadankan nilai yang mengandungi teks tertentu, anda boleh menggunakan wildcard dan gabungan. Dalam contoh yang ditunjukkan, formula dalam F5 adalah:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
di mana kod (B5: B15) dan kuantiti (C5: C15) dinamakan julat.
Penjelasan
Fungsi XLOOKUP mengandungi sokongan bawaan untuk wildcard, tetapi ciri ini mesti diaktifkan secara eksplisit dengan menetapkan mod padanan ke nombor 2.
Dalam contoh yang ditunjukkan, XLOOKUP dikonfigurasi agar sesuai dengan nilai yang dimasukkan dalam sel E5, yang mungkin muncul di mana saja dalam nilai pencarian di B5: B15. Formula dalam F5 adalah:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, dengan tanda bintang (*) bersambung depan dan belakang
- lookup_array - kod julat bernama (B5: B15)
- return_array - kuantiti julat yang dinamakan (C5: C15)
- if_not_found - rentetan "tidak sepadan"
- match_mode - disediakan sebagai 2 (pertandingan wildcard)
- search_mode - tidak disediakan. Lalai ke 1 (pertama hingga terakhir)
Untuk menjadikan padanan jenis "mengandung" secara automatik, tanda bintang wildcard (*) disiapkan dan ditambahkan pada nilai dalam sel E5 dengan gabungan:
"*"&E5&"*"
Selepas penggabungan, formula menjadi:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP mencari perlawanan pertama yang mengandungi "BCC" (050-BCC-123 pada baris 10) dan mengembalikan nilai yang sesuai dari array kembali, 50.
Perhatikan bahawa XLOOKUP tidak peka huruf besar kecil, memasukkan "bcc" di E5 akan menghasilkan hasil yang sama:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Lihat di bawah untuk pilihan untuk mengkonfigurasi XLOOKUP untuk padanan huruf besar-kecil.
Pilihan VLOOKUP
Formula VLOOKUP juga menyokong wildcard apabila disetel dengan tepat. Formula VLOOKUP yang setara untuk contoh ini ialah:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Penjelasan penuh di sini.
Dengan CARI dan CARI
Anda juga boleh menggunakan fungsi SEARCH dan FIND untuk melakukan padanan jenis "mengandung" dengan XLOOKUP. Untuk padanan tidak peka huruf besar kecil (seperti contoh di atas), anda boleh menggunakan CARI seperti ini:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Untuk padanan huruf besar-kecil, anda boleh menggunakan FIND:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Kedua pilihan di atas memudahkan memperluas kriteria untuk memasukkan syarat lain menggunakan logik boolean.
Logik untuk PENCARIAN ISNUMBER + dijelaskan di sini.
Pelbagai perlawanan
Sekiranya anda memerlukan banyak perlawanan, lihat fungsi FILTER.
