Fungsi (V,H)LOOKUP Vs. Fungsi MATCH

FUNGSI HLOOKUP, VLOOKUP dan IF

Mungkin anda sudah sangat mengenal fungsi IF karena fungsi ini merupakan fungsi standar yang harus dikuasai dalam menggunakan perhitungan di program Datasheet seperti Excel. Jika hanya menggunakan dua 'kemungkinan', fungsi ini terlihat begitu mudah. Tapi barangkali anda akan menemukan kesulitan jika anda memiliki beberapa 'kemungkinan'. Meskipun batas menggunakan nested fungsi IF hingga lebih dari 64, namun akan sangat tidak efisien jika kita menggunakan fungsi ini untuk kondisi dimana ada banyak 'kemungkinan' yang akan dicek karena kita harus mengetikkan deretan fungsi yang begitu panjang.

1. FUNGSI IF DENGAN DUA KEMUNGKINAN

=IF(AND(A2>0;A2=1);"Tunggal";"Jamak")

. FUNGSI IF DENGAN LEBIH DARI DUA(9) KEMUNGKINAN 


2=IF(A2=1;"Satu";IF(A2=2;"Dua";IF(A2=3;"Tiga";IF(A2=4;"Empat";IF(A2=5;"Lima";IF(A2=6;"Enam";IF(A2=7;"Tujuh"

;IF(A2=8;"Delapan";"Lebih Dari Delapan"))))))))

Anda dapat melihat bagaimana panjangnya fungsi IF yang kita gunakan jika ada lebih dari dua 'kemungkinan'. Karena itulah kita membutuhkan fungsi VLOOKUP dan HLOOKUP untuk menggantikan fungsi IF yang begitu panjang tersebut. Seperti yang dapat dilihat dari nama fungsi yang digunakan, kedua fungsi (VLOOKUP dan HLOOKUP) ini adalah (mungkin) turunan dari fungsi LOOKUP. Kita tidak perlu membahas fungsi LOOKUP karena terus terang saya tidak begitu suka menggunakan fungsi ini :). VLOOKUP adalah fungsi yang mencari nilai pada kolom pertama dari suatu tabel dan menghasilkan sebuah nilai yang memiliki baris yang sama dari kolom pertama dalam tabel tersebut. Seperti yang anda duga, huruf V pada VLOOKUP adalah singkatan dari vertical. Sedangkan HLOOKUP mencari suatu nilai pada baris atas suatu tabel atau array dan kemudian menghasilkan nilai yang memiliki kolom yang sama dengan kolom yang anda maksudkan pada tabel atau array. Konsep array mungkin sudah sangat diakrabi oleh programmer karena hampir dalam semua program konsep array akan selalu ada. Array adalah kumpulan data yang terikat dalam satu variabel. Kalau dimisalkan, array adalah kumpulan lidi yang bergabung menjadi sapu.

Seperti yang anda lihat, tabel sebelah kiri merupakan tabel data yang disusun secara vertikal sedangkan yang sebelah kanan disusun secara horisontal. Penyusunan tabel akan menentukan fungsi apakah yang akan digunakan, menggunakan VLOOKUP atau HLOOKUP. Misalkan kita ingin mengisikan sel 'B3' dengan harga sepatu maka fungsi yang digunakan adalah:

=VLOOKUP(LEFT(A3;LEN(A3)-1);$D$3:$E$7;2;FALSE)
-------1---------  ----2---- 3 --4--

FT(
--atau--
 
=HLOOKUP(L
EA3;LEN(A3)-1);$G$3:$K$4;2;FALSE)

--- 3 --4--

-------1---------  ----2
-

Untuk fungsi VLOOKUP kita menggunakan tabel yang kiri sedangkan fungsi HLOOKUP menggunakan tabel yang kanan. Konsep dasarnya baik fungsi VLOOKUP dan HLOOKUP memiliki tiga parameter penting dan satu parameter tambahan yang dipisahkan tanda titik koma ';' yaitu:

• 1. Sel atau teks yang ingin diuji atau dicek. Pada contoh diatas saya menggunakan fungsi tambahan LEFT dan LEN untuk memotong teks di sel A3 yaitu membuang karakter ':'. Fungsi LEN "terpaksa" digunakan karena panjang karakter di sel A3 tidak pasti. Baik VLOOKUP dan HLOOKUP bersifat incase sensitif alias tidak memperdulikan huruf kapital maupun huruf biasa. Jika range lookup diset FALSE anda dapat menggunakan tanda wildcard seperti '?' atau '*'. Tanda ? menggantikan semua karakter sedangkan tanda * menggantikan beberapa urutan karakter. Misalnya: teks 'mala*' akan sesuai dengan data malang, malas, malam, malaysia, dsb. Sedangkan teks 'pa?i' akan sesuai dengan data padi, pagi, pati, dsb.
• 2. Tabel sumber. Sebenarnya ada cara khusus untuk memberi nama tertentu pada tabel sumber tapi saya lebih suka menggunakan tanda dollar '$' (kebiasaan waktu menggunakan Lotus 123) yang diberikan pada tiap sel agar tabel sumber menjadi absolut atau tetap sehingga ketika rumus dicopykan kebawah tabel sumber tidak berubah atau bergeser. Jika anda lupa memberi tanda dollar tersebut maka bisa berakibat fatal dan fungsi akan menghasilkan data yang salah atau bahkan error.
• 3. Index kolom atau baris yang akan diambil datanya dimulai dari 1 sampai ... berapa banyak kolom yang berada pada range tabel yang anda masukkan. Jika nilai index kolom kurang dari satu, fungsi akan menghasilkan error #VALUE! dan jika lebih besar dari kolom yang anda berikan maka fungsi akan menghasilkan error #REF!.
• 4. Range Lookup yang berisi nilai logical "TRUE" atau "FALSE". Bagian ini dapat dihilangkan karena akan otomatis dianggap TRUE. Jika anda memberi nilai TRUE maka fungsi mencari nilai yang sama dan jika tidak ketemu akan mencari nilai tertinggi dibawah nilai yang diuji. Pastikan data pada tabel sumber berurutan, misalnya -2,-1,0,1,2. Jika data tidak berurutan maka kadang fungsi menghasilkan nilai yang tidak sesuai. Jika diisi FALSE maka hanya data yang sama yang dihasilkan. Data dalam tabel tidak harus berurutan. Jika ada lebih dari satu data yang sesuai maka data pertama yang akan digunakan. Jika data tidak ditemukan maka akan menghasilkan error #N/A

Fungsi MATCH

Fungsi ini akan menghasilkan posisi relatif dari suatu item dalam suatu array yang sesuai dengan nilai yang telah ditentukan dalam urutan tertentu. Anda harus menggunakan fungsi ini saat anda membutuhkan posisi dari suatu item dalam suatu range daripada menggunakan fungsi (V,H)LOOKUP yang menghasilkan nilai item itu sendiri. Mungkin ada pertanyaan, untuk apa sih sebenarnya fungsi ini?. Mungkin sekilas fungsi ini tidak begitu penting tapi dalam beberapa kasus mungkin saja fungsi ini lebih berguna dibanding fungsi yang lain. Sebagaimana konsep array yang sudah saya jelaskan, fungsi ini menghasilkan nilai posisi suatu item dalam array tersebut. Misalnya ada array= (10, 15, 20, 25, 35, 40, 45, 50), dan kita hanya ingin tahu posisi angka 20 pada array tersebut yaitu 3 maka fungsi MATCH dapat dipergunakan untuk maksud ini. Dalam excel posisi array dimulai dari angka 1 dan bukan dari angka 0 ( doh ... )

Dari gambar diatas kita tahu ada semacam form yang digunakan untuk mencari data siswa dilengkapi dengan keterangan peringkat di kelas, keterangan tambahan dan nilai siswa yang bersangkutan. Anda lihat bahwa tabel sumber di sebelah kanan sengaja saya buat menggunakan background warna-warni agar lebih mudah dilihat penggolongan siswa berdasarkan nilai yang dimilikinya. Selain itu data dalam tabel tersebut sudah berurutan dari nilai tertinggi hingga nilai terendah. Tentang bagaimana mengurutkan data tabel silakan anda tanya teman anda :). Saya berusaha agar dalam memasukkan nama siswa anda dapat menggunakan karakter wildcard seperti * atau ?, jadi untuk mencari siswa dengan nama Andi Susilo maka anda dapat memasukkan kata 'andi s*', dsb. Dalam mengisi kolom hasil pencarian ada beberapa kriteria antara lain:

• 1. Nama siswa dan Nilai rapor pada hasil pencarian dicari dengan menggunakan fungsi VLOOKUP
• 2. Peringkat peroleh dengan mencari baris keberapa dari tabel yang mengandung nama siswa yang bersangkutan.
• 3. Keterangan akan saya isikan dengan menggolongkan siswa berdasarkan nilai rapor yang diperoleh. Lima siswa teratas digolongkan 'Sangat Pandai' dikelas, lima berikutnya 'Cukup Pandai', lima siswa dibawahnya 'Agak Pandai' dan terakhir 'Kurang Pandai'. Tentu saja kategori saya buat tanpa ada maksud tertentu karena ini hanya contoh saja. Jadi anda tidak perlu risau jika Joko Budoyo dengan nilai rapor 85,8 digolongkan 'Kurang Pandai' :)

FUNGSI VLOOKUP UNTUK NAMA SISWA (sel B6)
=VLOOKUP(D3;G3:H22;1;FALSE)


POR (sel E6)
=VLOOKUP(D3;G3:H22;2;FALSE)

FUNGSI VLOOKUP UNTUK NILAI R
A
FUNGSI MATCH UNTUK MENCARI PERINGKAT (sel C6)
=MATCH(D3;G3:G22;0)

ILAI DI SEL C6 (se
-1 ---2-- 3

FUNGSI IF UNTUK MENCARI KETERANGAN BERDASARKAN 
Nl D6)

=5;"Sangat Pandai";IF(C6<=10;"Cukup Pandai";IF(C6<=15;"Agak Pandai";"Kurang Pandai")))

=IF(C6
<

Fungsi MATCH memiliki dua parameter penting dan satu paramater tambahan. Bagian tersebut yaitu:

• 1. Nilai yang diuji. Nilai ini bisa berupa sel, teks, angka atau nilai logikal (TRUE/FALSE).
• 2. Array sumber yaitu berupa range beberapa sel yang tidak terputus.
• 3. Jenis pencarian yang berupa angka -1, 0 atau 1. Jika ditentukan jenis 1 maka fungsi akan menghasilkan nilai tertinggi yang lebih kecil atau sama dengan nilai yang dites. Array sumber harus disusun secara berurutan dari data terkecil: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE. Jika ditetapkan 0 maka fungsi akan mencari data yang sama persis dan susunan array sumber tidak harus berurutan. Nilai -1 akan membuat fungsi mencari data yang lebih paling kecil yang lebih besar atau sama dengan nilai yang dites. Array sumber harus berurutan secara descending atau dari terbesar ke terkecil. Jika anda tidak memberi nilai pada jenis pencarian maka fungsi akan menganggapnya menjadi 1.

Pada gambar diatas, saya mencoba memasukkan input menggunakan wildcard '*' sehingga untuk mencari nama siswa 'Gunawan Wibisono' saya tidak harus mengetikkan semuanya. Form pencarian telah dapat dipergunakan, hingga kita tidak perlu mencari peringkat seorang siswa secara manual. Anda tinggal mengganti nama siswa di sel D3, maka data siswa akan muncul pada hasil pencarian. Sekarang kita kosongkan sel D3 dan.... ups!

Kita melihat error #N/A pada semua kolom hasil pencarian. Sebenarnya error #N/A juga akan muncul ketika nama yang kita masukkan tidak ditemukan pada tabel sumber. Karena itu kita coba menggunakan fungsi-fungsi tambahan yang mungkin jadi kelihatan agak rumit tapi jika anda merasa tidak terganggu dengan munculnya error #N/A maka anda tidak perlu merubah fungsi yang telah diberikan sebelumnya. Pada prinsipnya kita coba menggunakan fungsi logika tambahan untuk menangani error #N/A tersebut. Diantaranya seperti fungsi ISNA, ISBLANK, atau ISNUMBER ditambah fungsi NOT. Fungsi logika IF jelas sangat berperan disini. Sebenarnya ada fungsi khusus untuk menangani error yaitu fungsi IFERROR tapi karena menurut saya ada beberapa bagian yang tidak bisa dihandle dengan fungsi IFERROR maka saya lebih suka menggunakan kombinasi fungsi logika lainnya. Untuk pembahasan fungsi logika seperti ISBLANK, NOT, AND dsb. akan kita bahas kapan-kapan kalau ada kesempatan :)

FUNGSI VLOOKUP UNTUK NAMA SISWA (sel B6)

=IF(ISBLANK(D3);"";IF(ISNA(VLOOKUP(D3;G3:H22;1;FALSE)) = TRUE;"Tidak Ada Data";VLOOKUP(D3;G3:H22;1;FALSE)))
FUNGSI VLOOKUP UNTUK NILAI RAPOR (sel E6)

2;2;FALSE)) = TRUE;"";VLOOKUP(D3;G3:H22;2;FALSE)))

FUNGSI MATCH UNTUK MENCARI PERINGKAT (sel
=IF(ISBLANK(D3);"";IF(ISNA(VLOOKUP(D3;G3:H
2 C6)
=IF(ISBLANK(D3);"";IF(ISNA(MATCH(D3;G3:G22;0))=TRUE;"";MATCH(D3;G3:G22;0)))


UMBER(C6))=TRUE;"";IF(C6<=5;"Sangat Pandai";IF(C6<=10;"Cukup Pandai";IF
FUNGSI IF UNTUK MENCARI KETERANGAN BERDASARKAN NILAI DI SEL C6 (sel D6)
=IF(NOT(IS
N(C6<=15;

ai";"Kurang Pandai"))))

"Agak Pan
d

Kira-kira cukup begitu pembahasan kita tentang fungsi IF, (V,H)LOOKUP dan MATCH. Sebenarnya saya ingin membuat contoh yang lebih "keren" seperti penggolongan bahan kimia kedalam asam atau basa atau penggolongan hewan berdasarkan kelas ordo, species dsb. Tapi berhubung pengetahuan kimia dan biologi saya agak mepet, maka rencana itu gagal dikerjakan. Tapi anda dapat berimprovisasi menggunakan fungsi-fungsi tersebut sesuai dengan bidang yang anda tekuni. Saya pribadi cuma berharap agar pembahasan kali ini bisa bermanfaat dan Insyaallah lain waktu kita membahas fungsi lain yang belum kita bahas. Terima kasih.