Anda disini: M Jurnal » Rumus Excel » Rumus VLOOKUP Excel + 11 Contoh Cara Menggunakannya

Rumus VLOOKUP Excel + 11 Contoh Cara Menggunakannya

Fungsi VLOOKUP Excel adalah Rumus untuk mencari data secara vertikal berdasarkan kolom pertama pada sebuah tabel kemudian mengambil nilai yang terdapat pada kolom berikutnya pada baris yang sama dalam tabel data yang sesuai dengan kata kunci yang dicari.

Dengan kata lain, Anda dapat mengambil data pada Tabel Sumber atau Named Range ataupun Range Data berdasarkan kata kunci yang bisa Anda tentukan.

Ads

Misalnya, Saya ingin mencari gaji karyawan berdasarkan NIK. Daftar gaji karyawan sudah tersusun dalam sebuah Range Data (B2:E9). Sementara cell H2 berisi nomor NIK karyawan yang ingin Saya cari data nya.

Untuk mengambil data gaji berdasarkan NIK tersebut, silahkan ikuti cara menggunakan rumus VLOOKUP Excel berikut:

Cara menggunakan rumus VLOOKUP Excel
  1. Pertama, ketik Fungsi VLOOKUP seperti berikut: =VLOOKUP(
  2. Kedua, masukkan nilai yang akan dicari sebagai kata kunci, dalam contoh ini adalah NIK yang berada pada Cell H2 kemudian ketik operator pemisah rumus ( , atau ; )
  3. Ketiga, pilih (blok) range data sumber yaitu Range B2:E9 kemudian ketik operator pemisah rumus ( , atau ; )
  4. Keempat, ketik nomor kolom yang akan Anda ambil datanya. Dalam contoh ini, Saya akan mengambil data Gaji yang berada pada kolom ke 4 dari range data. Kemudian ketik operator pemisah rumus ( , atau ; ) sehingga rumus menjadi =VLOOKUP(H2,B2:E9,4,
  5. Terakhir, Ketik FALSE kemudian ketik tanda tutup kurung “)” dan tekan enter. Berikut hasilnya:
11 Contoh Menggunakan Fungsi VLOOKUP Excel

Dengan rumus ini, Excel akan mencari nomor NIK yang sama dengan Cell H2 di kolom pertama (Kolom NIK) pada Range Data. Kemudian mengambil nilai pada kolom ke 4 yaitu (Kolom Gaji) yang satu baris dengan nilai NIK yang dicari pada kolom pertama.

Note: Untuk mengambil nilai pada kolom lain, Anda hanya perlu mengubah angka 4 (pada tahap keempat) menjadi nomor kolom yang Anda inginkan.

Tentu saja rumus VLOOKUP tidak hanya bisa digunakan untuk contoh di atas saja. Pada Panduan ini Saya berikan 11 Contoh berbeda cara menggunakan rumus VLOOKUP.

Namun sebelum Anda lanjutkan ke contoh berikutnya, lebih baik pahami terlebih dahulu aturan penulisan rumus VLOOKUP Excel berikut:

Ads

Aturan Penulisan Rumus VLOOKUP Excel

Secara default, berikut aturan penulisan Fungsi VLOOKUP:

Tulis Fungsi / Rumus VLOOKUP:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

Terdapat 4 argument dan tidak semua nya bersifat wajib. Berikut makna keempat argument Fungsi VLOOKUP:

  1. lookup_value (Wajib) merupakan Kata Kunci yang akan dicari pada kolom pertama dari tabel sumber atau range data (table_array). Anda bisa mengisi lookup_value berupa angka, teks, tanggal ataupun nilai khusus baik itu diketik secara manual maupun berasal dari sebuah cell ataupun hasil sebuah rumus.
  2. table_array (Wajib) merupakan sumber referensi / sumber data yang akan Anda cari. Table_array minimal harus terdiri dari 2 kolom dan nilai yang akan dicari (lookup_value) terdapat pada kolom pertama pada table_array
  3. col_index_num (Wajib) merupakan nomor kolom dari table_array yang akan Anda ambil nilainya berdasarkan posisi (sebaris) dari lookup_value pada kolom 1. col_index_num harus berupa angka bulat yang lebih dari 0.
  4. [range_lookup] (Optional) merupakan tipe pencarian dari Fungsi VLOOKUP yaitu TRUE atau FALSE. Jangan salah pilih antara kedua tipe pencarian ini.

Jika Anda menggunakan TRUE atau tidak menggunakan TRUE atau FALSE, maka Excel akan mencari lookup_value pada kolom pertama di table_array yang sama atau yang paling mendekati (Dengan Syarat Kolom Pertama Tersusun Dari Nilai Terkecil ke Terbesar).

Jika Excel tidak menemukan nilai yang sama, maka nilai yang lebih kecil dan paling mendekati lookup_value akan digunakan.

Namun perhatikan, jika lookup_value (nilai yang ingin Anda cari) lebih kecil dari nilai pertama pada kolom pertama (table_array), maka Excel akan memberikan hasil #N/A

diskon-hosting

Sementara itu, jika Anda menggunakan FALSE, maka Excel akan mencari lookup_value yang sama dengan data pada kolom pertama di table_array.

Jika Excel tidak menemukan nilai yang sama, maka Rumus VLOOKUP akan menghasilkan error #N/A.

Penting! Saya anggap Anda sudah tahu perbedaan operator pemisah rumus yaitu tanda koma (,) untuk Format English dan Tanda Titik Koma (;) untuk Format Indonesia. Jika Anda belum mengetahuinya, silahkan kunjungi Panduan Mengubah Koma Menjadi Titik

Biar semakin paham, silahkan simak 11 Contoh cara menggunakan Rumus VLOOKUP berikut:

11 Contoh Rumus VLOOKUP Excel

Sebagai bahan latihan kali ini, silahkan download atau gunakan file Excel Online berikut:

Note:
1. Silahkan klik Icon Full Screen Pada Sudut Kanan Bawah File Excel Online berikut untuk menggunakan fitur Excel Online.
2. Klik Icon Download (gambar selembar kertas dengan tanda panah kebawah) pada baris sudut kanan bawah File Excel Online berikut untuk download file.
3. Jika Link Download atau Excel Online Error, silahkan beritahu Saya melalui kolom komentar (Terima kasih 🙂 ).

#1 VLOOKUP Tipe FALSE (Nilai yang Sama)

Untuk contoh ini, silahkan fokus ke WorkSheet FALSE pada Excel Online yang sudah Saya berikan.

Seperti yang sudah Saya jelaskan pada Aturan Penulisan Rumus VLOOKUP, [range_lookup] FALSE akan memerintahkan Excel untuk mencari data yang sama dengan lookup_value. Sementara itu, jika tidak ditemukan nilai yang sama pada kolom pertama, maka Excel memberikan hasil #N/A.

Coba perhatikan 2 contoh rumus VLOOKUP tipe FALSE berikut:

Contoh Cara menggunakan Rumus VLOOKUP Tipe FALSE

Contoh pertama memberikan hasil yang tepat karena nilai yang dicari yaitu NIK 12500 terdapat pada kolom pertama. Sementara contoh kedua memberikan hasil #N/A karena nilai yang dicari yaitu 12000 tidak ditemukan pada Kolom Pertama di Sumber Data (table_array).

Biar semakin paham, berikut makna Fungsi VLOOKUP untuk tipe FALSE (Nilai yang Sama):

Tulis Fungsi / Rumus VLOOKUP:
=VLOOKUP(H2,B2:E9,2,FALSE)

Artinya:
Pencarian Data Secara Vertikal Dengan Kata Kunci Terdapat Pada Cell H2 Yang dicari Pada Kolom Pertama di Range B2:E9 Kemudian Ambil Nilai Pada Kolom 2 yang Sebaris dengan Lokasi Kata Kunci ditemukan Jika Tidak Ditemukan, Berikan Hasil #N/A

#2 VLOOKUP Tipe TRUE (Nilai yang Sama / Lebih Rendah dan Mendekati)

Silahkan buka Sheet TRUE pada Excel Online. Pada contoh ini Saya ubah Kolom NIK menjadi Kolom Nomor sebagai ilustrasi. Coba perhatikan 3 contoh hasil VLOOKUP Tipe TRUE berikut:

Contoh Cara Menggunakan Rumus VLOOKUP Tipe TRUE

Contoh pertama bertujuan mencari angka 3 pada Kolom Nomor, kemudian mengambil nilai pada Kolom 2 (Yaitu Nama). Anda bisa lihat, Angka 3 ditemukan pada Kolom Nomor, sehingga Excel memberikan hasil “Ade” (dari kolom Nama).

Untuk contoh kedua, Excel juga menemukan angka 4 pada Kolom Nomor sehingga memberikan hasil “Putri”

Perbedaan tipe FALSE dan TRUE terlihat pada contoh ketiga. Tujuan Saya adalah mencari Angka 6 pada Kolom Nomor, Namun Excel memberikan hasil “Putri”. Kenapa begitu ?

Karena inilah maksud dari Sama / Lebih Rendah dan Mendekati

Ilustarasinya seperti gambar berikut:

Fungsi VLOOKUP Tipe TRUE

Pertama-tama, Excel akan mencari angka 6 (lookup_value) di Kolom Nomor. Pencarian dimulai dari cell pertama B3 = 1, lanjut, B4 =2, lanjut B5 = 3

Ketika Excel menemukan angka yang lebih besar dari 6 (yaitu cell B7 = 7), maka pencarian berakhir.

Karena Excel tidak menemukan angka 6, maka Excel akan menggunakan angka yang lebih kecil dan paling mendekati 6 di atas angka 7. Dalam contoh ini adalah angka 4.

Selanjutnya Excel mengambil nilai pada Kolom Kedua (col_index_num = 2) yaitu “Putri”.

Nah, sebagai rangkuman tipe TRUE, berikut makna fungsi VLOOKUP tersebut:

Tulis Fungsi / Rumus VLOOKUP:
=VLOOKUP(J2,B2:E9,2,TRUE)

Artinya:
Pencarian Data Secara Vertikal Dengan Kata Kunci Terdapat Pada Cell J2 Yang dicari Pada Kolom Pertama di Range B2:E9 Kemudian Ambil Nilai Pada Kolom 2 yang Sebaris dengan Lokasi Kata Kunci ditemukan Jika Tidak Ditemukan, Gunakan Nilai Yang Lebih kecil dan Paling Mendekati Kata Kunci

Note: Jika menggunakan tipe TRUE, maka data pada kolom pertama harus tersusun berurutan dari terkecil hingga terbesar. Jika tidak, Excel akan memberikan hasil #N/A

#3 VLOOKUP Selalu Mengambil Data Kekanan

Proses kerja rumus VLOOKUP selalu dimulai dari mencari data pada kolom paling kiri (Kolom 1) dari table_array (sumber data), kemudian mengambil data kekanan berdasarkan col_index_num.

Note: Argument col_index_num harus berupa angka bulat yang lebih besar dari 0 yaitu 1, 2, 3, 4, 5, dst..

Biar semakin paham proses kerja Rumus VLOOKUP, silahkan buka Sheet Arah VLOOKUP.

Sebagai contoh, Saya ingin mencari data berdasarkan NIK. Sementara, kolom NIK berada di tengah-tengah tabel. Di sebelah kiri Kolom NIK ada 3 kolom dan disebelah kanan ada 3 kolom.

Secara default, jika menggunakan Kolom NIK sebagai Kata Kunci (Kriteria), maka Anda tidak akan bisa mengambil data sebelah kiri kolom NIK.

Ilustrasinya seperti gambar berikut:

Alasan Fungsi VLOOKUP Mengambil data ke kanan Bukan kekiri

Note: col_index_num pertama (ke 1) selalu dihitung dari kolom pertama yang Anda pilih (blok) saja. Bukan dari semua kolom pada tabel yang Anda jadikan sumber data.

Dengan argument col_index_num lebih kecil dari 1, (0, -1, -2 dst…), maka Excel akan memberikan hasil #VALUE!.

Sementara itu, jika col_index_num lebih besar dari jumlah kolom yang ada pada Tabel Sumber, maka Excel memberikan hasil #REF!

Dalam contoh ini, jumlah kolom dari range data yang Saya pilih (blok) hanya 4 yaitu kolom NIK, Jenis Kelamin, Masa Kerja dan Gaji. Oleh karena itu Excel memberikan hasil #REF! pada cell C21 dan C22.

Penting! Inilah alasan kenapa Rumus VLOOKUP secara default tidak akan bisa digunakan untuk mengambil data ke arah kiri. Jika Anda ingin VLOOKUP mengambil data ke arah kiri, silahkan kunjungi SUB-BAB Left Lookup.

#4 VLOOKUP Jika Kolom Pertama Berisi Nilai Duplikat

Jika terdapat nilai duplikat (ganda) pada kolom pertama dari sumber data (table_array) Anda, maka Excel hanya akan mengambil nilai yang sama dan pertama kali ditemukan.

Sebagai contoh, silahkan buka Sheet Duplikat Excel Online. Pada kolom pertama (kolom nama) terdapat data duplikat yaitu Putri. Namun data NIK, Jabatan dan Gaji untuk kedua Putri tersebut tidak sama.

Saya coba menggunakan Rumus VLOOKUP tipe TRUE dan FALSE dengan kata kunci Putri. Hasilnya seperti gambar berikut:

Rumus VLOOKUP Untuk data Duplikat

Pertama-tama, Rumus VLOOKUP akan mencari kata “Putri” pada kolom pertama (Kolom Nama) dari atas kebawah.

Kemudian setelah menemukan kata “Putri” pertama kalinya (yaitu cell B6, Excel tidak melanjutkan pencarian. Melainkan mengambil nilai yang sebaris sesuai dengan col_index_num yang Anda tentukan.

Penting! Jika Anda ingin mencari dan menandai data duplikat, silahkan kunjungi Panduan Highlight Duplikat. Jika Anda ingin menghapus data duplikast, silahkan kunjungi Panduan Hapus Data Duplikat.

#5 VLOOKUP Tidak Peka Huruf Besar Kecil (Case-insensitive)

Secara default, Rumus VLOOKUP juga tidak sepeka wanita. Dalam mencari lookup_value (Kata Kunci) pada kolom pertama, VLOOKUP tidak mempertimbangkan huruf besar ataupun kecil.

Jika Anda mencari Putri, maka VLOOKUP juga menganggap PUTRI atau pUtri (atau kombinasi huruf besar kecil lainnya) sebagai data yang sama.

Silahkan buka Sheet Insensitive pada Excel Online atau perhatikan gambar berikut:

Fungsi VLOOKUP Tidak Peka huruf besar atau kecil

Saya bermaksud mencari dan mengambil data pUtRi, tapi VLOOKUP memberikan hasil dari data PUTRI baik menggunakan VLOOKUP tipe FALSE maupun TRUE.

Penting! Jika Anda ingin Fungsi VLOOKUP peka huruf besar dan kecil, silahkan kunjungi SUB-BAB Case Sensitive Lookup

#6 Mengubah Hasil #N/A VLOOKUP

Sebelumnya Saya sudah bahas tentang hasil #N/A dari Fungsi VLOOKUP tipe TRUE maupun FALSE. Jika ingin mengubah hasil #N/A menjadi nilai tertentu, silahkan kombinasikan Fungsi VLOOKUP dengan Fungsi IFNA.

Note: Fungsi IFNA hanya tersedia pada Excel Versi 2013 ke atas. Jika Anda menggunakan Excel Versi 2010, 2007 atau lebih lama, silahkan ubah IFNA menjadi IFERROR. Ingat, IFERROR juga bisa mengubah hasil pesan kesalahan lainnya seperti #NAME?, #REF! dll.

Silahkan ikuti cara berikut untuk mengubah hasil #N/A dari Rumus VLOOKUP menjadi nilai yang Anda inginkan:

Mengatasi dan Mengubah Hasil #N/A! Rumus VLOOKUP Excel
  1. Pertama, ketik Fungsi IFNA seperti berikut: =IFNA(
  2. Kedua, masukkan fungsi VLOOKUP sesuai tujuan Anda. Dalam contoh ini, rumus akan menjadi seperti berikut: =IFNA(VLOOKUP(H2,B2:E9,2,FALSE)
  3. Ketiga, ketik operator pemisah rumus ( , atau ; )
  4. Keempat, masukkan nilai yang Anda inginkan jika VLOOKUP menghasilkan #N/A. Misalnya, Saya ingin hasil menjadi Tidak Ada, maka ketik “Tidak Ada”. Note: Apit dengan tanda kutip dua ” “
  5. Terakhir, ketik tanda tutup kurung, kemudian tekan Enter.

Mudah bukan ? Sekarang Anda tidak akan lagi menemukan hasil #N/A. Berikut makna dari gabungan Fungsi IFNA dan VLOOKUP tersebut:

Tulis Fungsi / Rumus IFNA + VLOOKUP:
=IFNA(VLOOKUP(H2,B2:E9,2,FALSE),“Tidak Ada”)

Artinya:
Apakah Data Berikut Merupakan #N/A ?: Pencarian Data Secara Vertikal Dengan Kata Kunci Terdapat Pada Cell H2 Yang dicari Pada Kolom Pertama di Range B2:E9 Kemudian Ambil Nilai Pada Kolom 2 yang Sebaris Dengan Lokasi Kata Kunci ditemukan Jika Tidak ditemukan, berikan hasil #N/A. Jika hasil #N/A, ubah menjadi Tidak Ada

Bagaimana, tidak sulit bukan memahami Fungsi VLOOKUP ? Next, kita lanjut ke contoh yang lebih kompleks.

#7 Solusi AutoFill / Copy-Paste Rumus VLOOKUP

Ketika berurusan dengan rumus Excel, AutoFill ataupun Copy-Paste menjadi cara tercepat tanpa mengulang penulisan rumus.

Namun berbeda dengan VLOOKUP, Anda tidak bisa sembarangan AutoFill ataupun copy-paste rumus. Sebagai contoh, silahkan buka Sheet AutoFill Excel Online atau perhatikan gambar berikut:

Cara Autofill atau Copy Paste Rumus VLOOKUP Excel

Jika Saya AutoFill / Copy-Paste rumus VLOOKUP pada cell H3 ke Cell H4 sampai H6 , maka akan ada beberapa hasil error seperti gambar berikut:

Solusi Autofill / Copy Paste Rumus VLOOKUP Excel

Dalam contoh ini, kesalahan yang terjadi tepat pada cell reference untuk argument table_array.

Pada gambar bisa Anda lihat bahwa cell reference pada argument table_array berubah searah Anda melakukan AutoFill / Copy-Paste Rumus.

Begitu juga dengan argument lookup_value. Namun pada contoh ini, Argument lookup_value tidak menjadi masalah.

Solusinya, Anda bisa mengubah Cell Reference argument table_array menjadi Absolute Reference (Simbol Dollar $) atau menggunakan Fitur Tabel Bernama

Penting! Pelajari Selengkapnya tentang Cell Reference Excel

Selanjutnya Jika Saya AutoFill / Copy-Paste rumus VLOOKUP pada cell H3 ke Cell I3 sampai I6 , maka semua hasil rumus VLOOKUP menjadi salah seperti gambar berikut:

Jangan Sembarangan Autofill / Copy Paste Rumus VLOOKUP Excel

Bagian mana yang salah ? Coba lihat argument lookup_value berubah ke kolom Nama, argument table_array berubah menjadi C2:F9 dan argument col_index_num tidak berubah yang mana seharusnya diubah mejadi 4.

Note: Kearah manapun Anda lakukan AutoFill / Copy-Paste Rumus VLOOKUP, maka nilai argument col_index_num tidak akan berubah.

Berikut Pembuktian Solusi

Anda bisa menggunakan Fitur Tabel Bernama + Gabungan Fungsi IF Bertingkat dan VLOOKUP.

Penting! Jika Anda belum tahu cara membuat Tabel Bernama, silahkan kunjungi Panduan Membuat Tabel Excel

Keuntungan trik ini juga akan membuat Fungsi VLOOKUP Anda menjadi Rumus Terstruktur. Agar Rumus VLOOKUP bisa di copy-paste atau AutoFill, silahkan ikuti cara berikut:

Cara AutoFIll / Copy Paste Rumus VLOOKUP Excel
  1. Pertama, ketik Fungsi VLOOKUP seperti berikut: =VLOOKUP(
  2. Kedua, ketik simbol dolar ($) kemudian masukkan argument lookup_value dan ketik operator pemisah rumus ( , atau ; )
  3. Ketiga, ketik nama tabel Anda. Dalam contoh ini Tabel_Karyawan. Kemudian ketik operator pemisah rumus ( , atau ; )
  4. Keempat, masukkan fungsi IF untuk argument col_index_num sebagai berikut: IF(H$11=”Nama”,2,IF(H$11=”Gaji”,4,0))
  5. Kelima, ketik operator pemisah rumus ( , atau ; )
  6. Keenam, pilih FALSE atau TRUE kemudian ketik tutup kurung dan tekan enter.
  7. Terakhir, silahkan AutoFill / Copy-Paste Rumus pada Cell H12 ke Cell lainnya. Berikut hasilnya:
Cara AutoFIll / Copy Paste Rumus VLOOKUP Excel

Penjelasan: Dengan menggunakan Format Tabel Bernama, maka ketika AutoFill / Copy – Paste Rumus pada argument table_array tidak akan berubah.

Sementara Absolute Reference ($) akan mengunci kolom sesuai penempatan lambang dolarnya ya.

Terakhir, argument col_index_num VLOOKUP menggunakan Fungsi IF. Dengan ini, col_index_num akan berubah sesuai Fungsi IF yang digunakan.

#8 INDEX & MATCH (Alternatif Fungsi VLOOKUP)

Fungsi INDEX & MATCH bisa Anda gunakan sebagai alternatif fungsi VLOOKUP yang lebih lengkap.

Silahkan buka Sheet INDEX MATCH. Kemudian untuk mencari data berdasarkan NIK kemudian mengambil nilai Gaji yang sesuai, silahkan ikuti tahap-tahap Fungsi INDEX & MATCH berikut:

Kombinasi Fungsi INDEX dan MATCH untuk mencari dan mengambil data dari sumber
  1. Pertama, ketik Fungsi INDEX seperti berikut: =INDEX(
  2. Kedua, pilih (blok) data yang ingin Anda ambil nilai nya. Dalam contoh ini adalah range E3:E9. Kemudian ketik operator pemisah rumus ( , atau ; )
  3. Ketiga, masukkan fungsi MATCH sehingga rumus menjadi seperti berikut: =INDEX(E3:E9,MATCH(
  4. Keempat, Klik H2 sebagai lookup_value kemudian ketik operator pemisah rumus ( , atau ; )
  5. Kelima, pilih (blok) range B3:B9 sebagai lookup_array kemudian ketik operator pemisah rumus ( , atau ; )
  6. Keenam, ketik 0 sebagai [match_type] kemudian ketik tanda tutup kurung sebanyak 2x.
  7. Terakhir, tekan Enter dan Berikut hasilnya:
Kombinasi Fungsi INDEX dan MATCH untuk mencari dan mengambil data dari tabel sumber

Penting! Selengkapnya tentang Fungsi INDEX dan MATCH, Lihat Pintasan Panduan

#9 VLOOKUP Untuk 2 Kata Kunci (Kriteria)

Secara default, Fungsi VLOOKUP tidak bisa Anda gunakan untuk 2 kata kunci / kriteria / lookup_value. Dengan kata lain, Anda tidak bisa menggabungkan 2 buah fungsi VLOOKUP.

Sebagai alternatif yang lebih kompleks, Anda bisa menggunakan gabungan Fungsi INDEX & MATCH.

Sebagai contoh, silahkan buka Sheet 2 Kriteria. Pada contoh ini, ada 2 kolom yaitu Nama Depan dan Nama Belakang yang akan Saya jadikan kriteria pencarian.

Kemudian berdasarkan Nama Depan dan Nama Belakang yang cocok, Saya akan mengambil nilai Gaji. Silahkan ikuti tahap-tahap berikut:

Fungsi INDEX dan MACTH untuk mengatasi VLOOKUP 2 kriteria
  1. Pertama, ulangi tahap 1 sampai 2 pada Fungsi INDEX & MATCH sebelumnya sehingga rumus menjadi =INDEX(E3:E9,MATCH(
  2. Kedua, klik Cell H2 sebagai kriteria pertama dan ketik operator dan “&”. Kemudian klik cell H3 sebagai kriteria kedua dan ketik operator pemisah rumus ( , atau ; )
  3. Ketiga, pilih (blok) range B3:B9 sebagai lookup_array kriteria pertama dan ketik operator dan “&”. Kemudian pilih (blok) range C3:C9 sebagai lookup_array kriteria kedua dan ketik operator pemisah rumus ( , atau ; )
  4. Keempat, ketik angka 0 sebagai [match_type] kemudian ketik tanda tutup kurung sebanyak 2x.
  5. Terakhir, tekan tombol CTRL, Shift dan Enter pada keyboard secara bersamaan. Berikut hasilnya:
Kombinasi Fungsi INDEX dan MACTH Excel

Pada Formula Bar, Anda akan melihat gabungan fungsi INDEX & MATCH seperti berikut: {=INDEX(E3:E9,MATCH(H2&H3,B3:B9&C3:C9,0))}

Note: Jangan ketik tanda kurung kurawal { } secara manual, karena ini merupakan ARRAY Formula. Ketika Anda tekan CTRL, Shift dan Enter secara bersamaan, Excel menyisipkan kurung kurawal.

Silahkan pelajari tentang ARRAY Formula pada Panduan Rumus ARRAY Excel.

#10 VLOOKUP Untuk 2 Tabel Sumber

Contoh ini berguna jika Anda menggunakan 1 Fungsi VLOOKUP untuk 1 kata kunci dan mencarinya pada 2 Sumber Data (Tabel) berbeda.

Sebagai contoh, silahkan buka Sheet 2 Sumber. Tampilan awal data seperti gambar berikut:

Cara menggunakan Rumus VLOOKUP Untuk 2 Tabel Sumber Berbeda

Pada contoh ini, kriteria bonus yang didapatkan karyawan area Sumatera terdapat pada Tabel 1. Sedangkan Tabel 2 merupakan bonus untuk area Jawa.

Selanjutnya kita akan menghitung bonus yang didapatkan masing-masing karyawan sesuai Area Penjualan masing-masing pada Tabel Bonus Karyawan.

Sebelumnya Saya sudah berikan contoh menggunakan Tabel Bernama. Nah pada contoh kali ini, Saya akan menggunakan Named Range.

Sebelum menggunakan rumus VLOOKUP, silahkan ubah Tabel 1 (Area Sumatera) dan Tabel 2 (Area Jawa) menjadi Tabel Bernama atau Named Range.

Penting! Silahkan pelajari membuat Named Range pada Panduan 4 Cara Membuat Named Range Excel

Cara menggunakan Rumus VLOOKUP untuk 2 Data (Tabel) Sumber berbeda, silahkan ikuti tahap-tahap berikut:

Cara menggunakan rumus VLOOKUP untuk 2 Tabel Sumber Berbeda
  1. Pertama, ketik fungsi VLOOKUP seperti berikut: =VLOOKUP(
  2. Kedua, klik cell J4 sebagai lookup_value (kata kunci), kemudian ketik operator pemisah rumus.
  3. Ketiga, gunakan fungsi IF sebagai table_array untuk memilih antara kedua sumber data. Ketik fungsi IF sebagai berikut: IF(I4=”Sumatera”,Tabel1,Tabel2) kemudian ketik operator pemisah rumus ( , atau ; ). Note: Argument Tabel1 dan Tabel2 merupakan nama Named Range.
  4. Keempat, masukkan col_index_num yaitu 2. Karena kita akan mengambil data bonus dari Tabel1 atau Tabel2. Kemudian ketik operator pemisah rumus ( , atau ; )
  5. Kelima, gunakan tipe TRUE karena bonus (pada tabel1 dan 2) akan diberikan jika karyawan mencapai total penjualan tertentu.
  6. Keenam, ketik tanda tutup kurung dan tekan Enter.
  7. Terakhir, lakukan AutoFill / Copy – Paste Rumus kebawah untuk menghitung bonus yang didapatkan Karyawan lainnya sesuai Area. Berikut hasilnya:
Cara menggunakan Rumus VLOOKUP + IF untuk 2 tabel sumber berbeda

Anda bisa lihat, fungsi IF memfilter table_array sesuai kriteria.

Lihat Tabel1, Jika Total Penjualan mencapai 40.000.000, maka mendapatkan bonus 6.000.000. Jika Total Penjualan mencapai 60.000.000, maka mendapatkan bonus 7.000.000.

Sementara Total penjualan Rolan mencapai 51.710.000. Sehingga Rolan mendapatkan bonus 6.000.000 karena belum mencapati batas 60.000.000.

Contoh kedua, coba lihat Tabel2: Bonus untuk Area Jawa hanya jika Total Penjualan mencapai 50.000.000 dan 100.000.000.

Karyawan bernama Ade mencapai batas 50.000.000 dan Masayu mencapai batas 100.000.000. Sehingga mereka mendapatkan bonus. Sementara total penjualan Bela belum mencapati batas minimum untuk mendapatkan bonus.

#11 XLOOKUP (Alternatif Fungsi VLOOKUP)

Jika Anda menggunakan Office 365, Saya sarankan lebih baik menggunakan Fungsi XLOOKUP daripada VLOOKUP.

Fungsi XLOOKUP lebih mudah digunakan untuk fitur pencarian dan pengmbilan data yang lebih komplek daripada VLOOKUP.

Berikut contoh sederhana fungsi XLOOKUP:

Fungsi XLOOKUP Excel Office 365

Saya yakin masih banyak kondisi lain yang akan Anda temui ketika menggunakan fungsi VLOOKUP. Untuk itu, silahkan gunakan Pintasan Panduan Lookup & Reference Excel berikut:

Pintasan Panduan Lookup & Reference Excel

  1. Lookup & Reference: Dasar-dasar Fungsi Pencarian dan Referensi di Excel (VLOOKUP, HLOOKUP, MATCH, INDEX dan CHOSE)
  2. VLOOKUP: (Anda Disini)
  3. Tax Rates: Contoh kasus menghitung Pajak Penghasilan (PPH) menggunakan Fungsi VLOOKUP.
  4. INDEX: Cara menggunakan Fungsi INDEX untuk melakukan pencarian data dari 1 Range Data (Sumber) bahkan lebih. Ada 7 Contoh penggunaan yang berbeda + Tips
  5. MATCH: Cara menggunakan Fungsi MATCH Excel untuk mencari posisi item tertentu dalam Range Data, apakah berada pada kolom atau baris keberapa dari Range Data. Kriteria pencarian dapat ditentukan sendiri.
  6. INDIRECT: Cara menggunakan Fungsi INDIRECT. Anda pasti menemukan inspirasi dari fungsi ini.
  7. INDEX + MATCH: Gabungan Fungsi INDEX dan MATCH Excel sebagai alternatif fungsi VLOOKUP. Ikuti panduan ini jika Anda ingin fungsi pencarian tingkat lanjut diatas VLOOKUP
  8. Two-way Lookup: Mencari data tertentu berdasarkan 2 atau lebih kriteria. Seperti membuat form pencarian dengan 2 atau lebih kriteria.
  9. OFFSET: Cara menggunakan Fungsi OFFSET untuk mencari data berdasarkan lokasi (cell / sheet dll)
  10. Case-sensitive Lookup: Cara menggunakan Fungsi INDEX, MATCH, dan EXACT untuk mencari data (case-sensitive)
  11. Left Lookup: Cara mencari data ke arah kiri dari sumber. Contoh ini tidak bisa dilakukan menggunakan Fungsi VLOOKUP.
  12. Locate Maximum Value: Cara mengetahui nilai maximum pada data yang banyak. Anda juga bisa mengetahui pada cell mana nilai maximum tersebut berada.
  13. Two-column Lookup: Cara mencari data berdasarkan 2 kriteria berbeda.
  14. Closest Match: Kombinasi Fungsi INDEX, MACTH, MIN dan ABS untuk mencari data yang paling mendekati kriteria Anda.
  15. Compare Two Columns: Membandingkan 2 kolom. Dapat digunakan untuk menemukan data duplikat dan unik.
  16. XLOOKUP: Jika Anda pengguna Office 365, Lebih baik menggunakan Fungsi XLOOKUP daripada VLOOKUP

Apakah Panduan ini Membantu Menyelesaikan Permasalahan Anda ?

Rolan Mardani

Traktir Saya secangkir kopi supaya kuat begadang untuk membuat konten-konten Panduan yang berkualitas dan membantu menyelesaikan permasalahan Anda.

Bantu Orang Lain... Share Panduan ini:

Yakin Ga Mau Komen ?