Panduan Penggunaan Rumus VLOOKUP Beda Sheet


Beranda >> Blog Tutorial Excel >> Kumpulan Rumus Excel Lengkap dan Fungsinya >> Panduan Penggunaan Rumus VLOOKUP Beda Sheet



Pada tutorial ini, kamu akan mempelajari cara menggunakan rumus VLOOKUP jika tabel referensinya berada di sheet yang berbeda.

Sebelum mempelajari tutorial ini, kamu tentunya sudah harus memahami dasar penggunaan VLOOKUP secara umum. Jika kamu belum memahaminya, silahkan mempelajarinya dulu di sini.

VLOOKUP adalah rumus pencarian data yang cukup sering dipakai oleh pengguna excel. Terkadang, tabel referensi tempat mencari datanya berada di sheet berbeda dari tempatmu menulis VLOOKUPmu.

Hal ini membutuhkan cara khusus untuk penggunaan VLOOKUPnya agar ia bisa berfungsi dengan baik. Kita akan bahas secara detail di sini mengenai bagaimana caranya tersebut dilakukan.

Ingin mempelajari penggunaan excel secara lebih cepat dan lengkap? Ikuti kursus Udemy berbahasa Indonesia berikut!

Disclaimer: Artikel ini mengandung link afiliasi yang mungkin akan memberikan komisi kepada kami untuk setiap pembeliannya tanpa biaya tambahan apapun bagimu. Kami hanya akan menaruh link yang kami anggap dapat membantumu mengoptimalkan potensi excel dalam pekerjaanmu. Jika kamu ingin membaca lebih lanjut mengenai hal ini, silahkan kunjungi halaman kebijakan afiliasi kami.





Cara Penulisan dan Input

Secara umum, penulisan VLOOKUP yang memiliki tabel referensi di sheet berbeda adalah sebagai berikut.


=VLOOKUP(lookup_value, ’sheet_name’!table_array, col_index_num, [range_lookup])


Dan berikut penjelasan singkat mengenai masing-masing elemen dari input penulisannya tersebut.
  • lookup_value = nilai referensi pencarian dari VLOOKUPmu
  • sheet_name = nama sheet tempat tabel referensi di mana VLOOKUP akan mencari datamu berada
  • table_array = cell range tempat tabel referensimu berada di sheet lain
  • col_index_num = urutan kolom tempat hasilmu akan diambil di tabel referensimu (dihitung dari kolom paling kiri)
  • [range_lookup] = input opsional. Mode proses pencarian VLOOKUPmu, apakah harus ditemukan data sama persis dengan nilai referensi pencarianmu atau boleh kisarannya saja. Masukkan TRUE untuk kisaran dan FALSE untuk sama persis. Nilai awal input ini adalah TRUE



Contoh Penggunaan dan Hasil

Berikut akan diberikan dan dijelaskan contoh penggunaan dan hasil dari VLOOKUP beda sheet.

Pertama, kita lihat di sini bahwa tabel referensinya berada di tempat berbeda dengan penulisan VLOOKUPnya (tabel referensinya berada di sheet “Referensi Harga” dan penulisan VLOOKUPnya berada di sheet “Berapa Harganya”).

Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Referensi Contoh yang Berbeda Sheet Dengan VLOOKUPnya

Dan untuk mereferensikan tabel data tersebut dari sheetnya, kita melakukannya dengan cara seperti yang bisa dilihat di formula bar screenshot berikut.

Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Contoh VLOOKUP Berbeda Sheet

Bisa dilihat di sana bagaimana penulisan rumus VLOOKUPnya mengikuti pola yang sudah dijelaskan di bagian sebelumnya. Perbedaannya dengan penulisan rumus VLOOKUP biasa adalah adanya nama sheet dan tanda seru (!) sebagai pemisah sebelum cell range tabel referensinya dimasukkan.

Selain perbedaan tersebut, penulisan input lainnya sebenarnya sama saja. Dengan penulisan input seperti itu, maka kamu akan mendapatkan hasil VLOOKUP beda sheet sesuai kebutuhanmu.



Langkah-langkah Penulisan

Bagaimana cara melakukan penulisan rumus VLOOKUP dengan referensi berbeda sheet secara detailnya? Berikut akan diberikan langkah per langkahnya disertai dengan screenshot contoh penulisannya di excel untuk mempermudah pemahamanmu.

  1. Ketik tanda sama dengan ( = ) di cell tempatmu ingin menaruh hasil VLOOKUPnya

    Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Langkah 1

  2. Ketik VLOOKUP (boleh dengan huruf besar atau huruf kecil) dilanjutkan dengan tanda buka kurung

    Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Langkah 2

  3. Masukkan nilai referensi pencariannya atau koordinat cell di mana ia berada, lalu ketik tanda koma ( , )

    Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Langkah 3

  4. Masukkan cell range tempat tabel referensinya berada di sheet lain lalu ketik tanda koma. Kamu bisa memasukannya dengan klik ke sheet tempat tabelnya berada lalu drag pointermu pada cell range tabelnya tersebut.

    Setelah proses dragnya selesai, klik kembali ke sheet tempat penulisan VLOOKUPmu dilakukan. Nama sheet serta cell range tempat tabel referensinya berada sudah otomatis dimasukkan ke dalam VLOOKUPmu!

    Kamu juga bisa mengetikkan cell range tempat tabel referensinya tersebut secara langsung walau cara ini lebih merepotkan. Jika kamu mengetikkannya, jangan lupa mengetikkan nama sheetnya terlebih dahulu dalam tanda kutip ( ‘’ ) dilanjutkan dengan tanda seru ( ! ). Setelah itu, baru kamu mengetikkan cell range dari tabel referensinya.

    Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Langkah 4

  5. Masukkan urutan kolom tempat hasilnya akan diambil dalam tabel referensimu atau koordinat cell tempat angka perlambang urutannya berada

    Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Langkah 5

  6. Opsional: Ketik tanda koma lalu masukkan TRUE atau FALSE untuk mode pencarian VLOOKUP yang kamu inginkan. TRUE untuk mode pencarian yang memperbolehkan kisaran dan FALSE untuk mode pencarian yang harus sama persis. Jangan lupa mengurutkan kolom pertama tabel referensimu dari kecil ke besar (a ke z untuk teks) jika kamu menggunakan input TRUE. Nilai awal dari input ini jika tidak ada input yang diberikan adalah TRUE

    Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Langkah 6

  7. Ketik tanda tutup kurung

    Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Langkah 7

  8. Tekan enter
  9. Selesai!

    Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Langkah 9


Mau menginput dan memproses data excelmu secara lebih cepat? Gunakan Add-Ins dari XLTools! Pakai kode kupon diskon kami “7C2L8B31AZ” agar kamu mendapatkan diskon 10% ketika membeli lisensinya!



Cara Alternatif: Menggunakan Menu Define Name Excel

Malas berpindah-pindah sheet ketika menuliskan rumus VLOOKUPmu? Kamu bisa memberikan nama terhadap cell range tabel referensimu supaya kamu tidak perlu melakukan itu. Kamu tinggal mereferensikan nama tersebut ketika memberikan input tabel referensi pada VLOOKUPmu.

Pemberian namanya sendiri bisa kamu lakukan menggunakan menu Define Name excel. Sorot cell range tempat data tabel referensimu berada, klik kanan, dan pilih Define Name….

Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Lokasi Menu Define Name... Melalui Pilihan Klik Kanan

Pada dialog box yang muncul, masukkan nama yang nantinya akan kamu referensikan ketika ingin menggunakan tabel referensinya dalam penulisan rumusmu. Patut diingat bahwa nama yang kamu berikan tidak boleh mengandung spasi. Setelah selesai memberikan namanya, klik OK.

Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Dialog Box Menu Define Name Excel

Pemberian nama untuk cell rangemu selesai dilakukan!

Jika kamu ingin menggunakan nama tersebut, maka kamu tinggal mengetikkan namanya pada saat kamu membutuhkannya. Dalam konteks penulisan VLOOKUP, itu berarti kamu memasukkan namanya ketika kamu perlu menginput cell range tabel referensimu.

Secara umum, bentuk penulisannya kurang lebih dapat digambarkan sebagai berikut.


=VLOOKUP(nilai_referensi_pencarian, nama_tabel_referensi, urutan_kolom_hasil, [mode_pencarian])


Terlihat dari cara penulisannya tersebut bahwa perbedaannya dengan cara penulisan VLOOKUP beda sheet biasa hanya di input tabel referensinya saja, Input cell range diganti dengan nama tabel referensi yang sudah kamu simpan sebelumnya.

Nama yang sudah kamu simpan tersebut juga akan muncul dalam usulan excel ketika kamu mulai mengetikkan bagian awal namanya.

Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Usulan Excel Untuk Nama Cell Range

Lakukan penginputan nama tabel referensinya dengan benar dan hasil VLOOKUPmu akan segera didapatkan!

Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot VLOOKUP Dengan Input Tabel Referensi Nama Cell Range



VLOOKUP Beda Sheet dan Beda File

Bagaimana jika tabel referensimu tidak hanya ada di sheet yang berbeda tapi juga ada di file excel yang berbeda?

Prinsipnya sebenarnya hampir sama dengan VLOOKUP berbeda sheet namun filenya sama. Hanya saja, kamu perlu menambahkan nama file workbooknya pada input cell range tabel referensinya jika filenya sedang dibuka. Jika file tempat tabelnya berada sedang ditutup, kamu perlu menambahkan file pathnya juga sebelum nama file workbooknya.

Hal ini bisa dimasukkan otomatis jika kamu pergi ke file dan sheet tempat tabel referensinya berada ketika input VLOOKUPmu ingin dimasukkan. Lakukan drag pointer pada cell range dari tabelnya tersebut agar inputnya bisa otomatis dimasukkan di VLOOKUPmu.

Secara umum, cara penulisan VLOOKUP beda sheet beda file jika file tabelnya sedang dibuka dapat kamu lihat di bawah.


=VLOOKUP(nilai_referensi_pencarian, ’[nama_workbook]nama_sheet’!cell_range_tabel_referensi, urutan_kolom_hasil, [mode_pencarian])


Dan jika file tabelnya sedang ditutup, maka penulisannya menjadi seperti ini.


=VLOOKUP(nilai_referensi_pencarian, ’file_path[nama_workbook]nama_sheet’!cell_range_tabel_referensi, urutan_kolom_hasil, [mode_pencarian])


Untuk lebih jelasnya, silahkan lihat contoh berikut.

Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Tabel Referensi Contoh VLOOKUP Beda Sheet Beda File

Pada contoh di atas, dapat kita lihat suatu tabel referensi yang berada di file “Tabel Produk Harga” dan sheet “Referensi Harga”. Bagaimana cara mereferensikannya dalam rumus VLOOKUP yang dituliskan di sheet yang berbeda?

Kamu buka file VLOOKUP dan tabel referensinya secara bersamaan. Kemudian, kamu drag cell range tabel referensinya ketika sedang memberikan input VLOOKUPmu di bagian tabel referensi. Hasilnya akan menjadi seperti ini.

Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Contoh VLOOKUP Beda Sheet Beda File

Ketika file tabelnya ditutup, maka file pathnya akan otomatis ditambahkan jika kamu sudah memasukkan inputmu dengan cara drag seperti itu.

Masukkan inputnya dengan benar dan hasil VLOOKUPmu akan kamu dapatkan dengan mudah!



VLOOKUP Dengan Referensi Lebih Dari 1 Sheet dan Metode Pencarian Satu Per Satu

Mungkin terdapat situasi di mana tabel referensi yang ingin kita input dalam VLOOKUP lebih dari satu. Data pada tabel-tabel tersebut berbeda satu sama lainnya dan tabel-tabelnya terdapat di sheet yang berbeda juga. Kita ingin jika datanya tidak dapat ditemukan di satu tabel, maka VLOOKUP akan otomatis mencari data tersebut di tabel lainnya.

Bisakah hal tersebut dilakukan? Jawabannya bisa, dengan menggunakan kombinasi IFERROR dan VLOOKUP.

Secara umum, penulisan IFERROR VLOOKUP untuk tujuan pencarian data tersebut adalah sebagai berikut.


=IFERROR(VLOOKUP(nilai_referensi_pencarian, ’nama_sheet_1’!cell_range_tabel_referensi_1, urutan_kolom_hasil, [mode_pencarian]), IFERROR(nilai_referensi_pencarian, ’nama_sheet2’!cell_range_tabel_referensi_2, urutan_kolom_hasil, [mode_pencarian]), …, hasil_jika_data_tidak_ditemukan)


Dalam penulisannya tersebut, terlihat bahwa kamu perlu membuat tingkatan IFERROR VLOOKUP. Tuliskan lebih awal IFERROR VLOOKUP dengan input tabel referensi yang ingin dicari terlebih dahulu. Lalu, masukkan terus IFERROR VLOOKUPmu sampai semua tabel referensi tempat kamu ingin melakukan pencarian sudah dimasukkan.

Pada bagian terakhir penulisan IFERROR VLOOKUPnya, jangan lupa juga memasukkan hasil IFERROR VLOOKUPnya jika datamu tidak ditemukan di semua tabel referensinya.

Sebagai gambaran penulisannya, berikut contoh implementasi penulisan di atas serta hasilnya pada excel.

Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Contoh Aplikasi IFERROR VLOOKUP Untuk Melihat Tabel Referensi Beda Sheet Satu Per Satu

Dapat dilihat pada contohnya bahwa terdapat dua sheet yang memiliki tabel referensi terpisah. Dengan kondisi seperti itu dan kita ingin mencari datanya secara satu per satu, maka penulisan VLOOKUPnya seperti pada formula bar contohnya.

Dalam proses VLOOKUP tersebut, kita melihat tabel referensi di sheet “Referensi Harga 1” dahulu sebelum ke “Referensi Harga 2”. Pada kasus ini, harga produk yang kita cari ditemukan di tabel referensi pada sheet “Referensi Harga 2”.

Jika tidak ditemukan nilai referensi pencariannya pada kedua sheet tersebut, maka akan dihasilkan teks “Tidak Ditemukan” sesuai input dalam IFERRORnya.

Jika kamu membutuhkan tabel referensi lebih dari 2, maka kamu tinggal memasukkan IFERROR VLOOKUP sebanyak jumlah tabel referensimu.



VLOOKUP Dengan Referensi Sheet Dinamis

Bagaimana jika terdapat dua atau lebih tabel referensi berbeda sheet dan kita ingin mencari datanya berdasarkan nilai data tertentu? JIka nilai datanya ini maka tabel referensi ini yang dicari dan jika nilai datanya itu maka tabel referensi itu tempat pencariannya. Dengan kata lain, kita ingin tabel referensi VLOOKUP kita mempunyai referensi sheet dinamis.

Apakah hal itu bisa dilakukan dengan menggunakan VLOOKUP? Tentunya bisa! Untuk kebutuhan ini, kamu perlu mengkombinasikan penulisan VLOOKUPmu dengan IF.

Secara umum, penggabungan VLOOKUP dan IF untuk tujuan referensi sheet dinamis tersebut adalah sebagai berikut.


=VLOOKUP(nilai_referensi_pencarian, IF(kondisi_logika, ’nama_sheet_1’!cell_range_tabel_referensi_jika_kondisi_logikanya_benar…, ’nama_sheet_2’!cell_range_tabel_referensi_jika_kondisi_logikanya_salah), urutan_kolom_hasil, [mode_pencarian])


Seperti dapat kamu lihat, rumus IFnya di sini dimasukkan ke dalam bagian input tabel referensinya. Gunakan kondisi logika yang sesuai dengan kebutuhanmu dan masukkan cell range tabel referensinya jika hasil evaluasi kondisinya benar dan salah. Masukkan IF bertingkat sebanyak kebutuhanmu jika terdapat lebih dari 2 tabel referensi yang perlu direferensikan.

Contoh dari aplikasi VLOOKUP IF ini adalah sebagai berikut.

Panduan Penggunaan Rumus VLOOKUP Beda Sheet - Screenshot Contoh Aplikasi VLOOKUP IF Untuk Referensi Sheet Dinamis Dari VLOOKUP

Dalam contoh, tabel referensi VLOOKUPnya ingin ditentukan dari nilai cell kategori. Jika cell tersebut berisi “Laptop”, maka tabel referensinya harus diambil dari sheet “Referensi Harga Laptop”. Jika isinya adalah “Handphone”, maka tabel referensinya harus diambil dari sheet “Referensi Harga Handphone”.

Dengan kondisi seperti itu, maka input tabel referensi dari VLOOKUPnya menjadi seperti yang dapat kamu lihat pada formula bar contohnya. Kondisi logika yang dimasukkan dalam IFnya adalah apakah nilai cell kategorinya tersebut adalah “Laptop”. Hasil jika kondisi IFnya benar adalah cell range tabel referensi dari sheet “Referensi Harga Laptop”lah yang dipakai. Jika salah, maka jadi cell range tabel referensi dari sheet “Referensi Harga Handphone”.

Itu adalah contoh jika tabel referensi berbeda sheetnya berjumlah 2. Jika ada lebih dari 2, maka kamu tinggal memasukkan banyak IF yang disesuaikan dengan jumlah tabel referensimu tersebut.



Pembelajaran VLOOKUP Secara Lengkap dan Kasus Penggunaan Lainnya

Ingin mempelajari contoh kasus penggunaan VLOOKUP lainnya? Mungkin kamu ingin nilai referensi pencarian VLOOKUPmu lebih dari 1? Atau ingin menemukan kecocokan kedua atau ketiga untuk hasil VLOOKUPmu?

Kamu dapat mempelajarinya dengan melihat bagian-bagian dari tutorial VLOOKUP lengkap kami di sini!



Latihan

Setelah kamu mempelajari bagaimana cara penggunaan VLOOKUP dengan menggunakan tabel referensi berbeda sheet, sekarang saatnya mempraktekkan pemahamanmu dengan mengerjakan latihan berikut!

Unduh file latihannya dan kerjakan semua soalnya. Unduh file kunci jawabannya jika kamu sudah selesai mengerjakan latihannya dan ingin mengecek jawabanmu!

Link file latihan:
Unduh di sini

Pertanyaan

Jawab pertanyaan-pertanyaan berikut di sheet “Jawaban” pada cell yang sesuai!
  1. Berapa stok terakhir produk C di gudang pada akhir bulan Maret? Gunakan rumus VLOOKUP beda sheet biasa untuk menjawabnya!
  2. Berapa kuantitas penjualan produk F pada bulan Februari? Gunakan kombinasi rumus IFERROR VLOOKUP untuk menjawabnya dengan memasukkan tabel referensi bulan Maret terlebih dahulu! Masukkan nilai “Tidak Ditemukan” jika datanya memang tidak ditemukan!
  3. Berapa kuantitas penjualan produk A untuk nilai bulan di cell C4? Gunakan kombinasi rumus IF VLOOKUP untuk menjawabnya dengan memasukkan tabel referensi bulan Februari sebagai kondisi benarnya!

Link file kunci jawaban:
Unduh di sini



Catatan Tambahan

Jika kamu membutuhkan rumus pencarian yang lebih fleksibel dari VLOOKUP, gunakan INDEX MATCH! Pelajari tutorial lengkap penggunaan rumus ini hanya di sini!

Perlu mempelajari excel secara lebih cepat? Ikuti kursus Udemy berikut ini!

Tutorial terkait untuk kamu pelajari juga:



Ingin Belajar Lebih Banyak Tentang Excel?


Dapatkan info excel terbaru dari Compute Expert dengan mendaftarkan emailmu. Gratis!


Ingin Belajar Lebih Banyak Tentang Excel?


Dapatkan info excel terbaru dari Compute Expert dengan mendaftarkan emailmu. Gratis!



Ikuti konten tutorial kami juga di


Tutorial Pilihan CE

Cara Menghitung di Excel

Cara Menjumlahkan di Excel

Cara Pengurangan di Excel

Cara Mengalikan di Excel

Cara Membagi di Excel

Cara Menghitung Rata-rata di Excel



Rumus Excel

Rumus VLOOKUP

Rumus IF Excel

Rumus SUM Excel

Rumus COUNTIF Excel

Rumus COUNT



Tips dan Trik Excel

Cara Print Excel

Rumus Terbilang Excel

Worksheet Adalah

Range Adalah

Cara Menambah Kolom di Excel



Kontak Kami

Kebijakan Privasi

Kebijakan Afiliasi

Syarat & Ketentuan



© 2021 Compute Expert