Ringkasan
Untuk mencari data berdasarkan beberapa kriteria kompleks, Anda bisa menggunakan fungsi XLOOKUP dengan beberapa ekspresi berdasarkan logika boolean. Pada contoh yang ditunjukkan, rumus di G5 adalah:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
Dengan pengaturan default XLOOKUP untuk mode pencocokan (tepat) dan mode pencarian (pertama hingga terakhir), rumusnya cocok dengan catatan pertama di mana:
akun dimulai dengan "x" DAN wilayah adalah "timur", dan bulan BUKAN April.
yang merupakan rekor keempat (baris 8) dalam contoh yang ditampilkan.
Penjelasan
Biasanya, fungsi XLOOKUP dikonfigurasi untuk mencari nilai dalam array pencarian yang ada di lembar kerja. Namun, ketika kriteria yang digunakan untuk mencocokkan nilai menjadi lebih kompleks, Anda dapat menggunakan logika boolean untuk membuat array pencarian dengan cepat yang hanya terdiri dari 1 dan 0, lalu mencari nilai 1. Ini adalah pendekatan yang digunakan dalam contoh ini:
=XLOOKUP(1,boolean_array,result_array)
Dalam contoh ini, kriteria yang dibutuhkan adalah:
akun dimulai dengan "x" DAN wilayah adalah "timur", dan bulan BUKAN April.
Untuk masing-masing dari tiga kriteria terpisah di atas, kami menggunakan ekspresi logis terpisah. Ekspresi pertama menggunakan fungsi LEFT untuk menguji apakah Akun dimulai dengan "x":
LEFT(B5:B16)="x" // account begins with "x"
Karena kami memeriksa dua belas nilai, hasilnya adalah array dengan dua belas nilai seperti ini:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
Ekspresi kedua menguji jika Region adalah "east" menggunakan operator sama dengan (=):
C5:C16="east" // region is east
Seperti sebelumnya, kami mendapatkan array lain dengan dua belas nilai TRUE FALSE:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
Ekspresi ketiga perlu mengecualikan bulan April. Cara termudah untuk melakukannya adalah dengan menguji bulan April secara langsung dengan fungsi MONTH:
MONTH(D5:D16)=4 // month is April
Kemudian gunakan fungsi NOT untuk membalikkan hasil:
NOT(MONTH(D5:D16)=4) // month is not April
yang membuat larik dengan benar menggambarkan "bukan April":
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Selanjutnya, ketiga larik dikalikan bersama, dan operasi matematika memaksa nilai TRUE dan FALSE menjadi 1 dan 0:
(1;0;1;1;1;0;0;0;1;1;0;1)* (0;0;1;1;1;0;1;0;0;1;0;1)* (0;0;0;1;1;1;1;1;1;1;1;1)
Dalam aritmatika boolean, perkalian bekerja seperti fungsi logika AND, jadi hasil akhirnya adalah larik tunggal seperti ini:
(0;0;0;1;1;0;0;0;0;1;0;1)
Rumusnya sekarang dapat ditulis ulang seperti ini:
=XLOOKUP(1,(0;0;0;1;1;0;0;0;0;1;0;1),B5:E16)
Dengan 1 sebagai nilai pencarian, dan pengaturan default untuk mode pencocokan (tepat) dan mode pencarian (pertama hingga terakhir), XLOOKUP mencocokkan 1 pertama (posisi keempat) dan mengembalikan baris yang sesuai dalam larik hasil, yaitu B8: E8.
Pertandingan terakhir
Dengan menyetel argumen mode penelusuran opsional ke -1, Anda dapat menemukan "kecocokan terakhir" dengan kriteria yang sama seperti ini:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16,,,-1)
