Pencarian Dua Arah dengan INDEX-MATCH-MATCH

Bayangkan kisi penjualan bulanan dengan wilayah tersusun ke bawah di sebelah kiri dan bulan tersusun mendatar di bagian atas. Anda ingin mengetahui angka pada pertemuan wilayah dan bulan yang dipilih.

Pencarian biasa menemukan nilai dengan mencari hanya dalam satu arah. Pencarian dua arah mencari ke dua arah sekaligus: menemukan baris dan kolom yang tepat, lalu mengembalikan nilai pada titik perpotongannya.

Alat klasik untuk ini adalah INDEX yang digabungkan dengan dua pemanggilan MATCH, yang sering ditulis sebagai INDEX-MATCH-MATCH.

INDEX mengembalikan nilai dari suatu rentang berdasarkan posisinya. Bentuk lengkapnya adalah INDEX(array, row_num, column_num).

Berikan blok sel, nomor baris, dan nomor kolom, lalu fungsi ini mengembalikan nilai pada posisi tersebut. Misalnya, dalam kisi yang dimulai dari B2, permintaan baris 3 dan kolom 2 mengembalikan nilai yang berada 3 baris ke bawah dan 2 kolom ke samping di dalam blok tersebut.

Gagasan utamanya: INDEX memerlukan posisi, bukan label. Itulah yang disediakan oleh MATCH.

MATCH menemukan posisi suatu nilai di dalam satu baris atau kolom. Bentuknya adalah MATCH(lookup_value, lookup_array, match_type).

Gunakan 0 sebagai jenis pencocokan untuk pencocokan tepat. Hasilnya berupa angka yang menunjukkan posisi nilai, dihitung mulai dari 1.

Jika "East" adalah item kedua dalam rentang A2:A5, MATCH mengembalikan 2. Angka 2 tersebut dapat menjadi nomor baris untuk INDEX.

Untuk pencarian dua arah, jalankan MATCH dua kali:

• Satu MATCH menemukan posisi baris wilayah Anda.
• Satu MATCH menemukan posisi kolom bulan Anda.

Selanjutnya, masukkan kedua angka tersebut ke INDEX. MATCH untuk baris mencari rentang label vertikal; MATCH untuk kolom mencari rentang tajuk horizontal.

Hasilnya adalah satu sel tempat baris dan kolom tersebut berpotongan.

Bayangkan tata letak berikut. Label wilayah berada di A2:A5 (East, West, North, South). Tajuk bulan berada di B1:D1 (Jan, Feb, Mar). Angka penjualan sebenarnya mengisi B2:D5.

Dua sel masukan mengendalikan pencarian: G1 berisi wilayah yang Anda inginkan, dan G2 berisi bulan yang Anda inginkan.

Tujuan kita adalah membuat satu rumus yang membaca G1 dan G2 lalu mengembalikan angka penjualan yang cocok dari B2:D5.

Pertama, temukan wilayahnya. MATCH mencari nilai yang diketik di G1 dalam daftar label vertikal A2:A5.

Jika G1 berisi "North" dan North merupakan label ketiga, MATCH ini mengembalikan 3.

Angka ini memberi tahu INDEX baris mana dari blok data yang harus dibaca. Perhatikan bahwa kita mencari A2:A5, yaitu hanya labelnya, bukan datanya, sehingga posisi 3 sejajar dengan baris data ketiga.

Berikutnya, temukan bulannya. MATCH ini mencari nilai di G2 dalam baris tajuk horizontal B1:D1.

Jika G2 berisi "Feb" dan Feb merupakan tajuk kedua, MATCH mengembalikan 2.

Angka tersebut menjadi posisi kolom untuk INDEX. Sama seperti pada baris, kita hanya mencari tajuk B1:D1 agar posisinya sejajar dengan kolom data di B2:D5.

Sekarang bungkus kedua pemanggilan MATCH di dalam INDEX. Blok data B2:D5 adalah lariknya, MATCH untuk baris menyediakan nomor baris, dan MATCH untuk kolom menyediakan nomor kolom.

Saat G1 berisi "North" dan G2 berisi "Feb", MATCH untuk baris menghasilkan 3 dan MATCH untuk kolom menghasilkan 2, sehingga INDEX mengembalikan nilai pada baris 3, kolom 2 dari B2:D5.

Rumus tunggal ini merupakan pencarian dua arah yang lengkap.

Misalkan B2:D5 berisi: baris North memiliki Jan 50, Feb 80, Mar 65.

• MATCH("North", A2:A5, 0) mengembalikan 3.
• MATCH("Feb", B1:D1, 0) mengembalikan 2.
• INDEX(B2:D5, 3, 2) membaca baris 3, kolom 2, lalu mengembalikan 80.

Ubah G1 menjadi "East" atau G2 menjadi "Mar", maka seluruh rumus akan segera menghitung ulang. Itulah kekuatan INDEX yang digerakkan oleh dua pencarian MATCH.

VLOOKUP hanya mencari di kolom pertama dan mengembalikan nilai sejumlah kolom tetap di sebelah kanan. Untuk mengganti bulan, Anda harus memasukkan indeks kolom secara langsung atau menghitungnya sendiri.

INDEX-MATCH-MATCH memungkinkan baris dan kolom dipilih secara dinamis berdasarkan label. Anda dapat mengubah urutan kolom atau menambahkan bulan baru, dan rumusnya tetap berfungsi karena pencocokan dilakukan berdasarkan teks header, bukan jumlah kolom yang tetap.

Kesalahan yang paling umum adalah rentang yang tidak sesuai. Rentang MATCH baris harus memiliki tinggi yang sama dengan blok data INDEX, dan rentang MATCH kolom harus memiliki lebar yang sama.

Di sini, A2:A5 memiliki tinggi 4 baris dan B2:D5 juga memiliki tinggi 4 baris, sehingga hasil MATCH sebesar 3 benar-benar berarti baris data ketiga. Jika Anda tidak sengaja mencari di A1:A5 (yang menyertakan header), posisinya bergeser satu baris dan Anda mendapatkan sel yang salah.

Uji pemahaman Anda tentang pola pencarian dua arah.

Anda telah mempelajari pola pencarian dua arah:

• INDEX mengembalikan nilai pada posisi baris dan kolom di dalam sebuah blok.
• Satu MATCH menemukan baris dengan mencari label secara vertikal.
• MATCH kedua menemukan kolom dengan mencari header secara horizontal.

Rumus gabungan =INDEX(data, MATCH(row), MATCH(col)) membaca dua input dan mengembalikan nilai pada titik perpotongannya. Pastikan rentang MATCH berukuran sama dengan blok data agar tidak terjadi ketidaksesuaian.