FUNGSI REFERENSI INDEX DAN MATCH UBAH SUSUNAN DATA VERTIKAL MENJADI DATA HORIZONTAL

Para pengguna Microsoft Excel yang sudah bekerja pasti sudah pernah membuat tabel dengan sumber data nya dari tabel Referensi  atau tabel lainnya. Biasanya yang digunakan adalah fungsi Formula VLOOKUP dan HLOOKUP. Akan tetapi kedua fungsi ini memiliki kelemahan yaitu hanya dapat membaca tabel array ke arah bawah dan ke bawah dan datanya harus terurut secara Ascending saja untuk fungsi VLOOKUP.  Lalu bagaimana agar pencarian data tersebut secara fleksibel dan tidak harus mengurutkan terlebih dahulu tabel referensinya, dalam arti dapat mengambil data dari kolom ataupun baris manapun yang kita tentukan yaitu dengan menggunakan penggabungan fungsi INDEX DAN MATCH.

FUNGSI INDEX

Fungsi Index berfungsi untuk mendapatkan nilai dari suatu sel pada tabel referensi menggunakan baris dan kolom sebagai acuan. Misalkan berapa nilai pada baris 2 dan kolom 3 pada tabel Referensi maka akan terjawab nilainya. Dimana aturan penulisannya adalah

=INDEX(array;row_num;Column_num)

Keterangan:

• Array: range data yang akan diambil datanya
• Row_num: pergerakan nomor baris pada Array dan penomoran baris hanya pada ruang lingkup Array saja.
• Column_nuim: pergerakan nomor kolom pada Array dan penomoran baris hanya pada ruang lingkup Array saja.

Contoh kasus: Kita diminta untuk merubah data yang tersusun dari vertikal menjadi horizontal perhatikan tabel dibawah ini.

Untuk mengisi sel-sel yang kosong pada Tabel 2 kita memerlukan judul karena formula INDEX ini memelukan penomoran baris dan kolom sebagai acuan untuk mendapatkan nilai pada Tabel 1. Sehingga Tabel 2 menjadi,

Kita akan memanfaatkan data pada baris dan kolom Nomor yaitu 1,2, dan 3 sebagai Row_num dan Column_num pada argumen formula INDEX sehingga kita memerlukan Sel Absolut agar dapat Auto Fill untuk mengisi lainnya dengan begitu aturan penulisan formula menjadi,

C10 =INDEX($B$3:$D$6;D$9;$B10)

FUNGSI MATCH

Fungsi MATCH ini digunakan untuk mencari suatu nilai dari suatu range yang terdapat pada suatu kolom atau baris dan hasilnya berupa angka yang menunjukan baris atau kolom ke berapa posisi nilai itu dari range yang digunakan. Dengan aturan penulisannya adalah

=MATCH(lookup_value;lookup_array;[Match_type])

Keterangan:

Lookup_value adalah nilai kata kunci yang akan dicari pada lookup_array.

Lookup_array adalah range data dari suatu kolom atau baris

Match_type adalah angka yang menunjukan tipe pencocokan dimana ada 3 angka yaitu

1. Angka 1: jenis pencocokan dimana lookup_array harus dalam keadaan terurut secara ascending (kecil ke besar). Pencocokan dilakukan dengan mengambil nilai terbesar dari range data yang lebih kecil atau sama dari lookup_value.
2. Angka 0: jenis pencocokan dimana lookup_array dicari data yang sama persis dengan lookup_value. Urutan data tidak menjadi masalah. Jika ditemukan lebih dari satu data yang sama, maka akan diambil data yang pertama kali ditemukan
3. Angka -1: jenis pencocokan dimana lookup_array harus dalam keadaan terurut secara Ascending (besar ke kecil). Pencocokan dilakukan dengan mengambil nilai terkecil dari range data yang lebih besar atau sama dari lookup_value.

Contoh kasus kita akan mencari kolom ke berapakah nilai yang sesuai dengan kata kunci lookup_value pada tabel berikut ini,

Formula yang digunakan pada Tabel diatas adalah

N9=MATCH(M10;$M$2:$O$2;-1)

Keterangan:

N9 adalah sel yang akan diisi oleh nomor kolom dari lookup_array

M10 adalah kata kunci yang akan digunakan untuk dicocokkan dengan range M2:O2

-1 adalah match_type yang digunakan karena judul kolom pada tabel pertama dalam kondisi terurut secara Descending.

PENGABUNGAN FUNGSI INDEX DAN MATCH

Dari kedua fungsi INDEX dan MATCH yang sudah dipelajari dan dipahami diatas maka kita dapat menggabungkan sebagai satu kesatuan formula excel untuk mencari data yang sesuai dengan kata kunci.

Contoh kasusnya seperti pada contoh FUNGSI INDEX dimana Kita diminta untuk merubah data yang tersusun dari vertikal menjadi horizontal perhatikan tabel dibawah ini.

Untuk mengisi sel-sel yang kosong pada Tabel 2 kita memerlukan judul karena formula INDEX ini memerlukan penomoran kolom saja sebagai acuan untuk mendapatkan nilai pada Tabel 1. Sehingga Tabel 2 menjadi,

Kita akan memanfaatkan data pada Nomor yaitu 1,2, dan 3 sebagai Row_num dan fungsi MATCH sebagai Column_num pada argumen formula INDEX sehingga kita memerlukan Sel Absolut agar dapat Auto Fill untuk mengisi lainnya dengan begitu aturan penulisan formula menjadi,

C10 =INDEX($B$3:$D$6;C$9;MATCH($B10;$B$2:$D$2;-1))

Semoga Bermanfaat

Terima kasih telah berkunjung ke Agung Code

Excel

Share This :