Grafik Fungsi Excel VLOOKUP adalah alat canggih yang membantu Anda mencari nilai tertentu dengan mencocokkan kolom pertama tabel atau rentang secara vertikal, lalu mengembalikan nilai terkait dari kolom lain di baris yang sama. Meskipun VLOOKUP sangat berguna, terkadang sulit untuk dipahami bagi pemula. Tutorial ini bertujuan untuk membantu Anda menguasai VLOOKUP dengan menyediakan penjelasan langkah demi langkah dari argumen, contoh yang berguna dan solusi untuk kesalahan umum mungkin Anda temui saat menggunakan fungsi VLOOKUP.

• Penjelasan argumen langkah demi langkah
• Sintaks dan argumen
• Contoh VLOOKUP
  Pencocokan tepat vs pencocokan perkiraan   |   Vlookup dengan multi-kondisi
• VLOOKUP Error & solusi umum
  #T/A kesalahan   |   kesalahan #VALUE   |   kesalahan #REF   |   Nilai salah

Contoh VLOOKUP .xlsx Kesalahan VLOOKUP .xlsx

Related Videos

Penjelasan argumen langkah demi langkah

Seperti yang ditunjukkan pada tangkapan layar di atas, fungsi VLOOKUP digunakan untuk mencari email berdasarkan nomor ID yang diberikan. Sekarang saya akan memberikan penjelasan mendetail tentang cara menggunakan VLOOKUP dalam contoh ini dengan menguraikan setiap argumen langkah demi langkah.

Langkah 1: Mulai fungsi VLOOKUP

Pilih sel (H6 dalam hal ini) untuk mengeluarkan hasilnya, kemudian mulai fungsi VLOOKUP dengan mengetikkan konten berikut di Formula Bar.

=VLOOKUP(

Langkah 2: Tentukan nilai pencarian

Pertama, tentukan nilai pencarian (yang Anda cari) di fungsi VLOOKUP. Di sini, saya mereferensikan sel G6 yang berisi nomor ID tertentu 1005.

=VLOOKUP(G6

Langkah 3: Tentukan array tabel

Selanjutnya, tentukan rentang sel yang berisi nilai yang Anda cari dan nilai yang ingin Anda kembalikan. Dalam hal ini, saya memilih rentang B6:E12. Rumus sekarang muncul sebagai berikut:

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

Langkah 4: Tentukan kolom dari mana Anda ingin mengembalikan nilai

Kemudian tentukan kolom yang ingin Anda kembalikan nilainya.

Dalam contoh ini, karena saya perlu mengembalikan email berdasarkan nomor ID, di sini saya memasukkan angka 4 untuk memberi tahu VLOOKUP agar mengembalikan nilai dari kolom keempat rentang data.

=VLOOKUP(G6,B6:E12,4

Langkah 5: Temukan perkiraan atau pencocokan persis

Terakhir, tentukan apakah Anda mencari pencocokan perkiraan atau pencocokan tepat.

• Untuk menemukan sebuah pertandingan yang tepat, Anda perlu menggunakan SALAH sebagai argumen terakhir.
• Untuk menemukan sebuah perkiraan pertandingan, Gunakan BENAR sebagai argumen terakhir, atau biarkan kosong.

Dalam contoh ini, saya menggunakan FALSE untuk pencocokan persis. Rumusnya sekarang terlihat seperti ini:

=VLOOKUP(G6,B6:E12,4,FALSE

Tekan tombol Enter untuk mendapatkan hasilnya

Dengan menjelaskan setiap argumen satu per satu pada contoh di atas, sintaks dan argumen fungsi VLOOKUP kini jauh lebih mudah dipahami.

Sintaks dan argumen

=VLOOKUP (lookup_value, table_array, col_index, [range_lookup])

• Nilai lookup (wajib): Nilai (nilai nyata atau referensi sel) yang Anda cari. Ingat nilai ini harus berada di kolom pertama dari table_array.
• Tabel_array (wajib): Rentang sel berisi kolom nilai pencarian dan kolom nilai kembalian.
• indeks_kolom (wajib): Bilangan bulat mewakili nomor kolom yang berisi nilai kembalian. Dimulai dengan nomor 1 untuk kolom paling kiri dari table_array.
• Rentang_pencarian (opsional): Nilai logis yang menentukan apakah Anda ingin VLOOKUP menemukan pencocokan perkiraan atau pencocokan tepat.
  • Perkiraan kecocokan - Tetapkan argumen ini ke BENAR, 1 atau meninggalkannya kosong.
    penting: Untuk menemukan perkiraan kecocokan, nilai di kolom pertama dari table_array harus diurutkan dalam urutan menaik jika VLOOKUP mengembalikan hasil yang salah.
  • Benar-benar cocok - Tetapkan argumen ini ke SALAH or 0.

contoh

Bagian ini menunjukkan beberapa contoh untuk membantu Anda memiliki pemahaman yang lebih komprehensif tentang fungsi VLOOKUP.

Contoh 1: Pencocokan tepat vs. pencocokan perkiraan di VLOOKUP

Jika Anda bingung tentang pencocokan persis dan pencocokan perkiraan saat menggunakan VLOOKUP, bagian ini dapat membantu Anda menjernihkan kebingungan tersebut.

Pencocokan Persis di VLOOKUP

Dalam contoh ini, saya akan mencari nama yang sesuai berdasarkan skor yang tercantum dalam rentang E6:E8, jadi saya memasukkan rumus berikut di sel F6 dan menyeret gagang IsiOtomatis ke bawah ke F8. Dalam rumus ini, argumen terakhir ditentukan sebagai SALAH untuk melakukan pencarian pencocokan tepat.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

Namun, karena skor 98 tidak ada di kolom pertama rentang data, VLOOKUP mengembalikan hasil kesalahan #N/A.

Perkiraan kecocokan di VLOOKUP

Masih menggunakan contoh di atas, jika Anda mengubah argumen terakhir menjadi BENAR, VLOOKUP akan melakukan pencarian perkiraan kecocokan. Jika tidak ditemukan kecocokan, ia akan menemukan nilai terbesar berikutnya yang kurang dari nilai pencarian dan mengembalikan hasil yang sesuai.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Karena skor 98 tidak ada, VLOOKUP menemukan nilai terbesar berikutnya yang kurang dari 98, yaitu 95, dan mengembalikan nama skor 95 sebagai hasil terdekat.

Contoh 2: Gunakan VLOOKUP dengan banyak kriteria

Bagian ini menunjukkan cara menggunakan VLOOKUP dengan berbagai kondisi di Excel. Seperti yang ditunjukkan pada tangkapan layar di bawah, jika Anda mencoba mencari gaji berdasarkan nama yang diberikan (di sel H5) dan departemen (di sel H6), ikuti langkah-langkah di bawah ini untuk menyelesaikannya.

Langkah 1: Tambahkan kolom pembantu untuk menggabungkan nilai dari kolom pencarian

Dalam hal ini, kita perlu membuat kolom pembantu untuk menggabungkan nilai dari Nama kolom dan Departemen kolom.

1. Tambahkan kolom pembantu di sebelah kiri rentang data Anda dan berikan tajuk ke kolom ini. Lihat tangkapan layar:
   
   
2. Di kolom pembantu ini, pilih sel pertama di bawah tajuk, masukkan rumus berikut di Bilah formula, dan tekan Enter.
   

   =C6&" "&D6

   
   Catatan: Dalam rumus ini, kami menggunakan ampersand (&) untuk menggabungkan teks dalam dua kolom untuk menghasilkan satu bagian teks.

   • C6 adalah nama depan dari Nama kolom untuk bergabung, D6 adalah departemen pertama dari Departemen kolom untuk bergabung.
   • Nilai dari kedua sel ini digabungkan dengan spasi di antaranya.
3. Pilih sel hasil ini, lalu seret Tuas IsiOtomatis turun untuk menerapkan rumus ini ke sel lain di kolom yang sama.
   
   

Langkah 2: Terapkan fungsi VLOOKUP dengan kriteria yang diberikan

Pilih sel tempat Anda ingin menampilkan hasilnya (di sini saya memilih I7), masukkan rumus berikut di Bilah formula, lalu tekan Enter.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

Hasil

Kesalahan umum VLOOKUP dan solusinya

Bagian ini mencantumkan kesalahan umum yang mungkin Anda temui saat menggunakan VLOOKUP dan memberikan solusi untuk memperbaikinya.

Kesalahan #N/A dikembalikan

Kesalahan paling umum dengan VLOOKUP adalah kesalahan #N/A, yang berarti bahwa Excel tidak dapat menemukan nilai yang Anda cari. Berikut adalah beberapa alasan mengapa VLOOKUP dapat mengembalikan kesalahan #N/A.

Alasan 1: Nilai pencarian tidak ada di kolom pertama dari table_array

Salah satu keterbatasan Excel VLOOKUP adalah Anda hanya dapat melihat dari kiri ke kanan. Jadi, nilai pencarian harus di kolom pertama dari table_array.

Seperti yang ditunjukkan pada tangkapan layar di bawah, saya ingin mengembalikan nama berdasarkan judul pekerjaan yang diberikan. Di sini nilai pencarian (manajer penjualan) ada di kolom kedua dari table_array dan nilai kembalian ada di sebelah kiri kolom pencarian, jadi VLOOKUP mengembalikan kesalahan #N/A.

Solusi

Anda dapat menerapkan salah satu solusi berikut untuk memperbaiki kesalahan ini.

• Atur ulang kolom
  
  Anda dapat mengatur ulang kolom untuk menempatkan kolom pencarian di kolom pertama dari table_array.
• Gunakan fungsi INDEX dan MATCH secara bersamaan
  
  Di sini kami menggunakan fungsi INDEX dan MATCH bersama-sama sebagai alternatif VLOOKUP untuk mengatasi masalah ini.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Menggunakan fungsi XLOOKUP (tersedia di Excel 365, Excel 2021, dan versi yang lebih baru)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

Alasan 2: Nilai pencarian tidak ditemukan di kolom pencarian (cocok persis)

Salah satu alasan paling umum mengapa VLOOKUP mengembalikan kesalahan #N/A adalah karena nilai yang Anda cari tidak ditemukan.

Seperti yang ditunjukkan pada contoh di bawah ini, kita akan mencari nama berdasarkan skor 98 yang diberikan di E6. Namun, skor ini tidak ada di kolom pertama rentang data, sehingga VLOOKUP mengembalikan hasil kesalahan #N/A.

Solusi

Untuk memperbaiki kesalahan ini, Anda dapat mencoba salah satu solusi berikut.

• Jika Anda ingin VLOOKUP mencari nilai terbesar berikutnya yang kurang dari nilai pencarian, ubah argumen terakhir SALAH (cocok persis) untuk BENAR (perkiraan pertandingan). Untuk informasi lebih lanjut, silakan lihat Contoh 1: Pencocokan tepat vs pencocokan perkiraan menggunakan VLOOKUP.
• Untuk menghindari perubahan argumen terakhir dan mendapatkan pengingat jika nilai pencarian tidak ditemukan, Anda dapat memasukkan fungsi VLOOKUP ke dalam fungsi IFERROR:
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Alasan 3: Nilai pencarian lebih kecil dari nilai terkecil di kolom pencarian (perkiraan cocok)

Seperti yang ditunjukkan pada tangkapan layar di bawah, Anda sedang melakukan pencarian perkiraan kecocokan. Nilai yang Anda cari (nomor ID 1001 dalam kasus ini) lebih kecil dari nilai terkecil 1002 di kolom pencarian, oleh karena itu, VLOOKUP mengembalikan kesalahan #N/A.

Solusi

Berikut adalah dua solusi untuk Anda.

• Pastikan nilai pencarian lebih besar dari atau sama dengan nilai terkecil di kolom pencarian.
• Jika Anda ingin Excel mengingatkan Anda bahwa nilai pencarian tidak ditemukan, cukup tempatkan fungsi VLOOKUP di fungsi IFERROR sebagai berikut:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Alasan 4: Angka diformat sebagai teks

Seperti yang dapat Anda lihat pada tangkapan layar di bawah, hasil kesalahan #N/A dalam contoh ini disebabkan oleh ketidakcocokan tipe data antara sel pencarian (G6) dan kolom pencarian (B6:B12) dari tabel asli. Di sini nilai dalam G6 adalah angka, dan nilai dalam rentang B6:B12 adalah angka yang diformat sebagai teks.

Solusi

Untuk mengatasi masalah ini, Anda perlu mengonversi nilai pencarian kembali ke angka. Berikut adalah dua metode untuk Anda.

• Terapkan fitur Konversi ke Angka
  
  Klik pada sel yang ingin Anda ubah teks menjadi angka, pilih tombol ini  di samping sel lalu pilih Ubah ke Angka.
  
• Terapkan alat praktis untuk mengonversi kumpulan antara teks dan angka
  
  Grafik Konversi antara Teks dan Angka fitur dari Kutools untuk Excel membantu Anda dengan mudah mengonversi rentang sel dari teks ke angka dan sebaliknya. Dapatkan uji coba gratis fitur lengkap selama 30 hari sekarang!
  

Alasan 5: Table_array tidak konstan saat menyeret rumus VLOOKUP ke sel lain

Seperti yang ditunjukkan pada tangkapan layar di bawah, ada dua nilai pencarian di E6 dan E7. Setelah mendapatkan hasil pertama di F6, seret rumus VLOOKUP dari sel F6 ke F7, hasil kesalahan #N/A dikembalikan. Itu karena referensi sel (B6:C12) bersifat relatif secara default, dan disesuaikan saat Anda bergerak ke bawah melalui baris. Larik tabel telah dipindahkan ke B7:C13, yang tidak lagi berisi skor pencarian 73.

Solusi

Anda perlu mengunci larik tabel agar tetap konstan dengan menambahkan a $ tanda tangani sebelum baris dan kolom dalam referensi sel. Untuk mengetahui lebih lanjut tentang referensi absolut di Excel, lihat tutorial ini: Excel absolute reference (cara membuat dan menggunakan).

Kesalahan #VALUE dikembalikan

Kondisi berikut dapat menyebabkan VLOOKUP mengembalikan hasil kesalahan #VALUE.

Alasan 1: Nilai pencarian melebihi 255 karakter

Seperti yang ditunjukkan pada tangkapan layar di bawah, nilai pencarian di sel H4 melebihi 255 karakter, sehingga VLOOKUP mengembalikan hasil kesalahan #VALUE.

Solusi

Untuk mengatasi batasan ini, Anda dapat menerapkan fungsi pencarian berbeda yang dapat menangani string yang lebih panjang. Cobalah salah satu rumus berikut.

• INDEKS dan PERTANDINGAN:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• Fungsi XLOOKUP (tersedia di Excel 365, Excel 2021 dan versi yang lebih baru):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

Alasan 2: Argumen col_index kurang dari 1

Indeks kolom menentukan nomor kolom dalam larik tabel yang berisi nilai yang ingin Anda kembalikan. Argumen ini harus berupa angka positif yang sesuai dengan kolom yang valid dalam larik tabel.

Jika Anda memasukkan indeks kolom yang kurang dari 1 (yaitu, nol atau negatif), VLOOKUP tidak akan dapat menemukan kolom dalam larik tabel.

Solusi

Untuk memperbaiki masalah ini, pastikan argumen indeks kolom dalam rumus VLOOKUP Anda adalah angka positif yang sesuai dengan kolom yang valid dalam larik tabel.

Kesalahan #REF dikembalikan

Bagian ini mencantumkan satu alasan mengapa VLOOKUP mengembalikan kesalahan #REF dan memberikan solusi untuk masalah ini.

Alasan: Argumen col_index lebih besar dari jumlah kolom

Seperti yang Anda lihat pada tangkapan layar di bawah, susunan tabel hanya memiliki 4 kolom. Namun, indeks kolom yang Anda tentukan dalam rumus VLOOKUP adalah 5, yang lebih besar dari jumlah kolom dalam larik tabel. Akibatnya, VLOOKUP tidak akan dapat menemukan kolom dan pada akhirnya akan mengembalikan kesalahan #REF.

Solusi

• Tentukan nomor kolom yang benar
  Pastikan argumen indeks kolom dalam rumus VLOOKUP Anda adalah angka yang sesuai dengan kolom yang valid dalam larik tabel.
• Dapatkan nomor kolom secara otomatis berdasarkan tajuk kolom yang ditentukan
  Jika tabel berisi banyak kolom, Anda mungkin kesulitan menentukan nomor indeks kolom yang benar. Di sini, Anda dapat menyusun fungsi MATCH di fungsi VLOOKUP untuk menemukan posisi kolom berdasarkan header kolom tertentu.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  Note: Dalam rumus di atas, the COCOK("Email",B5:E5, 0) fungsi digunakan untuk mendapatkan nomor kolom dari "Email" di rentang tanggal B6:E12. Di sini hasilnya adalah 4, yang digunakan sebagai col_index di fungsi VLOOKUP.

Nilai yang salah dikembalikan

Jika Anda menemukan bahwa VLOOKUP tidak mengembalikan hasil yang benar, mungkin disebabkan oleh alasan berikut

Alasan 1: Kolom pencarian tidak diurutkan dalam urutan menaik

Jika Anda telah menetapkan argumen terakhir ke BENAR (Atau dibiarkan kosong) untuk pencocokan perkiraan, dan kolom pencarian tidak diurutkan dalam urutan menaik, nilai yang dihasilkan mungkin salah.

Solusi

Mengurutkan kolom pencarian dalam urutan menaik dapat membantu Anda memecahkan masalah ini. Untuk melakukannya, ikuti langkah-langkah di bawah ini:

1. Pilih sel data di kolom pencarian, buka Data tab, klik Urutkan dari yang terkecil ke terbesar dalam Sortir & Filter kelompok.
2. Dalam majalah Sortir Peringatan kotak dialog, pilih Perluas seleksi opsi, dan klik OK.

Alasan 2: Kolom disisipkan atau dihapus

Seperti yang ditunjukkan pada tangkapan layar di bawah, nilai yang awalnya ingin saya kembalikan ada di kolom keempat dari larik tabel, jadi saya menentukan nomor col_index sebagai 4. Saat kolom baru dimasukkan, kolom hasil menjadi kolom kelima dari tabel array, menyebabkan VLOOKUP mengembalikan hasil dari kolom yang salah.

Solusi

Berikut adalah dua solusi untuk Anda.

• Anda dapat mengubah nomor indeks kolom secara manual agar sesuai dengan posisi kolom pengembalian. Rumus di sini harus diubah menjadi:

  =VLOOKUP(H6,B6:F12,5,FALSE)

• Jika Anda selalu ingin mengembalikan hasil dari kolom certian, seperti kolom Email pada contoh ini. Rumus berikut dapat membantu mencocokkan indeks kolom secara otomatis berdasarkan tajuk kolom yang diberikan, terlepas dari apakah kolom disisipkan atau dihapus dari larik tabel.

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Catatan fungsi lainnya

• VLOOKUP hanya mencari nilai dari kiri ke kanan.
  Nilai pencarian ada di kolom paling kiri, dan nilai hasil harus ada di kolom mana pun di sebelah kanan kolom pencarian.
• Jika Anda membiarkan argumen terakhir kosong, VLOOKUP menggunakan pencocokan perkiraan secara default.
• VLOOKUP melakukan pencarian case-sensitive.
• Untuk beberapa kecocokan, VLOOKUP hanya mengembalikan kecocokan pertama yang ditemukannya dalam larik tabel, berdasarkan urutan baris dalam larik tabel.

Artikel terkait