Kiat. Coba gunakan fungsi XLOOKUP baru, versi VLOOKUP yang disempurnakan yang bekerja ke segala arah dan mengembalikan kecocokan persis secara default, lebih mudah dan nyaman digunakan daripada pendahulunya Gunakan VLOOKUP saat Anda perlu menemukan sesuatu di tabel atau rentang per baris. Misalnya mencari harga komponen otomotif berdasarkan nomor komponen, atau mencari nama karyawan berdasarkan ID karyawan Dalam bentuknya yang paling sederhana, kata fungsi VLOOKUP =VLOOKUP(Apa yang ingin Anda cari, di mana Anda ingin mencarinya, nomor kolom dalam rentang yang berisi nilai yang akan dikembalikan, mengembalikan hasil Terdekat atau Tepat – ditampilkan sebagai 1/BENAR, atau 0/FALSE)
Kiat. Rahasia VLOOKUP adalah mengatur data Anda sehingga nilai yang Anda cari (Buah) berada di sebelah kiri nilai yang dikembalikan (Jumlah) yang ingin Anda temukan
Detail teknis
Gunakan fungsi VLOOKUP untuk menemukan nilai dalam tabel
Sintaksis
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Sebagai contoh
=VLOOKUP(A2,A10. C20,2, BENAR)
=VLOOKUP("Febriansyah",B2. E7,2,SALAH)
=VLOOKUP(A2,'Detail Klien'. SEBUAH. F,3,SALAH)
Nama argumen
Keterangan
lookup_value (wajib)
Nilai yang ingin Anda temukan. Nilai yang ingin Anda temukan harus berada di kolom pertama rentang sel yang Anda tentukan dalam argumen table_array
Misalnya, jika tabel-array menyertakan sel B2. D7, maka lookup_value Anda harus berada di kolom B
Lookup_value bisa berupa nilai atau referensi ke sel
table_array (wajib)
Rentang sel yang akan dicari oleh VLOOKUP untuk lookup_value dan nilai kembalian. Anda bisa menggunakan rentang atau tabel bernama, dan Anda bisa menggunakan nama dalam argumen, bukan referensi sel.
Kolom pertama dalam rentang sel harus berisi lookup_value. Rentang sel juga perlu menyertakan nilai yang dikembalikan yang ingin Anda temukan
Pelajari cara memilih rentang di lembar kerja
col_index_num (wajib)
Nomor kolom (dimulai dengan 1 untuk kolom paling kiri dari table_array) yang berisi nilai yang dikembalikan
range_lookup (opsional)
Nilai logis yang menentukan apakah Anda ingin VLOOKUP menemukan hasil yang persis sama atau mendekati
Estimasi kecocokan - 1/TRUE mengasumsikan kolom pertama dalam tabel diurutkan secara numerik atau alfabet, kemudian akan dicari nilai terdekat. Ini adalah metode default jika Anda tidak menentukannya. Misalnya, =VLOOKUP(90,A1. B100,2, BENAR)
Pencocokan tepat - 0/FALSE mencari nilai tepat di kolom pertama. Misalnya, =VLOOKUP("Smith",A1. B100,2,SALAH)
Bagaimana cara memulai
Ada empat informasi yang akan dibutuhkan untuk membangun sintaks VLOOKUP
Nilai yang ingin Anda temukan, juga disebut nilai pencarian
Rentang tempat nilai pencarian berada. Ingatlah bahwa nilai pencarian harus selalu berada di kolom pertama dalam rentang agar VLOOKUP berfungsi dengan benar. Misalnya, jika nilai pencarian ada di sel C2, rentang Anda harus dimulai dengan C
Nomor kolom dalam rentang yang berisi nilai kembalian. Misalnya, jika Anda menentukan B2. D11 sebagai rentang, Anda harus menghitung B sebagai kolom pertama, C sebagai kolom kedua, dan seterusnya
Opsional, Anda dapat menentukan TRUE jika Anda menginginkan hasil yang mendekati atau FALSE jika Anda menginginkan hasil yang sama persis dari nilai kembalian. Jika Anda tidak menentukan apa pun, nilai default akan selalu BENAR atau sesuatu yang mendekatinya
Sekarang, letakkan semua hal yang disebutkan di atas sebagai berikut
=VLOOKUP(nilai pencarian, rentang yang berisi nilai pencarian, nomor kolom dalam rentang yang berisi nilai yang dikembalikan, Pencocokan Perkiraan (BENAR) atau Pencocokan Persis (FALSE))
Contoh
Berikut beberapa contoh VLOOKUP
Contoh 1
Contoh 2
Contoh 3
Contoh 4
Contoh 5
Gabungkan data dari beberapa tabel menjadi satu lembar kerja menggunakan VLOOKUP
Anda dapat menggunakan VLOOKUP untuk menggabungkan beberapa tabel menjadi satu, asalkan salah satu tabel memiliki kolom yang sama dengan semua tabel lainnya. Ini bisa sangat berguna jika Anda perlu berbagi buku kerja dengan orang yang memiliki versi Excel lebih lama yang tidak mendukung fitur data dengan beberapa tabel sebagai sumber data - dengan menggabungkan sumber ke dalam satu tabel dan mengubah sumber data dari fitur data ke tabel baru, data fitur dapat digunakan di versi Excel yang lebih lama (selama fitur data itu sendiri didukung oleh versi yang lebih lama)
Disini kolom A-F dan H memiliki nilai atau rumus yang hanya menggunakan nilai pada lembar kerja, dan kolom lainnya menggunakan VLOOKUP dan nilai kolom A (Kode Klien) dan kolom B (Pengacara) untuk mendapatkan data dari tabel lainnya
Salin tabel yang memiliki bidang umum ke lembar kerja baru, dan beri nama
Klik Data > Alat Data > Hubungan untuk membuka kotak dialog Kelola Hubungan
Untuk setiap hubungan yang terdaftar, perhatikan yang berikut ini
Bidang yang menautkan tabel (tercantum dalam tanda kurung di kotak dialog). Ini adalah lookup_value untuk rumus VLOOKUP Anda
Nama Tabel Pencarian Terkait. Ini adalah table_array dalam rumus VLOOKUP Anda
Bidang (kolom) di Tabel Pencarian Terkait yang memiliki data yang Anda inginkan di kolom baru Anda. Informasi ini tidak ditampilkan dalam dialog Kelola Kontak - Anda harus melihat Tabel Pencarian Terkait untuk mengetahui bidang mana yang ingin Anda ambil. Anda ingin mencatat nomor kolom (A=1) - ini adalah col_index_num dalam rumus Anda
Untuk menambahkan bidang ke tabel baru, masukkan rumus VLOOKUP Anda di kolom kosong pertama menggunakan informasi yang Anda kumpulkan di langkah 3
Dalam contoh kita, kolom G menggunakan Pengacara ( lookup_value ) untuk mendapatkan data Billing Tariff dari kolom keempat (col_index_num = 4) tabel lembar kerja Pengacara, tblAttorneys ( table_array ), dengan rumus =VLOOKUP([@Attorney],tbl_Attorneys, 4, SALAH)
Rumus juga dapat menggunakan referensi sel dan referensi rentang. Dalam contoh kita, itu adalah =VLOOKUP(A2,'Pengacara'. SEBUAH. D,4,SALAH)
Lanjutkan menambahkan bidang hingga Anda memiliki semua bidang yang diperlukan. Jika Anda mencoba menyiapkan buku kerja yang berisi fitur data yang menggunakan beberapa tabel, ubah sumber data fitur data ke tabel baru
Masalah umum
Masalah
Apa yang salah
Nilai yang dikembalikan salah
Jika range_lookup BENAR atau dihilangkan, kolom pertama harus diurutkan menurut abjad. Jika kolom pertama tidak diurutkan, nilai pengembaliannya mungkin sesuatu yang tidak Anda harapkan. Urutkan kolom itu terlebih dahulu, atau gunakan FALSE untuk pencocokan persis
#N/A dalam sel
Jika range_lookup adalah TRUE, maka jika nilai pada lookup_value lebih kecil dari nilai terkecil pada kolom pertama table_array, Anda akan mendapatkan nilai error #N/A
Jika range_lookup adalah FALSE, nilai kesalahan #N/A menunjukkan angka persisnya tidak ditemukan
Untuk informasi selengkapnya tentang memperbaiki kesalahan #N/A di VLOOKUP, lihat Cara memperbaiki kesalahan #N/A di fungsi VLOOKUP
#REF. di dalam sel
Jika col_index_num lebih besar dari jumlah kolom dalam array-tabel, Anda akan mendapatkan #REF. nilai kesalahan
Untuk informasi lebih lanjut tentang mengatasi #REF. di VLOOKUP, lihat Cara memperbaiki kesalahan #REF
#NILAI. di dalam sel
Jika table_array kurang dari 1, Anda akan mendapatkan #VALUE. nilai kesalahan
Untuk informasi lengkap mengatasi error #VALUE. di VLOOKUP, lihat Cara memperbaiki kesalahan #VALUE. dalam fungsi VLOOKUP
#NAMA?
Nilai kesalahan #NAME? . Untuk menemukan nama seseorang, pastikan Anda menggunakan tanda kutip di sekitar nama dalam rumus. Misalnya, masukkan nama sebagai "Febriansyah" di =VLOOKUP("Febriansyah", B2. E7,2,SALAH)
Untuk informasi selengkapnya, lihat Cara memperbaiki kesalahan #NAME
#meluap kesalahan. di dalam sel
kesalahan #SPILL. Ini biasanya berarti bahwa rumus Anda bergantung pada irisan implisit untuk nilai pencarian, dan menggunakan seluruh kolom sebagai referensi. Misalnya, =VLOOKUP(A. A A. C,2,SALAH). Anda dapat mengatasi masalah tersebut dengan menutup referensi pencarian dengan operator @ seperti ini. =VLOOKUP(@A. A A. C,2,SALAH). Atau, Anda dapat menggunakan metode VLOOKUP tradisional dan merujuk ke satu sel, bukan seluruh kolom. =VLOOKUP(A2,A. C,2,SALAH)
Praktik terbaik
Melakukan hal ini
Mengapa
Gunakan referensi absolut untuk range_lookup
Menggunakan referensi absolut memungkinkan Anda mengisi rumus sehingga rumus selalu mencari rentang pencarian yang sama
Pelajari cara menggunakan referensi sel absolut
Jangan simpan nomor atau tanggal sebagai teks
Saat mencari nilai numerik atau tanggal, pastikan data di kolom pertama table_array tidak disimpan sebagai nilai teks. Jika tidak, VLOOKUP dapat mengembalikan nilai yang salah atau tidak diharapkan
Urutkan kolom pertama
Urutkan kolom pertama dari table_array sebelum menggunakan VLOOKUP ketika range_lookup adalah BENAR
Gunakan karakter wildcard
Jika range_lookup adalah FALSE dan lookup_value adalah teks, Anda dapat menggunakan karakter wildcard—tanda tanya (?) dan tanda bintang (*)—dalam lookup_value. Tanda tanya cocok dengan karakter tunggal apa pun. Tanda bintang cocok dengan urutan karakter apa pun. Jika Anda ingin menemukan tanda tanya atau tanda bintang yang sebenarnya, ketikkan tanda gelombang (~) sebelum karakter
Misalnya, =VLOOKUP("Font?",B2. E7,2,FALSE) akan menemukan semua contoh Fontana dengan variabel huruf terakhir
Pastikan data Anda tidak mengandung karakter yang salah
Saat mencari nilai teks di kolom pertama, pastikan data di kolom pertama tidak memiliki spasi awal, spasi akhir, penggunaan tanda kutip lurus ( ' atau " ) dan kurva ( ' atau " yang tidak konsisten), atau karakter yang tidak dapat dicetak. Dalam hal ini, VLOOKUP dapat mengembalikan nilai yang salah atau tidak diharapkan
Untuk mendapatkan hasil yang akurat, coba gunakan fungsi CLEAN atau fungsi TRIM untuk menghapus spasi tambahan setelah nilai tabel di dalam sel
butuh lebih banyak bantuan?
Anda dapat bertanya kapan saja kepada pakar di Komunitas Teknologi Excel atau mendapatkan dukungan di Komunitas Jawaban