anaksmanda.com - Saat mengisi daftar nilai, mengetik ulang nama siswa satu per satu bukan hanya melelahkan, tetapi juga rawan salah ketik. Padahal data siswa biasanya sudah tersedia di sheet lain. Dengan VLOOKUP, kalian cukup mengetik NIS, lalu nama dan kelas siswa muncul otomatis.
Ambil nama, kelas, dan data lain dari tabel induk siswa hanya dengan mengetik NIS, lengkap dengan cara mengatasi error #N/A dan alternatif XLOOKUP.
Rumus singkat
Jika NIS diketik di A2 dan tabel induk siswa ada di sheet DataSiswa kolom A sampai D:
=VLOOKUP(A2;DataSiswa!$A$2:$D$200;2;FALSE)
Sebelum mulai
Rumus di artikel ini memakai pemisah titik koma (;). Rentang tabel wajib dikunci dengan tanda $, penjelasannya ada di fungsi tanda $ di Excel.
1. Menyiapkan Tabel Induk Siswa
Buat satu sheet khusus bernama DataSiswa yang berisi seluruh data siswa. Kolom pertama wajib berisi NIS, karena VLOOKUP selalu mencari di kolom paling kiri tabel.
| Kolom A NIS | Kolom B Nama | Kolom C Kelas | Kolom D L/P |
|---|---|---|---|
| 2324001 | Andi Pratama | XI-1 | L |
| 2324002 | Budi Santoso | XI-1 | L |
| 2324003 | Citra Lestari | XI-2 | P |
| 2324004 | Dewi Anggraini | XI-2 | P |
Aturan tabel induk
- Setiap NIS hanya muncul satu kali. Jika ada NIS ganda, VLOOKUP hanya mengambil yang pertama ditemukan.
- Tulis semua NIS dalam format yang sama, semuanya angka atau semuanya teks.
- Hindari baris kosong dan sel gabungan (merge) di dalam tabel.
2. Cara Kerja VLOOKUP
VLOOKUP mencari sebuah nilai di kolom pertama tabel, lalu mengambil data dari kolom lain di baris yang sama. Bentuk penulisannya:
Data yang dijadikan kunci pencarian, misalnya NIS di sel A2.
Rentang tabel induk yang dikunci, misalnya DataSiswa!$A$2:$D$200.
Urutan kolom yang diambil, dihitung dari kolom pertama tabel. Nama = 2, Kelas = 3, L/P = 4.
Mencari data yang sama persis. Untuk NIS, bagian ini wajib FALSE (atau 0).
Jangan lupa FALSE
Jika argumen terakhir dikosongkan, VLOOKUP memakai pencarian perkiraan (TRUE). Pada data NIS, hal ini bisa menampilkan nama siswa lain tanpa pesan error. Selalu tulis FALSE saat mencari kode, NIS, atau nama.
3. Menampilkan Nama dan Kelas dari NIS
Misalkan daftar nilai ada di sheet Nilai. Kolom A untuk NIS, kolom B untuk nama, dan kolom C untuk kelas.
- Ketik NIS siswa di sel A2 pada sheet Nilai.
- Klik sel B2, lalu ketik rumus nama:
=VLOOKUP(A2;DataSiswa!$A$2:$D$200;2;FALSE)
- Klik sel C2, lalu ketik rumus kelas. Bedanya hanya nomor kolom:
=VLOOKUP(A2;DataSiswa!$A$2:$D$200;3;FALSE)
- Tarik kedua rumus ke bawah. Setiap kali NIS baru diketik, nama dan kelasnya langsung muncul.
| NIS (A) | Nama (B) | Kelas (C) | Nilai (D) |
|---|---|---|---|
| 2324003 | Citra Lestari | XI-2 | 88 |
| 2324001 | Andi Pratama | XI-1 | 82 |
| 2324009 | #N/A | #N/A | 75 |
Urutan NIS di daftar nilai tidak harus sama dengan tabel induk. Baris terakhir menampilkan #N/A karena NIS 2324009 tidak ada di tabel induk. Cara mengatasinya dibahas di bagian 5.
4. VLOOKUP dari Sheet Lain
Cara termudah menulis rujukan ke sheet lain adalah dengan mengekliknya langsung:
- Ketik
=VLOOKUP(A2;di sel tujuan. - Klik tab sheet DataSiswa, lalu blok tabel dari A2 sampai D200.
- Tekan F4 agar rentang terkunci menjadi
$A$2:$D$200. - Lanjutkan mengetik
;2;FALSE), lalu tekan Enter. Excel kembali ke sheet asal secara otomatis.
Nama sheet yang mengandung spasi
Jika nama sheet berisi spasi, misalnya "Data Siswa", Excel menuliskannya di dalam tanda kutip tunggal: 'Data Siswa'!$A$2:$D$200. Jika rujukan diketik manual, jangan lupakan tanda kutip ini.
5. Mengatasi Error #N/A
Error #N/A berarti VLOOKUP tidak menemukan nilai yang dicari. Penyebabnya tidak selalu karena datanya memang tidak ada. Periksa kemungkinan berikut secara berurutan.
| Penyebab | Cara mengecek | Solusi |
|---|---|---|
| NIS memang tidak ada di tabel induk | Cari NIS dengan Ctrl + F | Tambahkan siswa ke tabel induk atau perbaiki NIS |
| NIS angka dan NIS teks tercampur | Teks biasanya rata kiri, angka rata kanan | Samakan format kedua kolom |
| Ada spasi tersembunyi | Klik sel dan lihat isinya di bilah rumus | Bersihkan dengan TRIM |
| Rentang tidak dikunci | Periksa rumus di baris bawah | Kunci tabel dengan $ |
| Kolom NIS bukan kolom pertama tabel | Lihat awal rentang tabel | Mulai rentang dari kolom NIS |
NIS angka dan NIS teks
Ini penyebab yang paling sering terjadi. NIS yang diawali angka nol, misalnya 0012345, biasanya disimpan sebagai teks agar nol di depannya tidak hilang. Jika NIS di tabel induk berupa teks, sedangkan NIS di daftar nilai berupa angka, Excel menganggap keduanya berbeda. Solusi cepatnya, ubah nilai yang dicari menjadi teks dengan menambahkan &"":
Solusi yang lebih rapi adalah menyamakan format kedua kolom sejak awal, misalnya mengatur kolom NIS sebagai Text di kedua sheet sebelum data diketik.
Menampilkan pesan yang lebih ramah
Agar tampilan tidak dipenuhi #N/A saat NIS belum diisi atau salah ketik, bungkus VLOOKUP dengan IFERROR:
Pakai IFERROR setelah rumus benar
IFERROR menyembunyikan semua jenis error, termasuk kesalahan penulisan rumus. Pastikan rumus VLOOKUP sudah berjalan benar lebih dulu, baru tambahkan IFERROR.
6. Alternatif Lebih Mudah: XLOOKUP
Microsoft kini menyarankan XLOOKUP sebagai pengganti VLOOKUP. XLOOKUP tidak memakai nomor kolom, bisa mencari ke arah kiri, dan secara bawaan sudah mencari data yang sama persis.
Urutannya: nilai yang dicari, kolom tempat mencari, kolom hasil, lalu pesan jika data tidak ditemukan. Karena kolom hasil ditulis langsung, rumus tidak rusak meskipun ada kolom baru yang disisipkan di tabel induk.
Perhatikan versi Excel
Menurut Microsoft, XLOOKUP tidak tersedia di Excel 2016 dan Excel 2019. Fungsi ini ada di Excel 2021, Excel 2024, dan Microsoft 365. Jika file dibuka di komputer sekolah dengan versi lama, VLOOKUP tetap pilihan yang paling aman.
7. Kesalahan Lain yang Sering Terjadi
Nomor kolom lebih besar dari jumlah kolom tabel. Rentang A:D hanya punya 4 kolom, jadi nomor kolom 5 menghasilkan #REF!.
Biasanya karena argumen terakhir kosong atau TRUE. Ganti dengan FALSE agar hanya NIS yang sama persis yang diambil.
Setelah kolom baru disisipkan di tabel induk, VLOOKUP mengambil kolom yang salah karena nomor kolomnya tetap. Perbarui nomor kolom atau gunakan XLOOKUP.
Siswa ditambahkan di bawah baris 200, di luar rentang tabel. Buat rentang yang cukup besar sejak awal atau perbarui rentangnya.
8. Pertanyaan yang Sering Diajukan
Apa beda VLOOKUP dan HLOOKUP?
VLOOKUP mencari secara vertikal, yaitu di kolom pertama tabel lalu mengambil data ke samping. HLOOKUP mencari secara horizontal, yaitu di baris pertama tabel lalu mengambil data ke bawah. Untuk data siswa yang tersusun ke bawah, VLOOKUP yang dipakai.
Bisakah VLOOKUP mencari NIS berdasarkan nama?
Tidak secara langsung, karena VLOOKUP hanya mengambil data di sebelah kanan kolom pencarian, sedangkan NIS berada di kiri nama. Gunakan XLOOKUP, atau gabungan INDEX dan MATCH, untuk pencarian ke arah kiri.
Apakah VLOOKUP membedakan huruf besar dan kecil?
Tidak. VLOOKUP menganggap "andi" dan "ANDI" sama. Ini salah satu alasan NIS lebih baik dijadikan kunci daripada nama.
Apakah VLOOKUP bisa mengambil data dari file Excel lain?
Bisa, tetapi rujukannya akan bergantung pada lokasi file tersebut. Jika file sumber dipindahkan atau diganti namanya, rumus perlu diperbarui. Untuk keperluan sekolah, menyimpan data induk di sheet lain dalam file yang sama biasanya lebih praktis.
Apakah VLOOKUP dan XLOOKUP ada di Google Sheets?
Ada. Keduanya tersedia di Google Sheets dengan cara penulisan yang sama, begitu juga IFERROR.
9. Kesimpulan
VLOOKUP membuat nama, kelas, dan data siswa lain muncul otomatis hanya dari NIS. Kuncinya ada pada tabel induk yang rapi: NIS di kolom pertama, tidak ada NIS ganda, dan formatnya seragam.
Selalu gunakan FALSE untuk pencarian persis, kunci rentang tabel dengan tanda $, dan bungkus dengan IFERROR setelah rumus berjalan benar. Jika memakai Excel 2021 ke atas, XLOOKUP bisa menjadi pilihan yang lebih sederhana.
- Rumus Excel pakai koma atau titik koma
- Fungsi tanda $ di Excel
- Rumus IF predikat nilai A, B, C, D
- Rumus IF AND: lulus dengan KKM dan kehadiran
- Rumus ranking siswa, termasuk jika nilainya sama
- Cara menghitung nilai rapor dengan bobot
- Rumus COUNTIF untuk menghitung siswa lulus dan remedial
- VLOOKUP untuk menampilkan nama siswa dari NIS (sedang dibaca)
- Rumus menghitung umur per tanggal tertentu
- Cara mengunci rumus agar tidak terhapus
- Studi kasus: membuat rekap nilai otomatis satu kelas
Komentar
Memuat komentar...
Tulis Komentar
Silakan bertanya atau berbagi pengalaman. Komentar berisi tautan aktif atau promosi akan dihapus.