Otomatisasi dengan Fungsi Index dan Match pada Excel

Bayangkan jika Anda harus membuat ribuan dokumen yang isinya secara umum sama namun ditujukan kepada ribuan orang yang berbeda. Jika dibuat satu per satu, mau sampai kapan jadinya? Solusinya, kita bisa buat formulir yang standar untuk semua orang kemudian memanggil data yang diinginkan sehingga dapat terisi secara otomatis. Dengan cara tersebut, kita dapat menyelesaikannya dengan lebih cepat dan dapat mengurangi tingkat kesalahan.

Bagaimana caranya jika kita menggunakan program aplikasi Microsoft Excel? Tidak perlu mengedit visual basic (VBA) secara manual, kita bisa memanfaatkan fungsi vlookup atau bisa juga menggunakan kombinasi fungsi index dan match. Syaratnya, data yang yang kita miliki harus tertata secara sistematis. Pada kesempatan kali ini kita akan membahas bagaimana menggunakan alternatif yang terakhir. Memang rumusannya akan lebih rumit dibandingkan dengan alternatif pertama namun kelebihannya yaitu data tidak perlu diurutkan berdasarkan abjad.

Misalnya kita ingin membuat surat pemberitahuan mengenai hasil Ujian Nasional yang dikirimkan via pos ke masing-masing alamat siswa yang mengikuti ujian tersebut. Langkah pertama yang harus dilakukan adalah membuat tabel yang berisi nama-nama siswa tersebut, nomor kartu pelajar, alamat, nilai, dan status kelulusan. Data tersebut kita buat di sheet 1 seperti yang terlihat pada gambar di bawah ini.

Kemudian kita buat formulir standar yang akan dikirimkan ke alamat masing-masing siswa. Contoh sederhannya seperti yang terlihat pada gambar berikut ini. Kita berharap isian mengenai nama, alamat, nilai, dan status kelulusan akan terisi secara otomatis apabila kita memasukkan kata kunci berupa nomor kartu pelajar.

Untuk keperluan otomatisasi tersebut kita perlu memasukkan rumus yang melibatkan fungsi index dan fungsi match ke cell F9 sampai dengan F11. Pada dasarnya fungsi index digunakan untuk memunculkan data pada baris dan kolom tertentu. Secara umum data mengenai nama berada pada kolom ke-2, alamat pada kolom ke-3, nilai pada kolom ke-4, dan status terletak pada kolom ke-5 (lihat sheet1). Sedangkan baris tempat data belum diketahui, tergantung data mana yang akan kita panggil. Untuk itulah kita membutuhkan fungsi match yang akan digunakan dalam rangka cocokkan kata kunci yang ada di sheet1 dan sheet2 yaitu berupa nomor kartu pelajar. Fungsi match inilah yang berperan mencari baris mana data yang diinginkan berada.

Untuk cell F9 masukkan rumus:

=INDEX(Sheet1!$A$1:$E$9;MATCH(Sheet2!$F$8;Sheet1!$A$2:$A$9;0);2)

Untuk cell F10 masukkan rumus:

=INDEX(Sheet1!$A$1:$E$9;MATCH(Sheet2!$F$8;Sheet1!$A$2:$A$9;0);3)

Untuk cell F11 masukkan rumus:

=INDEX(Sheet1!$A$1:$E$9;MATCH(Sheet2!$F$8;Sheet1!$A$2:$A$9;0);4)

Untuk cell F12 masukkan rumus:

=INDEX(Sheet1!$A$1:$E$9;MATCH(Sheet2!$F$8;Sheet1!$A$2:$A$9;0);5)

Jika kita perhatikan rumus untuk keempat cell di atas hampir sama. Yang berbeda hanya di bagian terakhir (berwarna biru) yaitu bagian yang menentukan pada kolom ke berapa data yang diinginkan berada. Bagian yang berwarna merah menyatakan tabel tempat data tersebut berada. Sedangkan bagian bagian yang berwarna kuning berguna untuk menentukan baris lokasi tempat data yang diinginkan berada. Dengan demikian jika mengetikkan nomor kartu pelajar pada cell F8 formulir standar maka secara otomatis cell F9 sampai dengan cell F12 akan terisi dengan sendirinya. Mudahkan? Selamat mencoba.

Baca juga:

Cara Membuat "Read More Links"
Cara Mudah Meningkatkan Kecepatan Komputer
Fungsi IF pada Microsoft Excel
Konversi Angka ke Huruf dengan Microsoft Excel
Mencari Produk yang Tepat pada ClickBank