Membuat 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.

Disclaimer: Artikel ini mungkin mengandung link afiliasi dari mana kami akan mendapatkan komisi untuk setiap transaksi/aksi terkualifikasi tanpa adanya biaya tambahan bagimu. Pelajari lebih lanjut

Daftar Isi:

• Cara penulisan dan input
• Contoh penggunaan dan hasil
• Langkah-langkah penulisan
• Cara alternatif: menggunakan menu define name excel
• VLOOKUP beda sheet dan beda file
• VLOOKUP dengan referensi lebih dari 1 sheet dan metode pencarian satu per satu
• VLOOKUP dengan referensi sheet dinamis
• Pembelajaran VLOOKUP secara lengkap dan kasus penggunaan lainnya
• Latihan
• Catatan tambahan

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”).

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

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
   

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

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

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.

   

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

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
   

7. Ketik tanda tutup kurung
   

8. Tekan enter
9. Selesai!
   

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….

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.

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.

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

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.

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.

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.

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.

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!

Tutorial terkait untuk kamu pelajari juga:

Bagaimana cara menggunakan rumus VLOOKUP?

Kenapa rumus VLOOKUP jadi na?

Bagaimana cara menggunakan VLOOKUP dengan data yang beda sheet?

Jelaskan apa perbedaan hlookup dan VLOOKUP antar sheet dengan hlookup dan VLOOKUP biasa?