Mencari data dengan VLOOKUP
Jika argumen terakhir VLOOKUP dihilangkan, Excel dan Google Sheets sama-sama memakai kecocokan perkiraan. Google menyarankan mengetik FALSE.
Daftar harga dengan kode
Sebuah toko menyimpan harganya di tabel kecil: kode di A2:A5, nama barang di B2:B5, harga di C2:C5. Di kasir, seseorang mengetik kode dan ingin tahu harganya. Itulah tugas VLOOKUP. Fungsi ini mencari nilai di kolom pertama tabel dan mengembalikan sesuatu dari baris yang sama. Microsoft mendaftar empat bagian yang diperlukannya: nilai yang dicari, rentang tempat nilai itu berada, nomor kolom dalam rentang yang berisi jawabannya, dan apakah kamu ingin kecocokan perkiraan (TRUE) atau tepat (FALSE). =VLOOKUP(104, A2:C5, 3, FALSE) menemukan 104 di kolom A dan mengembalikan harga dari kolom ketiga rentang.
Dua aturan menimbulkan sebagian besar masalah. Pertama, nilai yang dicari harus ada di kolom pertama rentang, dan jawabannya harus berada di sebelah kanannya. VLOOKUP tidak pernah melihat ke kiri, jadi jika kodenya ada di kolom B, rentangnya harus dimulai dari B. Kedua, nomor kolom dihitung dari kolom pertama rentang, bukan dari kolom A lembar kerja; pada B2:D9, B adalah 1, C adalah 2 dan D adalah 3. Kedua halaman bantuan juga memberi saran yang sama untuk rumus yang disalin. Jadikan rentangnya absolut, seperti $A$2:$C$5, agar tidak bergeser saat diisi ke bawah.
Jika kodenya tidak ada di daftar
Dengan FALSE, VLOOKUP mencari tepat nilai yang kamu berikan dan mengembalikan baris pertama yang cocok. Jika tidak ada yang cocok, hasilnya #N/A. Nomor kolom yang lebih besar dari banyak kolom dalam rentang memberi #REF!, dan nomor di bawah 1 memberi #VALUE!. Pencarian teks mengabaikan huruf besar-kecil tetapi tidak mengabaikan spasi: bantuan Google menunjukkan bahwa " Apple", "Apple " dan "Apple" adalah tiga nilai berbeda. Bilangan yang disimpan sebagai teks juga berbeda dari bilangannya, jadi kode yang diketik sebagai '104 tidak ditemukan oleh 104.
Dengan kecocokan tepat, karakter pengganti ? dan * berlaku di kedua program. ? mewakili satu karakter apa pun dan * mewakili rangkaian karakter apa pun, jadi "Ora*" menemukan Orange. Jika #N/A adalah hasil yang wajar, misalnya kode yang belum ada di daftar harga, bungkus pencarian dengan IFNA, yang ada di kedua program: =IFNA(VLOOKUP(E2, $A$2:$C$5, 3, FALSE), "not found"). IFERROR juga bisa. Namun, fungsi itu menyembunyikan semua jenis galat, termasuk nomor kolom yang salah yang justru ingin kamu lihat, dan halaman #VALUE! dari Microsoft memperingatkan tentang IFERROR karena alasan yang sama.
Seret untuk memutar. Gulir atau cubit untuk memperbesar.
Mencari rentang nilai, bukan kode yang tepat
TRUE, atau tanpa argumen keempat sama sekali, memberi kecocokan perkiraan, nilai bawaan di kedua program. Bantuan Google menjelaskan cara pencariannya: menelusuri kolom pertama ke bawah sampai menemukan nilai yang lebih besar dari kunci pencarian, lalu berhenti di baris sebelumnya. Cara ini cocok untuk rentang nilai. Pada tabel nilai huruf dengan 0, 60, 70, 80 dan 90 di kolom pertama, nilai 85 melewati 80, bertemu 90, dan mengembalikan B dari baris 80. Kolom pertama harus diurutkan dari terkecil ke terbesar, dan nilai di bawah baris pertama mengembalikan #N/A. Pada kolom yang tidak terurut, jawabannya tidak bisa dipercaya, karena itu Google menyarankan FALSE kecuali kamu memang bermaksud mencari rentang.
Seret untuk memutar. Gulir atau cubit untuk memperbesar.
| Microsoft Excel | Google Sheets | |
|---|---|---|
| Nama argumen ke-4 | range_lookup | is_sorted |
| Nilai bawaan jika dihilangkan | TRUE (perkiraan) | TRUE (perkiraan) |
| Saran di halaman bantuan | urutkan kolom pertama, atau pakai FALSE | FALSE disarankan |
| XLOOKUP | Microsoft 365 dan Excel 2021 ke atas; tidak ada di 2016 atau 2019 | tersedia |
| Rumus yang tidak terbaca | ditolak dengan pesan | #ERROR! |
| Referensi melingkar | peringatan, lalu 0 atau nilai terakhir | #REF! |
| ROUND tanpa digit | tidak diterima | membulatkan ke bilangan bulat |
| Argumen per fungsi | sampai 255 | berapa pun |
Kini kedua program juga punya XLOOKUP. Fungsi ini menerima rentang pencarian dan rentang hasil yang terpisah, jadi jawabannya boleh berada di sebelah kiri, dan memakai kecocokan tepat kecuali diminta lain. Microsoft mencatat bahwa XLOOKUP tidak tersedia di Excel 2016 atau Excel 2019, jadi berkas yang harus dibuka di versi-versi itu sebaiknya tetap memakai VLOOKUP. Tabel di atas mengumpulkan perbedaan kedua program yang sudah ditemui dalam kursus ini.
- Menghilangkan FALSE saat mencari kode. Nilai bawaannya kecocokan perkiraan, jadi kode yang tidak ada di daftar bisa mengembalikan harga dari baris di atasnya, bukan #N/A.
- Menghitung nomor kolom dari kolom A lembar kerja. Nomor kolom dihitung dari kolom pertama rentang. Dengan C2:F20, kolom E adalah 3, bukan 5.
- Mengisi pencarian ke bawah tanpa $ pada rentang. A2:C5 bergeser menjadi A3:C6, A4:C7 dan seterusnya, dan baris-baris bawah tidak lagi menemukan kode di bagian atas tabel.
- Membungkus setiap pencarian dengan IFERROR. Fungsi itu menyembunyikan #REF! akibat nomor kolom yang salah sekaligus #N/A yang memang diharapkan. IFNA hanya menyembunyikan #N/A.
- Kode 101, 103, 104, 107 dengan barang Apple, Banana, Orange, Pear di A2:B5. Apa yang dikembalikan =VLOOKUP(103, A2:B5, 2, FALSE)?
- Tabel yang sama. Apa yang dikembalikan =VLOOKUP(102, A2:B5, 2, FALSE)?
- #N/A
- Apple
- Banana
- #REF!
- Tabel yang sama. Apa yang dikembalikan =VLOOKUP(102, A2:B5, 2, TRUE)?
- Tabel yang sama. Apa yang dikembalikan =VLOOKUP(103, A2:B5, 3, FALSE)?
- #REF!
- #N/A
- #VALUE!
- Banana
- Rentangnya C2:F20. Nomor kolom berapa yang mengembalikan nilai dari kolom E?
- Tabel nilai: 0 F, 60 D, 70 C, 80 B, 90 A. Apa yang dikembalikan VLOOKUP perkiraan untuk 85?
- Tabel yang sama. Apa yang dikembalikan untuk tepat 90?
- Apa yang terjadi jika argumen keempat dihilangkan?
- Kecocokan perkiraan di kedua program
- Kecocokan tepat di kedua program
- Tepat di Excel, perkiraan di Sheets
- Galat
- Mengapa rentang tabel sebaiknya ditulis $A$2:$C$5?
- Agar tidak bergeser saat rumus diisi ke bawah
- Agar tabelnya terurut
- Agar hanya cocok dengan huruf besar
- Agar mengembalikan teks
- Barang ada di B2:B5 dan harga di C2:C5. Apa yang dikembalikan =IFNA(VLOOKUP("Pencil", B2:C5, 2, FALSE), "not found")?
- Versi Excel mana yang tidak punya XLOOKUP?
- Excel 2016 dan Excel 2019
- Microsoft 365
- Excel untuk web
- Excel 2021
- VLOOKUP(nilai, rentang, nomor kolom, FALSE) untuk kecocokan tepat.
- Nilai yang dicari harus ada di kolom pertama; nomor kolom dihitung dari rentang.
- TRUE (atau tanpa argumen) berarti perkiraan: kolom pertama harus terurut, dan hasilnya baris terakhir yang tidak lebih besar dari nilai yang dicari.
- Pakai rentang absolut, IFNA untuk #N/A yang diharapkan, dan FALSE kecuali kamu bermaksud mencari rentang nilai.
Sumber kursus ini (19)
- Microsoft Support, "Switch between relative, absolute, and mixed references" (support.microsoft.com/en-us/office/dfec08cd-ae65-4f56-839e-5f0d8d0baca9: $ before column and row, F4 to switch, copying two down and two right: $A$1 stays, A$1 to C$1, $A1 to $A3, A1 to C3)
- Microsoft Support, "AVERAGE function" (support.microsoft.com/en-us/office/047bac88-d466-426c-a32b-8f33eb960cf6: text, logical values and empty cells in a reference ignored, zeros included; 10, 7, 9, 27, 2 gives 11, with 5 gives 10; mean 5, median 4, mode 3 for 2 3 3 5 7 10)
- Microsoft Support, "COUNT function" (support.microsoft.com/en-us/office/a59cd7fc-b623-4d93-87a4-d23bf411294c: only numbers counted, dates count, errors and text do not)
- Microsoft Support, "ROUND function" (support.microsoft.com/en-us/office/c018c5d8-40fb-4053-90b1-b3e7f61a213c: num_digits required; 2.15 to 2.2, -1.475 to -1.48, 626.3 to 1000, 1.98 to 0, -50.55 to -100)
- Microsoft Support, "IF function" (support.microsoft.com/en-us/office/69aed7c9-4e8a-4755-a9bc-aa8bbff73be2: value_if_false optional, text in quotes except TRUE and FALSE, 0 and #NAME? problems)
- Microsoft Support, "VLOOKUP function" (support.microsoft.com/en-us/office/0bbc8083-26fe-4963-8ab8-93a18ad188a1: range_lookup TRUE by default, sorted first column, #N/A, #REF!, #VALUE!, absolute table range, wildcards with FALSE)
- Microsoft Support, "XLOOKUP function" (support.microsoft.com/en-us/office/b7fd680e-6d10-43e6-84f9-88eae8bf5929: not in Excel 2016 and Excel 2019, exact match by default)
- Microsoft Support, "How to correct a #VALUE! error" (support.microsoft.com/en-us/office/15e1b616-fbf2-4147-9c0b-0a11a20e409e: + and * may not calculate cells with text, use SUM instead, IFERROR hides all errors)
- Microsoft Support, "Excel specifications and limits" (15 digits, 64 nested levels, 255 arguments)
- Google Docs Editors Help, "VLOOKUP" (support.google.com/docs/answer/3093318: is_sorted TRUE by default, FALSE recommended, search down to a larger value, " Apple" and "Apple " differ, wildcards, IFNA, absolute range)
- Google Docs Editors Help, "XLOOKUP" (support.google.com/docs/answer/12405947)
- Google Docs Editors Help, "ROUND" (support.google.com/docs/answer/3093440: places optional, 0 by default, rounding by magnitude)
- Google Docs Editors Help, "IF" (support.google.com/docs/answer/3093364: order of the answers is the most common problem, IFS)
- Google Docs Editors Help, "AVERAGE" (support.google.com/docs/answer/3093615: text ignored, any number of arguments)
- Google Docs Editors Help, "COUNTA" (support.google.com/docs/answer/3093991: zero-length strings and spaces count)
- Google Docs Editors Help, "Keyboard shortcuts for Google Sheets" (support.google.com/docs/answer/181110: F4 and Fn+F4 while entering a formula, F4 as Redo)
- Pryor Learning and Exceljet (F4 cycle A1, $A$1, A$1, $A1; = comparison not case-sensitive)
- Ablebits (IF without value_if_false returns FALSE)
- Spreadsheet Class (Sheets circular dependency shows #REF!)
Diperiksa: 2026-10-19