Fungsi VLOOKUP adalah salah satu fungsi paling populer di Excel. Tutorial ini akan memperkenalkan cara menggunakan fungsi VLOOKUP di Excel dengan puluhan contoh dasar dan lanjutan langkah demi langkah.

Pengenalan fungsi VLOOKUP - Sintaks dan Argumen

Di Excel, fungsi VLOOKUP adalah fungsi yang kuat untuk sebagian besar pengguna Excel, ini memungkinkan Anda mencari nilai di paling kiri rentang data, dan mengembalikan nilai yang cocok di baris yang sama dari kolom yang Anda tentukan seperti tangkapan layar berikut yang ditampilkan .

Sintaks fungsi VLOOKUP:

Argumen:

Fungsi Catatan:

Contoh VLOOKUP dasar

Di bagian ini, kita akan membahas beberapa rumus Vlookup yang sering Anda gunakan.

2.1 Kecocokan tepat dan perkiraan kecocokan VLOOKUP

 2.1.1 Lakukan pencocokan persis VLOOKUP

Biasanya, jika Anda mencari kecocokan persis dengan fungsi VLOOKUP, Anda hanya perlu menggunakan FALSE sebagai argumen terakhir.

Misalnya, untuk mendapatkan nilai Matematika yang sesuai berdasarkan nomor ID tertentu, lakukan seperti ini:

Silakan salin dan tempel rumus di bawah ini ke sel kosong (di sini, saya pilih G2), dan tekan Enter kunci untuk mendapatkan hasil:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

Bagaimana rumus VLOOKUP bekerja?

Pertama, mencari ID - C1005 di kolom paling kiri dari tabel. Itu bergerak dari atas ke bawah dan menemukan nilai di sel A6.

Segera setelah menemukan nilainya, ia pergi ke kanan di kolom ketiga dan mengekstrak nilai di dalamnya.

Jadi, Anda akan mendapatkan hasil seperti tangkapan layar di bawah ini:

 2.1.2 Lakukan pencocokan perkiraan VLOOKUP

Pencocokan perkiraan berguna untuk mencari nilai di antara rentang data. Jika pencocokan tepat tidak ditemukan, perkiraan VLOOKUP akan mengembalikan nilai terbesar yang lebih kecil dari nilai pencarian.

Misalnya, jika Anda memiliki rentang data berikut, dan pesanan yang ditentukan tidak ada di kolom Pesanan, bagaimana cara mendapatkan Diskon terdekatnya di kolom B?

Langkah 1: Terapkan rumus VLOOKUP dan isi ke sel lain

Salin dan tempel rumus berikut ke sel tempat Anda ingin meletakkan hasilnya, lalu seret gagang isian ke bawah untuk menerapkan rumus ini ke sel lain.

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

Hasil:

Sekarang, Anda akan mendapatkan perkiraan kecocokan berdasarkan nilai yang diberikan, lihat tangkapan layar:

2.2 Lakukan VLOOKUP peka huruf besar-kecil di Excel

Secara default, fungsi VLOOKUP melakukan pencarian yang tidak peka huruf besar kecil yang artinya memperlakukan karakter huruf kecil dan huruf besar secara identik. Terkadang, Anda mungkin perlu melakukan pencarian peka huruf besar-kecil di Excel, fungsi VLOOKUP normal mungkin tidak menyelesaikannya. Dalam hal ini, Anda dapat menggunakan fungsi alternatif seperti INDEX dan MATCH dengan fungsi EXACT, atau fungsi LOOKUP dan EXACT.

Misalnya, saya memiliki rentang data berikut yang kolom ID berisi string teks dengan huruf besar atau kecil, sekarang, saya ingin mengembalikan skor Matematika yang sesuai dari nomor ID yang diberikan.

Langkah 1: Terapkan salah satu rumus dan isi ke sel lain

Silakan salin dan tempel salah satu rumus di bawah ini ke dalam sel kosong tempat Anda ingin mendapatkan hasilnya. Kemudian, pilih sel rumus, seret pegangan isian ke bawah ke sel tempat Anda ingin mengisi rumus ini.

Formula 1: Setelah menempelkan rumus, silakan tekan Ctrl + Shift + Enter kunci.

=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))

Formula 2: Setelah menempelkan rumus, silakan tekan Enter kunci.

=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)

Hasil:

Maka Anda akan mendapatkan hasil yang benar yang Anda butuhkan. Lihat tangkapan layar:

2.3 Nilai VLOOKUP dari kanan ke kiri di Excel

Fungsi VLOOKUP selalu mencari nilai di kolom paling kiri rentang data dan mengembalikan nilai yang sesuai dari kolom di sebelah kanan. Jika Anda ingin melakukan VLOOKUP terbalik yang artinya mencari nilai tertentu di kolom kanan dan mengembalikan nilai yang sesuai di kolom kiri seperti gambar di bawah ini yang ditampilkan:

Klik untuk mengetahui detail langkah demi langkah tentang tugas ini…

2.4 VLOOKUP nilai pencocokan kedua, ke-n atau terakhir di Excel

Biasanya, jika beberapa nilai yang cocok ditemukan saat menggunakan fungsi Vlookup, hanya record pertama yang cocok yang akan dikembalikan. Di bagian ini, saya akan berbicara tentang cara mendapatkan nilai pencocokan kedua, ke-n, atau terakhir dalam rentang data.

 2.4.1 VLOOKUP dan kembalikan nilai pencocokan kedua atau ke-n

Misalkan Anda memiliki daftar nama di kolom A, kursus pelatihan yang mereka beli di kolom B. Sekarang, Anda mencari kursus pelatihan ke-2 atau ke-n yang dibeli oleh pelanggan tertentu. Lihat tangkapan layar:

Di sini, fungsi VLOOKUP mungkin tidak menyelesaikan tugas ini secara langsung. Namun, Anda dapat menggunakan fungsi INDEX sebagai alternatif.

Langkah 1: Terapkan dan isi rumus ke sel lain

Misalnya, untuk mendapatkan nilai pencocokan kedua berdasarkan kriteria yang diberikan, silakan terapkan rumus berikut ke dalam sel kosong, dan tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil pertama. Lalu, pilih sel rumus, seret gagang isian ke bawah ke sel tempat Anda ingin mengisi rumus ini.

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

Hasil:

Sekarang, semua nilai yang cocok kedua berdasarkan nama yang diberikan telah ditampilkan sekaligus.

 2.4.2 VLOOKUP dan mengembalikan nilai pencocokan terakhir

Jika Anda ingin vlookup dan mengembalikan nilai pencocokan terakhir seperti gambar di bawah ini yang ditampilkan, ini VLOOKUP Dan Mengembalikan Nilai Pencocokan Terakhir tutorial dapat membantu Anda untuk mendapatkan nilai pencocokan terakhir secara detail.

2.5 Nilai pencocokan VLOOKUP antara dua nilai atau tanggal yang diberikan

Kadang-kadang, Anda mungkin ingin mencari nilai di antara dua nilai atau tanggal dan mengembalikan hasil yang sesuai seperti yang ditunjukkan pada tangkapan layar di bawah. Dalam kasus seperti itu, Anda dapat menggunakan fungsi LOOKUP alih-alih fungsi VLOOKUP dengan tabel yang diurutkan.

 2.5.1 Nilai pencocokan VLOOKUP antara dua nilai atau tanggal yang diberikan dengan rumus

Langkah 1: Atur data dan terapkan rumus berikut

Tabel asli Anda harus berupa rentang data yang diurutkan. Lalu, salin atau masukkan rumus berikut ke dalam sel kosong. Lalu, seret gagang isian untuk mengisi rumus ini ke sel lain yang Anda perlukan.

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

Hasil:

Dan sekarang, Anda akan mendapatkan semua catatan yang cocok berdasarkan nilai yang diberikan, lihat tangkapan layar:

 2.5.2 Nilai pencocokan VLOOKUP antara dua nilai atau tanggal yang diberikan dengan fitur praktis

Jika Anda merasa kesulitan untuk mengingat dan memahami rumus di atas, berikut saya akan memperkenalkan alat yang mudah – Kutools untuk Excel, Dengan yang LOOKUP antara Dua Nilai fitur, Anda dapat mengembalikan item yang sesuai berdasarkan nilai atau tanggal tertentu antara dua nilai atau tanggal dengan mudah.

1. Klik Kutools > PENCARIAN Super > LOOKUP antara Dua Nilai untuk mengaktifkan fitur ini.
2. Kemudian tentukan operasi dari kotak dialog berdasarkan data Anda.

2.6 Menggunakan wildcard untuk pencocokan sebagian dalam fungsi VLOOKUP

Di Excel, wildcard dapat digunakan dalam fungsi VLOOKUP, yang memungkinkan Anda melakukan pencocokan parsial pada nilai pencarian. Misalnya, Anda dapat menggunakan VLOOKUP untuk mengembalikan nilai yang cocok dari tabel berdasarkan bagian dari nilai pencarian.

Misalkan, saya memiliki berbagai data seperti gambar di bawah ini yang ditampilkan, sekarang, saya ingin mengekstrak skor berdasarkan nama depan (bukan nama lengkap). Bagaimana menyelesaikan tugas ini di Excel?

Langkah 1: Terapkan dan isi rumus ke sel lain

Harap salin atau masukkan rumus berikut ke dalam sel kosong, lalu seret gagang isian untuk mengisi rumus ini ke sel lain yang Anda perlukan:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

Hasil:

Dan semua skor yang cocok telah dikembalikan seperti gambar di bawah ini:

2.7 Nilai VLOOKUP dari lembar kerja lain

Biasanya, Anda mungkin harus bekerja dengan lebih dari satu lembar kerja, fungsi VLOOKUP dapat digunakan untuk mencari data dari lembar lain sama seperti pada satu lembar kerja.

Misalnya, Anda memiliki dua lembar kerja seperti gambar di bawah ini yang ditampilkan, untuk mencari dan mengembalikan data yang sesuai dari lembar kerja yang Anda tentukan, lakukan dengan langkah-langkah berikut:

Langkah 1: Terapkan dan isi rumus ke sel lain

Harap masukkan atau salin rumus di bawah ini ke sel kosong tempat Anda ingin mendapatkan item yang cocok. Lalu, seret gagang isian ke sel yang ingin Anda terapkan rumus ini.

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

Hasil:

Anda akan mendapatkan hasil yang sesuai seperti yang Anda butuhkan, lihat tangkapan layar:

2.8 Nilai VLOOKUP dari buku kerja lain

Bagian ini akan membahas tentang pencarian dan mengembalikan nilai yang cocok dari buku kerja berbeda dengan menggunakan fungsi VLOOKUP.

Misalnya, katakanlah Anda memiliki dua buku kerja. Buku kerja pertama berisi daftar produk dan biayanya masing-masing. Di buku kerja kedua, Anda ingin mengekstrak biaya yang sesuai untuk setiap item produk seperti gambar di bawah ini.

Langkah 1: Terapkan dan isi rumusnya

Buka kedua buku kerja yang ingin Anda gunakan, lalu terapkan rumus berikut ke dalam sel tempat Anda ingin meletakkan hasilnya di buku kerja kedua. Kemudian, seret dan salin rumus ini ke sel lain yang Anda perlukan

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

Hasil:

2.9 Mengembalikan teks kosong atau spesifik alih-alih kesalahan 0 atau #N/A

Biasanya, ketika Anda menggunakan fungsi VLOOKUP untuk mengembalikan nilai yang sesuai, jika sel yang cocok kosong, itu akan mengembalikan 0. Dan jika nilai yang cocok tidak ditemukan, Anda akan mendapatkan nilai kesalahan #N/A seperti yang ditunjukkan pada tangkapan layar di bawah ini. Jika Anda ingin menampilkan sel kosong atau nilai tertentu, bukan 0 atau #N/A, ini VLOOKUP Untuk Mengembalikan Nilai Kosong Atau Spesifik Bukan 0 Atau N/A tutorial dapat membantu Anda.

Contoh VLOOKUP lanjutan

3.1 Pencarian dua arah (VLOOKUP dalam baris dan kolom)

Terkadang, Anda mungkin perlu melakukan pencarian 2 dimensi, yang berarti mencari nilai di baris dan kolom secara bersamaan. Misalnya, jika Anda memiliki rentang data berikut, dan Anda mungkin perlu mendapatkan nilai untuk produk tertentu dalam kuartal tertentu. Bagian ini akan memperkenalkan rumus untuk menangani pekerjaan ini di Excel.

Di Excel, Anda bisa menggunakan kombinasi fungsi VLOOKUP dan MATCH untuk melakukan pencarian dua arah.

Silakan terapkan rumus berikut ke dalam sel kosong, lalu tekan Enter kunci untuk mendapatkan hasil.

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

3.2 Nilai pencocokan VLOOKUP berdasarkan dua kriteria atau lebih

Mudah bagi Anda untuk mencari nilai yang cocok berdasarkan satu kriteria, tetapi jika Anda memiliki dua kriteria atau lebih, apa yang dapat Anda lakukan?

 3.2.1 Pencocokan nilai VLOOKUP berdasarkan dua kriteria atau lebih dengan rumus

Dalam hal ini, fungsi LOOKUP atau MATCH dan INDEX di Excel dapat membantu Anda menyelesaikan pekerjaan ini dengan cepat dan mudah.

Misalnya, saya memiliki tabel data di bawah ini, untuk mengembalikan harga yang cocok berdasarkan produk dan ukuran tertentu, rumus berikut dapat membantu Anda.

Langkah 1: Terapkan salah satu formula

Formula 1: Setelah menempelkan rumus, silakan tekan Enter kunci.

=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))

Formula 2: Setelah menempelkan rumus, silakan tekan Ctrl + Shift + Enter kunci.

=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))

Hasil:

 3.2.2 Nilai pencocokan VLOOKUP berdasarkan dua kriteria atau lebih dengan fitur cerdas

Sulit untuk mengingat rumus rumit di atas yang perlu diterapkan berulang kali, yang dapat memperlambat efisiensi kerja Anda. Namun, Kutools untuk Excel menawarkan Pencarian multi-kondisi fitur yang memungkinkan Anda mengembalikan hasil yang sesuai berdasarkan satu atau beberapa kondisi hanya dengan beberapa klik.

1. Klik Kutools > PENCARIAN Super > Pencarian multi-kondisi untuk mengaktifkan fitur ini.
2. Kemudian tentukan operasi dari kotak dialog berdasarkan data Anda.

3.3 VLOOKUP untuk mengembalikan beberapa nilai dengan satu atau beberapa kriteria

Di Excel, fungsi VLOOKUP mencari nilai dan hanya mengembalikan nilai pertama yang cocok jika ada beberapa nilai terkait yang ditemukan. Terkadang, Anda mungkin ingin mengembalikan semua nilai yang sesuai dalam satu baris, dalam kolom, atau dalam satu sel. Bagian ini akan membahas tentang cara mengembalikan beberapa nilai yang cocok dengan satu atau beberapa kondisi di buku kerja.

 3.3.1 VLOOKUP semua nilai yang cocok berdasarkan satu atau beberapa kondisi secara horizontal

Dengan asumsi Anda memiliki tabel data yang berisi negara, kota, dan nama dalam rentang A1:C14, dan sekarang, Anda ingin mengembalikan semua nama secara horizontal yang berasal dari "AS" seperti gambar di bawah ini. Untuk menyelesaikan tugas ini, tolong klik di sini untuk mendapatkan hasil langkah demi langkah.

 3.3.2 VLOOKUP semua nilai yang cocok berdasarkan satu atau beberapa kondisi secara vertikal

Jika Anda perlu Vlookup dan mengembalikan semua nilai yang cocok secara vertikal berdasarkan kriteria tertentu seperti yang ditunjukkan tangkapan layar di bawah ini, silakan klik di sini untuk mendapatkan solusinya secara detail.

 3.3.3 VLOOKUP semua nilai yang cocok berdasarkan satu atau beberapa kondisi ke dalam sel tunggal

Jika Anda ingin Vlookup dan mengembalikan beberapa nilai yang cocok ke dalam satu sel dengan pemisah yang ditentukan, fungsi baru TEXTJOIN dapat membantu Anda menyelesaikan pekerjaan ini dengan cepat dan mudah.

3.4 VLOOKUP untuk mengembalikan seluruh baris sel yang cocok

Di bagian ini, saya akan berbicara tentang cara mengambil seluruh baris dari nilai yang cocok dengan menggunakan fungsi VLOOKUP.

Langkah 1: Terapkan dan isi rumus berikut

Silakan salin atau ketik rumus di bawah ini ke dalam sel kosong tempat Anda ingin menampilkan hasilnya, dan tekan Enter kunci untuk mendapatkan nilai pertama. Kemudian, seret sel rumus ke kanan hingga data seluruh baris ditampilkan.

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

Hasil:

Sekarang, Anda dapat melihat seluruh data baris dikembalikan. Lihat tangkapan layar:

3.5 VLOOKUP Bersarang di Excel

Terkadang, Anda mungkin perlu mencari nilai yang saling terkait di beberapa tabel. Dalam hal ini, Anda dapat menggabungkan beberapa fungsi VLOOKUP untuk mendapatkan nilai akhir.

Misalnya, saya memiliki lembar kerja yang berisi dua tabel terpisah. Tabel pertama mencantumkan semua nama produk beserta penjualnya yang sesuai. Tabel kedua mencantumkan total penjualan masing-masing salesman. Sekarang, jika Anda ingin menemukan penjualan setiap produk, seperti yang ditunjukkan pada tangkapan layar berikut, Anda dapat menyusun fungsi VLOOKUP untuk menyelesaikan tugas ini.

Rumus umum untuk fungsi VLOOKUP bersarang adalah:

Langkah 1: Terapkan dan isi rumus berikut

Harap terapkan rumus berikut ke dalam sel kosong, lalu seret gagang isian ke bawah ke sel yang ingin Anda terapkan rumus ini.

=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)

Hasil:

Sekarang, Anda akan mendapatkan hasilnya seperti yang ditunjukkan pada tangkapan layar berikut:

3.6 Periksa apakah ada nilai berdasarkan daftar data di kolom lain

Fungsi VLOOKUP juga dapat membantu Anda memeriksa apakah ada nilai berdasarkan daftar data di kolom lain. Misalnya, jika Anda ingin mencari nama di kolom C dan hanya mengembalikan Ya atau Tidak jika nama tersebut ditemukan atau tidak di kolom A seperti gambar di bawah ini.

Langkah 1: Terapkan dan isi rumus berikut

Silakan terapkan rumus berikut ke dalam sel kosong, lalu seret gagang isian ke bawah ke sel yang ingin Anda isi dengan rumus ini.

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

Hasil:

Dan Anda akan mendapatkan hasil yang Anda butuhkan, lihat tangkapan layar:

3.7 VLOOKUP dan jumlahkan semua nilai yang cocok dalam baris atau kolom

Saat bekerja dengan data numerik, Anda mungkin perlu mengekstrak nilai yang cocok dari tabel dan menjumlahkan angka dalam beberapa kolom atau baris. Bagian ini akan memperkenalkan beberapa rumus yang dapat membantu Anda menyelesaikan tugas ini.

 3.7.1 VLOOKUP dan jumlahkan semua nilai yang cocok dalam satu atau beberapa baris

Misalkan Anda memiliki daftar produk dengan penjualan selama beberapa bulan, seperti yang ditunjukkan pada tangkapan layar berikut. Sekarang, Anda perlu menjumlahkan semua pesanan dalam semua bulan berdasarkan produk yang diberikan.

Langkah 1: Terapkan dan isi rumus berikut

Harap salin atau masukkan rumus berikut ke dalam sel kosong, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan hasil pertama. Lalu, seret gagang isian ke bawah untuk menyalin rumus ini ke sel lain yang Anda perlukan.

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Hasil:

Semua nilai dalam satu baris dari nilai pencocokan pertama telah dijumlahkan, lihat tangkapan layar:

 3.7.2 VLOOKUP dan jumlahkan semua nilai yang cocok dalam satu atau beberapa kolom

Jika Anda ingin menjumlahkan nilai total untuk bulan tertentu seperti yang ditunjukkan pada tangkapan layar di bawah ini. Fungsi VLOOKUP normal mungkin tidak membantu Anda, di sini, Anda harus menerapkan fungsi SUM, INDEX, dan MATCH secara bersamaan untuk membuat rumus.

Langkah 1: Terapkan rumus berikut

Terapkan rumus di bawah ini ke sel kosong, lalu seret gagang isian ke bawah untuk menyalin rumus ini ke sel lain.

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

Hasil:

Sekarang, nilai pertama yang cocok berdasarkan bulan tertentu di kolom telah dijumlahkan, lihat tangkapan layar:

 3.7.3 VLOOKUP dan jumlahkan nilai pertama yang cocok atau semua nilai yang cocok dengan fitur canggih

Mungkin rumus di atas sulit untuk Anda ingat, dalam hal ini, saya akan merekomendasikan fitur yang ampuh - Pencarian dan Jumlah of Kutools untuk Excel, dengan fitur ini, Anda dapat Vlookup dan menjumlahkan pencocokan pertama atau semua nilai pencocokan dalam baris atau kolom semudah mungkin.

1. Klik Kutools > PENCARIAN Super > LOOKUP dan Jumlah untuk mengaktifkan fitur ini.
2. Kemudian tentukan operasi dari kotak dialog berdasarkan kebutuhan Anda.

 3.7.4 VLOOKUP dan jumlahkan semua nilai yang cocok baik dalam baris maupun kolom

Jika Anda ingin menjumlahkan nilai saat Anda perlu mencocokkan kolom dan baris, misalnya, untuk mendapatkan nilai total produk Sweater di bulan Mar seperti gambar di bawah ini.

Di sini, Anda dapat menggunakan fungsi SUMPRODCT untuk menyelesaikan tugas ini.

Harap terapkan rumus berikut ke dalam sel, lalu tekan Enter kunci untuk mendapatkan hasilnya, lihat tangkapan layar:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

3.8 VLOOKUP untuk menggabungkan dua tabel berdasarkan kolom kunci

Dalam pekerjaan sehari-hari Anda, saat menganalisis data, Anda mungkin perlu mengumpulkan semua informasi yang diperlukan ke dalam satu tabel berdasarkan satu atau beberapa kolom kunci. Untuk menyelesaikan tugas ini, Anda dapat menggunakan fungsi INDEX dan MATCH alih-alih fungsi VLOOKUP.

 3.8.1 VLOOKUP untuk menggabungkan dua tabel berdasarkan satu kolom kunci

Misalnya, Anda memiliki dua tabel, tabel pertama berisi data produk dan nama, dan tabel kedua berisi data produk dan pesanan, sekarang Anda ingin menggabungkan kedua tabel ini dengan mencocokkan kolom produk umum ke dalam satu tabel.

Langkah 1: Terapkan dan isi rumus berikut

Silakan terapkan rumus berikut ke dalam sel kosong. Lalu, seret gagang isian ke sel yang ingin Anda terapkan rumus ini

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

Hasil:

Sekarang, Anda akan mendapatkan tabel gabungan dengan kolom pesanan bergabung ke tabel pertama berdasarkan data kolom kunci.

 3.8.2 VLOOKUP untuk menggabungkan dua tabel berdasarkan beberapa kolom kunci

Jika dua tabel yang ingin Anda gabungkan memiliki beberapa kolom kunci, untuk menggabungkan tabel berdasarkan kolom umum tersebut, ikuti langkah-langkah di bawah ini.

Rumus generiknya adalah:

Langkah 1: Terapkan rumus berikut

Silakan terapkan rumus di bawah ini ke sel kosong tempat Anda ingin meletakkan hasilnya, lalu tekan Ctrl + Shift + Enter kunci bersama untuk mendapatkan nilai pertama yang cocok, lihat tangkapan layar:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

Langkah 2: Isi rumus ke sel lain

Kemudian, pilih sel rumus pertama, dan seret gagang isian untuk menyalin rumus ini ke sel lain sesuai kebutuhan:

3.9 Nilai pencocokan VLOOKUP di beberapa lembar kerja

Pernahkah Anda perlu melakukan VLOOKUP di beberapa lembar kerja di Excel? Misalnya, jika Anda memiliki tiga lembar kerja dengan rentang data, dan Anda ingin mengambil nilai tertentu berdasarkan kriteria dari lembar ini, Anda dapat mengikuti tutorial langkah demi langkah Nilai VLOOKUP di Beberapa Lembar Kerja untuk menyelesaikan tugas ini.

VLOOKUP cocok nilai mempertahankan pemformatan sel

Saat mencari nilai yang cocok, pemformatan sel asli seperti warna font, warna latar belakang, format data, dll. tidak akan disimpan. Untuk mempertahankan pemformatan sel atau data, bagian ini akan memperkenalkan beberapa trik untuk menyelesaikan pekerjaan.

4.1 VLOOKUP mencocokkan nilai dan mempertahankan warna sel, pemformatan font

Seperti yang kita semua ketahui, fungsi VLOOKUP normal hanya dapat mengambil nilai yang cocok dari rentang data lain. Namun, mungkin ada kejadian di mana Anda ingin mendapatkan nilai yang sesuai bersama dengan pemformatan sel, seperti warna isian, warna font, dan gaya font. Di bagian ini, kita akan membahas cara mengambil nilai yang cocok sambil mempertahankan pemformatan sumber di Excel.

Harap lakukan dengan langkah-langkah berikut untuk mencari dan mengembalikan nilai yang sesuai bersama dengan pemformatan sel:

Langkah 1: Salin kode 1 ke dalam Modul Kode Lembar

1. Di lembar kerja berisi data yang ingin Anda VLOOKUP, klik kanan tab lembar dan pilih Lihat kode dari menu konteks. Lihat tangkapan layar:
   
2. Di tempat terbuka Microsoft Visual Basic untuk Aplikasi jendela, harap salin kode VBA di bawah ini ke jendela Kode.
3. Kode VBA 1: VLOOKUP untuk mendapatkan pemformatan sel bersama dengan nilai pencarian

4. Sub Worksheet_Change(ByVal Target As Range)
   'Updateby Extendoffice
       Dim I As Long
       Dim xKeys As Long
       Dim xDicStr As String
       On Error Resume Next
       Application.ScreenUpdating = False
       xKeys = UBound(xDic.Keys)
       If xKeys >= 0 Then
           For I = 0 To UBound(xDic.Keys)
               xDicStr = xDic.Items(I)
               If xDicStr <> "" Then
                   Range(xDic.Keys(I)).Interior.Color = _
                   Range(xDic.Items(I)).Interior.Color
                   Range(xDic.Keys(I)).Font.FontStyle = _
                   Range(xDic.Items(I)).Font.FontStyle
                   Range(xDic.Keys(I)).Font.Size = _
                   Range(xDic.Items(I)).Font.Size
                   Range(xDic.Keys(I)).Font.Color = _
                   Range(xDic.Items(I)).Font.Color
                   Range(xDic.Keys(I)).Font.Name = _
                   Range(xDic.Items(I)).Font.Name
                   Range(xDic.Keys(I)).Font.Underline = _
                   Range(xDic.Items(I)).Font.Underline
               Else
                   Range(xDic.Keys(I)).Interior.Color = xlNone
               End If
           Next
           Set xDic = Nothing
       End If
       Application.ScreenUpdating = True
   End Sub
   

5. 

Langkah 2: Salin kode 2 ke jendela Modul

1. Masih dalam Microsoft Visual Basic untuk Aplikasi window, klik Menyisipkan > Modul, lalu salin kode VBA 2 di bawah ini ke jendela Modul.
2. Kode VBA 2: VLOOKUP untuk mendapatkan pemformatan sel bersama dengan nilai pencarian

3. Public xDic As New Dictionary
   Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
       Dim xFindCell As Range
       On Error Resume Next
       Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
       If xFindCell Is Nothing Then
           LookupKeepFormat = ""
           xDic.Add Application.Caller.Address, ""
       Else
           LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
           xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address
       End If
   End Function
   

4. 

Langkah 3: Pilih opsi untuk proyek VBA

1. Setelah memasukkan kode di atas, lalu klik Tools > Referensi dalam Microsoft Visual Basic untuk Aplikasi jendela. Kemudian periksa Runtime Microsoft Scripting kotak centang di Referensi - VBAProject kotak dialog. Lihat tangkapan layar:
2. Lalu klik OK untuk menutup kotak dialog, lalu simpan dan tutup jendela kode.

Langkah 4: Ketik rumus untuk mendapatkan hasilnya

1. Sekarang, kembali ke lembar kerja, terapkan rumus berikut. Lalu, seret gagang isian ke bawah untuk mendapatkan semua hasil beserta pemformatannya. Lihat tangkapan layar:
   

   =LookupKeepFormat(E2,$A$1:$C$10,3)

   
   

4.2 Menyimpan format tanggal dari nilai yang dikembalikan VLOOKUP

Saat menggunakan fungsi VLOOKUP untuk mencari dan mengembalikan nilai dengan format tanggal, hasil yang dikembalikan mungkin ditampilkan sebagai angka. Untuk mempertahankan format tanggal dalam hasil yang dikembalikan, Anda harus menyertakan fungsi VLOOKUP dalam fungsi TEXT.

Langkah 1: Terapkan dan isi rumus berikut

Silakan terapkan rumus di bawah ini ke dalam sel kosong. Lalu, seret gagang isian untuk menyalin rumus ini ke sel lain.

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

Hasil:

Semua tanggal yang cocok telah dikembalikan seperti gambar di bawah ini:

4.3 Mengembalikan komentar sel dari VLOOKUP

Pernahkah Anda perlu mengambil data sel yang cocok dan komentar terkaitnya menggunakan VLOOKUP di Excel, seperti yang ditampilkan dalam tangkapan layar berikut? Jika demikian, Fungsi yang Ditentukan Pengguna yang disediakan di bawah dapat membantu Anda menyelesaikan tugas ini.

Langkah 1: Salin kode ke dalam Modul

1. Tahan ALT + F11 kunci untuk membuka Microsoft Visual Basic untuk Aplikasi jendela.
2. Klik Menyisipkan > Modul, lalu salin dan tempel kode berikut di Jendela Modul.
   Kode VBA: Vlookup dan mengembalikan nilai yang cocok dengan komentar sel:

   Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant
   'Updateby Extendoffice
       Application.Volatile
       Dim xRet As Variant 'could be an error
       Dim xCell As Range
       xRet = Application.Match(LookVal, FTable.Columns(1), FType)
       If IsError(xRet) Then
           VlookupComment = "Not Found"
       Else
           Set xCell = FTable.Columns(FColumn).Cells(1)(xRet)
           VlookupComment = xCell.Value
           With Application.Caller
               If Not .Comment Is Nothing Then
                   .Comment.Delete
               End If
               If Not xCell.Comment Is Nothing Then
                   .AddComment xCell.Comment.Text
               End If
           End With
       End If
   End Function

3. Kemudian simpan dan tutup jendela kode.

Langkah 2: Ketik rumus untuk mendapatkan hasilnya

1. Sekarang, masukkan rumus berikut, dan seret gagang isian untuk menyalin rumus ini ke sel lain. Ini akan mengembalikan nilai dan komentar yang cocok secara bersamaan, lihat tangkapan layar:
   

   =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

   
   

4.4 Nomor VLOOKUP disimpan sebagai teks

Misalnya, saya memiliki rentang data di mana nomor ID di tabel asli dalam format angka dan nomor ID di sel pencarian disimpan sebagai teks, Anda mungkin mengalami kesalahan #N/A saat menggunakan fungsi VLOOKUP normal. Dalam hal ini, untuk mengambil informasi yang benar, Anda dapat menggabungkan fungsi TEXT dan VALUE dalam fungsi VLOOKUP. Di bawah ini adalah rumus untuk melakukannya:

Langkah 1: Terapkan dan isi rumus berikut

Harap terapkan rumus berikut ke dalam sel kosong, lalu seret gagang isian ke bawah untuk menyalin rumus ini.

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

Hasil:

Sekarang, Anda akan mendapatkan hasil yang benar seperti gambar di bawah ini yang ditampilkan: