Dasar lookup data (Fungsi lookup) 2 - Lookup 2 arah menggunakan OFFSET dan MATCH

Bismillah.

Pada pembahasan kali ini akan ditunjukan bagaimana menggunakan perpaduan fungsi MATCH dan OFFSET sebagai fungsi lookup (pencarian) untuk pencarian 2 arah pada Dataset yang berbentuk Cross Tabular seperti contoh dibawah.

Contoh dataset dengan layout Cross Tabular

Sebelumnya, silahkan download file contoh disini.

Kasus :

Cari data / angka penjualan masing-masing Sales tiap-tiap bulan pada laporan kuartal pada laporan penjualan seperti pada contoh diatas.

Penyelesaian :

Mencari data dari bentuk Dataset Cross-tabular (Atau Cross Tab. Disebut demikian karena datanya (pada contoh diatas, Range C4) berada dipersimpangan antara label bulan Februari dan label nama Sales3) seperti contoh, salah satunya dapat menggunakan perpaduan OFFSET dan MATCH.

Penulisan rumus lengkapnya adalah : =OFFSET(A1;(MATCH(I3;A2:A7;0));(MATCH(I2;B1:E1;0)))

Penjelasan rumus :

Syntax fungsi Match : MATCH(lookup_value, lookup_array, [match_type])

Bahasa manusianya : MATCH ( Nilai yang dicari, Tabel / Range yang akan dicari, [Kondisi pencarian] )

Catatan : 

Argumen Kondisi pencarian bersifat Optional. Bisa ditulis, bisa juga tidak. Argumen ini hanya menggunakan nilai 1,0 dan -1. Nilai 1 sama dengan lebih besar atau sama dengan (=>). Nilai 0 sama artinya dengan = atau sama persis. Sedangkan nilai -1 sama dengan lebih kecil atau sama  dengan (<=). Apabila tidak di tulis, maka Excel menganggap argumen ini bernilai 1.

Lebih praktisnya : apabila list acuan sudah di Sort dari kecil ke besar (Ascending) gunakan argumen 1. Kalau list dalam keadaan sudah di Sort dari besar ke kecil (Decending) maka gunakan -1.

Syntax fungsi OFFSET : OFFSET(reference, rows, cols, [height], [width])

Bahasa manusianya : OFFSET ( dari cell ini sebagai Acuan, Berapa banyak baris, Berapa banyak Kolom, [Sebanyak n baris], [Sebanyak n Kolom])

MATCH pertama digunakan untuk mencari data nama Sales dan MATCH kedua digunakan mencari data nama bulan. Karena fungsi OFFSET membutuhkan 3 argumen: cell acuan, Baris dan Kolom, maka 2 fungsi MATCH tersebut yang akan mengisi argumen Baris dan Kolom nya.

Semoga bermanfaat dan jelas ya ? kalau masih ada yang perlu ditanyakan, silahkan hubungi via email : gridsnlogic@gmail.com

Wassalam.