Vlookup dengan banyak kecocokan google sheets

Misalnya, Anda mungkin ingin menggabungkan nama depan dan nama belakang untuk mencari nilai menggunakan Vlookup

Dalam posting ini, Anda akan melihat cara Vlookup beberapa kriteria di Google Sheets, dengan tiga skenario berbeda

1. Vlookup Banyak Kriteria ke dalam Kolom Tunggal

Dalam hal ini, kami ingin menggabungkan kriteria pencarian untuk digunakan dalam rumus Vlookup. Misalnya, kita memiliki nama depan dan nama belakang seseorang tetapi tabel yang ingin kita cari hanya memiliki gabungan kolom nama lengkap

Triknya di sini adalah membuat rumus gabungan di dalam Vlookup untuk menggabungkan kriteria sebelum melakukan pencarian

Rumus untuk Vlookup ini dengan banyak kriteria relatif mudah

=VLOOKUP(A15&" "&B15,$A$1:$C$9,3,false)

Ini adalah rumus Vlookup biasa, dengan nilai gabungan sebagai argumen pertama. Kita perlu menggabungkan nama depan dan nama belakang sebelum mencari nama lengkap di tabel

Ini bagian dari rumus

A15&" "&B15_

menggabungkan Bob di kolom A dan Davis di kolom B menjadi Bob Davis, yang merupakan nilai yang kemudian kami cari di tabel dengan Vlookup

Ingin salinan lembar kerja contoh?

Ya, klik di sini

2. Kriteria Tunggal Vlookup menjadi Beberapa Kolom dengan Kolom Pembantu

Skenario ini kebalikan dari yang pertama. Dengan kata lain kami memiliki istilah pencarian yang lengkap, tetapi tabel pencarian kami memiliki banyak kolom yang perlu dicari

Misalnya, istilah pencarian kami adalah nama lengkap seseorang, tetapi tabel pencarian memiliki kolom untuk nama depan dan kolom untuk nama belakang. Dalam situasi ini, kami tidak dapat melakukan vlookup standar

Yang kami lakukan adalah membuat kolom pembantu di tabel pencarian, yang menggabungkan kolom yang diperlukan untuk membuat kolom pencarian baru. Jadi kami menggabungkan nama depan dan belakang untuk membuat kolom pembantu yang berisi nama lengkap

Ada berbagai rumus gabungan yang dapat Anda gunakan untuk membuat kolom pembantu, misalnya

A2&" "&B2

=JOIN(" ",A2:B2)

=CONCATENATE(A2," ",B2)_

Kolom pembantu ditunjukkan dengan warna kuning pada gambar di atas

Maka itu hanyalah Vlookup standar yang menggunakan kolom pembantu ini sebagai kolom pencarian

Misalnya rumus pada gambar di atas menggunakan kolom pembantu C (kuning) sebagai kolom pencarian

=VLOOKUP(A15,$C$1:$E$9,3,false)

Ingin salinan lembar kerja contoh?

Ya, klik di sini

3. Vlookup Kriteria Tunggal menjadi Beberapa Kolom Secara Dinamis dengan Rumus Array

Ini adalah skenario yang persis sama dengan 2 di atas, tetapi kali ini alih-alih membuat kolom pembantu langsung di tabel, kami akan menggunakan Rumus Array untuk melakukan semuanya secara dinamis dengan cepat

Memecah ini menjadi beberapa langkah

3. 1 Buat Larik Kolom Kriteria

Pertama kita perlu membuat array nama lengkap menggunakan rumus ini

=ArrayFormula($A$2:$A$9&" "&$B$2:$B$9)_

(Ini adalah rumus Array. Anda memasukkan rentang $A$2. $A$9&” “&$B$2. $B$9 lalu tekan Ctrl + Shift + Enter, atau Cmd + Shift + Enter (Mac) untuk menambahkan penunjukan Rumus Array. )

Inilah hasilnya, dihasilkan oleh rumus tunggal di sel B15

3. 2 Tambahkan kolom lain dari tabel asli

Pada langkah ini kita membuat tabel pencarian baru dengan menambahkan kolom lain dari tabel asli yang ingin kita cari, dengan menggunakan rumus ini

=ArrayFormula({$A$2:$A$9&" "&$B$2:$B$9,$C$2:$D$9})

Di sini kita menggunakan literal array — kurung kurawal { … } — untuk menggabungkan array. Menggunakan "," antara array kurung kurawal memperlakukannya sebagai kolom di samping satu sama lain, yang kita inginkan dalam kasus ini, seperti yang Anda lihat pada gambar

(Catatan 1. Jika kami menginginkan array di atas satu sama lain, kami menggunakan ";" . )

(Catatan 2. Untuk sebagian besar pengguna Eropa, sintaks Anda sedikit berbeda. Baca postingan ini. Menjelaskan perbedaan sintaks dalam formula Anda karena lokasi Google Sheets Anda. )

3. 3 Lakukan Vlookup

Sekarang kami telah membuat tabel baru dengan kolom nama lengkap, kami cukup menggunakan ini sebagai input rentang dalam vlookup standar, seperti yang ditunjukkan pada gambar pertama Bagian 3 di atas

Bagaimana Anda melakukan VLOOKUP dengan banyak kecocokan?

Bagaimana cara menggunakan banyak kecocokan di Google Sheets?

Bagaimana cara mengembalikan semua kecocokan di Google Sheets?