Fungsi VLOOKUP Excel digunakan untuk mencari dan mengembalikan informasi dari tabel data berdasarkan nilai pencarian yang Anda pilih.
Biasanya, VLOOKUP membutuhkan nilai pencarian berada di kolom paling kiri dari tabel data, dan fungsi mengembalikan bidang data lain yang terletak di baris yang sama di sebelah kanan nilai ini.
Namun, dengan menggabungkan VLOOKUP dengan fungsi MEMILIH, rumus pencarian kiri dapat dibuat bahwa:
- Mengizinkan nilai pencarian berasal dari kolom apa pun di tabel data
- Mengembalikan informasi yang berada di kolom mana pun di sebelah kiri nilai pencarian
Catatan: Instruksi ini berlaku untuk versi Excel 2019, 2016, 2013, 2010, dan Excel untuk Office 365.
01 05Tutorial: Menggunakan Fungsi VLOOKUP dan PILIHAN dalam Formula Pencarian Kiri
Untuk membuat rumus pencarian kiri yang terlihat pada gambar contoh, gunakan rumus:
= VLOOKUP ($ D $ 2, MEMILIH ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
Dalam contoh ini, rumus memungkinkan untuk menemukan bagian yang disediakan oleh perusahaan yang berbeda yang tercantum dalam kolom 3 dari tabel data.
Tugas fungsi PILIHAN dalam rumus adalah untuk mengelabui VLOOKUP agar percaya bahwa kolom 3 adalah kolom 1. Akibatnya, nama perusahaan dapat digunakan sebagai nilai pencarian untuk menemukan nama bagian yang disediakan oleh masing-masing perusahaan.
Masukkan Data Tutorial
- Masukkan judul Pemasok ke dalam sel D1.
- Masukkan judul Bagian dalam sel E1.
- Masukkan tabel data yang terlihat pada gambar di atas ke dalam sel D4 hingga F9
- Baris 2 dan 3 dibiarkan kosong untuk mengakomodasi kriteria pencarian dan formula pencarian kiri yang dibuat selama tutorial ini
Buka Kotak Dialog VLOOKUP
Meskipun dimungkinkan untuk mengetik formula langsung ke sel F1 di lembar kerja, banyak orang mengalami kesulitan dengan sintaks rumus.
Dalam hal ini, lebih aman untuk menggunakan kotak dialog VLOOKUP. Hampir semua fungsi Excel memiliki kotak dialog yang memungkinkan Anda untuk memasukkan masing-masing argumen fungsi pada baris terpisah.
- Klik pada sel E2 dari lembar kerja. E2 adalah lokasi di mana hasil dari formula pencarian kiri akan ditampilkan.
- Klik pada Rumus tab pita.
- Klik pada Lookup & Referensi pilihan dalam pita untuk membuka daftar drop-down fungsi.
- Klik VLOOKUP dalam daftar untuk memunculkan kotak dialog fungsi.
Memasukkan Argumen ke dalam Kotak Dialog VLOOKUP
Argumen fungsi adalah nilai yang digunakan oleh fungsi untuk menghitung hasil.
Dalam kotak dialog fungsi, nama setiap argumen berada pada baris terpisah yang diikuti oleh bidang untuk memasukkan nilai.
Masukkan nilai berikut untuk setiap argumen VLOOKUP pada baris yang benar dari kotak dialog seperti yang ditunjukkan pada gambar terlampir.
Nilai Pencarian
Nilai pencarian adalah bidang informasi yang digunakan untuk mencari susunan tabel. VLOOKUP mengembalikan bidang lain data dari baris yang sama dengan nilai pencarian.
Contoh ini menggunakan referensi sel ke lokasi tempat nama perusahaan akan dimasukkan ke dalam lembar kerja. Keuntungan dari ini adalah memudahkan untuk mengubah nama perusahaan tanpa mengedit formula.
- Klik pada nilai lookup baris di kotak dialog.
- Klik pada sel D2 untuk menambahkan referensi sel ini ke nilai lookup baris.
- tekan F4 kunci pada keyboard untuk membuat referensi sel mutlak - $ D $ 2.
Referensi Sel Absolut
Referensi sel absolut digunakan untuk argumen lookup value dan table array untuk mencegah kesalahan jika formula pencarian disalin ke sel lain di lembar kerja.
Memasuki Fungsi PILIHAN
Argumen table array adalah blok data yang bersebelahan dari mana informasi tertentu diambil.
Biasanya, VLOOKUP hanya melihat ke kanan dari argumen nilai pencarian untuk menemukan data dalam larik tabel. Agar terlihat kiri, VLOOKUP harus diakali dengan menata ulang kolom dalam larik tabel menggunakan fungsi PILIHAN.
Dalam rumus ini, fungsi PILIHAN menyelesaikan dua tugas:
- Ini menciptakan array tabel yang hanya dua kolom lebar (kolom D dan F).
- Ini mengubah urutan kanan-ke-kiri dari kolom dalam array tabel sehingga kolom F datang pertama dan kolom D adalah detik.
Memasuki Fungsi
Ketika memasukkan fungsi secara manual, masing-masing argumen fungsi harus dipisahkan oleh koma.
- Dalam kotak dialog fungsi VLOOKUP, klik pada Table_array baris.
- Masukkan berikut MEMILIH fungsi:PILIH ({1,2}, $ F: $ F, $ D: $ D)
Nomor Indeks Kolom
Biasanya, nomor indeks kolom menunjukkan kolom mana dari larik tabel berisi data yang Anda cari. Namun, dalam rumus ini, ini mengacu pada urutan kolom yang ditetapkan oleh fungsi MEMILIH.
Fungsi CHOOSE menciptakan sebuah array tabel yang memiliki lebar dua kolom dengan kolom F pertama diikuti oleh kolom D. Karena informasi yang dicari - nama bagian - berada di kolom D, nilai argumen indeks kolom harus diatur ke 2.
- Klik pada Col_index_num baris di kotak dialog.
- Ketik a 2 di baris ini.
Pencarian Jarak
Argumen Range_lookup VLOOKUP adalah nilai logis (hanya TRUE atau FALSE) yang menunjukkan apakah Anda ingin VLOOKUP untuk menemukan pencocokan tepat atau perkiraan untuk nilai pencarian.
- Jika TRUE atau jika argumen ini dihilangkan, VLOOKUP mengembalikan kecocokan tepat ke Lookup_value atau, jika pencocokan tepat tidak ditemukan, VLOOKUP mengembalikan nilai terbesar berikutnya. Untuk rumus untuk melakukan ini, data di kolom pertama Table_array harus disortir dalam urutan menaik.
- Jika FALSE, VLOOKUP hanya menggunakan pencocokan tepat ke Lookup_value. Jika ada dua atau lebih nilai di kolom pertama Table_array yang cocok dengan nilai pencarian, nilai pertama yang ditemukan digunakan. Jika pencocokan tepat tidak ditemukan, kesalahan # N / A dikembalikan.
Dalam tutorial ini, karena kami mencari nama bagian tertentu, Range_lookup diatur ke Salah sehingga hanya pertandingan yang tepat yang dikembalikan oleh rumus.
- Klik pada Range_lookup baris di kotak dialog.
- Ketik kata Salah di baris ini untuk menunjukkan bahwa kami ingin VLOOKUP untuk mengembalikan kecocokan tepat untuk data yang kami cari.
- Klik baik untuk melengkapi rumus pencarian kiri dan kotak dialog dekat.
- Karena kita belum memasukkan nama perusahaan ke dalam sel D2, kesalahan # N / A muncul di sel E2.
Mengembalikan Data Dengan Formula Pencarian Kiri
Untuk menemukan perusahaan mana yang memasok bagian-bagian mana, ketik nama perusahaan ke dalam sel D2 dan tekan tombol MEMASUKKAN kunci pada keyboard.
Nama bagian ditampilkan dalam sel E2.
- Klik pada sel D2 di lembar kerja Anda.
- Mengetik Gadget Plus ke dalam sel D2 dan tekan tombol MEMASUKKAN kunci pada keyboard.
- Teks "Gadget" - bagian yang disediakan oleh perusahaan Gadget Plus - harus ditampilkan di sel E2.
Uji rumus pencarian lebih lanjut dengan mengetikkan nama perusahaan lain ke dalam sel D2, dan nama bagian yang sesuai akan muncul di sel E2.
Jika pesan kesalahan seperti #N / A muncul di sel E2, periksa kesalahan ejaan di sel D2.
04 dari 05Membuat Array Tabel Dua Kolom
Sintaks untuk fungsi PILIHAN adalah:
= PILIH (Indeks_Jumlah, Nilai1, Nilai2, … Nilai254)
Fungsi CHOOSE biasanya mengembalikan satu nilai dari daftar nilai (Nilai1 ke Value254) berdasarkan nomor indeks yang dimasukkan.
Jika nomor indeks adalah 1, fungsi mengembalikan nilai1 dari daftar; jika nomor indeks adalah 2, fungsi mengembalikan Value2 dari daftar dan seterusnya.
Ketika beberapa nomor indeks dimasukkan, fungsi mengembalikan beberapa nilai dalam urutan yang diinginkan. MEMILIH untuk mengembalikan beberapa nilai dilakukan dengan membuat larik.
Untuk memasukkan larik, letakkan angka-angka yang dimasukkan dengan tanda kurung kurawal. Dua angka dimasukkan untuk nomor indeks:{ 1,2 }.
Perlu dicatat bahwa MEMILIH tidak terbatas untuk membuat tabel dua kolom. Dengan memasukkan nomor tambahan dalam larik - seperti {1,2,3} - dan rentang tambahan dalam argumen nilai, PILIH menciptakan tabel tiga kolom.
Kolom tambahan memungkinkan Anda untuk mengembalikan informasi yang berbeda dengan rumus pencarian kiri dengan mengubah argumen nomor indeks kolom VLOOKUP ke nomor kolom yang berisi informasi yang diinginkan.
05 dari 05Mengubah Urutan Kolom Dengan Fungsi PILIH
Dalam fungsi PILIH digunakan dalam rumus ini:
PILIH ({1,2}, $ F: $ F, $ D: $ D)
kisaran untuk kolom F tercantum sebelum kolom D.
Karena fungsi CHOOSE mengatur larik tabel VLOOKUP (sumber data untuk fungsi itu) mengalihkan urutan kolom dalam fungsi PILIHAN diteruskan ke VLOOKUP.
Sejauh menyangkut VLOOKUP, array tabel hanya dua kolom lebar dengan kolom F di sebelah kiri dan kolom D di sebelah kanan. Karena kolom F berisi nama perusahaan yang ingin kita cari, dan karena kolom D berisi nama bagian, VLOOKUP dapat melakukan tugas pencarian regulernya dalam mencari data yang terletak di sebelah kiri nilai pencarian.
Akibatnya, VLOOKUP dapat menggunakan nama perusahaan untuk menemukan bagian yang mereka berikan.
