Cara Menggabungkan VLOOKUP() dengan IF() di Excel

Jika Anda mengelola set data besar di Excel, Anda tahu betapa menantangnya mengatur dan menganalisisnya secara efisien. Tetapi saya punya solusinya: Anda dapat menempatkan fungsi VLOOKUP() di dalam pernyataan IF() untuk membuat lookup dinamis berdasarkan kondisi tertentu. Dengan pendekatan ini, Anda dapat melakukan lookup ke tabel yang berbeda berdasarkan suatu kondisi dan juga menangani error dengan lebih rapi.

Secara spesifik, VLOOKUP() menemukan titik data tertentu dalam sebuah tabel, dan pernyataan IF() memungkinkan Anda membuat keputusan bersyarat berdasarkan data tersebut. Keduanya, seperti kita tahu, sangat penting. Dalam artikel ini, kita akan menambah satu alat lagi ke kotak peralatan: Anda akan mempelajari cara memanfaatkan kekuatan kedua fungsi ini bersama-sama untuk melakukan lookup bersyarat, dan kami akan menjelaskannya dengan contoh praktis.

Jawaban Cepat: Cara Menggabungkan VLOOKUP() dan IF()

Untuk membuat lookup bersyarat, mulailah dengan pernyataan IF() dan gunakan VLOOKUP() di dalamnya untuk mengembalikan hasil yang berbeda berdasarkan sebuah kondisi. Kita dapat menggunakan kode berikut untuk memeriksa apakah suatu produk tersedia berdasarkan kuantitasnya. Ikuti langkah-langkah berikut:

• Mulai dengan fungsi IF(): =IF()

• Di dalam fungsi IF(), gunakan VLOOKUP().

=IF(VLOOKUP(C2, $A$2:$B$6, 2, FALSE) > 0, "In Stock", "Out of Stock")

Memahami Pernyataan VLOOKUP() dan IF() di Excel

VLOOKUP() membantu Anda menemukan data di sebuah tabel, sementara pernyataan IF() memungkinkan Anda membuat keputusan berdasarkan data tersebut. Bersama-sama, keduanya membantu menganalisis informasi dalam spreadsheet. Mari kita lihat masing-masing fungsi secara terpisah lalu kita gabungkan.

Apa itu VLOOKUP() di Excel?

VLOOKUP() mencari nilai tertentu di kolom pertama dari suatu rentang dan mengembalikan nilai dari kolom lain pada baris yang sama. Berikut sintaksnya agar Anda dapat menggunakan rumus VLOOKUP() untuk set data besar Anda.

=VLOOKUP(search_key, range, index, is_sorted)

Mari kita uraikan sintaks ini dan memahaminya:

• Search_key adalah nilai yang ingin Anda cari. Ini bisa berupa angka, teks, atau referensi ke sel yang berisi nilai pencarian.

• Range mendefinisikan rentang sel yang berisi data. Kolom pertama dalam rentang ini harus berisi search_key.

• Index adalah nomor kolom dalam rentang dari mana Anda ingin mengambil nilai. Kolom pertama adalah 1, kolom kedua adalah 2, dan seterusnya.

• Is_sorted adalah nilai logika (TRUE atau FALSE). Anda dapat menggunakan TRUE (atau 1) untuk angka dan FALSE (atau 0) untuk teks. Jika TRUE, VLOOKUP() mengasumsikan kolom pertama dari rentang diurutkan secara menaik dan mengembalikan kecocokan terdekat. Jika FALSE, VLOOKUP() mencari kecocokan yang persis. Jika argumen tidak ditentukan, TRUE menjadi default.

Mari pahami dengan contoh. Di sini saya memiliki daftar produk beserta ID dan harganya. Saya ingin menemukan harga untuk produk tertentu berdasarkan namanya.

Daftar produk dengan ID dan harganya. Sumber: Gambar oleh Penulis.

Untuk mulai, saya ingin mencari harga tablet secara khusus. Untuk melakukannya, saya memasukkan nilai search_key (dalam kasus saya, Tablet) untuk menemukan harganya di sel mana pun.

Masukkan nilai untuk menemukan harganya. Sumber: Gambar oleh Penulis.

Lalu, saya memilih sebuah sel dan mengetik =VLOOKUP().

Mengetik rumus VLOOKUP(). Sumber: Gambar oleh Penulis.

Berikutnya, saya memilih sel tempat search_key dimasukkan.

Memilih kolom tempat produk berada. Sumber: Gambar oleh Penulis.

Lalu, saya memilih rentang tabelnya.

Memilih rentang tabel. Sumber: Gambar oleh Penulis.

Menghitung dari kiri, saya memasukkan nomor kolom dari mana saya ingin mengambil data. Di sini, saya ingin mengetahui harga, jadi saya masukkan 2.

Memilih indeks kolom. Sumber: Gambar oleh Penulis.

Kemudian, saya mengetik FALSE untuk mendapatkan kecocokan yang persis.

Mengetik FALSE untuk kecocokan persis. Sumber: Gambar oleh Penulis.

Setelah mengisi semua nilai, saya menekan Enter:

Menggunakan VLOOKUP(), harga produk berhasil didapatkan. Sumber: Gambar oleh Penulis.

Seperti yang terlihat pada gambar, VLOOKUP() berhasil mengambil harga.

Apa itu IF() di Excel?

Pernyataan IF() membandingkan nilai dan memeriksanya terhadap kondisi yang ditentukan. Berikut sintaksnya:

=IF(logical_test, [value_if_true], [value_if_false])

Mari kita lihat bagian-bagian kuncinya untuk memahaminya:

• Logical_test adalah nilai atau ekspresi yang ingin Anda evaluasi sebagai TRUE atau FALSE. Ini adalah kondisi yang ingin Anda periksa.

• Value_if_true mengembalikan nilai jika logical_test bernilai TRUE.

• Value_if_false mengembalikan nilai jika logical_test bernilai FALSE.

Mari ambil contoh. Di sini saya ingin memberikan keterangan kepada siswa berdasarkan nilai mereka. Jadi, saya menyiapkan lembar dengan dua kolom: STUDENTS dan GRADES.

Lembar berisi daftar siswa dengan nilai mereka. Sumber: Gambar oleh Penulis.

Saya memilih sebuah sel dan mengetik =IF(). Tujuan saya adalah memeriksa apakah total angka lebih dari 50 lalu mencetak Excellent, atau jika nilainya kurang dari 50, saya mencetak Bad. Setelah menentukan kondisi, saya menekan Enter untuk mendapatkan hasil.

Memberikan keterangan kepada semua siswa menggunakan pernyataan IF. Sumber: Gambar oleh Penulis.

Kemudian, saya menyalin rumus hingga sel terakhir yang terisi dengan menyeretnya. Anda dapat melihat hasilnya. Saya telah memberikan keterangan kepada semua siswa hanya dengan satu rumus.

Cara Menggabungkan VLOOKUP() dengan IF() di Excel

Mari kita lihat contoh praktis untuk memahami bagaimana VLOOKUP() bekerja bersama IF().

Lookup bersyarat

Untuk membuat lookup bersyarat, seperti yang telah disebutkan, mulailah dengan pernyataan IF() dan gunakan VLOOKUP() di dalamnya untuk mengembalikan hasil yang berbeda berdasarkan suatu kondisi.

1. Mulai dengan fungsi IF(): =IF().

2. Di dalam fungsi IF(), gunakan VLOOKUP().

Mari coba contoh baru: Di sini saya memiliki daftar pesanan produk dengan waktu pesanannya masing-masing. Saya ingin melihat apakah suatu produk tertentu dipesan sebelum pukul 12.00 siang.

Tabel berisi daftar produk beserta ID pesanan dan waktunya. Sumber: Gambar oleh Penulis.

Pertama, saya memilih sebuah kolom dan memasukkan rumus berikut:

=IF(VLOOKUP(A3, A2:C5,3, FALSE) < TIME(12, 0, 0), "Ordered Before Noon", "Ordered After Noon")

Di sini, fungsi VLOOKUP() mencari Banana di kolom A dan mengembalikan waktu pesanan yang sesuai dari kolom B.

Pernyataan IF() memeriksa apakah waktu pesanan kurang dari 12. Jika ya, ia mengembalikan Ordered Before Noon. Jika tidak, ia mengembalikan Ordered After Noon.

Menghitung waktu pengiriman dengan menggabungkan rumus IF() dan VLOOKUP(). Sumber: Gambar oleh Penulis.

Anda dapat melihat betapa mudahnya saya melacak pengiriman produk yang diinginkan menggunakan lookup bersyarat.

Penanganan error

Untuk menangani error, mulailah dengan pernyataan IF() dan gunakan ISNA() bersama VLOOKUP() di dalamnya untuk memeriksa error. Misalnya, untuk menampilkan pesan khusus jika suatu produk tidak ditemukan:

1. Mulai dengan fungsi IF(): =IF().

2. Di dalam fungsi IF(), gunakan ISNA() bersama VLOOKUP() untuk memeriksa error.

Mari perjelas dengan contoh. Misalkan saya memiliki tabel harga produk dan saya ingin menampilkan pesan khusus setiap kali suatu produk tidak ditemukan.

Tabel berisi daftar produk dan harganya. Sumber: Gambar oleh penulis.

Untuk melakukannya, saya memilih sebuah sel dan memasukkan rumus berikut untuk menemukan harganya:

=(VLOOKUP(B7, $A$2:$B$5, 2, FALSE)

Lalu, saya menggabungkannya dengan ISNA() dan IF() untuk menangani error apa pun.

=IF(ISNA(VLOOKUP(B7, $A$2:$B$5, 2, FALSE)), "Product Not Found", (VLOOKUP(B7, $A$2:$B$5, 2, FALSE)))

Menangani error dengan menggabungkan rumus IF() dan ISNA(). Sumber: Gambar oleh penulis.

Di sini, ISNA() memeriksa apakah fungsi VLOOKUP() mengembalikan error #N/A, yang akan terjadi jika tidak tersedia. Dengan kata lain, jika ISNA() bernilai TRUE, pernyataan IF() mengembalikan Product Not Found; jika tidak, ia mengembalikan harga dari VLOOKUP().

Pengindeksan kolom dinamis

Untuk memilih indeks kolom secara dinamis untuk VLOOKUP(), mulailah dengan pernyataan IF() dan gunakan VLOOKUP() di dalamnya untuk memilih kolom yang berbeda berdasarkan suatu kondisi. Misalnya, untuk melakukan lookup ke kolom yang berbeda berdasarkan nilai ambang batas:

1. Mulai dengan fungsi IF(): =IF().

2. Di dalam fungsi IF(), gunakan VLOOKUP() untuk memeriksa ambang batas dan memilih kolom.

Di sini, saya memiliki tabel produk dengan kolom A berisi nama produk, kolom B berisi harga produk, dan kolom C berisi jumlah stok. Saya ingin melihat harga produk atau jumlah stok berdasarkan apakah harga di atas atau di bawah ambang batas tertentu, misalnya 50.

Tabel berisi daftar produk, ID, dan harga. Sumber: Gambar oleh Penulis.

Saya memilih sebuah sel dan memasukkan rumus berikut:

=IF(VLOOKUP(B9, $B$9:$D$14, 2, FALSE) > 50, VLOOKUP(B9,$B$9:$D$14, 3, FALSE), VLOOKUP(B9,$B$9:$D$14, 2, FALSE))

Menerapkan IF dengan VLOOKUP() bersarang. Sumber: Gambar oleh Penulis.

Begini cara kerja rumusnya:

• (VLOOKUP(B9, $B$9:$D$14, 2, FALSE) melakukan lookup produk di B9 dari kolom A dan mengembalikan harga dari kolom C.

• Pernyataan IF() memeriksa apakah harganya lebih besar dari $50.

• Jika benar, kode mengembalikan jumlah stok: VLOOKUP(B9,$B$9:$D$14, 3, FALSE).

• Jika salah, kode mengembalikan ID produk: VLOOKUP(B9,$B$9:$D$14, 2, FALSE)).

Jika Anda bekerja dengan set data yang sangat besar, Anda dapat menyalin rumus dengan menyeretnya hingga sel terakhir yang terisi.

Menampilkan hasil. Sumber: Gambar oleh Penulis.

Dan selesai. Seperti yang terlihat, kita dapat mengambil informasi yang diinginkan berdasarkan kondisi tertentu menggunakan rumus gabungan.

Teknik Lanjutan Menggunakan VLOOKUP() dan IF()

Sekarang Anda telah memahami dasar menggabungkan pernyataan IF() dengan VLOOKUP(), mari pelajari beberapa teknik lanjutan dari contoh yang saya coba sendiri.

Menggabungkan beberapa kriteria

Jika Anda melakukan lookup data berdasarkan beberapa kriteria, Anda dapat menggabungkan beberapa fungsi VLOOKUP() dalam sebuah pernyataan IF() untuk memeriksa apakah semua kondisi terpenuhi.

Di sini saya memiliki tabel dengan data pembelian pelanggan dan status keanggotaan. Dan saya ingin memeriksa apakah seorang pelanggan memenuhi syarat untuk program loyalitas, yang mensyaratkan setidaknya 500 di TOTAL PURCHASE ($) dan Gold sebagai MEMBERSHIP STATUS.

Tabel berisi detail pelanggan. Sumber: Gambar oleh Penulis.

Saya membuat kolom lain bernama Eligibility, yang menampilkan kriteria kelayakan. Lalu, saya mengetik rumus berikut dan menekan Enter:

=IF(AND(VLOOKUP(B2, $B$2:$D$11, 2, FALSE) >= 500, VLOOKUP(B2, $B$2:$D$11, 3, FALSE) = "Gold"), "Eligible", "Not Eligible")

Memeriksa kriteria kelayakan pelanggan dengan menggabungkan beberapa kriteria. Sumber: Gambar oleh Penulis.

Begini cara kerja rumus ini:

• VLOOKUP(B2, $B$2:$D$11, 2, FALSE) >= 500 memeriksa apakah total pembelian pelanggan setidaknya $500.

• VLOOKUP(B2, $B$2:$D$11, 3, FALSE) = "Gold") memeriksa apakah status keanggotaan John adalah Gold.

• AND menggabungkan kondisi untuk memastikan keduanya benar.

• IF() mengembalikan Eligible ketika kedua kondisi benar. Jika tidak, mengembalikan Not Eligible.

Menggunakan VLOOKUP() dengan IF() untuk perhitungan

Anda dapat menggunakan VLOOKUP() untuk menemukan nilai lalu menerapkan pernyataan IF() untuk melakukan perhitungan berdasarkan nilai tersebut.

Di sini saya menyiapkan lembar dengan produk dan harganya. Lalu, saya ingin menerapkan diskon 10% untuk produk di atas $100.

Tabel berisi daftar produk dengan harganya. Sumber: Gambar oleh Penulis.

Jadi, saya membuat kolom lain bernama DISCOUNT dan saya menulis rumus berikut di kolom tersebut untuk menampilkan diskon.

=IF(VLOOKUP(A2, $A$2:$B$10, 2, FALSE) > 100, VLOOKUP(A2, $A$2:$B$10, 2, FALSE) * 0.9, VLOOKUP(A2, $A$2:$B$10, 2, FALSE))

Sekarang, begini cara kerja rumus ini:

• VLOOKUP(A2, $A$2:$B$10, 2, FALSE) mengambil harga produk yaitu 56.

• =IF(VLOOKUP(A2, $A$2:$B$10, 2, FALSE) > 100, ..., ...) memeriksa apakah harga produk lebih besar dari 100.

• Karena kondisinya benar, VLOOKUP(A2, $A$2:$B$10, 2, FALSE) * 0.9 menerapkan diskon 10%.

• (VLOOKUP(A2, $A$2:$B$10, 2, FALSE)) mencetak harga asli produk di mana kondisi diskon tidak terpenuhi.

Begitulah saya mendapatkan hasil akhir rumus untuk produk tersebut, yaitu 135. Untuk mendapatkan hasil yang diinginkan untuk semua produk, Anda dapat menyeret rumus ke bawah dan menyalinnya hingga sel terakhir yang terisi.

Menggunakan VLOOKUP() dengan IF untuk menghitung diskon pada produk dengan harga lebih dari 100. Sumber: Gambar oleh Penulis.

Menangani set data besar

Anda juga dapat menggabungkan VLOOKUP() dengan fungsi IF() untuk mempermudah pengambilan data dan penanganan error saat bekerja dengan set data yang besar.

Ini contoh terakhir kita: Saya memiliki lembar berisi informasi karyawan, dan saya ingin mengambil departemen seorang karyawan serta menangani kasus ketika ID karyawan tidak ada.

Tabel berisi daftar karyawan dengan ID dan Departemennya. Sumber: Gambar oleh Penulis.

Jadi, saya memilih sebuah sel dan mengetik rumus berikut:

=IFERROR(VLOOKUP(E3, $A$2:$C$18, 3, FALSE), "Not Found")

Setelah menekan Enter, saya menyeret rumus untuk menyalinnya ke sel lainnya juga.

Mengambil departemen karyawan melalui ID dan menangani error jika karyawan tidak ditemukan. Sumber: Gambar oleh Penulis.

Dan Anda dapat melihat hasilnya di atas. Begini cara kerja rumus ini:

• VLOOKUP(E3, $A$2:$C$18, 3, FALSE) mengambil departemen karyawan.

• IFERROR menangani error. Alih-alih menampilkan pesan error (#N/A), ia akan menampilkan pesan yang ramah dan disesuaikan.

Singkatnya, Anda tidak perlu lagi mengutak-atik lembar kerja. Karena ketika Anda menggabungkan pernyataan VLOOKUP() dan IF(), Anda dapat menangani set data yang terbesar sekalipun dengan mudah di Excel.

Pemikiran Akhir

Dengan menggabungkan VLOOKUP() dengan pernyataan IF(), Anda dapat membuat spreadsheet yang lebih akurat dan tahan terhadap error. Pastikan untuk bereksperimen dengan contoh yang saya bagikan untuk melihat bagaimana teknik-teknik ini dapat menyederhanakan tugas manajemen data dan meningkatkan keterampilan Excel Anda.

Jika Anda ingin meningkatkan keterampilan, lihat kursus Introduction to Excel kami dan kemudian lanjutkan ke jalur keahlian Excel Fundamentals untuk menguasai dasar-dasarnya. Setelah nyaman dengan fungsi-fungsi ini, tingkatkan kemampuan analitis Anda dengan kursus Data Analysis in Excel kami. Namun jika Anda lebih berkutat di bidang keuangan, lihat kursus Financial Modeling in Excel untuk mengintegrasikan wawasan finansial.

Selain itu, kursus Data Preparation in Excel kami akan membantu Anda merampingkan proses pembersihan data, dan kursus Data Visualization in Excel akan membantu Anda menyajikan data dengan cara yang menarik.