Pertemuan 10: VLOOKUP, INDEX+MATCH, Teks, dan Tanggal

Tujuan dan alur praktikum

Lomba Debat Kebijakan Publik diikuti 15 peserta dari lima sekolah. Panitia perlu mencetak kartu peserta untuk masing-masing dari mereka. Alih-alih membuat 15 desain terpisah dan mengetik ulang data satu per satu, hari ini Anda membuat satu kartu "pintar": cukup ketik NIS peserta di satu sel, dan seluruh data lainnya (nama, sekolah, tim, bahkan usia) terisi sendiri.

Setelah praktikum ini, Anda mampu:

Mengambil data dari tabel lain dengan VLOOKUP.
Menjelaskan kapan INDEX+MATCH lebih tepat dipakai daripada VLOOKUP.
Mencari data pada tabel yang disusun mendatar dengan HLOOKUP.
Memotong dan menggabungkan teks dengan LEFT, MID, RIGHT, dan CONCAT.
Menghitung usia atau masa kerja otomatis dengan TODAY dan DATEDIF.

Alur waktu di lab

0–15'	Pengantar dan konsep VLOOKUP
15–35'	Lab menyusun VLOOKUP, lalu INDEX+MATCH dan HLOOKUP (materi)
35–55'	Fungsi teks dan fungsi tanggal (materi dan contoh)
55–125'	Praktik 1–5: database, kartu, usia, kode peserta, INDEX+MATCH/HLOOKUP
125–150'	Latihan mandiri, checklist, dan kuis

Satu sumber data, banyak keluaran

Bayangkan sebuah loket pelayanan: warga menyebutkan NIK, petugas mengetiknya di komputer, dan seketika muncul nama, alamat, serta status kependudukannya. Petugas itu tidak mengetik ulang data, hanya mencarinya dari database yang sudah ada. Fungsi pencarian di Excel bekerja dengan prinsip yang sama, dan menjadi dasar dari hampir semua sistem informasi pelayanan publik.

Satu database, banyak dokumen

Dengan pendekatan hari ini, kartu peserta, undangan bernama, dan daftar hadir semuanya bisa mengambil data dari satu Database yang sama. Jika ada data yang salah, Anda memperbaikinya sekali saja di Database, dan semua dokumen yang mengambil data dari sana ikut benar.

VLOOKUP: mencari tegak lurus

VLOOKUP (Vertical Lookup) mencari sebuah nilai pada kolom paling kiri sebuah tabel, lalu mengambil data dari kolom lain di baris yang sama.

=VLOOKUP( nilai_dicari , tabel , nomor_kolom , 0 )

Bagian	Penjelasan
nilai_dicari	Data kunci yang ingin dicocokkan, misalnya NIS pada sel B3.
tabel	Range tabel sumber data. Kolom pertamanya harus berisi data kunci itu.
nomor_kolom	Urutan kolom (dihitung dari kolom pertama tabel, mulai dari 1) yang nilainya ingin diambil.
0 atau FALSE	Meminta kecocokan persis. Hampir selalu inilah yang Anda inginkan.

Contoh: =VLOOKUP(B3,Database!B5:G19,2,0) berarti "cari nilai di B3 pada kolom pertama range Database!B5:G19, lalu ambil data dari kolom ke-2 range itu (dihitung dari kolom NIS)."

Kolom kunci harus di paling kiri

VLOOKUP hanya bisa mencari ke kanan. Jika NIS ada di kolom B dan Anda ingin mengambil data dari kolom A (di sebelah kirinya), VLOOKUP akan gagal. Untuk kasus seperti ini, gunakan INDEX+MATCH.

Lab: menyusun VLOOKUP

Susun sendiri rumus VLOOKUP untuk mengambil Nama dan Sekolah berdasarkan NIS yang diketik di E2.

F2 fx

Tugas di lab ini (sel kuning boleh diisi). Tabel bantu ada di kolom A–C; NIS yang dicari ada di D2.

Klik F2, susun VLOOKUP untuk mengambil Nama (kolom ke-2) berdasarkan NIS di D2, tekan Enter.
Klik F3, susun VLOOKUP untuk mengambil Sekolah (kolom ke-3), tekan Enter.
Ganti isi D2 menjadi P004, lalu P099 (NIS yang tidak ada). Apa yang tampil untuk NIS yang tidak terdaftar?

Arti #N/A

#N/A berarti "data tidak ditemukan" (Not Available), bukan tanda rumus rusak. Ini kesalahan yang wajar terjadi bila nilai yang dicari memang belum ada di tabel sumber, misalnya karena salah ketik NIS.

INDEX + MATCH: alternatif yang lebih lentur

Gabungan dua fungsi ini melakukan hal yang sama seperti VLOOKUP, tetapi lebih fleksibel karena kolom kuncinya boleh berada di mana saja.

MATCH: menemukan posisi

=MATCH(B3,Database!B5:B19,0) mencari di baris keberapa NIS itu berada di dalam range, lalu mengembalikan nomor urutnya (bukan datanya).

INDEX: mengambil isi berdasarkan posisi

=INDEX(Database!E5:E19,4) mengambil isi baris ke-4 dari range itu. Gabungkan keduanya, dan angka 4 pada INDEX digantikan hasil pencarian MATCH.

=INDEX( kolom_hasil , MATCH( nilai_dicari , kolom_kunci , 0 ) )

Contoh: =INDEX(Database!E5:E19,MATCH(B3,Database!B5:B19,0)) mengambil Nama Tim (kolom E) berdasarkan NIS di B3, meski kolom kunci (NIS) dan kolom hasil tidak harus berurutan atau bersebelahan.

Kapan memilih yang mana?

VLOOKUP lebih pendek dan cukup untuk sebagian besar kebutuhan sehari-hari. INDEX+MATCH dipilih ketika kolom kunci bukan di paling kiri, atau ketika tabelnya sangat besar dan performa pencarian menjadi perhatian. Menguasai keduanya membuat Anda tidak terjebak saat struktur data tidak ideal.

HLOOKUP dan pencarian rentang

HLOOKUP (Horizontal Lookup) bekerja seperti VLOOKUP, tetapi tabelnya disusun mendatar: baris pertama berisi data kunci, dan hasil diambil dari baris di bawahnya.

HLOOKUP juga sering dipakai untuk pencarian rentang (bukan kecocokan persis), misalnya mengklasifikasikan usia ke dalam kategori:

Usia Minimum	15	17	19
Kategori	Yunior	Senior	Terbuka

=HLOOKUP(17,B3:D4,2,TRUE) — argumen terakhir TRUE (bukan 0/FALSE) berarti "cari nilai terbesar yang masih lebih kecil atau sama dengan 17". Karena 17 berada di antara 17 dan 19, hasilnya Senior. Syaratnya, baris kunci harus terurut menaik.

TRUE hanya untuk data yang terurut

Pencarian rentang (TRUE) hanya bekerja benar jika data kuncinya sudah diurutkan dari kecil ke besar. Untuk mencocokkan data yang tidak terurut seperti NIS atau nama, selalu gunakan 0 atau FALSE.

Membedah dan menggabung teks

Fungsi	Fungsinya	Contoh	Hasil
`LEFT`	Mengambil beberapa huruf dari kiri	`=LEFT("Sekolah C",3)`	Sek
`RIGHT`	Mengambil beberapa huruf dari kanan	`=RIGHT("P003",3)`	003
`MID`	Mengambil huruf dari posisi tertentu	`=MID("2026001",3,2)`	26
`LEN`	Menghitung jumlah karakter	`=LEN("Ayu")`	3
`UPPER` / `LOWER` / `PROPER`	Mengubah huruf besar/kecil/kapital awal kata	`=PROPER("ayu lestari")`	Ayu Lestari
`CONCAT` atau `&`	Menggabungkan teks	`=B6&" - "&B7`	Sekolah C - Tim Hijau

Tanda & adalah cara cepat menggabung teks tanpa menulis fungsi, dan bisa dipakai berkali-kali dalam satu rumus: =B5&" ("&B6&")". Fungsi TEXTJOIN(pemisah, abaikan_kosong, range) berguna saat menggabung banyak sel sekaligus.

TODAY dan DATEDIF

=TODAY() menampilkan tanggal hari ini secara otomatis, mengikuti kalender komputer. Tidak perlu argumen apa pun di dalam kurungnya.

DATEDIF menghitung selisih dua tanggal dalam satuan yang Anda pilih:

=DATEDIF( tanggal_awal , tanggal_akhir , "satuan" )

Satuan	Artinya
`"Y"`	Selisih dalam tahun penuh
`"M"`	Selisih dalam bulan penuh
`"D"`	Selisih dalam hari

Untuk menghitung usia: =DATEDIF(TanggalLahir,TODAY(),"Y"). Karena bagian kedua memakai TODAY(), usia yang ditampilkan akan selalu benar kapan pun file itu dibuka, tanpa perlu dihitung ulang manual.

Dalam simulator di halaman ini

Supaya latihan konsisten untuk semua orang, simulator pada halaman ini menganggap "hari ini" sebagai 25 September 2026, bukan tanggal sebenarnya di komputer Anda. Di Excel sungguhan, TODAY() selalu mengikuti tanggal komputer.

Unduh file bahan

X ATTD_P10_Bahan_Database_Peserta.xlsxSheet Database (15 peserta), Kartu, dan Kategori Usia, tanpa rumus Unduh file

Simpan salinannya (F12) sebagai ATTD_P10_Database_Peserta_NIM_Nama.xlsx di folder 02_Excel\P10.

Praktik 1: Menyiapkan Database dan Kartu

Jelajahi sheet Database Berisi 15 peserta dengan kolom NIS, Nama, Asal Sekolah, Nama Tim, Tanggal Lahir, dan Kontak. Kolom Tanggal Lahir sudah berformat tanggal.
Jelajahi sheet Kartu Sel B3 adalah tempat mengetik NIS yang dicari. Baris di bawahnya (Nama, Asal Sekolah, dan seterusnya) akan kita isi dengan rumus pencarian.

Praktik 2: VLOOKUP pada kartu

Ambil Nama Di sheet Kartu, klik B5, ketik =VLOOKUP($B$3,Database!$B$5:$G$19,2,0), Enter.
Ambil Asal Sekolah dan Nama Tim Di B6, ketik =VLOOKUP($B$3,Database!$B$5:$G$19,3,0). Di B7, ganti angka kolomnya menjadi 4 untuk mengambil Nama Tim.
Ambil Tanggal Lahir Di B8, ambil kolom ke-5: =VLOOKUP($B$3,Database!$B$5:$G$19,5,0).
Uji dengan beberapa NIS Ganti isi B3 dengan beberapa NIS berbeda dari sheet Database, misalnya P007 dan P015. Pastikan seluruh data berubah mengikuti.

Kenapa alamat tabelnya memakai $?

$B$5:$G$19 dikunci penuh karena, walaupun di sini kita tidak menyalin rumus ke bawah, kebiasaan mengunci range tabel referensi akan sangat membantu saat Anda menyalin rumus VLOOKUP ke banyak baris pada latihan berikutnya.

Praktik 3: Usia otomatis dengan DATEDIF

Hitung usia Di B9 (baris "Usia (tahun)"), ketik =DATEDIF(B8,TODAY(),"Y").
Format Tanggal Lahir Pastikan B8 tampil sebagai tanggal (misalnya 20/01/2008), bukan angka aneh. Jika perlu, blok B8, Ctrl + 1, pilih kategori Date.
Bandingkan beberapa peserta Ganti NIS di B3 ke beberapa peserta lain dan amati usia yang dihasilkan. Semuanya dihitung otomatis dari tanggal lahir masing-masing.

Praktik 4: Kode peserta dan label lengkap

Panitia ingin setiap peserta mendapat kode unik untuk keperluan absensi, misalnya gabungan tiga huruf pertama sekolah dan tiga digit terakhir NIS.

Kode peserta Di B10 ("Kode Peserta"), ketik =LEFT(B6,3)&"-"&RIGHT($B$3,3). Untuk peserta dengan sekolah "Sekolah C" dan NIS "P003", hasilnya Sek-003.
Label lengkap untuk kartu nama meja Di B11 ("Label Lengkap"), ketik =PROPER(B5)&" ("&B6&") - "&B7. PROPER memastikan nama tertulis rapi (huruf besar di awal setiap kata) meski di Database mungkin diketik sembarangan.

Praktik 5: Mencoba INDEX+MATCH dan HLOOKUP

INDEX+MATCH sebagai pembanding Di sel kosong pada sheet Kartu, misalnya D7, ketik =INDEX(Database!$E$5:$E$19,MATCH($B$3,Database!$B$5:$B$19,0)). Bandingkan hasilnya dengan B7 (VLOOKUP kolom Nama Tim): keduanya harus sama.
Coba HLOOKUP Buka sheet Kategori Usia. Di B7, ketik =HLOOKUP(B6,B3:D4,2,TRUE). Ubah nilai usia di B6 menjadi 15, 16, 18, dan 20, lalu amati kategorinya berubah sesuai rentang.

Menghubungkan ke Praktik 3

Sebagai latihan tambahan (opsional), coba ganti B6 di sheet Kategori Usia dengan rujukan ke usia hasil DATEDIF di sheet Kartu, misalnya =Kartu!B9, sehingga kategori usia peserta terhitung otomatis dari data kelahirannya.

Tombol pintas hari ini

Tombol	Fungsi
`F4` (saat mengetik rumus)	Mengunci alamat sel/range menjadi absolut ($)
`Ctrl` + `;`	Menyisipkan tanggal hari ini sebagai nilai tetap (bukan rumus)
`Ctrl` + `1`	Membuka Format Cells, termasuk format Date
`Ctrl` + `	Menampilkan atau menyembunyikan rumus

Kesalahan yang sering terjadi

Tampilan	Penyebab	Solusi
#N/A	Nilai yang dicari tidak ada di kolom kunci, atau salah ketik.	Periksa ejaan nilai yang dicari dan pastikan ada di tabel sumber.
VLOOKUP mengambil data yang salah	Nomor kolom salah hitung, atau argumen terakhir bukan 0/FALSE sehingga mencari rentang, bukan persis.	Hitung ulang nomor kolom dari kolom pertama tabel, dan pastikan argumen terakhir 0.
#REF! pada VLOOKUP	Nomor kolom melebihi jumlah kolom pada range tabel.	Periksa kembali range tabel dan nomor kolomnya.
Usia yang dihasilkan DATEDIF aneh atau negatif	Urutan tanggal awal dan akhir terbalik.	Tanggal lahir harus jadi argumen pertama, TODAY() argumen kedua.
HLOOKUP dengan TRUE memberi hasil salah	Baris kunci tidak terurut menaik.	Urutkan dulu data kuncinya dari kecil ke besar, atau gunakan 0/FALSE jika mencari kecocokan persis.
Rumus VLOOKUP tidak bisa mengambil kolom di sebelah kiri kolom kunci	Keterbatasan VLOOKUP yang hanya mencari ke kanan.	Gunakan INDEX+MATCH.

Latihan mandiri

Dasar

Kontak peserta

Tambahkan baris "Kontak" pada kartu (B12), ambil datanya dengan VLOOKUP kolom ke-6 dari Database.

Menengah

Status remaja/dewasa

Tambahkan baris "Status" yang bernilai "Remaja" jika usia di bawah 18, atau "Dewasa" jika 18 ke atas, menggabungkan IF dengan hasil DATEDIF: =IF(B9<18,"Remaja","Dewasa").

Tantangan

Pencarian dua kriteria dengan INDEX+MATCH

Di sheet Database, tambahkan kolom bantu "Kunci" yang menggabungkan Sekolah dan Tim (=D5&"|"&E5). Lalu di sheet Kartu, buat pencarian yang mengambil NIS berdasarkan kombinasi Sekolah dan Tim tertentu memakai INDEX+MATCH terhadap kolom Kunci itu, misalnya mencari NIS pemilik "Sekolah A|Tim Emas".

Tugas pertemuan 10

Dikumpulkan

Kartu Anggota Study Club atau Panitia

Buat Database baru berisi minimal 10 orang (boleh anggota kelompok tugas, panitia kegiatan kampus, atau data rekaan yang wajar) dengan kolom: Kode Anggota, Nama, Program Studi/Jabatan, Divisi/Seksi, dan Tanggal Bergabung. Lalu buat sheet Kartu yang mengambil seluruh data tersebut berdasarkan Kode Anggota yang diketik di satu sel, dengan ketentuan:

Minimal tiga data diambil dengan VLOOKUP, dan minimal satu data yang sama diambil ulang dengan INDEX+MATCH sebagai pembanding.
Kolom "Lama Bergabung (bulan)" dihitung dengan DATEDIF dan TODAY.
Kolom "Kode Unik" yang digabung dari potongan teks (LEFT/RIGHT/MID) dan operator &.
Kolom "Label Lengkap" yang menggabungkan nama (diformat PROPER) dengan jabatan/divisinya.

Simpan sebagai ATTD_P10_Kartu_Anggota_NIM_Nama.xlsx di 02_Excel\P10, unggah ke OneDrive, dan kumpulkan melalui kanal yang ditentukan dosen.

Checklist hasil praktikum

Sheet Kartu mengambil Nama, Asal Sekolah, Nama Tim, dan Tanggal Lahir dengan VLOOKUP.
Mengubah NIS di B3 membuat seluruh data kartu ikut berubah.
Usia dihitung otomatis dengan DATEDIF dan TODAY.
Kode Peserta dan Label Lengkap tersusun dari LEFT/RIGHT/PROPER dan operator &.
Sudah mencoba INDEX+MATCH dan hasilnya sama dengan VLOOKUP.
Sudah mencoba HLOOKUP pada tabel Kategori Usia.
Memahami arti #N/A dan bisa menjelaskannya.
Tersimpan di 02_Excel\P10 dan OneDrive.

Kuis cek pemahaman

Pilih jawaban, lalu baca penjelasannya. Belum ada jawaban

1. Pada =VLOOKUP(B3,Database!B5:G19,4,0), angka 4 berarti...

Nomor kolom pada VLOOKUP dihitung dari kolom pertama range tabel, bukan dari kolom lembar kerja (A, B, C, ...).

2. Sel menampilkan #N/A. Artinya...

#N/A adalah kesalahan wajar yang menandakan data yang dicari memang belum ada, bukan kerusakan rumus.

3. Data yang dicari (Nama Tim) berada di kolom sebelah kiri kolom kunci (NIS). Fungsi yang tepat digunakan adalah...

VLOOKUP hanya bisa mengambil data di sebelah kanan kolom kunci. INDEX+MATCH tidak punya batasan arah seperti itu.

4. Rumus untuk menghitung usia dari tanggal lahir di B8 hingga hari ini adalah...

Tanggal awal (tanggal lahir) harus diletakkan lebih dulu, diikuti tanggal akhir (TODAY), lalu satuan "Y" untuk tahun.

5. Argumen terakhir HLOOKUP diisi TRUE. Syarat agar hasilnya benar adalah...

Pencarian rentang (TRUE) mengasumsikan data terurut; jika tidak, hasilnya bisa keliru.

Rubrik penilaian tugas

Aspek	Bobot	Kriteria nilai penuh
Kelengkapan Database	15%	Minimal 10 baris data yang wajar dan lengkap kolomnya.
VLOOKUP pada Kartu	25%	Minimal tiga data diambil dengan benar dan ikut berubah saat kunci pencarian diganti.
INDEX+MATCH	15%	Minimal satu data yang sama diambil ulang dengan INDEX+MATCH, hasilnya sama dengan VLOOKUP.
DATEDIF	15%	Lama bergabung terhitung otomatis dan masuk akal.
Fungsi teks (Kode Unik dan Label)	20%	Menggunakan LEFT/RIGHT/MID/PROPER dan operator & dengan tepat.
Kerapian dan file	10%	Format rapi, nama file sesuai aturan, terunggah ke OneDrive.

Pertemuan berikutnya

Pertemuan 11: Sort, Filter, Conditional Formatting lanjutan, dan grafik

Buka Pertemuan 11