VLOOKUP (Fungsi VLOOKUP)

Artikel ini menguraikan sintaks rumus dan penggunaan fungsi VLOOKUP di fungsiMicrosoft Excel.

Deskripsi

Anda dapat menggunakan fungsi VLOOKUP untuk mencari kolom pertama satu rentang 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 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

Buku kerja di bawah ini memperlihatkan contoh fungsi ini. Periksalah contohnya, ubah rumus yang ada, atau masukkan rumus Anda sendiri untuk mempelajari cara kerja fungsi ini.

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

Untuk bekerja secara lebih mendalam dengan data contoh di Excel, unduh buku kerja yang disematkan ke komputer Anda, lalu buka di Excel.

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

Bagian Atas Halaman

Terapkan Ke: Excel 2013, Excel Online



Apakah informasi ini bermanfaat?

Ya Tidak

Bagaimana kami dapat meningkatkannya?

255 karakter tersisa

Untuk memproteksi privasi Anda, jangan menyertakan informasi kontak dalam umpan balik Anda. Tinjau kebijakan privasi.

Terima kasih atas umpan balik Anda!

Sumber daya dukungan

Ganti bahasa