Fungsi VLOOKUP di Excel – Panduan Pemula: Sintaks dan Contoh

Konten

Fungsi VLOOKUP di Excel – deskripsi umum dan sintaks
- Sintaks fungsi VLOOKUP
Contoh VLOOKUP
Kecocokan persis atau perkiraan dalam fungsi VLOOKUP
- Contoh 1: Menemukan Kecocokan yang Tepat dengan VLOOKUP
- Contoh 2: Menggunakan VLOOKUP untuk Menemukan Kecocokan Perkiraan
VLOOKUP di Excel – Anda harus mengingat ini!

Hari ini kita memulai serangkaian artikel yang menjelaskan salah satu fitur Excel yang paling berguna VPR (VLOOKUP). Fungsi ini, pada saat yang sama, adalah salah satu yang paling kompleks dan paling sedikit dipahami.

Dalam tutorial ini di VPR Saya akan mencoba menjelaskan dasar-dasarnya sesederhana mungkin untuk membuat proses pembelajaran sejelas mungkin bagi pengguna yang tidak berpengalaman. Selain itu, kita akan mempelajari beberapa contoh dengan rumus Excel yang akan menunjukkan kasus penggunaan paling umum untuk fungsi tersebut VPR.

Fungsi VLOOKUP di Excel – deskripsi umum dan sintaks

Jadi apa itu? VPR? Pertama-tama, ini adalah fungsi Excel. Apa yang dia lakukan? Itu mencari nilai yang Anda tentukan dan mengembalikan nilai yang sesuai dari kolom lain. Secara teknis, VPR mencari nilai di kolom pertama dari rentang yang diberikan dan mengembalikan hasil dari kolom lain di baris yang sama.

Dalam aplikasi yang paling umum, fungsi VPR mencari database untuk pengidentifikasi unik yang diberikan dan mengekstrak beberapa informasi yang terkait dengannya dari database.

Huruf pertama dalam nama fungsi VPR (VLOOKUP) artinya Вvertikal (Vvertikal). Dengan itu Anda dapat membedakan VPR dari GPR (HLOOKUP), yang mencari nilai di baris atas rentang Гmendatar (Hsecara horizontal).

fungsi VPR tersedia di Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, dan Excel 2000.

Sintaks fungsi VLOOKUP

fungsi VPR (VLOOKUP) memiliki sintaks berikut:

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

ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Seperti yang Anda lihat, sebuah fungsi VPR di Microsoft Excel memiliki 4 opsi (atau argumen). Tiga yang pertama adalah wajib, yang terakhir adalah opsional.

nilai lookup (lookup_value) – Nilai yang akan dicari. Ini bisa berupa nilai (angka, tanggal, teks) atau referensi sel (berisi nilai pencarian), atau nilai yang dikembalikan oleh beberapa fungsi Excel lainnya. Misalnya, rumus ini akan mencari nilainya 40:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

Jika nilai pencarian kurang dari nilai terkecil di kolom pertama dari rentang yang dicari, fungsi VPR akan melaporkan kesalahan #PADA (#T/T).

table_array (tabel) – dua atau lebih kolom data. Ingat, fungsi VPR selalu mencari nilai di kolom pertama dari rentang yang diberikan dalam argumen table_array (meja). Rentang yang dapat dilihat dapat berisi berbagai data, seperti teks, tanggal, angka, boolean. Fungsinya tidak peka huruf besar/kecil, artinya karakter huruf besar dan kecil dianggap sama. Jadi rumus kita akan mencari nilainya 40 dalam sel dari A2 untuk A15, karena A adalah kolom pertama dari rentang A2:B15 yang diberikan dalam argumen table_array (meja):
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)

col_index_num (column_number) adalah jumlah kolom dalam rentang yang diberikan dari mana nilai dalam baris yang ditemukan akan dikembalikan. Kolom paling kiri dalam rentang yang diberikan adalah 1, kolom kedua adalah 2, kolom ketiga adalah 3 dan seterusnya. Sekarang Anda dapat membaca seluruh rumus:
=VLOOKUP(40,A2:B15,2)
=ВПР(40;A2:B15;2)
Rumus mencari nilai 40 dalam jangkauan A2: A15 dan mengembalikan nilai yang sesuai dari kolom B (karena B adalah kolom kedua dalam rentang A2:B15).

Jika nilai argumen col_index_num (nomor_kolom) kurang dari 1kemudian VPR akan melaporkan kesalahan #NILAI! (#NILAI!). Dan jika lebih dari jumlah kolom dalam rentang table_array (tabel), fungsi akan mengembalikan kesalahan # REF! (#TAUTAN!).

rentang_pencarian (range_lookup) – menentukan apa yang harus dicari:
- sama persis, argumen harus sama SALAH (SALAH);
- perkiraan kecocokan, argumen sama dengan KODE BENAR (TRUE) atau tidak ditentukan sama sekali.
Parameter ini opsional, tetapi sangat penting. Nanti di tutorial ini di VPR Saya akan menunjukkan kepada Anda beberapa contoh yang menjelaskan cara menulis rumus untuk menemukan kecocokan tepat dan perkiraan.

Contoh VLOOKUP

Semoga fungsinya VPR menjadi sedikit lebih jelas bagi Anda. Sekarang mari kita lihat beberapa kasus penggunaan VPR dalam rumus dengan data nyata.

Cara menggunakan VLOOKUP untuk mencari di lembar Excel lain

Dalam praktiknya, rumus dengan fungsi VPR jarang digunakan untuk mencari data pada lembar kerja yang sama. Lebih sering daripada tidak, Anda akan mencari dan mengambil nilai yang sesuai dari lembar lain.

Untuk menggunakan VPR, cari di lembar Microsoft Excel lain, Anda harus dalam argumen table_array (tabel) tentukan nama lembar dengan tanda seru diikuti dengan rentang sel. Misalnya, rumus berikut menunjukkan bahwa jangkauan A2: B15 ada di lembar bernama Sheet2.

=VLOOKUP(40,Sheet2!A2:B15,2)

=ВПР(40;Sheet2!A2:B15;2)

Tentu saja, nama lembar tidak harus dimasukkan secara manual. Mulailah mengetik rumus, dan ketika sampai pada argumen table_array (tabel), beralih ke lembar yang diinginkan dan pilih rentang sel yang diinginkan dengan mouse.

Rumus yang ditunjukkan pada tangkapan layar di bawah ini mencari teks "Produk 1" di kolom A (ini adalah kolom pertama dari rentang A1:B2) pada lembar kerja harga.

=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)

=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Harap diingat bahwa saat mencari nilai teks, Anda harus menyertakannya dalam tanda kutip (“”), seperti yang biasanya dilakukan dalam rumus Excel.

Untuk argumen table_array (tabel) diinginkan untuk selalu menggunakan referensi absolut (dengan tanda $). Dalam hal ini, rentang pencarian akan tetap tidak berubah saat menyalin rumus ke sel lain.

Cari di buku kerja lain dengan VLOOKUP

Untuk berfungsi VPR bekerja di antara dua buku kerja Excel, Anda perlu menentukan nama buku kerja dalam tanda kurung siku sebelum nama lembar.

Misalnya, di bawah ini adalah rumus yang mencari nilai 40 di atas lembaran Sheet2 Dalam buku Bilangan.xlsx:

=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)

=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)

Berikut adalah cara termudah untuk membuat rumus di Excel dengan VPRyang menautkan ke buku kerja lain:

Buka kedua buku. Ini tidak diperlukan, tetapi lebih mudah untuk membuat formula dengan cara ini. Anda tidak ingin memasukkan nama buku kerja secara manual, bukan? Selain itu, ini akan melindungi Anda dari kesalahan ketik yang tidak disengaja.
Mulai mengetik fungsi VPRdan ketika sampai pada argumen table_array (tabel), beralih ke buku kerja lain dan pilih rentang pencarian yang diperlukan di dalamnya.

Tangkapan layar di bawah ini menunjukkan rumus dengan pencarian yang disetel ke rentang di buku kerja Daftar Harga.xlsx di atas lembaran harga.

fungsi VPR akan berfungsi bahkan saat Anda menutup buku kerja yang dicari dan jalur lengkap ke file buku kerja muncul di bilah rumus, seperti yang diperlihatkan di bawah ini:

Jika nama buku kerja atau lembar berisi spasi, maka harus diapit dengan apostrof:

=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)

=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)

Cara menggunakan rentang atau tabel bernama dalam rumus dengan VLOOKUP

Jika Anda berencana untuk menggunakan rentang pencarian yang sama di beberapa fungsi VPR, Anda dapat membuat rentang bernama dan memasukkan namanya ke dalam rumus sebagai argumen table_array (meja).

Untuk membuat rentang bernama, cukup pilih sel dan masukkan nama yang sesuai di bidang Nama Depan, di sebelah kiri bilah rumus.

Sekarang Anda dapat menuliskan rumus berikut untuk mencari harga suatu produk: Produk 1:

=VLOOKUP("Product 1",Products,2)

=ВПР("Product 1";Products;2)

Sebagian besar nama rentang berfungsi untuk seluruh buku kerja Excel, jadi tidak perlu menentukan nama lembar untuk argumen table_array (tabel), meskipun rumus dan rentang pencarian berada di lembar kerja yang berbeda. Jika mereka berada di buku kerja yang berbeda, maka sebelum nama rentang Anda perlu menentukan nama buku kerja, misalnya, seperti ini:

=VLOOKUP("Product 1",PriceList.xlsx!Products,2)

=ВПР("Product 1";PriceList.xlsx!Products;2)

Jadi rumusnya terlihat lebih jelas, setuju? Selain itu, menggunakan rentang bernama adalah alternatif yang baik untuk referensi absolut karena rentang bernama tidak berubah saat Anda menyalin rumus ke sel lain. Ini berarti Anda dapat yakin bahwa rentang pencarian dalam rumus akan selalu tetap benar.

Jika Anda mengubah rentang sel menjadi spreadsheet Excel lengkap menggunakan perintah tabel (Tabel) tab Insersi (Sisipkan), lalu saat Anda memilih rentang dengan mouse, Microsoft Excel akan secara otomatis menambahkan nama kolom (atau nama tabel jika Anda memilih seluruh tabel) ke rumus.

Rumus yang sudah jadi akan terlihat seperti ini:

=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)

=ВПР("Product 1";Table46[[Product]:[Price]];2)

Atau bahkan mungkin seperti ini:

=VLOOKUP("Product 1",Table46,2)

=ВПР("Product 1";Table46;2)

Saat menggunakan rentang bernama, tautan akan mengarah ke sel yang sama di mana pun Anda menyalin fungsinya VPR dalam buku kerja.

Menggunakan Wildcard dalam Rumus VLOOKUP

Seperti banyak fungsi lainnya, VPR Anda dapat menggunakan karakter wildcard berikut:

Tanda tanya (?) – menggantikan karakter tunggal apa pun.
Asterisk (*) – menggantikan urutan karakter apa pun.

Menggunakan Wildcard dalam Fungsi VPR dapat berguna dalam banyak kasus, misalnya:

Ketika Anda tidak ingat persis teks yang perlu Anda temukan.
Bila Anda ingin menemukan beberapa kata yang merupakan bagian dari konten sel. Tahu bahwa VPR mencari berdasarkan isi sel secara keseluruhan, seolah-olah opsi diaktifkan Cocokkan seluruh konten sel (Seluruh sel) dalam pencarian Excel standar.
Saat sel berisi spasi ekstra di awal atau akhir konten. Dalam situasi seperti itu, Anda dapat memutar otak untuk waktu yang lama, mencoba mencari tahu mengapa formula itu tidak berhasil.

Contoh 1: Mencari teks yang diawali atau diakhiri dengan karakter tertentu

Katakanlah Anda ingin mencari pelanggan tertentu dalam database yang ditunjukkan di bawah ini. Anda tidak ingat nama belakangnya, tetapi Anda tahu bahwa itu dimulai dengan "ack". Berikut adalah formula yang akan melakukan pekerjaan dengan baik:

=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)

=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Sekarang setelah Anda yakin telah menemukan nama yang benar, Anda dapat menggunakan rumus yang sama untuk menemukan jumlah yang dibayarkan oleh pelanggan ini. Untuk melakukan ini, cukup ubah argumen ketiga dari fungsi VPR ke nomor kolom yang diinginkan. Dalam kasus kami, ini adalah kolom C (ke-3 dalam rentang):

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

=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Berikut adalah beberapa contoh lagi dengan wildcard:

~ Temukan nama yang diakhiri dengan "pria":

=VLOOKUP("*man",$A$2:$C$11,1,FALSE)

=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Temukan nama yang dimulai dengan "iklan" dan diakhiri dengan "anak":

=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)

=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Kami menemukan nama depan dalam daftar, yang terdiri dari 5 karakter:

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

=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

Untuk berfungsi VPR dengan wildcard bekerja dengan benar, sebagai argumen keempat yang harus selalu Anda gunakan SALAH (SALAH). Jika rentang pencarian berisi lebih dari satu nilai yang cocok dengan istilah pencarian dengan karakter pengganti, maka nilai pertama yang ditemukan akan dikembalikan.

Contoh 2: Gabungkan wildcard dan referensi sel dalam rumus VLOOKUP

Sekarang mari kita lihat contoh yang sedikit lebih rumit tentang cara mencari menggunakan fungsi VPR dengan nilai dalam sel. Bayangkan bahwa kolom A adalah daftar kunci lisensi, dan kolom B adalah daftar nama yang memiliki lisensi. Selain itu, Anda memiliki bagian (beberapa karakter) dari beberapa jenis kunci lisensi di sel C1, dan Anda ingin menemukan nama pemiliknya.

Ini dapat dilakukan dengan menggunakan rumus berikut:

=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)

=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Rumus ini mencari nilai dari sel C1 dalam rentang yang diberikan dan mengembalikan nilai yang sesuai dari kolom B. Perhatikan bahwa dalam argumen pertama, kita menggunakan karakter ampersand (&) sebelum dan sesudah referensi sel untuk menautkan string teks.

Seperti yang Anda lihat pada gambar di bawah ini, fungsinya VPR mengembalikan "Jeremy Hill" karena kunci lisensinya berisi urutan karakter dari sel C1.

Perhatikan bahwa argumen table_array (tabel) pada tangkapan layar di atas berisi nama tabel (Tabel7) alih-alih menentukan rentang sel. Inilah yang kami lakukan pada contoh sebelumnya.

Kecocokan persis atau perkiraan dalam fungsi VLOOKUP

Dan akhirnya, mari kita lihat lebih dekat argumen terakhir yang ditentukan untuk fungsi VPR - rentang_pencarian (interval_view). Seperti disebutkan di awal pelajaran, argumen ini sangat penting. Anda bisa mendapatkan hasil yang sama sekali berbeda dalam formula yang sama dengan nilainya KODE BENAR (Benar atau SALAH (SALAH).

Pertama, mari kita cari tahu apa yang dimaksud dengan Microsoft Excel dengan pencocokan tepat dan perkiraan.

Jika argumen rentang_pencarian (range_lookup) sama dengan SALAH (FALSE), rumus mencari kecocokan yang tepat, yaitu nilai yang sama persis seperti yang diberikan dalam argumen nilai lookup (nilai lookup). Jika di kolom pertama dari rentang tmampu_array (tabel) menemukan dua atau lebih nilai yang cocok dengan argumen nilai lookup (nilai_pencarian), maka yang pertama akan dipilih. Jika tidak ada kecocokan yang ditemukan, fungsi akan melaporkan kesalahan #PADA (#T/A). Misalnya, rumus berikut akan melaporkan kesalahan: #PADA (#T/A) jika tidak ada nilai dalam rentang A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)
=ВПР(4;A2:B15;2;ЛОЖЬ)
Jika argumen rentang_pencarian (range_lookup) sama dengan KODE BENAR (TRUE), rumus mencari kecocokan perkiraan. Lebih tepatnya, pertama fungsi VPR mencari kecocokan persis, dan jika tidak ada yang ditemukan, pilih salah satu perkiraan. Kecocokan perkiraan adalah nilai terbesar yang tidak melebihi nilai yang ditentukan dalam argumen. nilai lookup (nilai lookup).

Jika argumen rentang_pencarian (range_lookup) sama dengan KODE BENAR (TRUE) atau tidak ditentukan, maka nilai pada kolom pertama rentang harus diurutkan dalam urutan menaik, yaitu dari terkecil ke terbesar. Jika tidak, fungsi VPR dapat mengembalikan hasil yang salah.

Untuk lebih memahami pentingnya pilihan KODE BENAR (BENAR) atau SALAH (SALAH), mari kita lihat beberapa rumus lagi dengan fungsi VPR dan lihat hasilnya.

Contoh 1: Menemukan Kecocokan yang Tepat dengan VLOOKUP

Seperti yang Anda ingat, untuk mencari kecocokan yang tepat, argumen keempat dari fungsi VPR seharusnya penting SALAH (SALAH).

Mari kita kembali ke tabel dari contoh pertama dan mencari tahu hewan mana yang dapat bergerak dengan kecepatan 50 mil per jam. Saya percaya bahwa formula ini tidak akan menyebabkan Anda kesulitan:

=VLOOKUP(50,$A$2:$B$15,2,FALSE)

=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Perhatikan bahwa rentang pencarian kami (kolom A) berisi dua nilai 50 – dalam sel A5 и A6. Rumus mengembalikan nilai dari sel B5. Mengapa? Karena ketika mencari kecocokan yang tepat, fungsinya VPR menggunakan nilai pertama yang ditemukan yang cocok dengan yang dicari.

Contoh 2: Menggunakan VLOOKUP untuk Menemukan Kecocokan Perkiraan

Saat Anda menggunakan fungsi VPR untuk mencari kecocokan perkiraan, yaitu ketika argumen rentang_pencarian (range_lookup) sama dengan KODE BENAR (BENAR) atau dihilangkan, hal pertama yang perlu Anda lakukan adalah mengurutkan rentang menurut kolom pertama dalam urutan menaik.

Hal ini sangat penting karena fungsinya VPR mengembalikan nilai terbesar berikutnya setelah yang diberikan, dan kemudian pencarian berhenti. Jika Anda mengabaikan penyortiran yang benar, Anda akan mendapatkan hasil yang sangat aneh atau pesan kesalahan. #PADA (#T/T).

Sekarang Anda dapat menggunakan salah satu rumus berikut:

=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)

=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)

Seperti yang Anda lihat, saya ingin mencari tahu hewan mana yang memiliki kecepatan paling dekat 69 mil per jam. Dan inilah hasil fungsinya dikembalikan kepada saya VPR:

Seperti yang Anda lihat, rumus mengembalikan hasil Antelope (Antelope), yang kecepatannya 61 mil per jam, meskipun daftarnya juga termasuk Cheetah (Cheetah) yang berlari dengan kecepatan 70 mil per jam, dan 70 lebih dekat ke 69 daripada 61, bukan? Mengapa ini terjadi? Karena fungsinya VPR saat mencari kecocokan perkiraan, mengembalikan nilai terbesar yang tidak lebih besar dari yang dicari.

Saya harap contoh-contoh ini menjelaskan cara bekerja dengan fungsi VPR di Excel, dan Anda tidak lagi melihatnya sebagai orang luar. Sekarang tidak ada salahnya untuk mengulangi secara singkat poin-poin penting dari materi yang telah kita pelajari agar lebih baik dalam ingatan.

VLOOKUP di Excel – Anda harus mengingat ini!

fungsi VPR Excel tidak bisa melihat ke kiri. Itu selalu mencari nilai di kolom paling kiri dari rentang yang diberikan oleh argumen table_array (meja).
Sedang berfungsi VPR semua nilai tidak peka huruf besar/kecil, yaitu huruf kecil dan besar setara.
Jika nilai yang Anda cari kurang dari nilai minimum di kolom pertama dari rentang yang dicari, fungsi VPR akan melaporkan kesalahan #PADA (#T/T).
Jika argumen ke-3 col_index_num (nomor_kolom) kurang dari 1fungsi VPR akan melaporkan kesalahan #NILAI! (#NILAI!). Jika lebih besar dari jumlah kolom dalam rentang table_array (tabel), fungsi akan melaporkan kesalahan # REF! (#TAUTAN!).
Gunakan referensi sel absolut dalam argumen table_array (tabel) sehingga rentang pencarian yang benar dipertahankan saat menyalin rumus. Coba gunakan rentang atau tabel bernama di Excel sebagai alternatif.
Saat melakukan pencarian kecocokan perkiraan, ingatlah bahwa kolom pertama dalam rentang yang Anda cari harus diurutkan dalam urutan menaik.
Akhirnya, ingat pentingnya argumen keempat. Gunakan nilai KODE BENAR (BENAR) atau SALAH (SALAH) dengan sengaja dan Anda akan menyingkirkan banyak sakit kepala.

Dalam artikel berikut dari tutorial fungsi kami VPR di Excel, kita akan mempelajari contoh lebih lanjut, seperti melakukan berbagai perhitungan menggunakan VPR, mengekstrak nilai dari beberapa kolom, dan banyak lagi. Terima kasih telah membaca tutorial ini dan saya berharap dapat bertemu Anda lagi minggu depan!