=VLOOKUP(F2,A2:D6,3,FALSE) mencari nilai di F2 pada kolom pertama A2:D6 dan mengembalikan nilai dari kolom ketiga di baris yang sama. FALSE di akhir berarti "hanya pencocokan persis". Pilih produk lain di F2 dan harganya ikut berubah.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
Klik G2 untuk melihat tabel A2:D6 ditandai. Ubah 3 di rumusnya menjadi 2 dan G2 mengembalikan kategori, bukan harga, karena Category adalah kolom kedua tabel. Pencocokannya tidak membedakan huruf besar dan kecil: pear menemukan Pear.
Sintaks VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Argumen | Artinya | Di contoh ini |
|---|---|---|
lookup_value | Nilai yang dicari. | F2 (Pear) |
table_array | Tabel tempat mencari. VLOOKUP hanya mencari di kolom pertama-nya. | A2:D6 |
col_index_num | Kolom tabel yang dikembalikan, dihitung dari kolom pertama tabel (1). | 3 (Price) |
range_lookup | FALSE atau 0 untuk pencocokan persis. TRUE, 1 atau dikosongkan untuk pencocokan perkiraan. | FALSE |
Nomor kolomnya dihitung dari awal tabel, bukan dari kolom A di sheet. Di tabel yang dimulai dari kolom C, col_index_num 2 berarti kolom D. Angka yang lebih besar dari lebar tabel menghasilkan #REF!, dan 0 menghasilkan #VALUE!.
Di Excel yang memakai koma sebagai pemisah desimal, seperti Excel dengan pengaturan regional Indonesia, argumen dipisahkan dengan titik koma: =VLOOKUP(F2;A2:D6;3;FALSE).
Memilih kolom yang dikembalikan dengan MATCH
Angka 3 yang ditulis langsung akan rusak diam-diam saat seseorang menyisipkan kolom di dalam tabel: rumusnya tetap mengembalikan kolom ketiga, yang sekarang berisi data lain. Biarkan MATCH mencari nomor kolom dari judulnya. Di sini G1 adalah drop-down: pilih Stock atau Category dan G2 mengikutinya.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | 40 | Carrot | 0.8 | |
| 3 | Pear | Fruit | $1.50 | 25 | |||
| 4 | Carrot | Vegetable | $0.80 | 60 | |||
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
MATCH(G1,A1:D1,0) mengembalikan posisi "Price" di baris judul, yaitu 3, dan VLOOKUP memakainya sebagai nomor kolom: 0,8 untuk Carrot. Ini adalah pencarian dua arah: baris dipilih berdasarkan produk, kolom dipilih berdasarkan judul. Ide yang sama yang ditulis dengan INDEX, bukan VLOOKUP, ada di halaman INDEX dan MATCH.
Pencocokan perkiraan: VLOOKUP dengan TRUE
Dengan TRUE sebagai argumen terakhir, VLOOKUP tidak mencari nilai yang sama. Fungsi ini mencari nilai terbesar yang kurang dari atau sama dengan nilai yang dicari. Itulah yang kamu butuhkan untuk rentang bertingkat: golongan pajak, nilai huruf, tarif pengiriman, tingkat komisi. Kolom pertamanya harus diurutkan dari yang terkecil sampai terbesar.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Sales from | Rate | Rep | Sales | Rate | |
| 2 | 0 | 0% | Ana | 750 | 0% | |
| 3 | 1000 | 3% | Ben | 4,200 | 3% | |
| 4 | 5000 | 5% | Cara | 5,000 | 5% | |
| 5 | 10000 | 8% | Dev | 12,500 | 8% |
Angka 4.200 milik Ben tidak ada di kolom A. Nilai terbesar yang tidak melebihinya adalah 1.000, jadi ia mendapat 3%. Angka 5.000 milik Cara cocok persis dengan baris 5.000 dan mendapat 5%. Angka 12.500 milik Dev berada di atas tingkat terakhir dan mendapat persentase terakhir, 8%. Nilai di bawah tingkat pertama (di sini angka penjualan negatif) menghasilkan #N/A, itulah sebabnya tabelnya dimulai dari 0.
Tanda $ di $A$2:$B$5 menjaga tabelnya tetap di tempat saat F2 disalin ke bawah sampai F5. Tanpanya, F3 akan mencari di A3:B6 dan melewatkan tingkat pertama.
Menghilangkan argumen keempat sama dengan TRUE. Pada daftar produk yang tidak terurut, ini adalah bug yang diam-diam: Excel mencari seolah-olah daftarnya terurut dan bisa mengembalikan harga dari baris yang salah, atau #N/A untuk nilai yang sebenarnya ada. Saat kamu mencari nama, kode atau ID, selalu akhiri dengan FALSE.
Kenapa VLOOKUP menghasilkan #N/A
#N/A berarti "tidak ditemukan". Sheet di bawah ini menunjukkan tiga penyebab yang umum, dan kolom G mengulang setiap pencarian yang dibungkus dengan IFNA dan TRIM.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Stock | Look for | Price | Fixed |
| 2 | Apple | Fruit | $1.20 | 40 | Kiwi | #N/A | Not found |
| 3 | Pear | Fruit | $1.50 | 25 | Milk | #N/A | $1.10 |
| 4 | Carrot | Vegetable | $0.80 | 60 | Fruit | #N/A | Not found |
| 5 | Bread | Bakery | $2.40 | 15 | |||
| 6 | Milk | Dairy | $1.10 | 30 |
#N/A Nilai yang kamu cari tidak ada di rentang pencarian.- Nilainya tidak ada di tabel. Kiwi tidak ada di A2:A6. Itu memang "tidak ditemukan", dan
IFNA(...,"Not found")mengubahnya menjadi teks yang mudah dibaca. Ubah E2 menjadi Apple dan kedua kolom menampilkan harganya. - Spasi berlebih. E3 berisi
"Milk "dengan spasi di belakang, jadi tidak sama denganMilk.TRIM(E3)menghapusnya dan G3 menemukan harganya. Jika spasinya justru ada di tabel, bersihkan kolom A dengan TRIM sekali saja, bukan di setiap pencarian. - Nilainya ada di kolom lain. Fruit memang ada, tetapi di kolom B. VLOOKUP hanya mencari di kolom pertama tabel, jadi E4 gagal di kedua kolom. Mulai tabelnya dari kolom yang dicari, atau pakai XLOOKUP, yang menerima kolom pencarian dan kolom hasil secara terpisah.
Pakai IFNA, bukan IFERROR, untuk membungkus pencarian. IFNA hanya menangkap #N/A, jadi #REF! karena nomor kolom yang salah tetap terlihat, bukan disembunyikan sebagai "Not found".
Dua penyebab lainnya:
- Angka yang tersimpan sebagai teks. Jika kolom A berisi kode produk yang diketik sebagai teks (sering terjadi setelah impor, dengan segitiga hijau kecil di pojok) dan F2 berisi angka 101,
=VLOOKUP(F2,A2:B6,2,FALSE)menghasilkan #N/A walaupun 101 ada di daftar. Ubah salah satu sisinya:=VLOOKUP(F2&"",A2:B6,2,FALSE)mencari teks "101", dan=VLOOKUP(VALUE(F2),A2:B6,2,FALSE)mencari angka jika F2 berupa teks. - Pencocokan perkiraan pada data yang tidak terurut, seperti dijelaskan di bagian sebelumnya.
VLOOKUP menghasilkan 0, bukan sel kosong
Jika sel yang dituju VLOOKUP kosong, Excel menampilkan 0, bukan sel kosong. Angka 0 di kolom Stock lalu terbaca sebagai "stok habis", padahal stoknya belum pernah diisi. Tambahkan &"" ke rumusnya, atau uji panjang hasilnya:
=VLOOKUP(F2,A2:D6,4,FALSE)&""
=IF(LEN(VLOOKUP(F2,A2:D6,4,FALSE))=0,"",VLOOKUP(F2,A2:D6,4,FALSE))
Cara pertama lebih pendek tetapi mengubah setiap angka yang dikembalikannya menjadi teks, jadi SUM berikutnya akan melewatinya. Cara kedua mempertahankan angka sebagai angka.
VLOOKUP dari sheet lain
Tulis nama sheet dan ! di depan tabelnya. Saat menyusun rumus di Excel, klik tab sheet lain lalu pilih range-nya: Excel menuliskan Prices!A2:B6 untukmu. Di sini tab Orders mencari harga di tab Prices.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Product | Qty | Price | Total |
| 2 | 1001 | Pear | 3 | $1.50 | $4.50 |
| 3 | 1002 | Milk | 2 | $1.10 | $2.20 |
| 4 | 1003 | Apple | 5 | $1.20 | $6.00 |
| 5 | 1004 | Bread | 1 | $2.40 | $2.40 |
Buka tab Prices dan ubah harga Apple: total pesanannya langsung diperbarui. Dua hal yang perlu diperhatikan:
- Nama sheet yang mengandung spasi perlu tanda kutip tunggal:
=VLOOKUP(B2,'Price list'!$A$2:$B$6,2,FALSE). - Tabel di workbook lain menambahkan nama file di dalam kurung siku,
[Prices.xlsx]Prices!$A$2:$B$6. Jika file itu tertutup, Excel menampilkan path lengkapnya di rumus dan pencariannya tetap berjalan dari file yang tersimpan.
VLOOKUP dengan wildcard (pencocokan sebagian)
Dengan FALSE, nilai yang dicari boleh mengandung wildcard: * mewakili berapa pun jumlah karakter dan ? mewakili tepat satu karakter. "*"&E2&"*" menemukan produk pertama yang namanya mengandung teks di E2.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Contains | Price | |
| 2 | Green tea | Drinks | $3.20 | coffee | $2.90 | |
| 3 | Iced coffee | Drinks | $2.90 | |||
| 4 | Coffee beans | Pantry | $8.50 | |||
| 5 | Black tea | Drinks | $2.70 | |||
| 6 | Orange juice | Drinks | $3.40 |
"coffee" cocok dengan Iced coffee dan Coffee beans; VLOOKUP mengembalikan yang pertama dari atas, 8,50, atau menjadi juice. Untuk mencari tanda bintang atau tanda tanya yang sebenarnya, taruh tilde di depannya: "~*".
VLOOKUP ke kiri
VLOOKUP tidak bisa mengembalikan kolom di sebelah kiri kolom yang dicarinya: col_index_num hanya menghitung ke kanan, dan angka negatif adalah error. Untuk menemukan produk dari harga tertentu, cari di kolom C dan kembalikan kolom A dengan XLOOKUP atau INDEX dan MATCH:
=XLOOKUP(2.4, C2:C6, A2:A6) Excel 2021 and Microsoft 365
=INDEX(A2:A6, MATCH(2.4, C2:C6, 0)) every version
Keduanya mengembalikan Bread pada data di sheet pertama. XLOOKUP punya penjelasan lengkapnya.
Latihan: ongkos kirim berdasarkan berat
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | Cost | Weight (kg) | Cost | |
| 2 | 0 | $4.50 | 7 | ||
| 3 | 2 | $6.00 | |||
| 4 | 5 | $9.50 | |||
| 5 | 10 | $14.00 | |||
| 6 | 20 | $22.00 |
Giliranmu: Setiap ongkos berlaku mulai dari beratnya sampai berat berikutnya di daftar. Di E2, pakai VLOOKUP untuk mengembalikan ongkos kirim dari berat paket di D2.
VLOOKUP dengan dua kriteria
VLOOKUP hanya menerima satu nilai yang dicari. Untuk mencocokkan dua kolom, buat kolom bantu yang menggabungkan keduanya, taruh di posisi pertama tabel, lalu cari teks gabungan yang sama. Kolom A di bawah ini adalah =B2&"-"&C2 yang disalin ke bawah, jadi isinya Coffee-Small, Coffee-Large dan seterusnya.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Key | Product | Size | Price | Product | Size | Price |
| 2 | Coffee-Small | Coffee | Small | $2.50 | Tea | Large | |
| 3 | Coffee-Large | Coffee | Large | $3.50 | |||
| 4 | Tea-Small | Tea | Small | $2.00 | |||
| 5 | Tea-Large | Tea | Large | $3.00 | |||
| 6 | Juice-Small | Juice | Small | $3.00 |
Giliranmu: Kolom A menggabungkan produk dan ukuran dengan tanda hubung. Di G2, kembalikan harga untuk produk di E2 dan ukuran di F2.
Pemisahnya penting: "Tea"&"Large" menghasilkan TeaLarge, yang tidak cocok dengan apa pun di kolom A. Di Excel 2021 dan Microsoft 365, kamu bisa melewatkan kolom bantu dengan =XLOOKUP(1,(B2:B6=E2)*(C2:C6=F2),D2:D6); pencarian dengan banyak kriteria menunjukkan cara itu dan versi INDEX/MATCH-nya.
Pertanyaan yang sering diajukan
Bagaimana cara memakai VLOOKUP di Excel?
Ketik =VLOOKUP( lalu berikan empat argumen: nilai yang dicari, tabelnya (kolom pertamanya harus berisi nilai itu), nomor kolom yang dikembalikan, dan FALSE untuk pencocokan persis. =VLOOKUP("Pear",A2:D6,3,FALSE) mencari Pear di kolom A dan mengembalikan nilai dari kolom C di baris itu.
Apa arti TRUE atau FALSE di akhir VLOOKUP?
FALSE (atau 0) meminta pencocokan persis dan menghasilkan #N/A jika nilainya tidak ada. TRUE (atau 1, atau argumennya dihilangkan) meminta pencocokan perkiraan: nilai terbesar yang kurang dari atau sama dengan nilai yang dicari, yang hanya bekerja jika kolom pertamanya diurutkan naik.
Kenapa VLOOKUP saya hasilnya #N/A?
Nilainya tidak ditemukan di kolom pertama tabel. Penyebab yang biasa adalah salah ketik, spasi berlebih ("Milk " tidak sama dengan "Milk"), angka yang tersimpan sebagai teks hanya di salah satu sisi, atau nilai yang berada di kolom lain. Bungkus rumusnya dengan IFNA untuk menampilkan teksmu sendiri: =IFNA(VLOOKUP(F2,A2:D6,3,FALSE),"Not found").
Apakah VLOOKUP bisa mencari ke kiri?
Tidak. VLOOKUP hanya mengembalikan kolom di sebelah kanan kolom pertama tabel. Pakai =XLOOKUP(F2,C2:C6,A2:A6) di Excel 2021 atau Microsoft 365, atau =INDEX(A2:A6,MATCH(F2,C2:C6,0)) di versi mana pun.
Bagaimana cara memakai VLOOKUP dari sheet lain?
Tulis nama sheet dan tanda seru di depan range-nya: =VLOOKUP(B2,Prices!$A$2:$B$6,2,FALSE). Jika nama sheet-nya mengandung spasi, apit dengan tanda kutip tunggal: 'Price list'!$A$2:$B$6.
