Vba excel vlookup dari lembar kerja lain

Apa itu VLOOKUP? . ) Apa fungsinya? . Secara lebih teknis, fungsi VLOOKUP mencari nilai di kolom pertama rentang tertentu dan mengembalikan nilai di baris yang sama dari kolom lain

Dalam penggunaannya yang umum, Excel VLOOKUP mencari melalui kumpulan data Anda berdasarkan pengidentifikasi unik dan memberi Anda informasi yang terkait dengan pengidentifikasi unik tersebut

Huruf "V" adalah singkatan dari "vertikal" dan digunakan untuk membedakan VLOOKUP dari fungsi HLOOKUP yang mencari nilai dalam baris, bukan kolom (H singkatan dari "horizontal")

Fungsi ini tersedia di semua versi Excel 365 hingga Excel 2007

Tip. Di Excel 365 dan Excel 2021, Anda dapat menggunakan fungsi XLOOKUP, yang merupakan penerus VLOOKUP yang lebih fleksibel dan andal.

Sintaks VLOOKUP

Sintaks untuk fungsi VLOOKUP adalah sebagai berikut

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

Di mana

• Lookup_value (wajib) - adalah nilai yang dicari
  Ini bisa berupa nilai (angka, tanggal atau teks), referensi sel (referensi ke sel yang berisi nilai pencarian), atau nilai yang dikembalikan oleh beberapa fungsi lain. Tidak seperti angka dan referensi sel, nilai teks harus selalu diapit dengan "tanda kutip ganda"
• Table_array (wajib) - adalah rentang sel tempat mencari nilai pencarian dan untuk mengambil kecocokan. Fungsi VLOOKUP selalu mencari di kolom pertama larik tabel, yang mungkin berisi berbagai nilai teks, angka, tanggal, dan nilai logika
• Col_index_num (wajib) - adalah nomor kolom untuk mengembalikan nilai. Penghitungan dimulai dari kolom paling kiri dalam larik tabel, yaitu 1
• Range_lookup (opsional) - menentukan apakah akan mencari pencocokan perkiraan atau persis
  • BENAR atau dihilangkan (default) - perkiraan kecocokan. Jika pencocokan persis tidak ditemukan, rumus akan mencari nilai terbesar yang lebih kecil dari nilai pencarian. Memerlukan penyortiran kolom pencarian dalam urutan menaik
  • SALAH - sama persis. Rumus mencari nilai yang sama persis dengan nilai pencarian. Jika pencocokan persis tidak ditemukan, nilai #N/A dikembalikan

Rumus dasar VLOOKUP

Berikut adalah contoh rumus Excel VLOOKUP dalam bentuk yang paling sederhana. Silakan lihat rumus di bawah ini dan coba "terjemahkan" ke dalam bahasa Inggris

=VLOOKUP("lion", A2:B11, 2, FALSE)

• Argumen pertama (lookup_value) dengan jelas menunjukkan bahwa rumus mencari kata "singa"
• Argumen ke-2 (table_array) adalah A2. B11. Ingatlah bahwa pencarian dilakukan di kolom paling kiri, Anda dapat membaca rumus di atas sedikit lebih jauh. cari "singa" di rentang A2. A11. Sejauh ini, sangat bagus, bukan?
• Argumen ke-3 col_index_num adalah 2. Artinya, kami ingin mengembalikan nilai yang cocok dari kolom B, yang kedua dalam susunan tabel
• Argumen range_lookup ke-4 adalah FALSE, yang menunjukkan bahwa kita sedang mencari pencocokan tepat

Dengan semua argumen yang ditetapkan, Anda seharusnya tidak kesulitan membaca seluruh rumus. cari "singa" di A2. A11, temukan kecocokan persis, dan kembalikan nilai dari kolom B di baris yang sama

Demi kenyamanan, Anda dapat mengetikkan nilai yang diinginkan di beberapa sel, katakanlah E1, ganti teks "hardcoded" dengan referensi sel, dan dapatkan rumus untuk mencari nilai apa pun yang Anda masukkan di E1

=VLOOKUP(E1, A2:B11, 2, FALSE)_

Apakah masih ada yang tidak jelas?

Cara melakukan Vlookup di Excel

Saat menggunakan rumus VLOOKUP di lembar kerja kehidupan nyata, aturan praktis utamanya adalah ini. kunci susunan tabel dengan (seperti $A$2. $C$11) untuk mencegahnya berubah saat menyalin rumus ke sel lain

Nilai pencarian dalam banyak kasus harus berupa (seperti E2) atau Anda hanya dapat mengunci koordinat kolom ($E2). Saat rumus disalin ke bawah kolom, referensi akan menyesuaikan secara otomatis untuk setiap baris

Untuk melihat cara kerjanya dalam praktik, harap pertimbangkan contoh berikut. Ke tabel sampel kami, kami telah menambahkan satu kolom lagi yang memeringkat hewan berdasarkan kecepatan (kolom A) dan ingin menemukan pelari tercepat ke-1, ke-5, dan ke-10 di dunia. Untuk ini, masukkan peringkat pencarian di beberapa sel (E2. E4 pada tangkapan layar di bawah), dan gunakan rumus berikut

Untuk menarik nama hewan dari kolom B

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)

Untuk mengekstrak kecepatan dari kolom C

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

Masukkan rumus di atas di sel F2 dan G2, pilih sel tersebut, dan seret rumus ke baris di bawah

Jika Anda menyelidiki rumus di baris bawah, Anda akan melihat bahwa referensi nilai pencarian telah disesuaikan untuk baris tertentu tersebut, sedangkan larik tabel tidak berubah

Di bawah ini, Anda akan memiliki beberapa tip berguna yang akan menghemat banyak waktu sakit kepala dan pemecahan masalah

Contoh Excel VLOOKUP

Saya harap pencarian vertikal mulai terlihat sedikit lebih akrab bagi Anda. Untuk memperkuat pengetahuan Anda, mari buat beberapa rumus VLOOKUP lagi

Cara Vlookup dari lembar lain di Excel

Dalam praktiknya, fungsi VLOOKUP Excel jarang digunakan dengan data di lembar kerja yang sama. Paling sering Anda harus menarik data yang cocok dari lembar kerja yang berbeda

Untuk Vlookup dari lembar Excel yang berbeda, letakkan nama lembar kerja diikuti dengan tanda seru di argumen table_array sebelum referensi rentang. Misalnya untuk mencari di range A2. B10 di Sheet2, gunakan rumus ini

=VLOOKUP("Product1", Sheet2!A2:B10, 2)_

Tentu saja, Anda tidak perlu mengetikkan nama sheet secara manual. Sederhananya, mulailah mengetik rumus dan ketika sampai pada argumen table_array, alihkan ke lembar kerja pencarian dan pilih rentang menggunakan mouse

Misalnya, ini adalah cara mencari nilai A2 dalam rentang A2. A9 pada lembar kerja Harga dan kembalikan nilai yang cocok dari kolom C

=VLOOKUP(A2, Prices!$A$2:$C$9, 3, FALSE)

Catatan

• Jika nama spreadsheet berisi spasi atau karakter non-abjad, maka harus diapit dengan tanda kutip tunggal, mis. g. 'Daftar Harga'. $A$2. $C$9
• Jika Anda menggunakan rumus VLOOKUP untuk banyak sel, ingatlah untuk mengunci table_array dengan tanda $, seperti $A$2. $C$9

Cara Vlookup dari buku kerja lain di Excel

Untuk Vlookup dari buku kerja Excel yang berbeda, letakkan nama buku kerja yang diapit tanda kurung siku sebelum nama lembar kerja

Misalnya, inilah rumus untuk mencari nilai A2 pada lembar bernama Harga di Daftar_Harga. buku kerja xlsx

=VLOOKUP(A2, [Price_List.xlsx]Prices!$A$2:$C$9, 3, FALSE)_

Jika nama buku kerja atau nama lembar kerja berisi spasi atau karakter non-abjad, Anda harus mengapitnya dengan tanda kutip tunggal seperti ini

=VLOOKUP(A2, '[Price List.xlsx]Prices'!$A$2:$C$9, 3, FALSE)

Cara termudah untuk membuat rumus VLOOKUP yang merujuk ke buku kerja yang berbeda adalah ini

1. Buka kedua file
2. Mulai ketikkan rumus Anda, alihkan ke buku kerja lain, dan pilih larik tabel menggunakan mouse
3. Masukkan argumen yang tersisa dan tekan tombol Enter untuk menyelesaikan rumus Anda

Hasilnya akan terlihat seperti tangkapan layar di bawah ini

Setelah Anda menutup file dengan tabel pencarian, rumus VLOOKUP akan terus berfungsi, tetapi sekarang akan menampilkan jalur lengkap untuk buku kerja yang ditutup

Untuk informasi selengkapnya, silakan lihat Cara merujuk ke lembar atau buku kerja Excel lainnya

Cara Vlookup dari rentang bernama di lembar lain

Jika Anda berencana untuk menggunakan rentang pencarian yang sama di banyak rumus, Anda bisa membuat rentang bernama untuknya dan mengetikkan namanya langsung di argumen table_array

Untuk membuat rentang bernama, cukup pilih sel dan ketikkan nama yang Anda inginkan di kotak Nama di sebelah kiri bilah Rumus. Untuk langkah detailnya silahkan lihat

Untuk contoh ini, kami memberi nama Harga_2020 ke sel data (A2. C9) di lembar pencarian dan dapatkan rumus ringkas ini

=VLOOKUP(A2, Prices_2020, 3, FALSE)_

Sebagian besar nama di Excel berlaku untuk seluruh buku kerja, jadi Anda tidak perlu menentukan nama lembar kerja saat menggunakan rentang bernama

Jika rentang bernama ada di buku kerja lain, letakkan nama buku kerja di depan nama rentang, misalnya

=VLOOKUP(A2, 'Price List.xlsx'!Prices_2020, 3, FALSE)_

Rumus seperti itu jauh lebih mudah dipahami, bukan? . Karena rentang bernama tidak berubah, Anda dapat yakin bahwa susunan tabel Anda akan tetap terkunci di mana pun rumus dipindahkan atau disalin

Jika Anda telah mengonversi rentang pencarian Anda menjadi tabel Excel yang berfungsi penuh, maka Anda dapat melakukan Vlookup berdasarkan nama tabel, mis. g. Price_table dalam rumus di bawah ini

=VLOOKUP(E1, A2:B11, 2, FALSE)_0

Referensi tabel, juga disebut referensi terstruktur, tangguh dan kebal terhadap banyak manipulasi data. Misalnya, Anda dapat menghapus atau menambahkan baris baru ke tabel pencarian Anda tanpa khawatir memperbarui referensi

Cara Vlookup dari lembar lain di Excel

Dalam praktiknya, fungsi VLOOKUP Excel jarang digunakan dengan data di lembar kerja yang sama. Paling sering Anda harus menarik data yang cocok dari lembar kerja yang berbeda

Untuk Vlookup dari lembar Excel yang berbeda, letakkan nama lembar kerja diikuti dengan tanda seru di argumen table_array sebelum referensi rentang. Misalnya untuk mencari di range A2. B10 di Sheet2, gunakan rumus ini

=VLOOKUP("Product1", Sheet2!A2:B10, 2)_

Tentu saja, Anda tidak perlu mengetikkan nama sheet secara manual. Sederhananya, mulailah mengetik rumus dan ketika sampai pada argumen table_array, alihkan ke lembar kerja pencarian dan pilih rentang menggunakan mouse

Misalnya, ini adalah cara mencari nilai A2 dalam rentang A2. A9 pada lembar kerja Harga dan kembalikan nilai yang cocok dari kolom C

=VLOOKUP(A2, Prices!$A$2:$C$9, 3, FALSE)

Catatan

• Jika nama spreadsheet berisi spasi atau karakter non-abjad, maka harus diapit dengan tanda kutip tunggal, mis. g. 'Daftar Harga'. $A$2. $C$9
• Jika Anda menggunakan rumus VLOOKUP untuk banyak sel, ingatlah untuk mengunci table_array dengan tanda $, seperti $A$2. $C$9

Cara Vlookup dari buku kerja lain di Excel

Untuk Vlookup dari buku kerja Excel yang berbeda, letakkan nama buku kerja yang diapit tanda kurung siku sebelum nama lembar kerja

Misalnya, inilah rumus untuk mencari nilai A2 pada lembar bernama Harga di Daftar_Harga. buku kerja xlsx

=VLOOKUP(A2, [Price_List.xlsx]Prices!$A$2:$C$9, 3, FALSE)_

Jika nama buku kerja atau nama lembar kerja berisi spasi atau karakter non-abjad, Anda harus mengapitnya dengan tanda kutip tunggal seperti ini

=VLOOKUP(A2, '[Price List.xlsx]Prices'!$A$2:$C$9, 3, FALSE)

Cara termudah untuk membuat rumus VLOOKUP yang merujuk ke buku kerja yang berbeda adalah ini

1. Buka kedua file
2. Mulai ketikkan rumus Anda, alihkan ke buku kerja lain, dan pilih larik tabel menggunakan mouse
3. Masukkan argumen yang tersisa dan tekan tombol Enter untuk menyelesaikan rumus Anda

Hasilnya akan terlihat seperti tangkapan layar di bawah ini

Setelah Anda menutup file dengan tabel pencarian, rumus VLOOKUP akan terus berfungsi, tetapi sekarang akan menampilkan jalur lengkap untuk buku kerja yang ditutup

Untuk informasi selengkapnya, silakan lihat Cara merujuk ke lembar atau buku kerja Excel lainnya

Cara Vlookup dari rentang bernama di lembar lain

Jika Anda berencana untuk menggunakan rentang pencarian yang sama di banyak rumus, Anda bisa membuat rentang bernama untuknya dan mengetikkan namanya langsung di argumen table_array

Untuk membuat rentang bernama, cukup pilih sel dan ketikkan nama yang Anda inginkan di kotak Nama di sebelah kiri bilah Rumus. Untuk langkah detailnya silahkan lihat

Untuk contoh ini, kami memberi nama Harga_2020 ke sel data (A2. C9) di lembar pencarian dan dapatkan rumus ringkas ini

=VLOOKUP(A2, Prices_2020, 3, FALSE)_

Sebagian besar nama di Excel berlaku untuk seluruh buku kerja, jadi Anda tidak perlu menentukan nama lembar kerja saat menggunakan rentang bernama

Jika rentang bernama ada di buku kerja lain, letakkan nama buku kerja di depan nama rentang, misalnya

=VLOOKUP(A2, 'Price List.xlsx'!Prices_2020, 3, FALSE)_

Rumus seperti itu jauh lebih mudah dipahami, bukan? . Karena rentang bernama tidak berubah, Anda dapat yakin bahwa susunan tabel Anda akan tetap terkunci di mana pun rumus dipindahkan atau disalin

Jika Anda telah mengonversi rentang pencarian Anda menjadi tabel Excel yang berfungsi penuh, maka Anda dapat melakukan Vlookup berdasarkan nama tabel, mis. g. Price_table dalam rumus di bawah ini

=VLOOKUP(E1, A2:B11, 2, FALSE)_0

Referensi tabel, juga disebut referensi terstruktur, tangguh dan kebal terhadap banyak manipulasi data. Misalnya, Anda dapat menghapus atau menambahkan baris baru ke tabel pencarian Anda tanpa khawatir memperbarui referensi

Menggunakan wildcard dalam rumus VLOOKUP

Seperti banyak rumus lainnya, fungsi Excel VLOOKUP menerima karakter wildcard berikut

• Tanda tanya (?) untuk mencocokkan karakter tunggal apa pun
• Asterisk (*) untuk mencocokkan urutan karakter apa pun

Wildcard terbukti sangat berguna dalam banyak situasi

• Ketika Anda tidak ingat teks persis yang Anda cari
• Saat Anda mencari string teks yang merupakan bagian dari konten sel
• Saat kolom pencarian berisi spasi awal atau akhir. Dalam hal ini, Anda mungkin memeras otak untuk mencari tahu mengapa formula normal tidak bekerja

Contoh 1. Cari teks yang dimulai atau diakhiri dengan karakter tertentu

Misalkan Anda ingin mencari pelanggan tertentu di database di bawah ini. Anda tidak ingat nama belakangnya, tetapi Anda yakin bahwa itu dimulai dengan "ack"

Untuk mengembalikan nama belakang dari kolom A, gunakan rumus wildcard Vlookup berikut

=VLOOKUP(E1, A2:B11, 2, FALSE)_8

Untuk mengambil kunci lisensi dari kolom B, gunakan yang ini (perbedaannya hanya pada nomor indeks kolom)

=VLOOKUP(E1, A2:B11, 2, FALSE)_9

Anda juga dapat memasukkan bagian nama yang diketahui di beberapa sel, misalnya E1, dan menggabungkan karakter wildcard dengan referensi sel

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)0

Tangkapan layar di bawah ini menunjukkan hasilnya

Di bawah ini adalah beberapa rumus VLOOKUP lainnya dengan wildcard

Temukan nama belakang yang diakhiri dengan "anak"

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)1

Dapatkan nama yang dimulai dengan "joh" dan diakhiri dengan "son"

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)2

Tarik nama belakang 5 karakter

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)_3

Contoh 2. Wildcard VLOOKUP berdasarkan nilai sel

Dari contoh sebelumnya, Anda sudah mengetahui bahwa menggabungkan ampersand (&) dan referensi sel dapat dilakukan untuk membuat string pencarian. Untuk menemukan nilai yang berisi karakter tertentu di posisi mana pun, beri ampersand sebelum dan sesudah referensi sel

Katakanlah, Anda ingin mendapatkan nama yang sesuai dengan kunci lisensi tertentu, tetapi Anda tidak mengetahui seluruh kunci, hanya beberapa karakter. Dengan kunci di kolom A, nama di kolom B, dan bagian dari kunci target di E1, Anda dapat melakukan Vlookup wildcard dengan cara ini

Ekstrak kuncinya

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)_4

Ekstrak namanya

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)5

Catatan

• Agar rumus VLOOKUP wildcard berfungsi dengan benar, gunakan pencocokan persis (FALSE adalah argumen terakhir)
• Jika lebih dari satu kecocokan ditemukan, yang pertama dikembalikan

VLOOKUP BENAR vs SALAH

Dan sekarang, saatnya untuk melihat lebih dekat pada argumen terakhir dari fungsi Excel VLOOKUP. Meskipun opsional, parameter range_lookup sangat penting. Bergantung pada apakah Anda memilih BENAR atau SALAH, rumus Anda mungkin menghasilkan hasil yang berbeda

Contoh 1. Bagaimana melakukan pencocokan persis Vlookup

Untuk mencari kecocokan yang tepat, masukkan saja FALSE di argumen terakhir

Untuk contoh ini, mari ambil tabel kecepatan hewan, tukar kolom, dan coba temukan hewan yang dapat berlari 80, 50, dan 30 mil per jam. Dengan nilai pencarian di D2, D3 dan D4, masukkan rumus di bawah ini di E2, lalu salin ke dua sel lagi

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)6

Seperti yang Anda lihat, rumus mengembalikan "Lion" di E3 karena berjalan tepat 50 per jam. Untuk dua nilai pencarian lainnya, pencocokan persis tidak ditemukan, dan kesalahan #N/A muncul

Contoh 2. Cara Vlookup untuk perkiraan kecocokan

Untuk mencari perkiraan kecocokan, ada dua hal penting yang perlu Anda lakukan

• Urutkan kolom pertama table_array dari terkecil ke terbesar
• Gunakan BENAR untuk argumen range_lookup atau hilangkan

Mengurutkan kolom pencarian sangat penting karena fungsi VLOOKUP berhenti mencari segera setelah menemukan kecocokan yang lebih kecil dari nilai pencarian. Jika data tidak diurutkan dengan benar, Anda mungkin akan mendapatkan hasil yang sangat aneh atau banyak kesalahan #N/A

Untuk data sampel kami, perkiraan pencocokan rumus Vlookup adalah sebagai berikut

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)7

Dan mengembalikan hasil berikut

• Untuk nilai pencarian "80", "Cheetah" dikembalikan karena kecepatannya (70) adalah kecocokan terdekat yang lebih kecil dari nilai pencarian
• Untuk nilai pencarian "50", pencocokan persis dikembalikan (Lion)
• Untuk nilai pencarian "30", kesalahan #N/A dikembalikan karena nilai pencarian kurang dari nilai terkecil di kolom pencarian

Contoh 2. Cara Vlookup untuk perkiraan kecocokan

Untuk mencari perkiraan kecocokan, ada dua hal penting yang perlu Anda lakukan

• Urutkan kolom pertama table_array dari terkecil ke terbesar
• Gunakan BENAR untuk argumen range_lookup atau hilangkan

Mengurutkan kolom pencarian sangat penting karena fungsi VLOOKUP berhenti mencari segera setelah menemukan kecocokan yang lebih kecil dari nilai pencarian. Jika data tidak diurutkan dengan benar, Anda mungkin akan mendapatkan hasil yang sangat aneh atau banyak kesalahan #N/A

Untuk data sampel kami, perkiraan pencocokan rumus Vlookup adalah sebagai berikut

=VLOOKUP($E2, $A$2:$C$11, 2, FALSE)7

Dan mengembalikan hasil berikut

• Untuk nilai pencarian "80", "Cheetah" dikembalikan karena kecepatannya (70) adalah kecocokan terdekat yang lebih kecil dari nilai pencarian
• Untuk nilai pencarian "50", pencocokan persis dikembalikan (Lion)
• Untuk nilai pencarian "30", kesalahan #N/A dikembalikan karena nilai pencarian kurang dari nilai terkecil di kolom pencarian

Alat khusus untuk Vlookup di Excel

Tidak diragukan lagi, VLOOKUP adalah salah satu fungsi Excel yang paling kuat dan berguna, tetapi juga salah satu yang paling membingungkan. Untuk membuat kurva pembelajaran tidak terlalu curam dan pengalaman lebih menyenangkan, kami menyertakan beberapa alat penghemat waktu di Ultimate Suite for Excel kami