Dalam menggunakan rumus VLOOKUP terdapat tiga jenis penggunaan tergantung kepada Workbook atau Worksheet yang digunakan. Satu diantaranya adalah VLOOKUP beda file yang tentu saja tujuannya adalah menampilkan isi Cell dari Workbook yang berbeda.
Konsep dasar serta rumus VLOOKUP beda file secara umum memang tetap sesuai dengan urutan dasar argumen rumus VLOOKUP.
Hanya saja ada tambahan berupa alamat file atau lokasi file yang akan kita gunakan.
Untuk cara serta rumus dalam VLOOKUP beda file mari kita bahas bersama – sama dalam artikel ini sampai dengan selesai.
Cara VLOOKUP Beda File Dalam Excel
Supaya lebih mudah memahami konsep dasar dari rumus VLOOKUP beda file silahkan baca dan pelajari sesuai dengan urutan pembahasannnya.
Ada beberapa bagian yang akan kita bahas dalam VLOOKUP beda file ini mulai dari konsep dasar sampai dengan mengatasi error #N/A pada VLOOKUP beda file.
1. Jenis – Jenis VLOOKUP Dalam Excel
Seperti yang sudah kita ketahui bersama bahwa dalam Excel rumus VLOOKUP ini dapat digunakan untuk menampilkan isi Cell sesuai dengan kriteria yang telah kita tetapkan.
Jika belum memahami cara penggunaan dari rumus VLOOKUP silahkan buka dan pelajari dalam kategori Fungsi Pencarian dan Referensi.
Misalnya ada dua tabel yang sedang kita olah yaitu Tabel Barang dan Tabel Transaksi.
Didalam Tabel Barang berisi Kode Barang serta Nama Barang dan didalam Tabel Transaksi juga akan menampilkan Kode Barang beserta Nama Barangnya.
Pada saat Kode Barang diinput dalam Tabel Transaksi maka Nama Barang akan otomatis muncul.
Munculnya nama barang tersebut bisa dilakukan secara otomatis dengan menggunakan rumus VLOOKUP.
Baca Juga : Menggunakan Rumus VLOOKUP Untuk Mengambil Isi Cell Pada Microsoft Excel
Jika dilihat dari posisi Tabel didalam Workbook atau Worksheet maka rumus VLOOKUP ini bisa dibagi kedalam 3 jenis, yaitu sebagai berikut :
-
VLOOKUP Pada Sheet Yang SamaPada jenis VLOOKUP yang pertama ini posisi dari Tabel Barang dan Tabel Transaksi ada pada Sheet yang sama.
-
VLOOKUP Beda SheetDari nama sebenarnya sudah bisa ditebak bahwa pada jenis yang kedua ini posisi dari Tabel Barang dan Tabel Transaksi ada pada Sheet atau Worksheet yang berbeda.Hanya saja kedua tabel tersebut posisinya ada pada Workbook yang sama atau satu Workbook.Jika belum memahami perbedaan Worksheet dan Workbook silahkan buka dan pelajari dalam kategori Excel Dasar.
-
VLOOKUP Beda FileJenis VLOOKUP yang ketiga adalah VLOOKUP Beda File dan pada VLOOKUP ini posisi dari Tabel Transaksi dan Tabel Barang ada pada Worksheet yang berbeda.Maksudnya adalah Tabel Barang ada pada file Excel 1 sedangkan Tabel Transaksi ada pada file Excel 2 atau sebaliknya.
Jenis VLOOKUP yang ketiga atau VLOOKUP beda file akan kita bahas rumus serta contohnya dalam artikel ini.
2. Konsep Dasar VLOOKUP Beda File
Pada saat menggunakan rumus VLOOKUP ada empat informasi yang kita masukan kedalam argumennya yaitu sebagai berikut :
- Alamat Cell Kategori
- Alamat Tabel Referensi
- Posisi Kolom Yang akan diambil datanya
- Value TRUE atau FALSE
Jika kita menggunakan rumus VLOOKUP dengan kondisi beda file maka akan ada tambahan dua informasi lagi yaitu sebagai berikut :
- Nama Sheet / Worksheet
- Alamat File / Workbook
Dari penjelasan diatas dapat kita simpulkan bahwa alamat file serta nama file akan menjadi hal yang wajib untuk diketahui dalam rumus VLOOKUP beda file.
3. Rumus VLOOKUP Beda File
Dalam konsep dasar diatas disebutkan bahwa dalam rumus VLOOKUP beda file kita akan menyertakan alamat file atau alamat Workbook kedalam argumen rumus VLOOKUP.
Pada rumus VLOOKUP secara normal argumen yang digunakan adalah sebagai berikut :
=VLOOKUP(kode; tabel; kolom; True/False)
Sedangkan pada rumue VLOOKUP beda file urutan argumen tersebut menjadi seperti berikut ini :
=VLOOKUP(kode; [nama file.xlsx]nama sheet!tabel; kolom; True/False)
Argumen diatas akan tampil jika posisi Tabel sumber data atau Tabel Barang dalam kondisi dibuka.
Sedangkan jika file Tabel Barang dalam kondisi tidak dibuka maka argumennya akan menjadi seperti berikut ini :
=VLOOKUP(kode;’D:\[nama file.xlsx]nama sheet’!tabel; kolom; True/False)
Huruf D pada rumus tersebut muncul jika file ada pada drive D sedangkan jika file ada pada drive C maka huruf D akan berubah menjadi C.
Baca Juga : Rumus VLOOKUP Beda Sheet Dalam Excel Untuk Mengambil Isi Cell Dengan Kriteria
Dalam penggunaannya VLOOKUP beda file ini akan kita bagi menjadi dua pembahasan.
Pertama kita akan menggunakan alamat Cell untuk Tabel Barang kedalam VLOOKUP beda file.
Sedangkan pembahasan yang kedua kita akan menggunakan Define Name atau Nama Range kedalam argumen rumus VLOOKUP tersebut.
4. Contoh VLOOKUP Beda File
Untuk contoh dan cara VLOOKUP beda file yang pertama kita akan menggunakan alamat range dari tabel kedalam rumusnya.
Silahkan perhatikan gambar berikut ini :
Dalam contoh tersebut sudah ada dua file atau dua Workbook Excel yang sudah kita buat.
Workbook yang pertama adalah Barang dan Workbook yang kedua adalah Transaksi dimana kedua file tersebut ada pada drive D dalam komputer.
Dalam Tabel Barang pada Workbook yang pertama berisi kolom Kode atau kolom A serta Nama atau kolom B.
Selanjutnya pada Tabel Transaksi atau Workbook yang kedua berisi kolom Kode, Nama, Qty dan Jumlah.
Jika kolom Kode pada Tabel Transaksi diisi maka secara otomatis pada kolom Nama akan muncul Nama Barang sesuai dengan Nama pada Tabel Barang.
Adapun rumus VLOOKUP beda file yang digunakan pada Cell B4 adalah sebagai berikut :
=VLOOKUP(A4;[Barang.xlsx]Sheet1!$A$3:$B$8;2;0)
Rumus tersebut diatas akan terlihat jika file Barang dalam keadaan dibuka atau sudah kita buka.
Sedangkan jika file Barang sedang dalam keadaan ditutup maka rumus VLOOKUP beda file akan terlihat seperti berikut ini :
=VLOOKUP(A4;’D:\[Barang.xlsx]Sheet1′!$A$3:$B$8;2;0)
Perubahan rumus tersebut secara otomatis akan dilakukan oleh Excel jadi kita tidak perlu mengubahnya secara manual.
Untuk langkah – langkah membuat rumus VLOOKUP beda file seperti pada contoh diatas adalah sebagai berikut :
- Buka Workbook atau file Excel Barang dan Transaksi
- Klik Cell B4 pada Workbook Transaksi
- Ketik rumus : =VLOOKUP(
- Klik Cell A4 : =VLOOKUP(A4
- Ketik titik koma atau koma : =VLOOKUP(A4;
- Buka Workbook atau file Excel Barang
- Pada file Barang klik Cell A3 kemudian geser mouse sampai dengan Cell B8 : =VLOOKUP(A4;[Barang.xlsx]Sheet1!$A$3:$B$8
- Ketik titik koma atau koma : =VLOOKUP(A4;[Barang.xlsx]Sheet1!$A$3:$B$8;
- Ketik angka 2 ( kolom Nama pada Tabel Barang ) : =VLOOKUP(A4;[Barang.xlsx]Sheet1!$A$3:$B$8;2
- Ketik titik koma atau koma : =VLOOKUP(A4;[Barang.xlsx]Sheet1!$A$3:$B$8;2;
- Ketik angka 0 : =VLOOKUP(A4;[Barang.xlsx]Sheet1!$A$3:$B$8;2;0
- Akhiri dengan kurung tutup : =VLOOKUP(A4;[Barang.xlsx]Sheet1!$A$3:$B$8;2;0)
- Tekan Enter
Sampai dengan langkah yang ketiga belas kita sudah selesai membuat rumus VLOOKUP beda file.
Selanjutnya silahkan copy dan pastekan rumus tersebut sampai dengan Cell 10 atau Cell yang lain sesuai dengan kebutuhan.
4. Contoh VLOOKUP Beda File Dengan Nama Range
Selanjutnya untuk contoh yang kedua kita akan menggunakan Nama Range atau Define Name pada Tabel Barang.
Dalam Excel Nama Range atau Define Name ini bisa kita buat melalui Tab Formulas serta kelompok menu Defined Names.
Sebenarnya rumus yang digunakan pada cara yang kedua ini hampir sama dengan cara yang pertama.
Hanya saja sebelum dibuatkan rumus VLOOKUP beda file terlebih dahulu Tabel Barang dibuatkan Nama Rangenya.
Adapun langkah – langkah membuat Nama Range untuk Tabel Barang dengan Define Name adalah sebagai berikut :
- Buka File / Workbook Barang
- Pilih atau blok Cell A3 sampai dengan Cell B8
- Klik Tab Formulas
- Klik Define Name yang ada pada kelompok menu Defined Names dan akan muncul kotak dialog New Name
- Dalam kotak Name isi dengan : Tabel_Barang
- Dalam kotak Refers to pastikan sudah terisi dengan rumus : =Sheet1!$A$3:$B$8
- Klik OK
Selanjutnya setelah Nama Range berhasil kita buat sesuai dengan langkah – langkah diatas silahkan buka Workbook atau file Transaksi dan buat rumus VLOOKUP beda filenya.
Baca Juga : Cara Membuat Nama Range Dengan Define Name di Excel
Konsep VLOOKUP beda file dengan nama range memang sedikit berbeda dengan konsep tanpa nama range.
Tapi proses pembuatannya tetap mirip seperti pada cara yang pertama dan sudah dijelaskan diatas.
Adapun rumus VLOOKUP beda file dengan menggunakan nama range pada Cell B4 adalah sebagai berikut :
=VLOOKUP(A4;’D:\Barang.xlsx’!Tabel_Barang;2;0)
Rumus VLOOKUP beda file diatas akan terlihat jika posisi Tabel Barang atau Workbook Barang dalam keadaan ditutup.
Sedangkan jika Workbook Barang dalam keadaan dibuka maka rumusnya akan menjadi seperti berikut ini :
=VLOOKUP(A4;Barang.xlsx!Tabel_Barang;2;0)
Rumus ini terlihat cukup berbeda dengan cara pada pembahasan yang pertama yaitu tanpa nama range.
Tapi hasilnya tetap sama dan nama barang akan otomatis muncul pada Tabel Transaksi seperti pada contoh gambar diatas.
Adapun langkah – langkah membuat rumus VLOOKUP beda file dengan Nama Range atau Define Name adalah sebagai berikut :
- Buka Workbook atau file Excel Barang dan Transaksi
- Klik Cell B4 pada Workbook Transaksi
- Ketik rumus : =VLOOKUP(
- Klik Cell A4 : =VLOOKUP(A4
- Ketik titik koma atau koma : =VLOOKUP(A4;
- Ketik Nama File Tabel Barang : =VLOOKUP(A4;Barang.xlsx
- Ketik Nama Range yang sudah dibuat disertai tanda seru : =VLOOKUP(A4;Barang.xlsx!Tabel_Barang
- Ketik titik koma atau koma : =VLOOKUP(A4;Barang.xlsx!Tabel_Barang;
- Ketik angka 2 ( kolom Nama Tabel Barang ) : =VLOOKUP(A4;Barang.xlsx!Tabel_Barang;2
- Ketik titik koma atau koma : =VLOOKUP(A4;Barang.xlsx!Tabel_Barang;2;
- Ketik angka 0 : =VLOOKUP(A4;Barang.xlsx!Tabel_Barang;2;0
- Akhiri dengan kurung tutup : =VLOOKUP(A4;Barang.xlsx!Tabel_Barang;2;0)
- Tekan Enter
Sampai dengan langkah yang ketiga belas diatas kita sudah selesai membuat rumus VLOOKUP beda file dengan Nama Range atau Define Name.
Mohon diperhatikan, meskipun dalam langkah diatas tidak ada membuka file Barang selain pada langkah yang pertama tetapi file Barang tersebut harus dalam keadaan terbuka atau sudah dibuka.
Jika file Barang tidak dalam keadaan dibuka maka rumus yang diketik harus melibatkan Drive lokasi file yaitu D:\.
5. Cara Input Kode Barang Baru Pada VLOOKUP Beda File
Sebenarnya jika kita tambahkan Kode Barang yang baru pada Tabel Barang kemudian file Tabel Barang disimpan maka saat kita ketik Kode Barang baru tersebut pada Tabel Transaksi akan otomatis muncul.
Lalu bagaimana jika kita menambahkan Kode Barang yang baru pada Tabel Barang kemudian Transaksi baru akan dibuka ?
Kondisi tersebut tidak perlu dikhawatirkan karena secara otomatis pada saat kita ketik Kode Barang baru tersebut Nama dalam Tabel Transaksi akan tetap muncul.
Sejauh yang saya tes dan berdasarkan pengalaman tidak ada masalah pada saat menambahkan Kode Baru pada Tabel Barang.
Hanya saja posisi Tabel Barang statusnya tidak dinamis, maksudnya terbatas pada area yang dipilih saja.
Jika kode barang sangat sering bertambah tentu kita akan sedikit kerepotan menambahkan Kode serta update Nama Rangenya.
Baca Juga : Range Dinamis Pada Rumus VLOOKUP Beda File Dalam Excel
Untuk itu kita akan mencoba menerapkan range dinamis pada rumus VLOOKUP beda file.
Tetapi pembahasan tersebut akan dibuat pada artikel tersendiri dan silahkan buka artikelnya jika ingin membandingkan rumusnya.
6. Mengatasi Error #N/A Pada VLOOKUP Beda File
Seperti yang disebutkan diatas bahwa rumus VLOOKUP beda file ini akan berjalan dengan baik meskipun ada penambahan kode atau perubahan Nama range.
Tetapi sama halnya seperti pada penggunaan rumus VLOOKUP biasanya akan muncul error #N/A.
Pada kasus rumus VLOOKUP beda file ada dua kondisi yang memungkinkan munculnya error #N/A.
Kondisi atau kemungkinan yang pertama adalah Kode Barang pada Tabel Barang belum tersedia.
Misalnya kita ketik Kode Barang B-005 pada File Tabel Transaksi sedangkan dalam Tabel Barang Kode B-005 tersebut belum ditambahkan.
Dengan kondisi tersebut maka secara otomatis rumus VLOOKUP beda file akan menampilkan error #N/A.
Selanjutnya kondisi atau kemungkinan yang kedua adalah Kode Barang pada Tabel Transaksi atau kolom A belum diisi atau masih dalam keadaan kosong.
Kondisi yang kedua ini juga sama dengan kondisi yang pertama yaitu akan menampilkan error #N/A.
Untuk mengatasi kedua kondisi diatas kita bisa menambahkan rumus IFERROR pada rumus VLOOKUP beda file.
Misalnya pada contoh rumus yang kedua dengan menambahkan IFERROR maka rumusnya akan menjadi seperti berikut ini :
=IFERROR(VLOOKUP(A4;Barang.xlsx!Tabel_Barang;2;0);”-“)
Dengan menambahkan rumus IFERROR seperti diatas maka error #N/A akan otomatis bisa dihindari.
Dalam kondisi tertentu kadang jika kita membuat link antar file akan muncul pesan untuk mengupdate link tersebut.
Jika ini terjadi saat menggunakan VLOOKUP beda file silahkan klik tombol update pada pesan kesalahan yang muncul.
Baca Juga : Cara Update Link Antar File Dalam Excel
Untuk update link antar file sudah saya bahas dalam artikel yang lain dan silahkan untuk dipelajari.
7. Contoh File Excel VLOOKUP Beda File
Pembahasan yang terakhir mari kita lihat contoh file Excel VLOOKUP beda file yang akan saya sertakan pada artikel ini.
Untuk mendapatkan contoh VLOOKUP beda file silahkan klik tombol berikut ini :
Silahkan ambil contoh file Excelnya melalui tombol yang sudah disediakan diatas.
Untuk dapat menggunakan contoh file tersebut silahkan ikuti langkah – langkah berikut ini :
- Ekstrak filenya menggunakan Winrar
- Simpan file Barang.xlsx dan file Transaksi.xlsx pada Drive D
- Buka file Transaksi untuk melihat rumus yang digunakan
Pastikan menyimpan file tersebut di drive D pada komputer sebelum membuka file Excel Transaksi.
Karena jika file Transaksi dibuka tetapi file Barang tidak ada di Drive D maka Tabel Transaksi hanya akan menampilkan error saja.
Itulah pembahasan kita kali ini tentang cara VLOOKUP beda file dan semoga bermanfaat untuk semua pembaca.
