Formula umum
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Ringkasan
Untuk mencari nilai dengan mencocokkan di beberapa kolom, Anda bisa menggunakan rumus array yang didasarkan pada MMULT, TRANSPOSE, COLUMN, dan INDEX. Pada contoh yang ditampilkan, rumus di H4 adalah:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
di mana "nama" adalah rentang bernama C4: E7, dan "grup" adalah rentang bernama B4: B7. Rumus mengembalikan grup yang dimiliki setiap nama.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control shift enter.
Penjelasan
Bekerja dari dalam ke luar, kriteria logis yang digunakan dalam rumus ini adalah:
--(names=G4)
di mana nama adalah rentang bernama C4: E7. Ini menghasilkan hasil TRUE / FALSE untuk setiap nilai dalam data, dan negatif ganda memaksa nilai TRUE FALSE menjadi 1 dan 0 untuk menghasilkan array seperti ini:
(0,0,0;1,0,0;0,0,0;0,0,0)
Larik ini terdiri dari 4 baris kali 3 kolom, cocok dengan struktur "nama".
Array kedua dibuat dengan ekspresi ini:
TRANSPOSE(COLUMN(names)^0))
Fungsi COLUMN digunakan untuk membuat array numerik dengan 3 kolom dan 1 baris, dan TRANSPOSE mengonversi array ini menjadi 1 kolom dan 3 baris. Menaikkan pangkat nol cukup mengubah semua angka dalam larik menjadi 1. Fungsi MMULT kemudian digunakan untuk melakukan perkalian matriks:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
dan hasilnya masuk ke fungsi MATCH sebagai larik, dengan 1 sebagai nilai pencarian:
MATCH(1,(0;1;0;0),0)
Fungsi MATCH mengembalikan posisi pertandingan pertama, yang sesuai dengan baris dari baris pertama yang cocok yang memenuhi kriteria yang disediakan. Ini dimasukkan ke dalam INDEX sebagai nomor baris, dengan rentang bernama "grup" sebagai larik:
=INDEX(groups,2)
Akhirnya, INDEX mengembalikan "Bear", grup milik Adam.
Literal mengandung kriteria
Untuk memeriksa nilai teks tertentu alih-alih pencocokan tepat, Anda dapat menggunakan fungsi ISNUMBER dan SEARCH bersamaan. Misalnya, untuk mencocokkan sel yang berisi "apel", Anda dapat menggunakan:
=ISNUMBER(SEARCH("apple",data))
Rumus ini dijelaskan di sini.
