Saat pertama kali mempelajari Excel, Anda mungkin menjumpai beberapa fungsi yang penting untuk mereferensikan dan mencari data. Dalam banyak kasus, ini dilakukan menggunakan fungsi Lookup di Excel.

Dalam artikel ini, kami akan membahas berbagai fungsi terkait pencarian informasi di Excel dan menjelaskan cara menggunakannya melalui contoh. Jika Anda ingin mengasah keterampilan Excel di semua area, pastikan untuk melihat jalur keterampilan Excel Fundamentals kami.

Apa itu Fungsi Lookup di Excel?

Fungsi lookup di Excel memungkinkan pengguna mencari data tertentu dalam suatu dataset dan mengembalikan informasi yang sesuai dari kolom atau baris lain. Fungsi ini penting dalam analisis data untuk referensi, silang referensi, dan mengekstraksi wawasan bermakna dari dataset yang besar atau kompleks.

Mengapa Fungsi Lookup Penting? 

Fungsi lookup sangat berguna untuk pengambilan data yang efisien. Pengambilan data yang efisien krusial bagi produktivitas, terutama saat menangani spreadsheet besar atau mengotomatisasi alur kerja pelaporan. Fungsi lookup mengurangi pencarian manual, memastikan konsistensi, dan memungkinkan solusi data yang terukur.

Sebagai contoh, bayangkan Anda memiliki tabel penjualan dengan ribuan baris dan kolom, dan Anda perlu mencari total penjualan untuk produk atau wilayah tertentu. 

Tanpa fungsi lookup, Anda harus mencari secara manual setiap baris untuk menemukan informasi yang relevan, yang memakan waktu dan rentan terhadap kesalahan manusia.

Pelajari lebih lanjut tentang fungsi-fungsi ini di kursus Data Preparation in Excel kami.

Jenis-Jenis Fungsi Lookup di Excel

Excel menawarkan beberapa fungsi lookup, masing-masing dengan kekuatan unik yang disesuaikan untuk berbagai kasus penggunaan. Memahami perbedaan di antara fungsi-fungsi ini dapat sangat meningkatkan kemampuan Anda dalam mengelola dan menganalisis data secara efisien.

Mari kita lihat beberapa jenis fungsi yang umum di bawah ini.

1. LOOKUP

Sumber: Microsoft

Fungsi lookup asli adalah yang paling dasar dan bekerja dengan array yang diurutkan. Fungsi ini mencari nilai dalam satu baris atau kolom dan mengembalikan nilai dari posisi yang sama di baris atau kolom lain. Hanya mendukung pencocokan perkiraan.

2. VLOOKUP (Vertical Lookup)

Sumber: Microsoft

VLOOKUP adalah salah satu fungsi yang paling banyak digunakan di workbook Excel lama. Fungsi ini mencari nilai di kolom pertama tabel dan mengembalikan nilai pada baris yang sama dari kolom yang ditentukan. 

Fungsi ini mendukung pencocokan tepat maupun perkiraan, tetapi memiliki keterbatasan, seperti tidak dapat mencari ke kiri dan masalah kinerja pada dataset besar.

3. HLOOKUP (Horizontal Lookup)

Sumber: Tutorial HLOOKUP

HLOOKUP bekerja mirip dengan VLOOKUP tetapi mencari nilai di baris pertama tabel dan mengembalikan nilai dari baris yang ditentukan. Ini berguna saat data Anda disusun secara horizontal, bukan vertikal.

Anda sebaiknya mempertimbangkan menggunakan HLOOKUP dibanding VLOOKUP saat data Anda disusun dalam baris, bukan kolom.

4. INDEX dan MATCH

Metode INDEX dan MATCH menampilkan kombinasi kuat dari dua fungsi:

• INDEX mengembalikan nilai sel berdasarkan nomor baris dan kolom.
• MATCH mengembalikan posisi suatu nilai dalam baris atau kolom. Digunakan bersama, keduanya menawarkan fleksibilitas lebih dibanding VLOOKUP dan lebih disukai dalam skenario kompleks karena mendukung pencarian kiri-ke-kanan maupun kanan-ke-kiri serta tidak memerlukan data yang diurutkan.

5. XLOOKUP

Diperkenalkan di Excel 2019 dan Microsoft 365, XLOOKUP adalah pengganti modern untuk VLOOKUP dan HLOOKUP. Fungsi ini memungkinkan pencocokan tepat atau perkiraan, bekerja secara horizontal maupun vertikal, dan mendukung penanganan galat, menjadikannya fungsi lookup paling serbaguna hingga saat ini.

Fungsi LOOKUP

Mari mulai dengan mengeksplorasi fungsi LOOKUP di Excel, membahas sintaks dan parameter, bentuk vektor, serta contoh penerapannya. 

Sintaks dan parameter

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Berikut beberapa penjelasan:

• lookup_value: Nilai yang akan dicari.
• lookup_vector: Baris atau kolom tunggal tempat pencarian dilakukan.
• result_vector (opsional): Rentang yang berisi nilai yang akan dikembalikan.

Menggunakan bentuk vektor

Bentuk vektor adalah yang paling umum digunakan dan mengharuskan lookup_vector dan result_vector memiliki ukuran yang sama.

Penjelasan dengan contoh

=LOOKUP(90, A2:A10, B2:B10)

Fungsi ini mencari nilai terbesar yang kurang dari atau sama dengan 90 di A2:A10, lalu mengembalikan nilai yang sesuai dari B2:B10.

Dalam kasus ini, hanya baris 5 yang cocok dengan parameter yang kita tetapkan. Oleh karena itu, keluaran fungsi akan menghasilkan hasil “Medium-High”.

Keterbatasan fungsi LOOKUP

• Persyaratan data diurutkan: lookup_vector harus diurutkan menaik.
• Perilaku kecocokan perkiraan: Tidak mendukung kecocokan tepat—mengembalikan nilai terdekat yang kurang dari atau sama dengan lookup_value.

Fungsi VLOOKUP

Berikutnya adalah fungsi VLOOKUP, yang merupakan salah satu fungsi yang paling umum digunakan di versi Excel lama. 

Sintaks dan parameter

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

• lookup_value: Nilai yang dicari di kolom pertama table_array.
• table_array: Rentang tabel tempat pencarian dilakukan.
• col_index_num: Nomor kolom tempat nilai akan dikembalikan.
• range_lookup: Opsional; TRUE untuk perkiraan, FALSE untuk tepat.

Cara menggunakan VLOOKUP: Panduan langkah demi langkah dengan contoh

Sekarang, mari kita lihat secara singkat bagaimana fungsi ini bekerja menggunakan sebuah contoh.

Kita akan menggunakan fungsi berikut pada dataset sederhana.

=VLOOKUP("John", A2:C10, 3, FALSE)

Fungsi ini akan mencari setiap entri milik John dan menampilkan jumlah gajinya.

Mencari "John" di kolom A dan mengembalikan nilai dari kolom ketiga (kolom C) pada baris yang sama.

Sesuai perkiraan, keluaran fungsi VLOOKUP adalah 6000, yang sesuai dengan baris 6, yaitu nilai yang kita cari karena milik John.

Kecocokan tepat vs perkiraan

Kecocokan tepat adalah jenis pencocokan yang paling sering digunakan di VLOOKUP. Artinya, fungsi hanya akan mengembalikan hasil jika menemukan kecocokan persis untuk nilai pencarian di kolom pertama dari rentang yang ditentukan.

Sebagai contoh, jika kita menggunakan kecocokan tepat saat mencari gaji John, fungsi VLOOKUP hanya akan mengembalikan hasil jika ada baris dengan "John" di kolom pertama dan gajinya di kolom kedua.

Di sisi lain, kecocokan perkiraan memberikan fleksibilitas dalam menemukan nilai. Ini berguna saat bekerja dengan data numerik atau data yang mungkin memiliki sedikit variasi (misalnya karena pembulatan atau pemformatan).

Ringkasnya:

• Tepat (FALSE): Menemukan kecocokan persis.
• Perkiraan (TRUE): Memerlukan data yang diurutkan dan menemukan kecocokan terdekat.

Kesalahan umum dan pemecahan masalah

Saat pertama kali bekerja dengan VLOOKUP, beberapa kesalahan umum dapat muncul. 

Menangani galat #N/A

Salah satu kesalahan paling umum saat menggunakan VLOOKUP adalah #N/A, yang berarti "Not Available" (Tidak Tersedia). Ini biasanya terjadi ketika nilai yang dicari tidak dapat ditemukan dalam rentang yang ditentukan.

Ini juga dapat disebabkan oleh lookup_value yang hilang atau data yang tidak diurutkan saat menggunakan kecocokan perkiraan.

Untuk mengatasi galat ini, periksa kembali bahwa nilai pencarian Anda dieja dengan benar dan ada di kolom pertama dari rentang yang Anda cari. Juga, pastikan data Anda diurutkan menaik jika menggunakan kecocokan perkiraan.

Masalah lain yang mungkin terjadi adalah ketidaksesuaian tipe data. Misalnya, jika satu kolom berisi angka yang direpresentasikan sebagai teks dan kolom lain berisi angka sebenarnya, VLOOKUP mungkin tidak berfungsi dengan baik.

Masalah nomor indeks kolom

Kesalahan lain yang mungkin terjadi adalah galat #REF!. Jika col_index_num melebihi jumlah kolom dalam table_array, maka akan terjadi galat #REF!.

Fungsi HLOOKUP

Untuk mencari nilai di baris teratas tabel dan mengembalikan nilai dari baris tertentu pada kolom yang sama, fungsi HLOOKUP sering menjadi pilihan terbaik. Mari kita lihat cara kerjanya.

Sintaks dan parameter

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

• lookup_value: Nilai yang dicari di baris pertama.
• table_array: Tabel tempat pencarian dilakukan.
• row_index_num: Nomor baris tempat nilai akan dikembalikan.
• range_lookup: TRUE (perkiraan) atau FALSE (tepat).

Cara menggunakan HLOOKUP: Panduan langkah demi langkah dengan contoh

=HLOOKUP("Q1", A1:D3, 2, FALSE)

Fungsi HLOOKUP di atas mencari "Q1" di baris 1 dan mengembalikan nilai dari baris 2 pada kolom yang sama. 

Mari jalankan fungsinya dan lihat hasilnya.

Seperti yang terlihat di atas, fungsi seharusnya mengembalikan 1200. Karena fungsi mencari nilai yang ditemukan di baris 2 berdasarkan nilai yang ditentukan di baris 1, 1200 adalah keluaran yang benar yang kita cari.

Fungsi INDEX dan MATCH

Selanjutnya, mari lihat bagaimana fungsi INDEX dan MATCH di Excel digabungkan untuk mencari nilai. 

Sintaks dan contoh penggunaan

=INDEX(array, row_num, [column_num])

Fungsi INDEX mengembalikan nilai pada baris dan kolom tertentu dalam suatu rentang.

=MATCH(lookup_value, lookup_array, [match_type])

Fungsi MATCH mengembalikan posisi relatif lookup_value dalam lookup_array.

Menggunakan INDEX dan MATCH bersama

Saat fungsi INDEX dan MATCH digunakan bersama, keduanya memberikan alat yang kuat untuk mencari nilai dalam tabel atau rentang. Kombinasi ini memungkinkan Anda menentukan lokasi pasti nilai yang ingin dikembalikan, alih-alih harus memasukkan nomor baris dan kolom secara manual.

Mari kita lihat sebuah contoh:

=INDEX(B2:B10, MATCH("John", A2:A10, 0))

Kombinasi ini menemukan baris tempat "John" muncul di A2:A10, lalu mengembalikan nilai yang sesuai dari B2:B10.

Mari kita lihat keluaran dari fungsi tersebut.

Ketika kedua fungsi digunakan bersama, hasilnya setara dengan menggunakan fungsi VLOOKUP. Namun, menggunakan INDEX dan MATCH memungkinkan fleksibilitas lebih dalam memilih kolom mana yang akan dikembalikan, serta dapat mencari di beberapa kolom.

Fungsi XLOOKUP (Excel 2019 dan yang Lebih Baru)

Terakhir, mari lihat salah satu fungsi lookup Excel terbaru, XLOOKUP.

Sintaks dan parameter

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

Contoh praktis

Kita akan menerapkan fungsi ini pada dataset dan contoh sederhana.

=XLOOKUP("John", A2:A10, B2:B10, "Not Found")

Fungsi di atas mencari "John" di A2:A10 dan mengembalikan nilai dari B2:B10; mengembalikan "Not Found" jika tidak ditemukan.

Melihat keluaran dari rumus di atas, terlihat bahwa hasil “Not Found” ditampilkan. Ini berarti tidak ada entri dengan nama karyawan “John”.

Perbandingan: LOOKUP vs. VLOOKUP vs. XLOOKUP

Mari satukan semua fungsi ini untuk melihat perbandingannya dan meninjau perbedaannya.

Kesimpulan

Fungsi lookup adalah alat penting di Excel untuk pengambilan data yang efisien. Meskipun LOOKUP, VLOOKUP, dan HLOOKUP memiliki kegunaannya, INDEX/MATCH dan XLOOKUP memberikan fleksibilitas dan kekuatan lebih. Memahami kapan dan bagaimana menggunakan masing-masing dapat secara signifikan meningkatkan efisiensi spreadsheet dan kemampuan analitis Anda.

Jika Anda mencari lebih banyak sumber tentang Excel, kursus Data Preparation in Excel kami adalah tempat yang tepat untuk memulai. Untuk menguasai dasar-dasar Excel, jalur keterampilan Excel Fundamentals kami adalah tempat terbaik untuk memulai. Anda juga dapat melihat tutorial Excel kami tentang Rumus Excel Dasar atau Vlookup dari sheet lain di Excel juga.