Formula umum
=XLOOKUP("*"&value&"*",lookup,results,,2)
Ringkasan
Untuk menggunakan XLOOKUP untuk mencocokkan nilai yang berisi teks tertentu, Anda bisa menggunakan karakter pengganti dan penggabungan. Dalam contoh yang ditunjukkan, rumus di F5 adalah:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
di mana kode (B5: B15) dan kuantitas (C5: C15) diberi nama rentang.
Penjelasan
Fungsi XLOOKUP berisi dukungan bawaan untuk karakter pengganti, tetapi fitur ini harus diaktifkan secara eksplisit dengan menyetel mode pencocokan ke nomor 2.
Dalam contoh yang diperlihatkan, XLOOKUP dikonfigurasi agar sesuai dengan nilai yang dimasukkan di sel E5, yang mungkin muncul di mana saja dalam nilai pencarian di B5: B15. Rumus di F5 adalah:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, dengan tanda bintang (*) bersambung depan dan belakang
- lookup_array - kode rentang bernama (B5: B15)
- return_array - kuantitas rentang bernama (C5: C15)
- if_not_found - string "tidak cocok"
- match_mode - disediakan sebagai 2 (pencocokan karakter pengganti)
- search_mode - tidak tersedia. Default ke 1 (pertama sampai terakhir)
Untuk membuat pencocokan jenis "berisi" otomatis, tanda bintang karakter pengganti (*) diawali dan ditambahkan ke nilai dalam sel E5 dengan penggabungan:
"*"&E5&"*"
Setelah penggabungan, rumusnya menjadi:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP menemukan kecocokan pertama yang berisi "BCC" (050-BCC-123 di baris 10) dan mengembalikan nilai yang sesuai dari larik yang dikembalikan, 50.
Perhatikan bahwa XLOOKUP tidak peka huruf besar / kecil, memasukkan "bcc" di E5 akan mengembalikan hasil yang sama:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Lihat di bawah untuk opsi mengkonfigurasi XLOOKUP untuk kecocokan case-sensitive.
Opsi VLOOKUP
Rumus VLOOKUP juga mendukung karakter pengganti jika disetel ke pencocokan tepat. Rumus VLOOKUP yang setara untuk contoh ini adalah:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Penjelasan lengkapnya di sini.
Dengan SEARCH and FIND
Dimungkinkan juga untuk menggunakan fungsi SEARCH dan FIND untuk melakukan kecocokan tipe "berisi" dengan XLOOKUP. Untuk pencocokan tidak peka huruf besar / kecil (seperti contoh di atas), Anda dapat menggunakan SEARCH seperti ini:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Untuk pertandingan yang peka huruf besar / kecil, Anda dapat menggunakan FIND sebagai gantinya:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Kedua opsi di atas mempermudah perluasan kriteria untuk menyertakan ketentuan lain menggunakan logika boolean.
Logika untuk ISNUMBER + PENCARIAN dijelaskan di sini.
Pertandingan ganda
Jika Anda membutuhkan beberapa kecocokan, lihat fungsi FILTER.
