Cara menggunakan Fungsi VLOOKUP Excel

Meskipun tidak selalu diperlukan, ini adalah cara terbaik untuk terlebih dahulu mengurutkan rentang data yang VLOOKUP sedang mencari dalam urutan menaik menggunakan kolom pertama rentang untuk kunci pengurutan.

Jika data tidak diurutkan, VLOOKUP mungkin mengembalikan hasil yang salah.

Pertandingan Tepat vs. Perkiraan

VLOOKUP dapat diatur sehingga hanya mengembalikan informasi yang sama persis dengan Nilai lookup atau dapat diatur untuk mengembalikan pertandingan perkiraan

Faktor yang menentukan adalah Range_lookup argumen:

• set ke FALSE itu hanya mengembalikan informasi yang berkaitan dengan pencocokan tepat ke Nilai lookup
• diatur ke TRUE atau dihilangkan mengembalikan informasi yang tepat atau perkiraan terkait dengan Nilai lookup

Dalam contoh di atas, Range_lookup diatur ke FALSE jadi VLOOKUP harus menemukan yang sama persis untuk istilah tersebut Widget dalam urutan tabel data untuk mengembalikan harga satuan untuk item itu. Jika pencocokan tepat tidak ditemukan, kesalahan # N / A dikembalikan oleh fungsi.

Catatan: VLOOKUP tidak case sensitive - keduanya Widget dan widget ejaan yang dapat diterima untuk contoh di atas.

Jika ada beberapa nilai yang cocok - misalnya, Widget adalah terdaftar lebih dari sekali di kolom 1 dari tabel data - informasi yang terkait dengan nilai pencocokan pertama yang ditemui pergi dari atas ke bawah dikembalikan oleh fungsi.

Memasukkan Argumen Fungsi VLOOKUP Excel Menggunakan Menunjuk

Pada contoh gambar pertama di atas, rumus berikut yang berisi fungsi VLOOKUP digunakan untuk mencari harga unit Widget terletak di tabel data.

= VLOOKUP (A2, $ A $ 5: $ B $ 8,2, FALSE)

Meskipun rumus ini hanya dapat diketik ke dalam sel lembar kerja, opsi lain, seperti yang digunakan dengan langkah-langkah yang tercantum di bawah ini, adalah menggunakan kotak dialog fungsi, yang ditunjukkan di atas, untuk memasukkan argumennya.

• Menggunakan kotak dialog sering membuatnya lebih mudah untuk memasukkan argumen fungsi dengan benar dan menghilangkan kebutuhan untuk memasukkan koma pemisah antar argumen.

Langkah-langkah di bawah ini digunakan untuk memasukkan fungsi VLOOKUP ke sel B2 menggunakan kotak dialog fungsi.

Membuka Kotak Dialog VLOOKUP

1. Klik pada sel B2 untuk menjadikannya sel aktif - lokasi di mana hasil fungsi VLOOKUP ditampilkan
2. Klik pada Rumus tab.
3. Memilih Lookup & Referensi dari pita untuk membuka daftar tarik-turun fungsi
4. Klik VLOOKUP dalam daftar untuk memunculkan kotak dialog fungsi

Data yang dimasukkan ke dalam empat baris kosong dari kotak dialog membentuk argumen untuk fungsi VLOOKUP.

Menunjuk ke Referensi Sel

Argumen untuk fungsi VLOOKUP dimasukkan ke dalam baris terpisah dari kotak dialog seperti yang ditunjukkan pada gambar di atas.

Referensi sel yang akan digunakan sebagai argumen dapat diketik ke dalam baris yang benar, atau, seperti yang dilakukan dalam langkah-langkah di bawah ini, dengan titik dan klik - yang melibatkan sorot kisaran sel yang diinginkan dengan penunjuk tetikus - dapat digunakan untuk memasukkannya ke dalam kotak dialog.

Menggunakan Referensi Sel Relatif dan Absolut dengan Argumen

Tidak jarang menggunakan banyak salinan VLOOKUP untuk mengembalikan informasi yang berbeda dari tabel data yang sama.

Untuk mempermudah melakukan ini, sering VLOOKUP dapat disalin dari satu sel ke sel lainnya. Ketika fungsi disalin ke sel lain, perawatan harus dilakukan untuk memastikan bahwa referensi sel yang dihasilkan benar diberikan lokasi baru fungsi tersebut.

Pada gambar di atas, tanda dolar ( $ ) mengelilingi referensi sel untuk Table_array argumen yang menunjukkan bahwa mereka adalah referensi sel absolut, yang berarti mereka tidak akan berubah jika fungsi disalin ke sel lain.

Ini diinginkan karena banyak salinan VLOOKUP akan mereferensikan tabel data yang sama sebagai sumber informasi.

Referensi sel yang digunakan untuk lookup_value - A2 - di samping itu , tidak dikelilingi oleh tanda dolar, yang membuatnya menjadi referensi sel relatif. Referensi sel relatif berubah ketika mereka disalin untuk mencerminkan lokasi baru mereka relatif terhadap posisi data yang mereka rujuk.

Referensi sel relatif memungkinkan untuk mencari beberapa item dalam tabel data yang sama dengan menyalin VLOOKUP ke beberapa lokasi dan memasuki berbagai lookup_values .

Memasukkan Argumen Fungsi

1. Klik pada Nilai lookup baris dalam VLOOKUP kotak dialog
2. Klik pada sel A2 di lembar kerja untuk memasukkan referensi sel ini sebagai kunci_penelusuran argumen
3. Klik pada Table_array garis kotak dialog
4. Sel sorotan A5 ke B8 di lembar kerja untuk memasukkan rentang ini sebagai Table_array argumen - judul tabel tidak termasuk
5. tekan F4 kunci pada keyboard untuk mengubah rentang ke referensi sel absolut
6. Klik pada Col_index_num garis kotak dialog
7. Ketik a 2 pada baris ini sebagai Col_index_num argumen, karena tingkat diskonto terletak di kolom 2 dari Table_array argumen
8. Klik pada Range_lookup garis kotak dialog
9. Ketik kata Salah sebagai Range_lookup argumen
10. tekan Memasukkan kunci pada keyboard untuk menutup kotak dialog dan kembali ke lembar kerja
11. Jawaban $ 14,76 - harga unit untuk Widget - akan muncul di sel B2 dari lembar kerja
12. Ketika Anda mengklik sel B2, fungsi lengkap = VLOOKUP (A2, $ A $ 5: $ B $ 8,2, FALSE) muncul di bilah rumus di atas lembar kerja

Pesan Kesalahan VLOOKUP Excel

Pesan kesalahan berikut ini terkait dengan VLOOKUP:

Kesalahan # N / A ("value not available") ditampilkan jika:

• Itu Nilai lookup tidak ditemukan di kolom pertama jarak argumen
• Itu Table_array argumen tidak akurat. Misalnya, argumen mungkin menyertakan kolom kosong di sisi kiri rentang
• Itu Range_lookup argumen diatur ke FALSE dan pencocokan tepat untuk kunci_penelusuran Argumen tidak dapat ditemukan di kolom pertama jarak
• Itu Range_lookup argumen diatur ke TRUE dan semua nilai di kolom pertama jarak lebih besar dari kunci_penelusuran

A #REF! kesalahan ditampilkan jika:

• Itu Col_index_num argumen lebih besar dari jumlah kolom dalam susunan tabel.