Lookup & Reference – Jonathan Web

Rumus-rumus pencarian data yang memungkinkan kamu mengambil informasi dari tabel lain secara otomatis. Ini adalah kemampuan inti Excel untuk database dan laporan.

VLOOKUP

Mencari data secara vertikal berdasarkan nilai kunci

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP mencari nilai di kolom pertama sebuah tabel, lalu mengambil nilai dari kolom lain di baris yang sama. Huruf V berarti Vertical — pencarian dilakukan ke bawah.

Parameter

Parameter	Keterangan
lookup_value	Nilai yang dicari (bisa sel atau teks langsung)
table_array	Rentang tabel tempat mencari data
col_index_num	Nomor kolom dalam tabel yang nilainya ingin diambil
range_lookup	FALSE untuk pencarian tepat, TRUE untuk pencarian perkiraan

Contoh Soal

Mencari nama produk berdasarkan kode produk

Tabel referensi: A1:C10 berisi Kode (kolom 1), Nama Produk (kolom 2), Harga (kolom 3). Di sel E1 ada kode ‘P003’.

=VLOOKUP(E1, A1:C10, 2, FALSE)

Hasil: ‘Meja Kerja’ — Excel mencari ‘P003’ di kolom A, lalu mengambil nilai di kolom ke-2 dari baris yang sama.

Tips & TrikSelalu gunakan FALSE untuk exact match. Gunakan TRUE hanya untuk tabel konversi seperti tabel nilai/grade yang sudah diurutkan.

HLOOKUP

Mencari data secara horizontal — kebalikan VLOOKUP

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

HLOOKUP mencari nilai di baris pertama tabel, lalu mengambil nilai dari baris tertentu di kolom yang sama. Digunakan saat data disusun horizontal.

Parameter

Parameter	Keterangan
lookup_value	Nilai yang dicari di baris pertama tabel
table_array	Rentang tabel horizontal
row_index_num	Nomor baris yang nilainya ingin diambil
range_lookup	FALSE untuk tepat, TRUE untuk perkiraan

Contoh Soal

Mengambil data kuartal dari tabel horizontal

Baris 1: Q1, Q2, Q3, Q4 (A1:D1). Baris 2: target penjualan tiap kuartal. Ingin mengambil target untuk ‘Q3’.

=HLOOKUP(“Q3”, A1:D2, 2, FALSE)

Hasil: nilai target Q3 dari baris ke-2.

Tips & TrikHLOOKUP jarang digunakan karena data biasanya disusun vertikal. Pertimbangkan XLOOKUP yang lebih fleksibel untuk keduanya.

XLOOKUP

Pengganti modern VLOOKUP — lebih fleksibel dan powerful

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP adalah evolusi dari VLOOKUP dan HLOOKUP. Bisa mencari ke segala arah, mendukung pesan error kustom, dan tidak perlu menentukan nomor kolom secara manual.

Parameter

Parameter	Keterangan
lookup_value	Nilai yang dicari
lookup_array	Rentang kolom/baris tempat mencari
return_array	Rentang yang nilainya ingin dikembalikan
if_not_found	Teks yang tampil jika tidak ditemukan (opsional)

Contoh Soal

Mencari harga produk dengan pesan error yang ramah

Kode produk di A1:A100, harga di C1:C100. Mencari harga untuk kode di sel F1.

=XLOOKUP(F1, A1:A100, C1:C100, “Produk tidak ditemukan”)

Hasil: harga produk jika ditemukan, atau teks ‘Produk tidak ditemukan’ jika kode tidak ada.

Tips & TrikXLOOKUP tersedia di Excel 2021, Microsoft 365, dan Excel untuk web. Jika masih menggunakan Excel 2019 ke bawah, gunakan VLOOKUP atau INDEX+MATCH.

INDEX

Mengambil nilai dari posisi baris dan kolom tertentu dalam tabel

=INDEX(array, row_num, [col_num])

INDEX bekerja seperti koordinat peta — kamu tentukan tabelnya, lalu baris ke berapa dan kolom ke berapa yang ingin diambil nilainya. Sering dipadukan dengan MATCH.

Parameter

Parameter	Keterangan
array	Rentang tabel atau array data
row_num	Nomor baris dalam rentang
col_num	Nomor kolom dalam rentang (opsional)

Contoh Soal

Mengambil nilai spesifik dari matriks data

Data penjualan 5 produk × 4 bulan tersimpan di B2:E6. Ingin mengambil penjualan produk ke-3 di bulan ke-2.

=INDEX(B2:E6, 3, 2)

Hasil: nilai di baris 3, kolom 2 dari rentang B2:E6 — yaitu sel C4.

Tips & TrikINDEX sendirian berguna untuk data statis. Kekuatan sesungguhnya muncul saat dikombinasikan dengan MATCH untuk pencarian dua arah yang dinamis.

MATCH

Menemukan posisi (nomor urut) suatu nilai dalam sebuah daftar

=MATCH(lookup_value, lookup_array, [match_type])

MATCH tidak mengembalikan nilai data, melainkan mengembalikan POSISI dari nilai yang dicari. Hasilnya berupa angka yang bisa digunakan oleh INDEX.

Parameter

Parameter	Keterangan
lookup_value	Nilai yang posisinya ingin ditemukan
lookup_array	Daftar/rentang tempat mencari
match_type	0 = tepat, 1 = kurang dari, -1 = lebih dari

Contoh Soal

Menemukan posisi nama karyawan dalam daftar

Daftar nama karyawan di A1:A20. Ingin tahu di baris ke berapa nama ‘Budi Santoso’ berada.

=MATCH(“Budi Santoso”, A1:A20, 0)

Hasil: 7 — artinya ‘Budi Santoso’ ada di posisi ke-7. Angka ini bisa langsung dipakai sebagai row_num di INDEX.

Tips & TrikSelalu gunakan match_type = 0 untuk pencarian data tepat. Nilai 1 dan -1 hanya untuk data yang sudah diurutkan.

INDEX + MATCH

Kombinasi paling powerful untuk pencarian data dua arah

=INDEX(return_range, MATCH(lookup_value, lookup_array, 0))

INDEX+MATCH menggantikan VLOOKUP dengan lebih banyak keunggulan: bisa mencari ke kiri, tidak perlu menghitung nomor kolom, dan tidak terpengaruh jika kolom ditambah atau dihapus.

Parameter

Parameter	Keterangan
return_range	Kolom yang nilainya ingin diambil
lookup_value	Nilai yang dicari
lookup_array	Kolom tempat mencari nilai

Contoh Soal

Mencari gaji berdasarkan nama karyawan — dengan pencarian ke kiri

Kolom A: gaji, Kolom B: ID karyawan, Kolom C: nama. VLOOKUP tidak bisa mengambil gaji (di sebelah kiri nama), tapi INDEX+MATCH bisa.

=INDEX(A1:A20, MATCH(“Budi Santoso”, C1:C20, 0))

Hasil: gaji Budi Santoso — MATCH menemukan posisi nama di kolom C, INDEX mengambil gaji di kolom A dari posisi yang sama.

Tips & TrikRumus wajib untuk pengguna Excel menengah ke atas. Lebih fleksibel dari VLOOKUP dan bekerja di semua versi Excel.

OFFSET

Mengambil nilai dengan berpindah sekian baris/kolom dari titik awal

=OFFSET(reference, rows, cols, [height], [width])

OFFSET memungkinkan kamu ‘berjalan’ dari sebuah sel ke sel lain dengan menentukan berapa baris dan kolom yang ingin dilompati. Berguna untuk referensi dinamis.

Parameter

Parameter	Keterangan
reference	Sel titik awal
rows	Jumlah baris yang dilompati (negatif = ke atas)
cols	Jumlah kolom yang dilompati (negatif = ke kiri)
height	Tinggi area yang dikembalikan (opsional)
width	Lebar area yang dikembalikan (opsional)

Contoh Soal

Mengambil nilai bulan sebelumnya secara dinamis

Data bulanan di kolom B. Di sel B10 ada data bulan ini. Ingin mengambil data bulan lalu (B9) secara dinamis.

=OFFSET(B10, -1, 0)

Hasil: nilai di B9 — berpindah 1 baris ke atas dari B10. Cocok untuk perbandingan bulan lalu vs bulan ini.

Tips & TrikOFFSET adalah rumus volatile yang dihitung ulang setiap kali sheet berubah. Untuk data besar, INDEX+MATCH bisa lebih efisien.

CHOOSE

Memilih nilai dari daftar berdasarkan nomor indeks

=CHOOSE(index_num, value1, [value2], …)

CHOOSE seperti menu pilihan — berikan nomor 1, 2, 3, dst., maka CHOOSE mengembalikan pilihan yang sesuai dari daftar yang sudah kamu siapkan.

Parameter

Parameter	Keterangan
index_num	Angka yang menentukan pilihan (1=value1, 2=value2, dst.)
value1	Nilai pertama yang bisa dipilih
value2+	Nilai-nilai lainnya (sampai 254 pilihan)

Contoh Soal

Menampilkan nama kuartal berdasarkan nomor

Sel A1 berisi angka kuartal (1, 2, 3, atau 4). Ingin menampilkan nama kuartal secara otomatis.

=CHOOSE(A1, “Kuartal 1”, “Kuartal 2”, “Kuartal 3”, “Kuartal 4”)

Jika A1=2, hasil: ‘Kuartal 2’. Lebih rapi dari IF bertumpuk untuk pilihan yang terbatas.

Tips & TrikCHOOSE juga bisa memilih rentang data: =SUM(CHOOSE(A1, B1:B10, C1:C10)) untuk menjumlahkan kolom berbeda tergantung pilihan.

INDIRECT

Membuat referensi sel dinamis dari sebuah teks

=INDIRECT(ref_text, [a1])

INDIRECT mengubah teks menjadi referensi sel yang nyata. Berguna saat nama sheet atau rentang sel perlu dibuat dinamis berdasarkan isi sel lain.

Parameter

Parameter	Keterangan
ref_text	Teks yang berisi alamat sel atau nama rentang
a1	TRUE untuk gaya A1, FALSE untuk gaya R1C1

Contoh Soal

Menjumlahkan sheet yang dipilih secara dinamis

Ada 4 sheet: Jan, Feb, Mar, Apr. Di sel A1 ketik nama sheet yang ingin dijumlahkan, misalnya ‘Jan’.

=SUM(INDIRECT(A1&”!B1:B30″))

Jika A1=’Jan’, rumus menjadi =SUM(Jan!B1:B30). Ganti isi A1 menjadi ‘Feb’ untuk menjumlahkan sheet Feb.

Tips & TrikINDIRECT juga volatile. Gunakan dengan bijak pada file besar. Pastikan nama sheet yang diketik di sel persis sama dengan nama sheet aslinya.

ADDRESS

Menghasilkan alamat sel (seperti ‘C5’) dari nomor baris dan kolom

=ADDRESS(row_num, col_num, [abs_num], [a1], [sheet_text])

ADDRESS menghasilkan teks berupa alamat sel. Misalnya baris 2, kolom 3 menghasilkan ‘$C$2’. Sering digunakan bersama INDIRECT untuk referensi super dinamis.

Parameter

Parameter	Keterangan
row_num	Nomor baris
col_num	Nomor kolom (1=A, 2=B, dst.)
abs_num	1=$A$1 (default), 2=A$1, 3=$A1, 4=A1 (relatif)

Contoh Soal

Membuat referensi sel dinamis dari angka baris dan kolom

Ingin mendapatkan alamat sel di baris ke-5, kolom ke-3 (yaitu C5), lalu mengambil nilainya.

=INDIRECT(ADDRESS(5, 3))

ADDRESS(5,3) menghasilkan teks ‘$C$5’, lalu INDIRECT mengubahnya menjadi referensi nyata ke sel C5.

Tips & TrikADDRESS berguna saat kamu perlu membangun alamat sel secara programatik untuk laporan yang merujuk ke baris/kolom tertentu berdasarkan input pengguna.