Formula umum
=XLOOKUP(1,--EXACT(range1,"RED"),range2)
Ringkasan
Untuk membuat pencocokan tepat peka huruf besar / kecil, Anda dapat menggunakan fungsi XLOOKUP dengan fungsi PERSIS. Dalam contoh yang ditunjukkan, rumus di F5 adalah:
=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15)
yang cocok dengan "RED" (case-sensitive) dan mengembalikan seluruh baris.
Penjelasan
Dengan sendirinya, fungsi XLOOKUP tidak peka huruf besar-kecil. Nilai pencarian "RED" akan cocok dengan "red", "RED", atau "Red". Kita dapat mengatasi batasan ini dengan membuat array pencarian yang sesuai untuk XLOOKUP dengan ekspresi logis.
Bekerja dari dalam ke luar, untuk memberikan XLOOKUP kemampuan untuk mencocokkan kasus, kami menggunakan fungsi EXACT seperti ini:
EXACT(B5:B15,"RED") // test for "RED"
Karena ada 11 nilai dalam rentang E5: D15, EXACT mengembalikan larik dengan 11 hasil TRUE FALSE seperti ini:
(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Perhatikan posisi TRUE sesuai dengan baris yang warnanya "MERAH".
Untuk singkatnya (dan untuk memungkinkan logika diperpanjang dengan mudah dengan logika boolean), kami memaksa nilai TRUE FALSE menjadi 1 dan 0 dengan negatif ganda:
--EXACT(B5:B15,"RED") // convert to 1s and 0s
yang menghasilkan array seperti ini:
(0;0;0;0;1;0;0;0;0;0;0)
Perhatikan posisi 1 sesuai dengan baris yang warnanya "MERAH". Larik ini dikembalikan langsung ke fungsi XLOOKUP sebagai argumen larik pencarian.
Kami sekarang dapat dengan mudah rumus untuk:
=XLOOKUP(1,(0;0;0;0;1;0;0;0;0;0;0),B5:D15)
Dengan nilai pencarian 1, XLOOKUP menemukan 1 di posisi ke-5, dan mengembalikan baris ke-5 dalam array yang dikembalikan, B9: D9.
Memperluas logika
Struktur logika dapat dengan mudah diperpanjang. Misalnya, untuk mempersempit kecocokan menjadi "MERAH" di bulan April, Anda dapat menggunakan rumus seperti ini:
=XLOOKUP(1,EXACT(B5:B15,"RED")*(MONTH(C5:C15)=4),B5:D15)
Di sini, karena masing-masing dari dua ekspresi mengembalikan larik nilai TRUE FALSE, dan karena larik ini dikalikan bersama, operasi matematika memaksa nilai TRUE dan FALSE menjadi 1 dan 0. Tidak perlu menggunakan negatif ganda.
Karena nilai pencarian tetap 1, seperti pada rumus di atas.
Pertandingan pertama dan terakhir
Kedua rumus di atas akan mengembalikan kecocokan pertama dari "MERAH" dalam kumpulan data. Jika Anda membutuhkan kecocokan terakhir, Anda dapat melakukan pencarian terbalik dengan mengatur argumen mode pencarian untuk XLOOKUP ke -1:
=XLOOKUP(1,--EXACT(B5:B15,"RED"),B5:D15,,,-1) // last match
Jika Anda perlu mengembalikan hasil dari beberapa kecocokan, lihat fungsi FILTER.
