Cara Melakukan VLOOKUP() dengan Banyak Kriteria

VLOOKUP() adalah salah satu fungsi Excel yang paling banyak digunakan. Fungsi ini memungkinkan Anda mencari dan mengambil data dari kolom tertentu dalam sebuah tabel. Selain itu, fungsi ini membantu menggabungkan data dari lembar yang berbeda atau menemukan informasi terkait.

Namun tahukah Anda bahwa fungsionalitas VLOOKUP() dapat diperluas untuk memasukkan banyak kriteria? Ini akan membantu pengguna yang sering perlu mencocokkan beberapa kondisi saat mencari data, yang merupakan masalah umum ketika bekerja dengan spreadsheet.

Dalam artikel ini, kita akan melihat bagaimana Anda dapat menggunakan VLOOKUP() dengan banyak kriteria untuk meningkatkan pekerjaan Anda. Jika Anda baru mengenal Excel, lihat terlebih dahulu kursus Pengenalan Excel kami untuk mempelajari seluk-beluk mengelola tabel dan menerapkan perhitungan.

Dasar-dasar VLOOKUP()

Sebelum kita masuk ke VLOOKUP() dengan banyak kriteria, mari kita tinjau dulu sintaks dasar VLOOKUP() berikut ini:

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

Pada rumus di atas:

• lookup_value adalah nilai yang ingin Anda cari. Ini bisa berupa angka, teks, atau referensi ke sebuah sel yang berisi nilai pencarian.

• table_array adalah rentang sel yang berisi data. Kolom pertama dalam rentang ini harus berisi kunci pencarian.

• col_index_num adalah nomor kolom dalam rentang dari mana Anda ingin mengambil nilai.

• range_lookup adalah tempat Anda memasukkan nilai logis (TRUE atau FALSE). Jika TRUE, VLOOKUP() mengasumsikan kolom pertama dari rentang diurutkan menaik dan mengembalikan kecocokan terdekat.

Mari pahami dengan contoh. Di sini, saya memiliki lembar penuh data karyawan, dan saya ingin dengan cepat menemukan nama karyawan tertentu berdasarkan nomor ID mereka. Untuk melakukannya, saya akan menggunakan VLOOKUP().

Tabel yang berisi detail karyawan. Sumber: Gambar oleh Penulis.

Pertama, saya memasukkan nilai di sel F6 yang cocok dengan nilai di tabel. Dalam kasus saya, saya melihat kolom referensi EMP ID ketika saya memilih 4032.

Memasukkan nilai VLOOKUP() di sebuah sel. Sumber: Gambar oleh Penulis.

Lalu, saya memilih sel G6 dan mulai mengetik rumus saya.

Memasukkan rumus VLOOKUP() di sebuah sel. Sumber: Gambar oleh Penulis.

Berikutnya, saya memilih sel tempat saya memasukkan 4032. Ini adalah nilai yang saya cari dari tabel.

Memasukkan nomor sel tempat nilai VLOOKUP() disimpan. Sumber: Gambar oleh Penulis.

Kemudian, saya memilih rentang tabel (A2:D11).

Memilih rentang tabel yang berisi data. Sumber: Gambar oleh Penulis.

Menghitung dari kiri, saya memilih kolom nomor 2 karena saya mencari FIRST NAME dan FIRST NAME adalah kolom kedua dari kiri.

Memasukkan nomor kolom yang Anda inginkan hasilnya. Sumber: Gambar oleh Penulis.

Setelah mengetik FALSE untuk kecocokan tepat, saya menekan Enter. Anda dapat melihat bagaimana fungsi VLOOKUP() menemukan FIRST NAME karyawan dengan EMP ID sama dengan 4032.

Mengambil output yang diinginkan. Sumber: Gambar oleh Penulis.

Cara Menggunakan VLOOKUP() dengan Banyak Kriteria

VLOOKUP() dalam bentuk dasarnya bekerja baik untuk pencarian sederhana, tetapi bisa menjadi menantang ketika Anda menggabungkan banyak kriteria. Namun Anda dapat mengatasi keterbatasan ini dengan beberapa teknik. Mari pahami bagaimana caranya.

VLOOKUP() dengan kolom bantu

Jika Anda perlu menggunakan VLOOKUP() dengan banyak kriteria, Anda dapat menggunakan kolom bantu untuk menggabungkan beberapa kriteria menjadi satu kriteria tunggal. Mari ilustrasikan dengan contoh berikut, di mana saya ingin menemukan jumlah penjualan untuk setiap orang berdasarkan tanggal dan produk.

Tabel yang berisi detail pelanggan. Sumber: Gambar oleh Penulis.

Untuk memulai, saya membuat kolom baru dengan menggabungkan beberapa kriteria menjadi pengenal unik. Dalam kasus saya, tiga kolom akan digabungkan: Customer Name, Product, dan Date.

Membuat kolom 'helper'. Sumber: Gambar oleh Penulis.

Berikut rumus yang saya gunakan untuk menggabungkan kolom-kolom menjadi satu:

=B2&"|"&C2&"|"&D2

Seperti yang Anda lihat, ada simbol pipa setelah nama kolom. Ini berfungsi sebagai pemisah antar data setiap kolom, dan umumnya dianggap praktik yang baik saat melakukan penggabungan.

Menggabungkan tiga kolom dalam satu sel. Sumber: Gambar oleh Penulis.

Jika Anda bertanya-tanya mengapa ada angka alih-alih tanggal meskipun kita memilih kolom tanggal, ini karena format tanggal telah diubah menjadi format angka. Pencarian akan tetap berfungsi, tetapi jika Anda ingin melihat data dalam bentuk yang lebih mudah dikenali, gunakan fungsi TEXT(). Fungsi TEXT() memiliki dua parameter: value adalah nilai yang ingin Anda ubah, dan format_text adalah format yang Anda inginkan untuk nilai tersebut.

Rumus TEXT. Sumber: Gambar oleh Penulis.

Jadi, untuk menggabungkan sekaligus memformat tanggal, saya mengetik hal berikut:

=B2&"|"&C2&"|"&TEXT(D2, "DD-MM-YY")

Memformat tanggal menggunakan rumus TEXT(). Sumber: Gambar oleh Penulis.

Lalu, saya seret rumus ke bawah untuk mengisi kolom baru di semua baris.

Menyalin rumus dengan menyeretnya. Sumber: Gambar oleh Penulis.

Selesai. Ketiga kolom berhasil digabungkan. Selanjutnya, saya menyiapkan tabel lookup, yang dapat Anda lihat pada gambar di bawah.

Menyiapkan tabel saya. Sumber: Gambar oleh Penulis.

Lalu, saya mengetik rumus berikut dan menekan Enter. Anda dapat melihat hasilnya di bawah.

=VLOOKUP(G6&"|"&H6&"|"&TEXT(I6, "DD-MM-YY"),$A$2:$E$11, 5,0)

Mengambil data penjualan menggunakan Vlookup dengan banyak kriteria. Sumber: Gambar oleh Penulis.

VLOOKUP() dengan fungsi CHOOSE()

Fungsi CHOOSE() memilih dan mengembalikan nilai dari sebuah daftar berdasarkan indeks atau posisi tertentu. Berikut adalah sintaks fungsi CHOOSE():

CHOOSE(index_num, value1, [value2], ...)

Dalam rumus ini:

• index_num adalah angka yang menentukan nilai mana yang dipilih.

• value1, value2, … adalah daftar nilai yang akan dipilih.

Fungsi CHOOSE() dapat membantu saat Anda perlu mencari nilai berdasarkan banyak kriteria atau ketika struktur data tidak pas dalam satu tabel. Dalam kasus seperti ini, CHOOSE() akan membuat tabel virtual untuk menggabungkan kolom dari berbagai sumber atau menyusun ulang kolom agar sesuai dengan kebutuhan lookup Anda. Mari pahami dengan contoh. Saya memiliki dataset berikut:

Tabel yang berisi nilai siswa dalam tiga kuartal. Sumber: Gambar oleh Penulis.

Sekarang, saya ingin mengambil nilai siswa untuk kuartal pertama. Jadi, saya memasukkan rumus berikut di sel F2:

=VLOOKUP($E2&"|"&F$1, CHOOSE({1,2}, $A$2:$A$16&"|"&$B$2:$B$16, C2:C15), 2, FALSE)

Mengambil nilai siswa dari kuartal pertama. Sumber: Gambar oleh Penulis.

Begini cara kerja rumusnya:

• CHOOSE({1,2}, $A$2:$A$16&"|"&$B$2:$B$16, C2:C15) membuat kolom bantu virtual dengan menggabungkan beberapa kriteria menjadi pengenal unik.

• =VLOOKUP($E2&"|"&F$1, ..., 2, FALSE) mencari nilai yang ditentukan di kolom virtual pertama ini dan mengambil nilai yang terkait dari kolom kedua.

Dengan cara ini, Anda juga dapat menemukan yang Anda butuhkan tanpa membuat kolom tambahan pada lembar kerja Anda.

Alternatif untuk VLOOKUP() dengan Banyak Kriteria

Meskipun VLOOKUP() adalah fungsi yang banyak digunakan di Excel, ini bukan satu-satunya fungsi untuk mencari data. Anda dapat mengeksplorasi alternatif seperti INDEX() dan MATCH(). Mari kita lihat bagaimana keduanya bekerja bersama untuk tujuan yang sama seperti VLOOKUP() dengan banyak kriteria.

INDEX() dan MATCH() sebagai VLOOKUP()

Fungsi INDEX() dan MATCH() yang digunakan bersama memungkinkan pencarian kolom yang dinamis. Anda dapat menggunakannya untuk bekerja dengan data yang tidak diurutkan secara menaik, dan keduanya juga dapat menangani pencarian horizontal dan vertikal. Mari tunjukkan dengan dataset berikut:

Tabel yang berisi data karyawan. Sumber: Gambar oleh Penulis.

Dari data ini, saya ingin menemukan posisi John, yang berusia 25 tahun dan tinggal di Chicago.

Membuat tabel referensi. Sumber: Gambar oleh Penulis.

Sekarang kita ketik rumus berikut di G5:

=INDEX(C2:C5,MATCH(1,(G3=A2:A5)*(G4=B2:B5)*(G6=D2:D5),0))

Menggunakan INDEX() dan MATCH() bersama. Sumber: Gambar oleh Penulis.

Penutup

Saya telah membahas beberapa teknik terpenting yang dapat digunakan dengan VLOOKUP()—kolom bantu, rumus array, dan bahkan fungsi canggih seperti INDEX() dan MATCH(). Metode-metode ini memudahkan penanganan dataset besar dan membuat Anda lebih percaya diri terhadap data Anda.

Jika Anda ingin mempelajari lebih lanjut tentang analisis data dan visualisasi di Excel, lihat kursus berikut: Data Analysis in Excel dan Data Visualization in Excel. Kami juga memiliki bacaan yang lebih singkat, yaitu tutorial Visualizing Data in Excel.