Formula generik
(=INDEX(data,MATCH(TRUE,EXACT(val,lookup_col),0),col_num))
Ringkasan
Pencarian sensitif kes
Secara lalai, carian standard dengan VLOOKUP atau INDEX + MATCH tidak peka huruf besar-kecil. Kedua-dua VLOOKUP dan MATCH hanya akan mengembalikan perlawanan pertama, tanpa mengira kes.
Walau bagaimanapun, jika anda perlu melakukan pencarian peka huruf besar kecil, anda boleh melakukannya dengan formula array yang menggunakan fungsi INDEX, MATCH, dan EXACT.
Dalam contohnya, kami menggunakan formula berikut
(=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3))
Formula ini adalah formula larik dan mesti dimasukkan dengan Control + Shift + Enter.
Penjelasan
Oleh kerana MATCH sahaja tidak peka huruf besar kecil, kami memerlukan kaedah untuk mendapatkan Excel untuk membandingkan kes. Fungsi EXACT adalah fungsi yang sempurna untuk ini, tetapi cara kita menggunakannya agak tidak biasa, kerana kita perlu membandingkan satu sel dengan pelbagai sel.
Bekerja dari dalam ke luar, kami mempunyai yang pertama:
EXACT(F4,B3:B102)
di mana F4 mengandungi nilai pencarian, dan B3: B102 adalah rujukan ke ruang carian (Nama pertama). Kerana kami memberikan EXACT array sebagai argumen kedua, kami akan mendapatkan kembali array nilai BENAR seperti ini:
(PALSU, SALAH, SALAH, SALAH, SALAH, BENAR, dll.)
Ini adalah hasil membandingkan nilai dalam B4 setiap sel di ruang carian. Di mana sahaja kita melihat BENAR, kita tahu kita mempunyai padanan tepat yang sesuai dengan kes.
Sekarang kita perlu mendapatkan kedudukan (iaitu nombor baris) dari nilai BENAR dalam array ini. Untuk ini, kita dapat menggunakan MATCH, mencari BENAR dan mengatur dalam mod padanan tepat:
MATCH(TRUE,EXACT(F4,B3:B102),0)
Penting untuk diperhatikan bahawa PERTANDINGAN akan selalu mengembalikan pertandingan pertama jika terdapat pendua, jadi jika terdapat padanan tepat lain di lajur, anda hanya akan mencocokkan yang pertama.
Sekarang kita mempunyai nombor baris. Seterusnya, kita hanya perlu menggunakan INDEX untuk mendapatkan semula nilai di persimpangan baris dan lajur yang betul. Nombor lajur dalam kes ini dikodekan keras sebagai 3, kerana data julat yang dinamakan merangkumi semua lajur. Formula terakhir adalah:
(=INDEX(data,MATCH(TRUE,EXACT(F4,B3:B102),0),3))
Kita mesti memasukkan formula ini sebagai formula array kerana array yang dibuat oleh EXACT.
Formula ini akan mengambil kedua-dua nilai teks dan angka. Sekiranya anda ingin mendapatkan nombor sahaja, anda boleh menggunakan formula berdasarkan SUMPRODUCT; lihat pautan di bawah
