VLOOKUP (Fungsi VLOOKUP)
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.
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. Argumenlookup_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 dalamtable_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_valuedikembalikan.
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_arraytidak 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.
- 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 |
No comments:
Post a Comment