Dengan menggabungkan fungsi VLOOKUP Excel dengan fungsi COLUMN, Anda dapat membuat rumus pencarian yang mengembalikan beberapa nilai dari satu baris database atau tabel data. Pelajari cara membuat rumus pencarian yang mengembalikan beberapa nilai dari satu rekaman data.
Petunjuk dalam artikel ini berlaku untuk Excel 2019, 2016, 2013, 2010; dan Excel untuk Microsoft 365.
Intisari
Rumus pencarian membutuhkan fungsi COLUMN untuk bersarang di dalam VLOOKUP. Nesting fungsi melibatkan memasukkan fungsi kedua sebagai salah satu argumen untuk fungsi pertama.
Masukkan Data Tutorial
Dalam tutorial ini, fungsi COLUMN 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.
Langkah pertama dalam tutorial ini adalah memasukkan data ke dalam lembar kerja Excel. Untuk mengikuti langkah-langkah dalam tutorial ini, masukkan data yang ditunjukkan pada gambar di bawah ini ke dalam sel berikut:
- Masukkan rentang data teratas ke dalam sel D1 hingga G1.
- Masukkan rentang kedua ke dalam sel D4 hingga G10.
Kriteria pencarian dan rumus pencarian yang dibuat dalam tutorial ini dimasukkan pada baris 2 lembar kerja.
Tutorial ini tidak menyertakan pemformatan Excel dasar yang ditunjukkan pada gambar, tetapi ini tidak memengaruhi cara kerja rumus pencarian.
Buat Rentang Bernama untuk Tabel Data
Rentang bernama adalah cara mudah untuk merujuk ke rentang data dalam rumus. Daripada mengetik referensi sel untuk data, ketik nama rentang.
Keuntungan kedua menggunakan rentang bernama adalah bahwa referensi sel untuk rentang ini tidak pernah berubah bahkan ketika rumus disalin ke sel lain di lembar kerja. Nama rentang adalah alternatif untuk menggunakan referensi sel absolut untuk mencegah kesalahan saat menyalin rumus.
Nama rentang tidak menyertakan judul atau nama bidang untuk data (seperti yang ditunjukkan pada baris 4), hanya datanya.
-
Sorot sel D5 ke G10 di lembar kerja.
Image -
Letakkan kursor pada Name Box yang terletak di atas kolom A, ketik Table, lalu tekan Enter. Sel D5 hingga G10 memiliki nama rentang Tabel.
Image - Nama rentang untuk argumen array tabel VLOOKUP digunakan nanti dalam tutorial ini.
Buka Kotak Dialog VLOOKUP
Meskipun mungkin untuk mengetikkan rumus pencarian langsung ke dalam sel di lembar kerja, banyak orang merasa sulit untuk menjaga sintaks tetap lurus - terutama untuk rumus kompleks seperti yang digunakan dalam tutorial ini.
Sebagai alternatif, gunakan kotak dialog Argumen Fungsi VLOOKUP. Hampir semua fungsi Excel memiliki kotak dialog di mana setiap argumen fungsi dimasukkan pada baris terpisah.
-
Pilih sel E2 dari lembar kerja. Ini adalah lokasi dimana hasil rumus pencarian dua dimensi akan ditampilkan.
Image -
Pada pita, buka tab Rumus dan pilih Pencarian & Referensi.
Image -
Pilih VLOOKUP untuk membuka kotak dialog Function Arguments.
Image - Kotak dialog Function Arguments adalah tempat parameter fungsi VLOOKUP dimasukkan.
Masukkan Argumen Nilai Pencarian
Biasanya, nilai pencarian cocok dengan bidang data di kolom pertama tabel data. Dalam contoh ini, nilai pencarian mengacu pada nama bagian yang ingin Anda cari informasinya. Tipe data yang diperbolehkan untuk nilai pencarian adalah data teks, nilai logika, angka, dan referensi sel.
Referensi Sel Absolut
Saat rumus disalin di Excel, referensi sel berubah untuk mencerminkan lokasi baru. Jika ini terjadi, D2, referensi sel untuk nilai pencarian, berubah dan membuat kesalahan di sel F2 dan G2.
Referensi sel absolut tidak berubah saat rumus disalin.
Untuk mencegah kesalahan, ubah referensi sel D2 menjadi referensi sel absolut. Untuk membuat referensi sel absolut, tekan tombol F4. Ini menambahkan tanda dolar di sekitar referensi sel seperti $D$2.
-
Dalam kotak dialog Argumen Fungsi, tempatkan kursor di kotak teks lookup_value. Kemudian, di lembar kerja, pilih cell D2 untuk menambahkan referensi sel ini ke lookup_value. Sel D2 adalah tempat nama bagian akan dimasukkan.
Image -
Tanpa memindahkan titik penyisipan, tekan tombol F4 untuk mengonversi D2 ke referensi sel absolut $D$2.
Image - Biarkan kotak dialog fungsi VLOOKUP terbuka untuk langkah berikutnya dalam tutorial.
Masukkan Argumen Array Tabel
Array tabel adalah tabel data yang dicari oleh rumus pencarian untuk menemukan informasi yang Anda inginkan. Array tabel harus berisi setidaknya dua kolom data.
Kolom pertama berisi argumen nilai pencarian (yang telah diatur di bagian sebelumnya), sedangkan kolom kedua dicari dengan rumus pencarian untuk menemukan informasi yang Anda tentukan.
Argumen larik tabel harus dimasukkan sebagai rentang yang berisi referensi sel untuk tabel data atau sebagai nama rentang.
Untuk menambahkan tabel data ke fungsi VLOOKUP, letakkan kursor di kotak teks table_array di kotak dialog dan ketik Tableuntuk memasukkan nama rentang untuk argumen ini.
Sarang Fungsi KOLOM
Biasanya, VLOOKUP hanya mengembalikan data dari satu kolom tabel data. Kolom ini diatur oleh argumen nomor indeks kolom. Namun, dalam contoh ini, ada tiga kolom, dan nomor indeks kolom perlu diubah tanpa mengedit rumus pencarian. Untuk melakukannya, susun fungsi COLUMN di dalam fungsi VLOOKUP sebagai argumen Col_index_num.
Saat menyusun fungsi, Excel tidak membuka kotak dialog fungsi kedua untuk memasukkan argumennya. Fungsi COLUMN harus dimasukkan secara manual. Fungsi COLUMN hanya memiliki satu argumen, argumen Referensi, yang merupakan referensi sel.
Fungsi COLUMN mengembalikan nomor kolom yang disediakan sebagai argumen Referensi. Ini mengubah huruf kolom menjadi angka.
Untuk mencari harga suatu barang, gunakan data di kolom 2 tabel data. Contoh ini menggunakan kolom B sebagai Referensi untuk memasukkan 2 ke dalam argumen Col_index_num.
-
Dalam kotak dialog Function Arguments, letakkan kursor di kotak teks Col_index_num dan ketik COLUMN(. (Pastikan untuk menyertakan braket bulat terbuka.)
Image -
Dalam lembar kerja, pilih sel B1 untuk memasukkan referensi sel tersebut sebagai argumen Referensi.
Image - Ketik kurung bulat penutup untuk menyelesaikan fungsi KOLOM.
Masukkan Argumen Pencarian Rentang VLOOKUP
Argumen Range_lookup VLOOKUP adalah nilai logis (TRUE atau FALSE) yang menunjukkan apakah VLOOKUP harus menemukan kecocokan yang tepat atau mendekati dengan nilai Lookup_value.
- TRUE atau Dihilangkan: VLOOKUP mengembalikan kecocokan yang mendekati dengan Nilai_Pencarian. Jika kecocokan persis tidak ditemukan, VLOOKUP mengembalikan nilai terbesar berikutnya. Data pada kolom pertama Table_array harus diurutkan secara ascending.
- FALSE: VLOOKUP menggunakan kecocokan persis dengan Nilai_Pencarian. Jika ada dua atau lebih nilai di kolom pertama Table_array yang cocok dengan nilai pencarian, nilai pertama yang ditemukan digunakan. Jika kecocokan persis tidak ditemukan, kesalahan N/A dikembalikan.
Dalam tutorial ini, informasi spesifik tentang item perangkat keras tertentu akan dicari, sehingga Range_lookup diatur ke FALSE.
Dalam kotak dialog Function Arguments, tempatkan kursor di kotak teks Range_lookup dan ketik False untuk memberi tahu VLOOKUP agar mengembalikan data yang sama persis.
Pilih OK untuk menyelesaikan rumus pencarian dan menutup kotak dialog. Sel E2 akan berisi kesalahan N/A karena kriteria pencarian belum dimasukkan ke dalam sel D2. Kesalahan ini bersifat sementara. Ini akan diperbaiki ketika kriteria pencarian ditambahkan pada langkah terakhir dari tutorial ini.
Salin Rumus Pencarian dan Masukkan Kriteria
Rumus pencarian mengambil data dari beberapa kolom tabel data sekaligus. Untuk melakukan ini, rumus pencarian harus berada di semua bidang yang Anda inginkan informasinya.
Untuk mengambil data dari kolom 2, 3, dan 4 dari tabel data (harga, nomor bagian, dan nama pemasok), masukkan sebagian nama sebagai Nilai_Pencarian.
Karena data diletakkan dalam pola reguler di lembar kerja, salin rumus pencarian di sel E2 ke sel F2 dan G2 Saat rumus disalin, Excel memperbarui referensi sel relatif dalam fungsi COLUMN (sel B1) untuk mencerminkan lokasi baru rumus. Excel tidak mengubah referensi sel absolut (seperti $D$2) dan rentang bernama (Tabel) saat rumus disalin.
Ada lebih dari satu cara untuk menyalin data di Excel, tetapi cara termudah adalah menggunakan Fill Handle.
-
Pilih cell E2, tempat rumus pencarian berada, untuk menjadikannya sel aktif.
Image -
Seret gagang isian ke sel G2. Sel F2 dan G2 menampilkan kesalahan N/A yang ada di sel E2.
Image -
Untuk menggunakan rumus pencarian untuk mengambil informasi dari tabel data, di lembar kerja pilih sel D2, ketik Widget, dan tekan Masuk.
Image Informasi berikut ditampilkan di sel E2 hingga G2.
- E2: $14.76 - harga widget
- F2: PN-98769 - nomor bagian untuk widget
- G2: Widgets Inc. - nama pemasok widget
-
Untuk menguji rumus array VLOOKUP, ketikkan nama bagian lain ke dalam sel D2 dan amati hasilnya di sel E2 hingga G2.
Image - Setiap sel yang berisi rumus pencarian berisi bagian data yang berbeda tentang item perangkat keras yang Anda cari.
Fungsi VLOOKUP dengan fungsi bersarang seperti COLUMN menyediakan metode yang kuat untuk mencari data di dalam tabel, menggunakan data lain sebagai referensi pencarian.
