VLOOKUP

 

Jika memiliki informasi yang diketahui di spreadsheet, Anda dapat menggunakan VLOOKUP untuk menelusuri informasi terkait menurut baris. Misalnya, jika ingin membeli jeruk, Anda dapat menggunakan VLOOKUP untuk menelusuri harganya.

VLOOKUP untuk BigQuery

Pencarian vertikal. Menampilkan nilai dalam kolom data dengan posisi di mana kecocokan ditemukan di kolom penelusuran.

Contoh Penggunaan

VLOOKUP("Apple",nama_tabel!buah,nama_tabel!harga)

Sintaksis

VLOOKUP(kunci_penelusuran, rentang,indeks, diurutkan)

• kunci_penelusuran: Nilai yang akan ditelusuri di kolom penelusuran.
• kolom_penelusuran: Kolom data yang dipertimbangkan untuk penelusuran.
• kolom_hasil: Kolom data yang akan dipertimbangkan untuk hasil.
• diurutkan: [OPSIONAL] Cara menemukan kecocokan untuk kunci_penelusuran.
  • SALAH: Nilai ini direkomendasikan untuk pencocokan persis.
  • BENAR: Untuk pencocokan hasil terdekat, nilai ini merupakan default-nya jika diurutkan tidak ditentukan.
    Tips: Sebelum Anda menggunakan pencocokan hasil terdekat, urutkan kunci penelusuran dalam urutan menaik. Jika tidak, Anda mungkin akan mendapatkan nilai hasil yang salah. Pelajari alasan Anda mungkin mendapatkan nilai hasil yang salah.

Tips: Untuk kueri database yang lebih fleksibel di BigQuery, gunakan XLOOKUP.

Sintaksis

=VLOOKUP(kunci_penelusuran, rentang, indeks, [diurutkan])

Input

1. kunci_penelusuran: Nilai yang akan ditelusuri di kolom pertama rentang.
2. rentang: Nilai atas dan bawah yang perlu dipertimbangkan untuk penelusuran.
3. indeks: Indeks kolom dengan nilai hasil dari rentang. Indeks harus berupa bilangan bulat positif.
4. diurutkan: Input opsional. Pilih salah satu opsi:
   • SALAH = Pencocokan persis. Nilai ini disarankan.
   • BENAR = Pencocokan hasil terdekat. Nilai ini merupakan default-nya jika diurutkan tidak ditentukan.
     Penting: Sebelum Anda menggunakan pencocokan hasil terdekat, urutkan kunci penelusuran dalam urutan menaik. Jika tidak, Anda mungkin akan mendapatkan nilai hasil yang salah. Pelajari alasan Anda mungkin mendapatkan nilai hasil yang salah.

Nilai hasil

Nilai pertama yang cocok dari rentang yang dipilih.

Detail teknis:

Contoh:

=VLOOKUP(G9, B4:D8, 3, SALAH)

=VLOOKUP("Apple", B4:D8, 3, BENAR)

Input	Deskripsi
kunci_penelusuran	Ini adalah nilai yang Anda telusuri di kolom pertama rentang. Jika Anda mengharapkan nilai non-error, kunci penelusuran harus berada di kolom pertama rentang. Referensi sel juga didukung. Untuk melakukan pemeriksaan sederhana: Jika kunci_penelusuran terletak di B3, maka rentang harus dimulai dengan kolom B.
rentang	Ini adalah rentang tempat: Fungsi menelusuri kunci penelusuran yang ditentukan di kolom pertamanya. VLOOKUP menampilkan nilai dari kolom yang ditentukan oleh indeks. Anda juga dapat menggunakan rentang bernama. Untuk menampilkan nilai non-error, kunci penelusuran harus berada di kolom pertama rentang. Untuk melakukan pemeriksaan sederhana: Jika kunci_penelusuran terletak di B3, maka rentang harus dimulai dengan kolom B.
indeks	Juga disebut “Nomor kolom”. Ini adalah indeks kolom dalam rentang yang berisi nilai hasil. Indeks terkecil adalah 1. Indeks terbesar adalah jumlah kolom maksimum dalam rentang tersebut. Setelah Anda menyiapkan rentang, VLOOKUP hanya mempertimbangkan kolom kunci penelusuran, jika indeks = 1 , atau kolom yang berada di paling kanan. Tips: Saat Anda menggunakan VLOOKUP, bayangkan bahwa kolom rentang diberi nomor dari kiri ke kanan dan dimulai dengan 1.
diurutkan	Ini adalah input opsional. Dua pilihan yang tersedia adalah BENAR dan SALAH. Jika diurutkan ditetapkan ke BENAR, maka VLOOKUP akan menggunakan pencocokan hasil terdekat. Penting: Sebelum Anda menggunakan pencocokan hasil terdekat, urutkan kunci penelusuran dalam urutan menaik. Jika tidak, Anda mungkin akan mendapatkan nilai hasil yang tak terduga. Pelajari alasan Anda mungkin mendapatkan nilai hasil yang salah. Jika diurutkan ditetapkan ke SALAH, maka VLOOKUP akan menggunakan pencocokan persis. Jika diurutkan tidak ditentukan, nilainya adalah BENAR secara default. Sebaiknya Anda: Gunakan SALAH untuk nilai diurutkan karena perilakunya yang konsisten, terlepas dari apakah kolom kunci penelusuran diurutkan atau tidak. Selalu tentukan nilai diurutkan agar lebih mudah dibaca, meskipun input ini bersifat opsional.

 

Output

Deskripsi

Nilai hasil

Ini adalah nilai yang ditampilkan VLOOKUP berdasarkan input Anda. Hanya ada satu nilai hasil dari setiap fungsi VLOOKUP . Jika ada beberapa nilai kunci penelusuran yang cocok, nilai dalam kolom nilai hasil yang kunci penelusuran terkaitnya pertama kali cocok di kolom kunci penelusuran akan ditampilkan. Jika #T/A ditampilkan, berarti nilai tidak ditemukan. Jika Anda mendapatkan nilai atau error yang diharapkan seperti #T/A atau #VALUE!, mulai pecahkan masalah. Jika Anda ingin mengganti #T/A dengan nilai lain, pelajari lebih lanjut cara menggunakan IFNA() di VLOOKUP().

Contoh VLOOKUP dasar:

VLOOKUP pada kunci penelusuran yang berbeda

Gunakan VLOOKUP untuk menemukan harga Orange dan Apple.

Coba

Penjelasan:

Saat menggunakan VLOOKUP, Anda dapat menggunakan kunci penelusuran yang berbeda seperti "Apple" dan "Orange".

Untuk menampilkan nilai non-error, kunci penelusuran ini harus berada di kolom pertama rentang. Jika tidak ingin mengisi nilai untuk kunci penelusuran, Anda juga dapat menggunakan referensi sel, misalnya "G9".

kunci_penelusuran adalah "Orange"	=VLOOKUP("Orange", B4:D8, 3, SALAH) Nilai hasil = $1,01
kunci_penelusuran adalah "Apple"	=VLOOKUP("Apple", B4:D8, 3, SALAH) Nilai hasil = $1,50
kunci_penelusuran yang menggunakan referensi sel "Apple" di G9	=VLOOKUP(G9, B4:D8, 3, SALAH) Nilai hasil = $1,50

VLOOKUP pada indeks kolom yang berbeda

Gunakan VLOOKUP untuk menemukan jumlah Orange di kolom indeks kedua.

Coba

Penjelasan:

Saat Anda menggunakan VLOOKUP, bayangkan kolom rentang diberi nomor dari kiri ke kanan dan dimulai dari 1. Untuk menemukan informasi target, Anda harus menentukan indeks kolomnya. Misalnya, kolom 2 untuk jumlah.

Indeks = 2 Menemukan jumlah orange, yang merupakan kolom kedua dari rentang.

=VLOOKUP(G3, B4:D8, 2, SALAH) Nilai hasil = 5

VLOOKUP pencocokan persis atau pencocokan hasil terdekat

• Gunakan pencocokan persis VLOOKUP untuk menemukan ID yang tepat.
• Gunakan pencocokan hasil terdekat VLOOKUP untuk menemukan ID yang dibulatkan.

Coba

Penjelasan:

Gunakan pencocokan hasil terdekat atau diurutkan = BENAR saat Anda menelusuri pencocokan terbaik, tetapi bukan pencocokan persis.

Jika Anda ingin menelusuri ID = 102, yang tidak ada di tabel, pencocokan hasil terdekat akan mengurangi nilainya dan memberi Anda ID = 101 sebagai hasil. Hal ini terjadi karena di kolom kunci penelusuran, 101 adalah nilai terdekat yang juga kurang dari 102.

Pencocokan hasil terdekat menelusuri kolom kunci penelusuran hingga menemukan nilai yang lebih besar dari kunci penelusuran Anda. Kemudian, pencocokan ini berhenti di baris sebelum nilai yang lebih besar dan menampilkan nilai dari kolom nilai hasil di baris tersebut. Hal ini berarti, jika kolom kunci penelusuran Anda tidak diurutkan dalam urutan menaik, kemungkinan besar Anda mendapatkan nilai hasil yang salah.

Penting: Sebelum Anda menggunakan pencocokan hasil terdekat, urutkan kunci penelusuran dalam urutan menaik untuk menampilkan nilai yang benar. Jika tidak, Anda mungkin akan mendapatkan nilai hasil yang tak terduga.

Jika Anda menelusuri pencocokan persis, seperti diurutkan = SALAH, hasil yang ditampilkan adalah pencocokan persis. Misalnya, nama buah untuk ID = 103 adalah "Banana". Jika tidak ada kecocokan persis, Anda akan mendapatkan error #T/A. Karena perilakunya yang lebih dapat diprediksi, sebaiknya gunakan pencocokan persis.

Pencocokan persis	=VLOOKUP(G6, A4:D8, 2, SALAH) Nilai hasil = "Apple"
Pencocokan hasil terdekat	=VLOOKUP(G3, A4:D8, 2, BENAR) ATAU =VLOOKUP(G3, A4:D8, 2) Nilai hasil = "Banana"

Penggunaan umum VLOOKUP

Mengganti nilai error dari VLOOKUP

Anda mungkin ingin mengganti nilai error yang ditampilkan VLOOKUP saat kunci penelusuran tidak ada. Dalam kasus ini, jika tidak ingin mendapatkan hasil #T/A, Anda dapat menggunakan fungsi IFNA() untuk mengganti #T/A. Pelajari IFNA() lebih lanjut.

Coba

Awalnya, VLOOKUP menampilkan #T/A karena kunci penelusuran "Pensil" tidak ada di kolom "Buah". IFNA() mengganti error #T/A dengan input kedua yang ditentukan dalam fungsi. Dalam kasus kami, inputnya adalah “TIDAK DITEMUKAN”.

=IFNA(VLOOKUP(G3, B4:D8, 3, SALAH),"TIDAK DITEMUKAN") Nilai HASIL = “TIDAK DITEMUKAN”

Tips: Jika Anda ingin mengganti error lain seperti #REF!, pelajari IFERROR() lebih lanjut.

VLOOKUP dengan beberapa kriteriA

VLOOKUP tidak dapat diterapkan SECARA langsung di beberapa kriteria. Sebagai gantinya, buat kolom bantuan baru untuk menerapkan VLOOKUP secara langsung pada beberapa kriteria untuk menggabungkan beberapa kolom yang ada.

Coba

1. Anda dapat membuat kolom Bantuan jika menggunakan "&" untuk menggabungkan First Name dan Last Name.	=C4&D4 dan seret dari B4 ke B8 sehingga Anda akan mendapatkan kolom Bantuan.
2. Gunakan referensi sel B7, JohnLee, sebagai kunci penelusuran.	=VLOOKUP(B7, B4:E8, 4, SALAH) Nilai hasil = "Support"

VLOOKUP dengan karakter pengganti atau kecocokan parsial

Di VLOOKUP, Anda juga dapat menggunakan karakter pengganti atau kecocokan parsial. Anda dapat menggunakan karakter pengganti berikut:

• Tanda tanya "?" cocok dengan karakter tunggal apa pun.
• Tanda bintang "*" cocok dengan urutan karakter apa pun.

Untuk menggunakan karakter pengganti di VLOOKUP, Anda harus menggunakan pencocokan persis: "diurutkan = SALAH".

Coba

"St*" digunakan untuk mencocokkan apa pun yang dimulai dengan "St" terlepas dari jumlah karakternya, seperti "Steve", "St1", "Stock", atau "Steeeeeeve".

=VLOOKUP("St*", B4:D8, 3, SALAH) Nilai hasil = "Marketing"

Memecahkan masalah error & praktik terbaik:

Nilai hasil salah

• Menampilkan nilai yang tak terduga: Jika Anda menetapkan diurutkan sebagai BENAR, tetapi kolom pertama Anda dalam rentang tidak diurutkan secara numerik atau menurut abjad dalam urutan menaik, maka ubah diurutkan ke SALAH.

• VLOOKUP menampilkan kecocokan pertama: VLOOKUP hanya menampilkan kecocokan pertama. Jika Anda memiliki beberapa kunci penelusuran yang cocok, sebuah nilai akan ditampilkan, tetapi mungkin bukan nilai yang diharapkan.
• Data yang tidak rapi: Terkadang, nilai yang dimulai dan diakhiri dengan spasi mungkin tampak serupa, tetapi VLOOKUP memperlakukan nilai tersebut secara berbeda. Misalnya, nilai berikut berbeda untuk VLOOKUP:
  • " Apple"
  • "Apple "
  • "Apple"

Untuk mendapatkan hasil yang diharapkan, hapus spasi sebelum Anda menggunakan VLOOKUP.

Untuk mempelajari lebih lanjut, lihat bagian praktik terbaik kami.

#T/A

• Jika hasil terdekat atau diurutkan = BENAR digunakan, dan jika kunci penelusuran di VLOOKUP lebih kecil dari nilai terkecil di kolom pertama, maka VLOOKUP akan menampilkan #T/A.
• Jika pencocokan persis atau diurutkan = SALAH digunakan, kecocokan persis dari kunci penelusuran di VLOOKUP tidak akan ditemukan di kolom pertama. Jika tidak ingin mendapatkan hasil #T/A saat kunci penelusuran tidak ditemukan di kolom pertama, Anda dapat menggunakan fungsi IFNA().

#REF!

Anda mungkin salah menentukan rentang dengan angka yang lebih besar dari jumlah maksimum kolom rentang. Untuk menghindari hal ini, pastikan Anda:

• Menghitung kolom dari rentang yang dipilih, bukan seluruh tabel.
• Memulai penghitungan dari 1, bukan 0.

#VALUE!

Jika Anda mendapatkan error #VALUE!, Anda mungkin:

• Telah memasukkan teks atau nama kolom yang salah untuk indeks.
• Telah memasukkan angka yang lebih kecil dari 1 untuk indeks. indeks harus setidaknya sama dengan 1 dan lebih kecil dari jumlah maksimum kolom rentang. VLOOKUP hanya dapat melakukan penelusuran di kolom kunci penelusuran, jika indeks = 1, atau kolom yang berada di paling kanan.

Penting: index hanya dapat memproses angka.

#NAME?

• Anda mungkin melewatkan tanda kutip pada kunci penelusuran jika kunci_penelusuran adalah data teks.

Praktik terbaik

 

Hal yang harus dilakukan	Alasan
Menggunakan referensi absolut untuk range	Sebaiknya gunakan: Referensi absolut untuk rentang VLOOKUP VLOOKUP(G3, $B$3:$D$7, 3, SALAH) Anda tidak boleh menggunakan: VLOOKUP(G3, B3:D7, 3, SALAH) Hal ini mencegah perubahan yang tidak dapat diprediksi dalam rentang saat input disalin atau ditarik ke bawah.
Mengurutkan kolom pertama dalam urutan menaik saat Anda menggunakan pencocokan hasil terdekat, seperti diurutkan = BENAR.	Jika menggunakan pencocokan hasil terdekat atau diurutkan = BENAR, Anda harus mengurutkan kolom pertama dalam urutan menaik. Jika tidak, Anda kemungkinan besar akan mendapatkan nilai hasil yang salah. Pelajari lebih lanjut cara mengurutkan.
Merapikan data sebelum menggunakan VLOOKUP	Sebelum menggunakan VLOOKUP, jangan lupa untuk merapikan data Anda. Data yang tidak rapi dapat menyebabkan VLOOKUP menampilkan nilai yang tidak dapat diprediksi. Berikut adalah beberapa kesalahan umum dari data yang tidak rapi: Diawali spasi: " apple" Diakhiri spasi: "apple " Kosong atau spasi: "" dan " " tidak setara Untuk memangkas spasi kosong di awal atau di akhir, Anda dapat menggunakan Data Pembersihan data Pangkas spasi kosong.
Jangan simpan nilai angka atau tanggal sebagai teks	Pastikan nilai tanggal atau angka di kolom pertama rentang VLOOKUP, seperti kolom kunci penelusuran, tidak disimpan sebagai nilai teks. Anda mungkin mendapatkan nilai hasil yang tak terduga. Di bagian atas Spreadsheet, pilih kolom kunci penelusuran. Ketuk Menu Format Angka. Pilih salah satu opsi bergantung pada jenis data yang diinginkan: Tanggal Nomor