Formula umum
=SUMPRODUCT(--(EXACT(val,lookup_col)),result_col)
Ringkasan
Pencarian case sensitive di Excel
Secara default, pencarian standar di Excel tidak peka huruf besar-kecil. Baik VLOOKUP dan INDEX / MATCH hanya akan mengembalikan kecocokan pertama, mengabaikan kapitalisasi.
Cara langsung untuk mengatasi batasan ini, adalah dengan menggunakan rumus array berdasarkan INDEX / MATCH dengan EXACT. Namun, jika Anda hanya mencari nilai numerik, SUMPRODUCT + EXACT juga memberikan cara yang menarik dan fleksibel untuk melakukan pencarian case-sensitive.
Dalam contoh, kami menggunakan rumus berikut
=SUMPRODUCT(--(EXACT(E3,B3:B8)),C3:C8)
Meskipun rumus ini adalah rumus array, rumus ini tidak perlu dimasukkan dengan Control + Shift + Enter, karena SUMPRODUCT menangani array secara native.
Penjelasan
SUMPRODUCT dirancang untuk bekerja dengan array, yang dikalikan, lalu dijumlahkan.
Dalam hal ini, kami adalah dua array dengan SUMPRODUCT: B3: B8 dan C3: C8. Triknya adalah dengan menjalankan pengujian pada nilai di kolom B, lalu ubah hasil nilai TRUE / FALSE menjadi 1 dan 0. Kami menjalankan pengujian dengan PERSIS seperti ini:
EXACT(E3,B3:B8)
Yang menghasilkan larik ini:
(FALSE; FALSE; TRUE; FALSE; FALSE; FALSE)
Perhatikan bahwa nilai sebenarnya di posisi 3 adalah kecocokan kita. Kemudian kita menggunakan negatif ganda (yaitu -, yang secara teknis adalah "unary ganda") untuk memaksa nilai TRUE / FALSE ini menjadi 1 dan 0. Hasilnya adalah array ini:
(0; 0; 1; 0; 0; 0)
Pada titik ini dalam perhitungan, rumus SUMPRODUCT terlihat seperti ini:
=SUMPRODUCT((0;0;1;0;0;0),(875;750;775;675;800;825))
SUMPRODUCT kemudian mengalikan item dalam setiap array bersama-sama untuk menghasilkan array akhir:
(0; 0; 775; 0; 0; 0)
SUMPRODUCT mana yang kemudian menjumlahkan, dan mengembalikan 775.
Jadi, inti dari rumus ini adalah nilai FALSE digunakan untuk membatalkan semua nilai lainnya. Satu-satunya nilai yang bertahan adalah yang BENAR.
Perhatikan bahwa karena kami menggunakan SUMPRODUCT, rumus ini hadir dengan twist unik: jika ada beberapa kecocokan, SUMPRODUCT akan mengembalikan jumlah kecocokan tersebut. Ini mungkin atau mungkin tidak yang Anda inginkan, jadi berhati-hatilah jika Anda mengharapkan banyak kecocokan!
Ingat, rumus ini hanya berfungsi untuk nilai numerik, karena SUMPRODUCT tidak menangani teks. Jika Anda ingin mengambil teks, gunakan INDEX / MATCH + EXACT.
