Dengan menggabungkan fungsi VLOOKUP Excel dengan fungsi COLUMN kita dapat membuat rumus pencarian yang memungkinkan Anda untuk mengembalikan beberapa nilai dari satu baris database atau tabel data.
Dalam contoh yang ditunjukkan pada gambar di atas, rumus pencarian membuatnya mudah mengembalikan semua nilai - seperti harga, nomor komponen, dan pemasok - yang terkait dengan berbagai potongan perangkat keras.
01 dari 10
Return Multiple Values dengan Excel VLOOKUP
Mengikuti langkah-langkah yang tercantum di bawah ini membuat rumus pencarian yang terlihat pada gambar di atas yang akan mengembalikan beberapa nilai dari satu rekaman data.
Rumus pencarian mengharuskan fungsi COLUMN disarangkan di dalam VLOOKUP.
Bersarang fungsi melibatkan memasukkan fungsi kedua sebagai salah satu argumen untuk fungsi pertama.
Dalam tutorial ini, fungsi COLUMN akan dimasukkan sebagai argumen nomor indeks kolom untuk VLOOKUP.
Langkah terakhir dalam tutorial melibatkan menyalin rumus pencarian ke kolom tambahan untuk mengambil nilai tambahan untuk bagian yang dipilih.
Isi Tutorial
- Memasukkan Data Tutorial
- Membuat Rentang Bernama untuk Tabel Data
- Memulai Fungsi VLOOKUP
- Memasukkan Argumen Nilai Pencarian menggunakan Referensi Sel Absolut
- Memasukkan Argumen Larik Tabel
- Memasukkan Fungsi KOLOM Bersarang
- Melengkapi Fungsi VLOOKUP
- Menyalin Rumus Pencarian dengan Penanganan Isi
- Mengambil Data dengan Formula Pencarian
02 dari 10
Masukkan Data Tutorial
Langkah pertama dalam tutorial adalah memasukkan data ke dalam lembar kerja Excel.
Untuk mengikuti langkah-langkah dalam tutorial masukkan data yang ditunjukkan pada gambar di atas ke sel - sel berikut.
- Masukkan kisaran data teratas ke dalam sel D1 ke G1
- Masukkan rentang kedua ke dalam sel D4 ke G10
Kriteria pencarian dan formula pencarian yang dibuat selama tutorial ini akan dimasukkan ke baris 2 dari lembar kerja.
Tutorial tidak menyertakan pemformatan yang terlihat pada gambar, tetapi ini tidak akan memengaruhi cara kerja rumus pencarian.
Informasi tentang opsi pemformatan yang serupa dengan yang terlihat di atas tersedia dalam Tutorial Memformat Excel Dasar ini.
Langkah-langkah Tutorial
- Masukkan data seperti yang terlihat pada gambar di atas ke dalam sel D1 ke G10
03 dari 10
Membuat Rentang Bernama untuk Tabel Data
Rentang bernama adalah cara mudah untuk merujuk ke berbagai data dalam rumus. Daripada mengetikkan referensi sel untuk data, Anda cukup mengetikkan nama rentang.
Keuntungan kedua untuk menggunakan rentang bernama adalah bahwa referensi sel untuk rentang ini tidak pernah berubah bahkan ketika formula disalin ke sel lain di lembar kerja.
Karena itu, nama rentang merupakan alternatif untuk menggunakan referensi sel absolut untuk mencegah kesalahan saat menyalin rumus.
Catatan: Nama rentang tidak menyertakan judul atau nama bidang untuk data (baris 4) tetapi hanya data itu sendiri.
Langkah-langkah Tutorial
- Sel sorotan D5 ke G10 di lembar kerja untuk memilihnya
- Klik pada Kotak Nama yang terletak di atas kolom A
- Ketik "Tabel" (tanpa tanda kutip) di Kotak Nama
- Tekan tombol ENTER pada keyboard
- Sel D5 hingga G10 sekarang memiliki nama rentang "Tabel". Kami akan menggunakan nama untuk argumen array meja VLOOKUP nanti di tutorial
04 dari 10
Membuka Kotak Dialog VLOOKUP
Meskipun dimungkinkan untuk hanya mengetik rumus pencarian kami langsung ke sel di lembar kerja, banyak orang merasa sulit untuk menjaga sintaks lurus - terutama untuk rumus kompleks seperti yang kami gunakan dalam tutorial ini.
Alternatif, dalam hal ini, adalah menggunakan kotak dialog VLOOKUP. Hampir semua fungsi Excel memiliki kotak dialog yang memungkinkan Anda untuk memasukkan masing-masing argumen fungsi pada baris terpisah.
Langkah-langkah Tutorial
- Klik pada sel E2 dari lembar kerja - lokasi di mana hasil dari rumus pencarian dua dimensi akan ditampilkan
- Klik pada tab Rumus pada pita
- Klik opsi Pencarian & Referensi di pita untuk membuka daftar tarik-turun fungsi
- Klik VLOOKUP dalam daftar untuk membuka kotak dialog fungsi
05 dari 10
Memasukkan Argumen Nilai Pencarian menggunakan Referensi Sel Absolut
Biasanya, nilai pencarian cocok dengan bidang data di kolom pertama dari tabel data.
Dalam contoh kita, nilai pencarian mengacu pada nama bagian perangkat keras yang ingin kita cari informasinya.
Jenis data yang diijinkan untuk nilai pencarian adalah:
- data teks
- nilai logis (hanya TRUE atau FALSE)
- sebuah angka
- referensi sel ke nilai di lembar kerja
Dalam contoh ini, kita akan memasukkan referensi sel ke tempat nama bagian akan ditempatkan - sel D2.
Referensi Sel Absolut
Pada langkah selanjutnya dalam tutorial, kami akan menyalin rumus pencarian di sel E2 ke sel F2 dan G2.
Biasanya, ketika formula disalin di Excel, referensi sel berubah untuk mencerminkan lokasi baru mereka.
Jika ini terjadi, D2 - referensi sel untuk nilai pencarian - akan berubah ketika rumus disalin membuat kesalahan dalam sel F2 dan G2.
Untuk mencegah kesalahan, kami akan mengubah referensi sel D2 menjadi referensi sel absolut .
Referensi sel absolut tidak berubah ketika formula disalin.
Referensi sel absolut dibuat dengan menekan tombol F4 pada keyboard. Melakukannya menambahkan tanda dolar di sekitar referensi sel seperti $ D $ 2
Langkah-langkah Tutorial
- Klik pada baris lookup_value di kotak dialog
- Klik pada sel D2 untuk menambahkan referensi sel ini ke garis lookup_value . Ini adalah sel tempat kami akan mengetikkan nama bagian yang kami cari informasinya
- Tanpa memindahkan titik penyisipan, tekan tombol F4 pada keyboard untuk mengonversi D2 ke referensi sel absolut $ D $ 2
- Biarkan kotak dialog fungsi VLOOKUP terbuka untuk langkah berikutnya dalam tutorial
06 dari 10
Memasukkan Argumen Larik Tabel
Array tabel adalah tabel data yang dicari pencarian rumus untuk menemukan informasi yang kita inginkan.
Array tabel harus berisi setidaknya dua kolom data .
- kolom pertama berisi argumen nilai pencarian (langkah sebelumnya dalam tutorial)
- yang kedua, dan kolom tambahan apa pun, akan dicari oleh rumus pencarian untuk menemukan informasi yang kami tentukan.
Argumen larik tabel harus dimasukkan sebagai rentang yang berisi referensi sel untuk tabel data atau sebagai nama rentang .
Untuk contoh ini, kita akan menggunakan nama rentang yang dibuat di langkah 3 dari tutorial.
Langkah-langkah Tutorial
- Klik pada baris table_array di kotak dialog
- Ketik "Tabel" (tanpa tanda kutip) untuk memasukkan nama rentang untuk argumen ini
- Biarkan kotak dialog fungsi VLOOKUP terbuka untuk langkah berikutnya dalam tutorial
07 dari 10
Bersarang Fungsi KOLOM
Biasanya VLOOKUP hanya mengembalikan data dari satu kolom tabel data dan kolom ini diatur oleh argumen nomor indeks kolom .
Dalam contoh ini, kami memiliki tiga kolom yang ingin kami kembalikan datanya sehingga kami perlu cara mudah mengubah nomor indeks kolom tanpa mengedit rumus pencarian kami.
Di sinilah fungsi COLUMN masuk. Dengan memasukkannya sebagai argumen nomor indeks kolom , ini akan berubah ketika rumus pencarian disalin dari sel D2 ke sel E2 dan F2 nanti di tutorial.
Fungsi Bersarang
Fungsi COLUMN, oleh karena itu, bertindak sebagai argumen nomor indeks kolom VLOOKUP.
Ini dilakukan dengan menyarangkan fungsi COLUMN di dalam VLOOKUP di baris Col_index_num dari kotak dialog.
Memasukkan Fungsi KOLOM secara Manual
Ketika fungsi bertumpuk, Excel tidak mengizinkan kita untuk membuka kotak dialog fungsi kedua untuk memasukkan argumennya.
Fungsi COLUMN, oleh karena itu, harus dimasukkan secara manual di baris Col_index_num .
Fungsi COLUMN hanya memiliki satu argumen - argumen Referensi yang merupakan referensi sel.
Memilih Argumen Referensi Fungsi KOLOM
Tugas COLUMN berfungsi untuk mengembalikan jumlah kolom yang diberikan sebagai argumen Referensi .
Dengan kata lain, ia mengubah huruf kolom menjadi angka dengan kolom A menjadi kolom pertama, kolom B yang kedua dan seterusnya.
Karena bidang data pertama yang ingin kita kembalikan adalah harga barang - yang ada di kolom dua tabel data - kita dapat memilih referensi sel untuk sel mana pun di kolom B sebagai Argumen Referensi untuk mendapatkan angka 2 untuk argumen Col_index_num .
Langkah-langkah Tutorial
- Dalam kotak dialog fungsi VLOOKUP, klik pada baris Col_index_num
- Ketik kolom nama fungsi diikuti dengan braket bulat terbuka " ( "
- Klik sel B1 di lembar kerja untuk memasukkan referensi sel itu sebagai argumen Referensi
- Ketikkan tanda kurung tutup putaran " ) " untuk melengkapi fungsi COLUMN
- Biarkan kotak dialog fungsi VLOOKUP terbuka untuk langkah berikutnya dalam tutorial
08 dari 10
Memasukkan Argumen Pencarian Rentang VLOOKUP
Argumen Range_lookup VLOOKUP adalah nilai logis (hanya TRUE atau FALSE) yang menunjukkan apakah Anda ingin VLOOKUP untuk menemukan pencocokan tepat atau perkiraan untuk Lookup_value.
- 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 akan 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 informasi spesifik tentang item perangkat keras tertentu, kami akan menetapkan Range_lookup sama dengan False .
Langkah-langkah Tutorial
- Klik pada baris Range_lookup di kotak dialog
- Ketik kata Salah dalam baris ini untuk menunjukkan bahwa kami ingin VLOOKUP untuk mengembalikan kecocokan tepat untuk data yang kami cari
- Klik OK untuk melengkapi rumus pencarian dan tutup kotak dialog
- Karena kami belum memasukkan kriteria pencarian ke dalam sel D2, kesalahan # N / A akan ada di sel E2
- Kesalahan ini akan diperbaiki ketika kami akan menambahkan kriteria pencarian di langkah terakhir dari tutorial
09 dari 10
Menyalin Rumus Pencarian dengan Penanganan Isi
Rumus pencarian dimaksudkan untuk mengambil data dari beberapa kolom dari tabel data pada satu waktu.
Untuk melakukan ini, rumus pencarian harus berada di semua bidang dari mana kita menginginkan informasi.
Dalam tutorial ini kita ingin mengambil data dari kolom 2, 3, dan 4 dari tabel data - yaitu harga, nomor bagian, dan nama pemasok ketika kita memasukkan nama bagian sebagai Lookup_value.
Karena data diletakkan dalam pola biasa di lembar kerja , kita dapat menyalin rumus pencarian di sel E2 ke sel F2 dan G2.
Saat formula disalin, Excel akan memperbarui referensi sel relatif dalam fungsi COLUMN (B1) untuk mencerminkan lokasi baru rumus.
Selain itu, Excel tidak mengubah referensi sel absolut $ D $ 2 dan Tabel rentang bernama saat rumus disalin.
Ada lebih dari satu cara untuk menyalin data di Excel, tetapi mungkin cara termudah adalah dengan menggunakan Fill Handle .
Langkah-langkah Tutorial
- Klik pada sel E2 - di mana rumus pencarian berada - untuk menjadikannya sel aktif
- Tempatkan pointer mouse di atas kotak hitam di sudut kanan bawah. Penunjuk akan berubah menjadi tanda tambah " + " - ini adalah pegangan isian
- Klik tombol kiri mouse dan seret gagang isian ke G2 sel
- Lepaskan tombol mouse dan sel F3 harus berisi rumus pencarian dua dimensi
- Jika dilakukan dengan benar, sel F2 dan G2 seharusnya sekarang juga mengandung kesalahan # N / A yang ada di sel E2
10 dari 10
Memasukkan Kriteria Pencarian
Setelah formula pencarian telah disalin ke sel yang diperlukan, ia dapat digunakan untuk mengambil informasi dari tabel data.
Untuk melakukannya, ketikkan nama item yang ingin Anda ambil ke dalam Lookup_value cell (D2) dan tekan tombol ENTER pada keyboard.
Setelah selesai, setiap sel yang berisi rumus pencarian harus berisi bagian data yang berbeda tentang item perangkat keras yang Anda cari.
Langkah-langkah Tutorial
- Klik pada sel D2 di lembar kerja
- Ketik Widget ke sel D2 dan tekan tombol ENTER pada keyboard
- Informasi berikut ini harus ditampilkan dalam sel E2 ke G2:
- E2 - $ 14,76 - harga sebuah widget
- F2 - PN-98769 - nomor bagian untuk widget
- G2 - Widgets Inc. - nama pemasok untuk widget
- Uji rumus array VLOOKUP lebih lanjut dengan mengetikkan nama bagian lain ke dalam sel D2 dan mengamati hasilnya di sel E2 hingga G2
Jika pesan kesalahan seperti #REF! muncul di sel E2, F2, atau G2, daftar pesan kesalahan VLOOKUP ini dapat membantu Anda menentukan di mana letak masalahnya.