LookUp ke kiri atau ke kanan untuk satu kriteria

---

Muatan :

• Sekilas tentang kegiatan lookup dan beberapa fungsi yang dapat digunakan
• Lookup secara exact (sama persis antara nilai lookup dengan referensi)
• Lookup secara approximate pada referensi yang berupa interval-interval kelas atau kelompok

---

Kilasan

Kegiatan me-lookup data sering dilakukan. Excel menyediakan grup fungsi LookUp & Reference. Fungsi yang sering digunakan antara lain VLookUp atau HLookUp, LookUp, kombinasi Offset dengan Match, kombinasi Index dengan Match. Masih banyak kombinasi beberapa fungsi yang bisa digunakan dalam kegiatan lookup, tetapi pembahasan akan dibatasi pada fundamental kegiatan lookup itu sendiri, sehingga dapat menyusun kombinasi beberapa fungsi untuk kegiatan lookup.



Beberapa hal yang perlu diingat :

• Kegiatan lookup secara exact pada data yang tidak unique akan menghasilkan data yang pertama kali ditemui.
• Kegiatan lookup secara approximate, menuntut data yang terurut sesuai karakteristik fungsi yang digunakan. Jika tidak terurut, maka dapat kesalahan pada hasil sangat terbuka.

Fungsi VLookUp atau HLookUp

Kedua fungsi ini hanya berbeda pada orientasi susunan data. Fungsi VLookUp menggunakan data berorientasi vertikal atau record tersusun dalam baris. Fungsi HLookUp menggunakan data berorientasi horisontal atau record tersusun dalam kolom. Kedua fungsi membutuhkan kolom nilai yang di-lookup pada field pertama (terkiri untuk VLookUp atau teratas untuk HLookUp), sehingga penggunaannya terbatas pada lookup ke kanan atau ke bawah saja. Data hasil lookup diletakkan mulai field pertama sampai field ke-n sesuai kebutuhan. Kedua fungsi membutuhkan nomor index field yang akan menjadi kolom atau baris output dan bersifat case insensitive.

Fungsi LookUp

Penyertaan kolom atau baris hasil hanya terdiri dari satu kolom atau satu baris saja. Jadi fungsi LookUp bisa digunakan untuk me-lookup ke arah kiri (atas) ataupun kanan (bawah). Karakteristik utamanya adalah selalu mencari yang sama atau yang tertinggi dari data lookup yang kurang dari nilai lookup. Artinya, susunan data lookup yang terurut menaik sangatlah dianjurkan untuk mendapatkan ketepatan hasil kalkulasi.

Formula kombinasi fungsi Offset dengan Match

Fungsi Offset merujuk pada sebuah cell yang menjadi patokan. Fungsi Match digunakan untuk mencari nilai geserannya. Jadi fungsi Match bisa digunakan pada sisi rows atau cols sesuai kebutuhan. Kegiatan lookup dilakukan oleh fungsi Match, sedangkan fungsi Offset adalah untuk mengambil data hasil lookup.

Formula kombinasi fungsi Index dengan Match

Fungsi Index merujuk pada suatu range yang merupakan range hasil lookup. Hasil lookup ditentukan oleh fungsi Match yang menghasilkan nomor index data pada array untuk Match. Jadi fungsi Match digunakan untuk mendapatkan posisi hasil pada referensi fungsi Index. Fungsi Index menghasilkan output berupa range perpotongan antara baris dan kolom.

---

Lookup data yang sama persis (exact)

Data contoh pertama adalah seperti gambar di bawah ini. Data bersifat unique records.

Contoh masalah berdasar data di atas adalah :

1. Kapan pesanan customer 3 terkirim ?
2. Berapa nomor invoice customer 4 ?
3. Customer berapa yang qty-nya tertinggi ?

Berikut solusi masalah lookup di atas.

1. Kapan pesanan customer 3 terkirim ?
   Field lookup value ada di kolom B. Field hasil lookup ada di kolom F. Jenisnya lookup ke kanan. Jadi, fungsi VLookUp bisa digunakan.
   
   VLookUp membutuhkan lookup_array dari kolom B sampai kolom F (B2:F5). Index_col kolom hasil (kolom F) dari kolom B yang ber-index_col = 1 adalah 5. Tipe pencariannya adalah exact match (FALSE).
   
   Index(Match) tersusun dari fungsi Match sebagai inti kegiatan lookup dan fungsi Index untuk mengambil hasil output. Oleh sebab itu, fungsi Index diberi referensi kolom F (kolom hasil). Fungsi Match membutuhkan kolom B (lookup_array) untuk mencari nilai lookup (3) dengan tipe pencarian adalah exact (0). Fungsi Match diletakkan pada sisi row_number dari fungsi Index karena orientasi data yang vertikal.
   
   Offset adalah fungsi lain untuk mengambil hasil lookup. Kegiatan lookup tetap menggunakan fungsi Match. Fungsi Offset disini merujuk ke cell header kolom lookup_value. Kolom hasil adalah geseran (offset) 4 kolom ke kanan (+). Geseran barisnya adalah sebanyak hasil fungsi Match.
   
   LookUp membutuhkan array hasil komparasi nilai-nilai kolom lookup (kolom B) dengan nilai lookup yang harus sama. Hasil dari komparasi ini adalah nilai TRUE atau FALSE, sehingga lookup_value yang digunakan oleh fungsi LookUp adalah nilai TRUE. Kolom hasil adalah kolom F.


2. Berapa nomor invoice customer 4 ?
   Field lookup value ada di kolom B. Field hasil lookup ada di kolom A. Jenisnya lookup ke kiri. Jadi, fungsi VLookUp tidak bisa digunakan. Seluruh susunan memiliki kemiripan dengan soal nomor 1, kecuali nama kolomnya.


3. Customer berapa yang qty-nya tertinggi ? Field lookup value ada di kolom E. Nilai lookup adalah nilai maksimum kolom E, sehingga membutuhkan fungsi Max untuk menentukannya. Field hasil lookup ada di kolom B. Jenisnya lookup ke kiri. Jadi, fungsi VLookUp tidak bisa digunakan. Jadi, perbedaan dengan soal nomor 2 adalah pada sisi penentuan nilai lookup yang merupakan hasil kalkulasi sebuah fungsi, dalam hal ini berupa fungsi Max.

Kegiatan lookup seperti di atas adalah jenis pencarian secara exact (sama persis).

---

Lookup pada referensi berupa interval nilai (approximate)

Berikut ini adalah data untuk contoh lookup yang berjenis approximate. Data bersifat unique records dan terurut menaik (ascending) pada kolom usia anak dan baris usia pengabdian. Usia pada data referensi adalah batas bawah suatu interval. Misalnya data usia 0 adalah batas bawah dari interval usia 0 sampai kurang dari 3 tahun (0 >= usia < 3).

Masalah yang akan dibahas adalah :

Berapa tunjangan untuk anak yang didapatkan seorang pegawai yang telah bekerja selama 12 tahun dan memiliki anak berumur 8 tahun.

Pada data yang terurut menaik, beberapa solusi yang mungkin adalah seperti gambar berikut ini.

Khabar !!!

Fungsi Match memiliki kemampuan untuk melakukan kegiatan lookup pada data yang terurut menurun (descending), yaitu dengan input parameter match_type bernilai -1.

Pada kasus ini, kegiatan lookup dilakukan 2 (dua) kali, yaitu terhadap :

1. Kolom usia anak untuk mendapatkan posisi baris data yang sesuai kriteria usia anak.
2. Kolom usia pengabdian untuk mendapatkan posisi kolom data yang sesuai kriteria pengabdian.

Perpotongan kedua hasil lookup tersebut, menunjukkan nilai hasilnya.

VLookUp digunakan dengan susunan :

• Nilai lookup_value adalah usia anak yang ditanyakan, yaitu 8.
• Data rujukan pencarian adalah kolom usia anak (kolom A), sehingga table_array adalah seluruh data mulai dari kolom A sampai kolom G, yaitu A2:G12.
• Nilai col_index harus dicari dengan kegiatan lookup menggunakan fungsi Match. Susunan fungsi Match adalah :
  • Nilai lookup_value berupa nilai pengabdian, yaitu 12
  • Kegiatan lookup dilakukan pada seluruh header (A1:G1), karena fungsi VLookUp membutuhkan table_array dari kolom A.
  • Cara pencarian fungsi Match adalah approximate (nilai tertinggi yang kurang dari atau sama dengan lookup_value) dengan match_type diberi nilai 1.
• Cara pencarian fungsi VLookUp adalah approximate, yaitu mengisi input parameter range_lookup dengan nilai TRUE.

Index(Match) dapat digunakan dengan susunan sebagai berikut :

• Fungsi Index diberi rujukan array hasil berupa seluruh range data selain kolom usia anak, yaitu range B2:G12.
• Bagian row_num pada fungsi Index didapat melalui kegiatan lookup menggunakan fungsi Match terhadap data usia anak (A2:A12) berdasar nilai usia anak yang dicari, yaitu 8, dengan match_type bernilai 1.
• Bagian col_num pada fungsi Index didapat melalui kegiatan lookup menggunakan fungsi Match terhadap header yang merupakan nilai-nilai pengabdian (B1:G1) berdasar nilai pengabdian, yaitu 12, dengan match_type bernilai 1.

Offset digunakan bersama fungsi Match untuk memperoleh nilai geseran (offsetting) sisi row dan col. Susunan fungsi Match adalah seperti pada Index(Match). Patokan (anchor) dari fungsi Offset diset pada cell sebelum data pertama usia anak dan sebelum data pertama pengabdian, yaitu cell A1.

LookUp lebih mirip seperti VLookUp. Fungsi ini selalu bekerja dengan array 1 (satu) dimensi. Susunannya adalah sebagai berikut.

• Nilai lookup adalah usia anak, yaitu 8.
• Data pencarian (array lookup) adalah kolom usia anak, yaitu range A2:A12 yang menjadi nilai input parameter lookup_vector.
• Data hasil lookup (array hasil) ditentukan memanfaatkan fungsi Offset dan Match, seperti cara Offset di atas, dengan nilai input parameter row adalah 0.

---

Closing :

Konsep kegiatan lookup di atas dapat dikembangkan lebih lanjut pada hampir seluruh input parameter-nya. Penyelesaian lookup dengan banyak kriteria (multi criteria) adalah hasil pengembangan lebih lanjut konsep lookup di atas. Sebagian besar adalah dengan memanfaatkan kemampuan array formula.

---

File(s) :

Lookup 1 kriteria pada sheet LookUp1 dan LookUp2

---

Coretan terkait :

• Array Formula.

---