Vlookup untuk Mengembalikan Banyak Nilai

Excel Vlookup untuk Mengembalikan Banyak Nilai

Salah satu fungsionalitas utama dari fungsi VLOOKUP adalah ia akan berfungsi untuk nilai unik. Jika ada nilai duplikat, maka nilai apa pun yang ditemukan pertama kali akan dikembalikan untuk semua nilai pencarian lainnya. Salah satu hal utama yang perlu kita ingat saat menerapkan rumus VLOOKUP. Saat lookup_value muncul beberapa kali, kita perlu menyertakan strategi yang berbeda jika memiliki beberapa nilai. Artikel ini akan menunjukkan cara mengembalikan beberapa nilai menggunakan fungsi VLOOKUP.

Bagaimana Cara Mengembalikan Banyak Nilai menggunakan Fungsi Vlookup?

Seperti yang telah kami sampaikan di atas, VLOOKUP berfungsi untuk nilai unik. Untuk nilai duplikat, ini akan mengembalikan nilai yang ditemukan pertama kali.

Perhatikan data di bawah ini.

Kami memiliki ‘Tabel 1’ dan ‘Tabel 2.’ Dalam ‘Tabel 1’, kami memiliki buah-buahan dan harganya di berbagai kota. Untuk ‘Tabel 2’, kita perlu menentukan ‘Harga Biaya’ dari ‘Tabel 1’ menggunakan fungsi VLOOKUP. Oleh karena itu, Terapkan fungsi VLOOKUPTerapkan Fungsi VLOOKUPFungsi excel VLOOKUP mencari nilai tertentu dan mengembalikan kecocokan yang sesuai berdasarkan pengenal unik. Pengidentifikasi unik secara unik terkait dengan semua catatan database. Misalnya, ID karyawan, nomor daftar siswa, nomor kontak pelanggan, alamat email penjual, dll., adalah pengidentifikasi unik. baca selengkapnya dulu.

Kami memiliki harga yang sama untuk semua kota. Misalnya, dalam ‘Tabel 1’ untuk ‘Apple’ di kota ‘Bangalore,’ kita memiliki 108. Karena ini adalah nilai pertama yang ditemukan dalam tabel untuk ‘Apple’, hasilnya sama untuk semua kota.
Demikian pula, untuk ‘Anggur’, nilai pertama adalah 79. Nilai yang sama telah dikembalikan untuk semua kota. Dalam kasus ‘Oranye’ juga, ini memberikan 56 untuk semua kota.

Jadi, dalam kasus ini, kita perlu membuat kolom ‘Pembantu’ untuk membuat daftar nilai pencarian yang unik. Setiap buah memiliki harga yang berbeda untuk setiap kota, sehingga kombinasi nama buah dan kota dapat membuat daftar yang unik. Sisipkan kolom pembantu, dan gabungkan nama buah dan kota.

Jadi setiap nama buah digabungkan dengan kotanya dengan mencantumkan garis miring ke belakang (/) sebagai pemisah antara nama buah dan nama kota.

Sekarang, kembali ke ‘Tabel 2’ dan buka fungsi VLOOKUP.

Sekarang, kita perlu menyertakan strategi yang sama dari kolom helper di sini untuk memilih lookup_value. Tapi, pertama, pilih nama buahnya.

Kemudian gabungkan garis miring ke belakang sebelum digabungkan dengan nama kota.

Sekarang, gabungkan nama kota.

Sekarang, lookup_value mirip dengan kolom helper. Sekarang, pilih array tabel mulai dari kolom pembantu.

Sekarang, sebutkan nomor kolom 4 dan range_lookup sebagai FALSE atau 0.

Kami memiliki daftar harga biaya baru dengan angka yang akurat, berkat kolom pembantu atau kombinasi nama buah dan kota.

Gunakan Metode Alternatif untuk Banyak Nilai

Kita telah melihat bagaimana kolom pembantu dapat membantu mengambil banyak nilai menggunakan rumus VLOOKUP. Tapi bayangkan situasi di bawah ini.

Dalam hal ini, kami tidak memiliki nama kota untuk membuat kolom gabungan, jadi kami mungkin perlu menggunakan strategi yang berbeda. Di bawah ini adalah rumus kompleks yang bisa kita gunakan untuk mendapatkan beberapa nilai dari nilai unik duplikat.

=INDEX($B$2:$B$11, SMALL(IF( E3=$A$2:$A$11, ROW($A$2:$A$11)- ROW($A$2)+1), ROW(1: 1)))

Catatan: Rumus di atas adalah larik formulaArray Rumus FormulaArray adalah rumus yang sangat membantu dan ampuh yang digunakan di Excel untuk menjalankan beberapa perhitungan yang paling rumit. Ada dua jenis rumus array: satu yang mengembalikan hasil tunggal dan yang lainnya mengembalikan beberapa hasil.Baca lebih lanjut, jadi Anda harus menutup dengan Ctrl + Shift + Enter .

Rumus ini terlihat panjang. Namun, kami memiliki metode alternatif lain: gabungkan nama buah dengan hitungannya di daftar.

Terapkan fungsi COUNTIF di bawah ini Fungsi COUNTIFFungsi COUNTIF di Excel menghitung jumlah sel dalam rentang berdasarkan kriteria yang telah ditentukan sebelumnya. Ini digunakan untuk menghitung sel yang menyertakan tanggal, angka, atau teks. Misalnya, COUNTIF(A1:A10,’Trump’) akan menghitung jumlah sel dalam rentang A1:A10 yang berisi teks ‘Trump’ baca lebih lanjut untuk membuat kolom pembantu.

Fungsi di atas akan memberi kita hitungan setiap buah yang digabungkan dengan namanya. Misalnya, lihat baris nomor 4. Dalam hal ini, kita memiliki hitungan ‘Apel’ 2 kali, jadi hitungannya mengatakan 2 dan digabungkan dengan nama buahnya menghasilkan ‘2Apple’. Jadi, ini akan membuat daftar buah yang unik.

Sekarang, buat tabel pencarianTabel PencarianTabel Pencarian adalah tabel bernama sederhana yang digunakan dalam kombinasi dengan fungsi VLOOKUP untuk menemukan data apa pun dalam kumpulan data yang besar. Kita dapat memilih tabel dan menamainya, lalu ketikkan nama tabel alih-alih referensi untuk mencari nilainya. Baca lebih lanjut seperti di bawah ini.

Sekarang, buka fungsi VLOOKUP di tabel pencarian di sel H3.

Gabungan nilai pertama dihitung dalam kolom pembantu, jadi pilih nilai numerik dan hubungkan dengan nama buah.

Pilih tabel dan masukkan column_index_number untuk mendapatkan hasilnya.

Hal-hal untuk diingat

VLOOKUP mengembalikan nilai yang sama untuk lookup_values jika lookup_value memiliki nama duplikat.
Untuk mengambil banyak nilai dari lookup_value yang sama, kita harus membuat kolom pembantu menggunakan tiga metode di atas.

Artikel yang Direkomendasikan

Artikel ini adalah panduan untuk VLOOKUP untuk Mengembalikan Banyak Nilai. Di sini, kita membahas cara mengembalikan banyak nilai menggunakan fungsi VLOOKUP, metode alternatif untuk banyak nilai, dan template Excel yang dapat diunduh. Anda juga dapat melihat fungsi-fungsi berguna ini di Excel:-