Jika Anda telah sudah biasa melakukan pekerjaan dengan jumlah data yang cukup besar di Excel, maka lazimnya data-data tersebut akan dibagikan ke dalam beberapa sheet terpisah. Salah satu maksudnya biar data tetap sanggup terorganisasi dengan baik, sehingga gampang apabila dicari kembali.
Pada Excel, lazimnya data-data antar sheet saling berelasi, misalkan ada dua sheet yakni sheet A_ dan sheet B_. Bagaimana cara menghubungkan data dari banyak sekali sheet tersebut apabila diperlukan?
Salah satu caranya yakni dengan memakai fungsi/rumus Lookup, yakni fungsi yang menjalankan penelusuran nilai padanan dari suatu sel ke sel yang terdapat pada tabel lain, baik sheet yang serupa maupun terpisah.
Fungsi lookup pada Excel ada dua, yaitu:
- Fungsi VLOOKUP (Vertical Lookup)
- Fungsi HLOOKUP (Horizontal Lookup)
Fungsi VLOOKUP Excel
Apa itu Fungsi VLOOKUP??
VLOOKUP yakni fungsi excel yang menjalankan penelusuran dari suatu nilai pada kolom pertama dari suatu tabel array, dan apabila didapatkan akan mengembalikan nilai yang lain pada baris yang serupa dari hasil pencarian.
Rumus Fungsi VLOOKUP
Berikut yakni syntax dan pemberitahuan dari fungsi VLOOKUP
=VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])
Keterangan:
- lookup_value : nilai yang hendak dicari ke table_array.
Ini sanggup berupa nilai (nomor, tanggal atau teks) atau rujukan sel (referensi ke sel yang berisi nilai lookup), atau nilai yang dikembalikan oleh beberapa fungsi Excel lainnya. Sebagai contoh, rumus: =VLOOKUP (40;A2:B11;2;FALSE) akan mencari nilai 40. - table_array : range nilai dimana terdapat nilai yang dicari.
Ingat, fungsi VLOOKUP senantiasa mencari nilai lookup di kolom pertama table_array. Table_array Anda mungkin berisi banyak sekali nilai menyerupai teks, tanggal, angka, atau nilai-nilai logis. Nilai di sini bersifat sensitif, yang memiliki arti bahwa teks abjad besar dan abjad kecil mesti diamati selaku nilai identik.
Jadi, teladan rumus yang kita gunakan disini adalah:
=VLOOKUP (40;A2:B11;2;FALSE)
artinya akan mencari nilai “40″ dalam sel A2 hingga A11. - col_index_num : indeks kolom yang di cari.
Nomor kolom di table_array di mana nilai dalam baris yang sesuai mesti dikembalikan. Sedangkan tabel yang paling kiri dari table_array diputuskan dengan 1, kolom kedua yakni 2, kolom ketiga yakni 3, dan seterusnya.
Contoh:
=VLOOKUP (40;A2:B11;2;FALSE)
artinya akan mencari nilai “40″ dalam sel A2 hingga B11 dan mengembalikan nilai yang tepat dari kolom B (karena B yakni kolom ke-2 dalam table_array A2:B11). - range_lookup (FALSE/TRUE atau 0/1)
menyeleksi apakah Anda sedang mencari nilai yang serupa (FALSE) atau fikiran pencocokan (TRUE atau diabaikan). Parameter final ini yakni opsional, tetapi sungguh penting.
Contoh:
Yang Harus di Ingat dalam Penggunaan Excel VLOOKUP
- Fungsi Excel VLOOKUP tidak sanggup membaca dari kiri, tetapi fungsi ini senantiasa mencari nilai lookup di kolom paling kiri dari rentang lookup (table_array).
- Dalam rumus VLOOKUP, semua nilai yakni nilai sensitif, yang memiliki arti bahwa karakter abjad besar dan abjad kecil diamati secara detail.
- Jika nilai lookup lebih kecil dari nilai terkecil di kolom pertama dari jangkauan penelusuran Anda, maka fungsi VLOOKUP akan menciptakan hasil #N/A error.
- Jika parameter ke tiga (col_index_num) kurang dari 1, rumus VLOOKUP akan memperlihatkan hasil #VALUE! eror. Sebaliknya, apabila dalam pengolahan bahwa ini lebih besar dari jumlah kolom dalam rentang penelusuran Anda (table_array), rumus akan memperlihatkan #REF! error.
- Gunakan rujukan sel adikara dalam rumus table_array VLOOKUP Anda untuk mendapat rentang lookup yang sempurna di saat menghadapi rumus. Pertimbangkan untuk memakai rentang lookup dengan nama atau tabel Excel selaku alternatif.
- Saat mencari dengan memakai sistem pencocokan fikiran (range_lookup set ke TRUE atau diabaikan), senantiasa miliki data pada kolom pertama dalam rentang penelusuran yang diurutkan dalam urutan yang menarik.
- Dan terakhir, ingat wacana pentingnya parameter akhir. Penyediaan TRUE atau FALSE di di saat yang sempurna akan menghindarkan Anda dari banyak kebingungan.
Contoh Fungsi VLOOKUP
Buatlah tabel berikut di sheet1 dan sheet2.
Gunakan fungsi VLookup untuk menghubungkan data Sheet1 dan di Sheet2, sehingga kolom nama barang dan harga di sheet1 akan tampil di sheet2 menurut Kode barang.
Langkah-langkahnya selaku berikut:
Langkah 1: Aktifkan Sheet2
Langkah 2: Pilih sel B3
Langkah 3: Tulis rumus =VLOOKUP(A3:A6;Sheet1!A2:C5;2;FALSE)
Langkah 4: Agar nilai lookup_value dan table_array absolut, tekan F4 sehabis memblok range lookup_value dan table_array.
Sehingga rumus diatas menjadi:
=VLOOKUP($A$3:$A$6;Sheet1!$A$2:$C$5;2;FALSE)
Langkah 5: Kemudian tekan Enter, copy kemudian paste rumus ini ke sel berikutnya. Meskipun rumus sel B3, B4, B5 dan B6 sama tetapi hasilnya berlainan sesuai dengan data yang ada di Sheet1
Langkah 6: Sekarang aktifkan sel C3
Langkah 7: Tulis rumus =VLOOKUP(A3:A6;Sheet1!A2:C5;3;FALSE)
Langkah 8: Ubah nilai lookup_value dan table_array dengan menekan F4 dikeyboard. Rumus diatas menjadi =VLOOKUP($A$3:$A$6;Sheet1!$A$2:$C$5;3;FALSE)
Langkah 9: Lanjutkan dengan menyalin kemudian memastekan rumus ini ke sel berikutnya.
Langkah 10: Bila rumus yang anda tulis telah benar maka hasil kesudahannya menyerupai gambar berikut:
Langkah 11: Selesai
Fungsi HLOOKUP Excel
Apa itu Fungsi HLOOKUP?
Fungsi HLOOKUP yakni fungsi excel yang menjalankan penelusuran horizontal dengan mencari nilai di baris atas suatu tabel dan mengembalikan nilai dalam kolom yang serupa menurut index_number.
Rumus Fungsi HLOOKUP
Berikut yakni syntax dan pemberitahuan dari fungsi HLOOKUP
=HLOOKUP(lookup_value; table_array; row_index_num; [range_lookup])
Keterangan:
- lookup_value : nilai yang hendak dicari ke table_array
- table_array : tabel mana yang nilainya sedang dicari
- row_index_num : indeks baris yang di cari
- range_lookup (FALSE/TRUE)
menyeleksi apakah Anda sedang mencari nilai yang serupa (FALSE) atau fikiran pencocokan (TRUE atau diabaikan). Parameter final ini yakni opsional, tetapi sungguh penting.
Contoh Fungsi HLOOKUP
Buatlah tabel menyerupai gambar berikut
Langkah-langkah menciptakan rumus HLOOKUP:
Langkah 1: Pilih sel C3
Langkah 2: Untuk menulis rumus Hlookup bahwasanya sama saja dengan langkah menulis rumus Vlookup sebelumnya. Untuk memperbesar pengetahuan teman-teman, kita juga sanggup menulis rumus Vlookup maupun Hlookup memakai kotak pembicaraan yang tersedia pada excel.
Begini caranya. Pada santapan FORMULAS klik Lookup & Reference pada tab Function Library kemudian klik HLOOKUP
Langkah 3: Akan timbul tabel pembicaraan berikut:
Langkah 4: Isilah tabel pembicaraan sesuai dengan data yang tertera pada gambar berikut, kemudian klik OK.
Langkah 5: Hasilnya selaku berikut:
Pada gambar di atas, terlihat bahwa sel C3 menciptakan hasil #N/A error. Kenapa demikian???
Sampai langkah ini masih wajar sebenarnya, jangan kalut walaupun nilai yang tertera masih menimbulkan nilai error, itu tidak apa-apa kok.
Nilai error ini akan berubah otomatis sehabis kita memasukkan nama item di sel C3. Sekedar info, teman-teman juga sanggup mengerti banyak sekali pesan error yang ada di excel dengan membaca postingan 7 pesan error excel.
Mari kita lanjut!!!
Langkah 6: Sekarang pilih sel C2
Agar lebih gampang maka nama item di sel C2 kita munculkan dalam bentuk santapan yang sanggup kita pilih sesuka hati kita. Makara kita tidak perlu mengetik satu persatu nama itemnya. Caranya yakni dengan menciptakan daftar dropdown yang berisi daftar nama-nama item.
Langkah 7: Pilih Data Validation dari santapan Data menyerupai gambar berikut.
Langkah 8: Pada Allow pilih List
Langkah 9: Untuk santapan Source, isikan rumus =C$5:I$5
Langkah 10: Lebih jelasnya, lihat gambar berikut, terakhir klik OK.
Langkah 11: Hasilnya selaku berikut:
Dari daftar dropdown, silahkan pilih salah satu nama item yang tersedia. Nama item yang diseleksi akan dijadikan selaku lookup_value di C2. Sehingga dengan demikian harga di C3 akan terisi otomatis dengan menyesuaikan harga item pada tabel HLOOKUP yang tersedia.
Langkah 12: Selesai
Bagaimana teman-teman?? Praktis atau tidak?? Kalau masih ada halangan mari kita bahas lewat kolom komentar dibawah.
Itu saja dahulu postingan wacana fungsi VLOOKUP dan fungsi HLOOKUP excel yang turut dilengkapi dengan teladan beserta cara menggunakannya, mudah-mudahan berharga buat teman-teman sekalian.