Cara Menggunakan Rumus Fungsi Vlookup Beda Sheet

Cara menggunakan rumus fungsi Vlookup beda sheet (lembar kerja pada excel) pada prinsipnya sama saja, anda tinggal mengarahkan sheet yang menjadi referensi/acuan untuk menjawab kolom yang ingin di-isikan.

Dan sebelum melanjutkan tulisan cara menggunakan rumus fungsi VLOOKUP beda sheet, kebetulan saya menggunakan Microsoft excel 2013. Bila anda menggunakan versi Ms. Excel lainnya  seperti: excel 2007, 2010 dan yang terbaru 2016, tidak masalah kok, semua bisa digunakan.

Oke, untuk memudahkan anda lebih memahami contoh soal excel beda sheet, saya akan mengambil soal yang ada pada tulisan saya sebelumnya yaitu cara menggunakan rumus excel pada fungsi vlookup dan hlookup.

Contoh Gambar soal sebelumnya seperti ini?

gambar vlookup hlookup 1

Pada gambar di atas, tabel utama untuk anda isi dan tabel bantu/referensi masih berada dalam satu lembar kerja.

Nah tulisan kali ini…

Tabel akan dipisah yaitu : sheet1 yang merupakan tabel utama “Bagian operator” dimana anda harus mengisikan kolom kosong untuk dijawab.

Gambar untuk sheet 1
tabel sheet pertama

Sheet 2  berisikan Tabel Bantu (Referensi), yang akan digunakan untuk menjawab soal/kolom-kosong  yang ada di sheet pertama.

Sheet 2 ini berisikan Tabel gaji

Berikut ini gambarnya…

tabel sheet 2

Lanjuuut ke cara pengisian kolom yang kosong…

#Mengisi Kolom Gaji Pokok Beda sheet

Untuk mengisi gaji pokok, gunakanlah fungsi VLOOKUP, mengapa harus VLOOKUP? Karena kita akan mengambil datanya dari sheet 2, yaitu Tabel Gaji.. Dimana Gaji pokok itu didapatkan berdasarkan golongannya, dan posisi data-data yang pada gaji pokok itu berbentuk kolom bukan baris..

Ingat !!

VLOOKUP merupakan fungsi yang digunakan untuk pembacaan tabel berbentuk vertikal/tegak lurus

Penulisan fungsi Vlookup

=VLOOKUP(Nilai_kunci, letak_tabel, nomor_kolom)

Nilai kuncinya adalah Golongan (Gol) terletak pada tabel “bagian operator” (sheet1). Nah nilai kunci ini harus ada juga di letak_tabel  yang digunakan sebagai tabel referensi yaitu tabel gaji (sheet2)..

Oke, langkah pertama..

Letakkan kursor di bawah kolom Gaji Pokok, atau Cell D5, lalu ketikkan Fungsi VLOOKUP dan arahkan kursor  ke cell Golongan (B5), seperti gambar berikut ini:
mengisi nilai kunci

Sampai disini, proses pengerjaan belum selesai..

Silahkan, arahkan kursor ke sheet 2 untuk mengambil tabel_data dan nomor_kolom..

tabel data1 vlookup

Blok Area di sheet 2, dari B3 sampai E5 untuk mengambil tabel data seperti gambar diatas, setelah itu letakkan koma dan isi dengan angka 2 pada formula bar (tempat mengisi penulisan rumus data), seperti berikut ini:

isi formula barSetelah proses pengerjaan diatas telah selesai, silahkan tekan enter..

Maka hasil pengerjaan telah selesai, tinggal anda drag/copy ke bawah untuk mengisi kolom gaji pokok dibawahnya…Tapiii? Hasilnya akan menjadi seperti ini:

tidak menggunakan absolut

Kenapa?

Karena pada saat pengerjaan di sheet 2, Anda tidak menggunakan tanda dollar ($), yang berfungsi untuk mengabsolutkan data pembanding (kolom gaji pokok pada tabel gaji ) yang posisi datanya tetap…

Jadi sebelum anda menekan enter..tambahkan dollar diantara kolom (Huruf)  $B$3 dan $E$5. Pemberian tanda dollar ($) akan lebih mudah/praktis dengan menekan tombol F4 pada keyboard setelah memblok area B3:E5. Rumus lengkapnya seperti ini:

pemberian tanda dollar pada fungsiJadi hasil keseluruhannya akan tampak seperti gambar dibawah:

hasil gaji pokok

Bila anda bertanya mengapa nomor kolom diisi dengan 2, pada penulisan hasil lengkap =VLOOKUP(B5,sheet!$B$3:$E$5,2)

Karena data tabel pembandingnya (tabel gaji yang berada pada sheet2) bentuk/formatnya vertikal dan letak gaji pokok yang dijadikan tabel referensi berada pada kolom ke 2 …Coba anda lihat gambarnya:

letak nomor kolom vlookupKeterangan:

  • Panah angka 1 : menunjukkan kolom pertama yaitu : kolom golongan
  • Panah angka 2: menunjukkan kolom kedua yaitu: kolom gaji pokok
  • Panah angka 3 : menunjukkan kolom ketiga : kolom tunjangan
  • Panah angka 4 : menunjukkan kolom keempat: kolom transportasi

Untuk Contoh Soal Excel berupa rumus penggabungan beberapa fungsi Vlookup, Hlookup, Mid, Left dan If.. Baca: cara menggunakan rumus kombinasi excel dengan contoh kasus soal

#Mengisi Tunjangan beda sheet

Proses pengerjaan untuk kolom tunjangan sama dengan waktu anda mengisi gaji pokok, yang membedakan hanya indeks atau nomor kolomnya, dimana tunjangan berada pada kolom ketiga. Hasilnya seperti dibawah ini:

tunjangan-beda-sheet

#Mengisi Transportasi beda sheet

Letakkan kursor di Cell F5 pada tabel gaji pokok (sheet1), setelah itu lakukan proses yang sama seperti anda mengisi gaji pokok dan tunjangan. Yang berbeda adalah nomor kolomnya, sesuai dengan tabel data yang ada pada Tabel Gaji (sheet2), posisi transportasi berada pada indeks ke 4. Hasilnya bisa anda lihat, seperti berikut ini:

transportasi beda sheet

#Mengisi Total Gaji

Untuk total Gaji, anda tidak membutuhkan sheet 2, karena untuk mengisi total gaji anda cukup menggunakan fungsi SUM untuk menjumlahkan total gaji yang didapatkan oleh masing-masing karyawan. Caranya, Letakkan kursor di cell G5, lalu ketikkan =SUM(D5:F5).

Hasil keseluruhan dari pengerjaan VLOOKUP beda sheet seperti dibawah ini:

gaji-karyawan

Untuk memudahkan pembacaan/penyebutan nilai mata uangnya, seperti: 1000000 (satu juta) menjadi 1,000,000, anda bisa menggunakan format number pada excel.  Blok semua nilai mata uang yang telah anda kerjakan dari D5 sampai G9, lalu klik coma style.

grup number

Setelah anda mengklik tanda koma, otomatis nilai mata uangnya berubah, misal: 1000000 menjadi 1,000,000.00. Dua angka dibelakang merupakan decimal, bila anda merasa tidak perlu untuk menggunakannya atau ingin menghilangkannya, silahkan klik decrease decimal, sehingga hasilnya menjadi 1,000,000. Bila ingin kembali menggunakannya?Anda cukup menggunakan increase decimal.

Hasil keseluruhan dengan menggunakan comma style, seperti ini:

gaji-karyawan dengan koma

Oke, untuk penggunaan vlookup beda sheet saya cukupkan sampai disini, untuk mengisi pajak dan gaji bersih, silahkan baca kelanjutanya pada tulisan:  cara menggunakan fungsi HLOOKUP beda sheet.

Update 10 Desember 2018: Untuk mendapatkan materi tulisan VLOOKUP beda sheet, dan kebetulan telah saya gabungkan dengan HLOOKUP, anda bisa mendownloadnya disini.

Oh ya, bagi anda yang ingin membuat/menggunakan VLOOKUP dan HLOOKUP pada satu sheet yang sama untuk pengerjaan… Gaji pokok, kolom tunjangan, transportasi, total gaji, pajak dan gaji bersih.

Silahkan dibaca:  cara menggunakan rumus pada fungsi vlookup dan hlookup ms.excel

27 Replies to “Cara Menggunakan Rumus Fungsi Vlookup Beda Sheet”

  1. KALO VLOOKUP GABUNGAN DG IF TP BEDA SHEET GMN?
    MISAL FILE SUMBER SHEET 1, DI SHEET 2 ITU DATA NYA TP PILIHAN
    Jika file sumber isian 1 maka di sheet 2 kolom 1 isiannya 1, kolom 2,3 isiannya –
    jIKA File sumber isian 2 maka di sheet 2 kolom 1 isiannya -, kolom 2 isiannya 2, kolom 3 isiannya –

  2. gan mau tanya kalo nilai kuncinya sama tapi nilai kolomnya lebih dari 1 gimana ya cara ambil nilai kolom yang terbaru ?

    1. iya sama dengan pertanyaan yang akan saya ajukan. saya butuh pencerahan untuk kasus itu juga. jika “nilai kunci” dan “nomor kolom” di sheet berbeda dan “nomor kolom” lebih dari satu kolom. bagaimana solusinya.

  3. Mas Bro Umar Danny, terima kasih atas sharing ilmunya. Gampang dimengerti penjelasannya. Berkah selalu y Mas Bro.

  4. Bang, tadi gue tes kek gitu, rumus udh sama, tapi hasilnya nol, wkwk gimana ya bang

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.