VLOOKUP (Fungsi VLOOKUP)

Artikel ini menguraikan sintaks rumus dan penggunaan fungsi VLOOKUP di (fungsi: Rumus yang ditulis sebelumnya yang mengambil nilai, menjalankan operasi, dan mengembalikan nilai. Gunakan fungsi untuk menyederhanakan dan mempersingkat rumus pada lembar kerja, terutama rumus yang melakukan perhitungan panjang atau kompleks.)Microsoft Excel.

Deskripsi

Anda dapat menggunakan fungsi VLOOKUP untuk mencari kolom pertama satu rentang (rentang: Dua atau beberapa sel di sebuah lembar. Sel dalam sebuah rentang bisa berdekatan atau tidak berdekatan.) sel, lalu mengembalikan nilai dari sel mana pun di baris rentang yang sama. Sebagai contoh, misalnya Anda memiliki daftar karyawan dalam rentang A2:C10. Nomor ID karyawan diletakkan di kolom pertama rentang ini, seperti ditunjukkan pada ilustrasi berikut.

Satu rentang sel pada lembar kerja

JIka Anda mengetahui nomor ID karyawan, Anda dapat menggunakan fungsi VLOOKUP untuk mengembalikan departemen atau nama karyawan tersebut. Untuk mendapatkan nama karyawan nomor 38, Anda dapat menggunakan rumus =VLOOKUP(38, A2:C10, 3, FALSE). Rumus ini mencari nilai 38 di kolom pertama rentang A2:C10, lalu mengembalikan nilai yang terdapat di kolom ketiga rentang tersebut dan di baris yang sama dengan nilai pencarian ("Axel Delgado").

V dalam VLOOKUP adalah singkatan dari vertikal. Gunakan VLOOKUP, sebagai ganti HLOOKUP bila nilai perbandingan Anda terletak di kolom sebelah kiri data yang akan ditemukan.

Sintaks

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

Sintaks fungsi VLOOKUP terdiri dari argumen (argumen: Sebuah nilai yang menyediakan informasi untuk sebuah tindakan, kejadian, metode, properti, fungsi atau prosedur.) berikut:

  • lookup_value    Diperlukan. Nilai yang akan dicari di kolom pertama tabel atau rentang. Argumen lookup_value dapat berupa satu nilai atau referensi. Jika nilai yang Anda berikan untuk argumen lookup_value lebih kecil dari nilai terkecil di kolom pertama argumen table_array, VLOOKUP mengembalikan nilai kesalahan #N/A.
  • table_array    Diperlukan. Pilih rentang sel yang berisi data tersebut. Anda dapat menggunakan referensi ke satu rentang (misalnya, A2:D8), atau nama rentang. Nilai-nilai di kolom pertama table_array adalah nilai yang dicari oleh lookup_value. Nilai ini dapat berupa teks, angka, atau nilai logika. Teks huruf besar dan huruf kecil sama saja.
  • col_index_num    Diperlukan. Nomor kolom dalam argumen table_array yang merupakan asal dari nilai yang cocok yang harus dikembalikan. Argumen col_index_num 1 mengembalikan nilai di kolom pertama dalam table_array; col_index_num 2 mengembalikan nilai di kolom kedua dalam table_array, dan seterusnya.

Jika argumen col_index_num adalah:

  • Kurang dari 1, VLOOKUP mengembalikan nilai kesalahan #VALUE!.
  • Lebih besar dari jumlah kolom dalam table_array, VLOOKUP mengembalikan nilai kesalahan #REF!.
  • range_lookup    Opsional. Nilai logika yang menetapkan apakah Anda ingin VLOOKUP menemukan hasil yang persis sama atau mendekati:
    • Jika range_lookup adalah TRUE atau dikosongkan, hasil yang persis sama atau mendekati dikembalikan. Jika hasil yang persis sama tidak ditemukan, nilai terbesar berikutnya yang kurang dari lookup_value dikembalikan.

 Penting   Jika range_lookup adalah TRUE atau dikosongkan, nilai-nilai di kolom pertama table_array harus ditempatkan dalam urutan naik, jika tidak, VLOOKUP mungkin tidak mengembalikan nilai yang benar.

Untuk informasi lebih lanjut, lihat Mengurutkan data dalam rentang atau tabel.

Jika range_lookup adalah FALSE, nilai di kolom pertama table_array tidak perlu diurutkan.

  • Jika argumen range_lookup adalah FALSE, VLOOKUP hanya akan menemukan hasil yang persis sama. Jika ada dua nilai atau lebih di kolom pertama table_array yang cocok dengan lookup_value, nilai yang pertama ditemukan akan digunakan. Jika hasil yang persis sama tidak ditemukan, nilai kesalahan #N/A akan dikembalikan.

Keterangan

  • Ketika mencari nilai teks di kolom pertama table_array, pastikan bahwa data di kolom pertama table_array tidak berisi spasi awal, spasi akhir, penggunaan tanda kutip lurus ( ' atau " ) dan lengkung ( ‘ atau “) secara inkonsisten, atau karakter noncetak. Dalam kasus ini, VLOOKUP mungkin mengembalikan nilai yang salah atau tidak diharapkan.

Untuk informasi lebih lanjut, lihat fungsi CLEAN dan TRIM.

  • Ketika mencari nilai angka atau tanggal, pastikan bahwa data di kolom pertama table_array tidak disimpan sebagai nilai teks. Dalam kasus ini, VLOOKUP mungkin mengembalikan nilai yang salah atau tidak diharapkan.
  • Jika range_lookup FALSE dan lookup_value adalah teks, Anda dapat mengunakan karakter wildcard,  — tanda tanya (?) dan tanda bintang (*) — dalam lookup_value. Tanya 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.

Contoh

Salin contoh data di dalam tabel berikut ini dan tempel ke dalam sel A1 lembar kerja Excel yang baru. Agar rumus memperlihatkan hasil, pilih datanya, tekan F2, lalu tekan Enter. Jika perlu, Anda bisa menyesuaikan lebar kolom untuk melihat semua data.

Kerapatan Kekentalan Suhu
0,457 3,55 500
0,525 3,25 400
0,606 2,93 300
0,675 2,75 250
0,746 2,57 200
0,835 2,38 150
0,946 2,17 100
109 195 50
129 1,71 0
Rumus Deskripsi Hasil
=VLOOKUP(1,A2:C10,2) Dengan menggunakan hasil yang hampir sama, rumus mencari nilai 1 di kolom A, menemukan nilai terbesar yang lebih kecil atau sama dengan 1 di kolom A yaitu 0,946, lalu mengembalikan nilai dari kolom B dalam baris yang sama. 2,17
=VLOOKUP(1,A2:C10,3,TRUE) Dengan menggunakan hasil yang hampir sama, rumus mencari nilai 1 di kolom A, menemukan nilai terbesar yang lebih kecil atau sama dengan 1 di kolom A yaitu 0,946, lalu mengembalikan nilai dari kolom C dalam baris yang sama. 100
=VLOOKUP(0,7,A2:C10,3,FALSE) Dengan menggunakan hasil yang persis sama, rumus mencari nilai 0,7 dalam kolom A. Karena tidak ada hasil yang persis sama dalam kolom A, kesalahan dikembalikan. #N/A
=VLOOKUP(0,1,A2:C10,2,TRUE) Dengan menggunakan hasil yang hampir sama, rumus mencari nilai 0,1 dalam kolom A. Karena 0,1 lebih kecil dari nilai terkecil dalam kolom A, nilai kesalahan dikembalikan. #N/A
=VLOOKUP(2,A2:C10,2,TRUE) Dengan menggunakan hasil yang hampir sama, rumus mencari nilai 2 di kolom A, menemukan nilai terbesar yang kurang dari atau sama dengan 2 di kolom A, yaitu 1,29, lalu mengembalikan nilai dari kolom B dalam baris yang sama. 1,71

Contoh 2

ID-Item Item Biaya Markup
ST-340 Kereta Bayi $145,67 30%
BI-567 Bib $3,56 40%
DI-328 Popok $21,45 35%
WI-989 Tisu $5,12 40%
AS-469 Aspirator $2,56 45%
Rumus Deskripsi Hasil
= VLOOKUP("DI-328", A2:D6, 3, FALSE) * (1 + VLOOKUP("DI-328", A2:D6, 4, FALSE)) Menghitung harga eceran popok dengan menambahkan persentase markup ke biaya. $28,96
= (VLOOKUP("WI-989", A2:D6, 3, FALSE) * (1 + VLOOKUP("WI-989", A2:D6, 4, FALSE))) * (1 - 20%) Menghitung harga jual tisu penyeka dengan mengurangi diskon yang ditentukan dari harga eceran. $5,73
= IF(VLOOKUP(A2, A2:D6, 3, FALSE) >= 20, "Markup sebesar" & 100 * VLOOKUP(A2, A2:D6, 4, FALSE) &"%", "Biaya di bawah $20,00") Jika biaya item lebih besar atau sama dengan $20.00, rumus menampilkan string "Markup sebesar nn%"; jika sebaliknya, rumus menampilkan string "Biaya di bawah $20,00" Markup sebesar 30%
= IF(VLOOKUP(A3, A2:D6, 3, FALSE) >= 20, "Markup sebesar: " & 100 * VLOOKUP(A3, A2:D6, 4, FALSE) &"%", "Biaya sebesar $" & VLOOKUP(A3, A2:D6, 3, FALSE)) Jika biaya item lebih besar atau sama dengan $20,00, rumus menampilkan string Markup sebesar nn%"; jika sebaliknya, rumus menampilkan string "Biaya sebesar $n.nn" Biaya sebesar $3,56

Contoh 3

ID Nama belakang Nama depan Jabatan Tanggal Lahir
1 Davis Sara Staf Penjualan 12/8/1968
2 Fontana Olivier V.P. Penjualan 2/19/1952
3 Leal Karina Staf Penjualan 8/30/1963
4 Patten Michael Staf Penjualan 9/19/1958
5 Burke Brian Manajer Penjualan 3/4/1955
6 Sousa Luis Staf Penjualan 7/2/1963
Rumus Deskripsi Hasil
=INT(YEARFRAC(DATE(2004,6,30), VLOOKUP(5,A2:E7,5, FALSE), 1)) Untuk tahun fiskal 2004, rumus menemukan usia karyawan dengan ID 5. Menggunakan fungsi YEARFRAC untuk mengurangi tanggal lahir dari tanggal berakhir tahun fiskal dan menampilkan hasil sebagai bilangan bulat dengan menggunakan fungsi INT. 49
=IF(ISNA(VLOOKUP(5,A2:E7,2,FALSE)) = TRUE, "Karyawan tidak ditemukan", VLOOKUP(5,A2:E7,2,FALSE)) Jika terdapat karyawan dengan ID 5, rumus menampilkan nama belakang karyawan; jika tidak maka akan menampilkan pesan "Karyawan tidak ditemukan".

Fungsi ISNA mengembalikan nilai TRUE ketika fungsi VLOOKUP mengembalikan nilai kesalahan #N/A.
Burke
=IF(ISNA(VLOOKUP(15,A3:E8,2,FALSE)) = TRUE, "Karyawan tidak ditemukan", VLOOKUP(15,A3:E8,2,FALSE)) Jika terdapat karyawan dengan ID 15, rumus menampilkan nama belakang karyawan tersebut; jika tidak maka akan menampilkan pesan "Karyawan tidak ditemukan".

Fungsi ISNA mengembalikan nilai TRUE ketika fungsi VLOOKUP mengembalikan nilai kesalahan #N/A.
Karyawan tidak ditemukan
=VLOOKUP(4,A2:E7,3,FALSE) & " " & VLOOKUP(4,A2:E7,2,FALSE) & " adalah" & VLOOKUP(4,A2:E7,4,FALSE) Untuk karyawan dengan ID 4,rumus menggabungkan (mengombinasikan) nilai dari tiga sel ke dalam kalimat lengkap. Michael Patten adalah seorang Staf Penjualan

Atas Halaman Atas Halaman

 
 
Berlaku pada:
Excel 2013, Excel Online