Formula generik
=INDEX(range1,MATCH(TRUE,EXACT(A1,range2),0))
Ringkasan
Untuk melakukan carian sensitif huruf, anda boleh menggunakan fungsi EXACT bersama dengan INDEX dan MATCH. Dalam pertunjukan contoh, formula dalam F5 adalah:
=INDEX(C5:C14,MATCH(TRUE,EXACT(E5,B5:B14),0))
yang berusia 39 tahun, usia "JILL SMITH".
Catatan: ini adalah formula larik dan mesti dimasukkan dengan Control + Shift + Enter, kecuali di Excel 365.
Penjelasan
Pada intinya, ini adalah formula INDEX dan MATCH, dengan fungsi EXACT yang digunakan di dalam MATCH untuk melakukan padanan huruf besar-kecil.
Bekerja dari dalam ke luar, EXACT dikonfigurasi untuk membandingkan nilai di E5 dengan nama dalam julat B5: B14:
EXACT(E5,B5:B14) // returns array of results
Fungsi EXACT melakukan perbandingan peka huruf besar kecil dan mengembalikan BENAR atau SALAH. Hanya nilai yang sepadan dengan tepat yang akan mengembalikan BENAR. Kerana kami memeriksa nama di E5 ("JILL SMITH") terhadap semua sepuluh nama dalam julat B5: B14, kami mendapat kembali sepuluh nilai BENAR dan SALAH seperti ini:
(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE)
Susunan ini dikembalikan secara langsung ke fungsi MATCH sebagai lookup_array seperti ini:
MATCH(TRUE,(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE),0)
Dengan nilai carian TRUE, MATCH mengembalikan 5, kerana satu-satunya TRUE dalam array berada di kedudukan kelima. Perhatikan juga bahawa match_type diatur ke zero (0) untuk memaksa match yang tepat.
Fungsi MATCH mengembalikan hasil terus ke fungsi INDEX sebagai nombor baris, jadi kita sekarang dapat menulis semula formula seperti ini:
=INDEX(C5:C14,5) // returns 39
INDEX mengembalikan usia di baris kelima, 39, sebagai hasil akhir.
Kerana menggunakan fungsi EXACT seperti ini adalah operasi array, formula itu adalah formula array dan mesti dimasukkan dengan Control + Shift + Enter, kecuali di Excel 365.
Dengan XLOOKUP
Dalam Excel 365, fungsi XLOOKUP dapat dikonfigurasikan untuk melakukan pencarian sensitif huruf dengan cara yang serupa seperti ini:
=XLOOKUP(TRUE,EXACT(J5,B5:B14),C5:C14,"na",0)
Perhatikan nilai carian dan tatasusunan disusun seperti fungsi MATCH di atas. Selepas EXACT berjalan, kami mempunyai:
=XLOOKUP(TRUE,(FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE),C5:C14,"na",0)
dan XLOOKUP mengembalikan item ke-5 dari julat C5: C14 (39) sebagai hasil akhir.
